Skip to main content
Postgres Logo

PostgreSQL is a powerful, open-source object-relational database system. The Postgres connectors allows you to capture inserts, updates, and deletes from a table and stream to a destination.

Adding Resource#

Meroxa supports two methods for capturing data changes from PostgreSQL:

See pros and cons of the connection methods.

Using the CLI, you can run the following command to add a new resource:

meroxa resource add postgres --type postgres -u postgres://$PG_USER:$PG_PASS@$PG_URL:$PG_PORT/$PG_DB

The -u option requires a valid access URL.

If desired, the --metadata '{"logical_replication":"true"}' configuration enables logical replication.

Setup#

Environments#

The following PostgreSQL environments are supported:

Meroxa has been tested against the following PostgreSQL versions:

  • 9.6.x
  • 10.x
  • 11.x
  • 12.x

Networking#

To add as a resource, the PostgreSQL instance needs to be accessible by Meroxa.

If your instance is publically accessible, there is nothing to do. If not, you may:

Access URL#

The access URL contains the credentials needed to access your instance:

postgres://$PG_USER:$PG_PASS@$PG_URL:$PG_PORT/$PG_DB

In the example above, replace following variables with valid credentials from your Postgres environment:

  • $PG_USER - PostgreSQL Username
  • $PG_PASS - PostgreSQL Password
  • $PG_URL - PostgreSQL URL
  • $PG_DB - PostgreSQL Database Name
  • $PG_PORT - PostgreSQL Port (e.g., 5432).

Logical Replication vs. Polling#

Here are the pros and cons of Logical Replication vs Polling PostgreSQL connections:

Logical Replication:#

Pros

  • Logical replication is a more efficient means of capturing changes with the least amount of overhead to your database. This method is used by PostgreSQL itself for logical replication.
  • Unlike the Polling method, Logical Replication can capture DELETE operations.

Cons

  • Read Replicas do not support Logical Replication.
  • Requires a database reboot to enable.
  • Specific PostgreSQL environments (e.g., Heroku) do not support Logical Replication.

Polling#

Pros

  • Supports Read Replicas.
  • Allows you to poll using a custom query. This give you the ability do complex joins, select subsets of columns in a table, or even filter data. 

Cons

  • Can not detect DELETE operations.
  • Due to repeated polling, this may cause performance overhead to your database.