Skip to main content

Logical Replication

Logical replication works by leveraging PostgreSQL's logical decoding. The Meroxa Connector uses this to consume the changes from PostgreSQL's transaction log and produces a Data Record for every INSERT, UPDATE, and DELETE operation.

To add a new resource, see Add Postgres Resource.

Setup#

Permissions#

In order for logical replication to be used, the configured Resource credentials must have the appropriate permissions to create a replication slot. To create a new user with replication enabled, you can run the following:

CREATE USER meroxa WITH REPLICATION ENCRYPTED PASSWORD 'secret`123';

If the configured $PG_USER does not have the ability to create a replication slot, Meroxa will automatically fall back to Polling for changes.

Enable Replica Identity#

REPLICA IDENTITY is a PostgreSQL-specific table-level setting that determines the amount of information for UPDATE and DELETE events.

Meroxa will still capture INSERT, UPDATE and DELETE events, but will only see the resulting change after, but not the data before the operation was made.

To capture full changes, run the following:

ALTER TABLE "TABLE_NAME" REPLICA IDENTITY FULL;

For more information, see Replica Identity.

Source Configuration#

Creating Connectors#

The Input for this connector is an Postgres Table.

For example, to create a connector using the CLI, you may use the following command:

meroxa connector create from-pg --from postgres --input $TABLE

The format of the Postgres Table is public.TableName. You must include the table schema name.

Data Record#

On every update, the Data Record takes the following format:

{
"before": {
"id": 11,
"email": "ec@example.com",
"name": "Nell Abbott",
"birthday": "12/21/1959",
"createdAt": 1618255874536,
"updatedAt": 1618255874537
},
"after": {
"id": 11,
"email": "nell-abbott@example.com",
"name": "Nell Abbott",
"birthday": "12/21/1959",
"createdAt": 1618255874536,
"updatedAt": 1618255874537
},
"source": {
"version": "1.2.5.Final",
"connector": "postgresql",
"name": "resource-217-139903",
"ts_ms": 1618255875129,
"snapshot": "false",
"db": "my_database",
"schema": "public",
"table": "User",
"txId": 8355,
"lsn": 478419097256,
"xmin": null
},
"op": "u",
"ts_ms": 1618255875392,
"transaction": null
}