Skip to main content

Logical Replication

Setup

Enable Write-Ahead Log (WAL)

The wal_level found in the postgresql.conf configuration file, determines how information is written to the Write-Ahead Log (WAL).

By default, this is set to replica. To enable Logical Replication your wal_level must be set to logical. This will increase the WAL volume for tables configured for REPLICA IDENTITY FULL.

Check what your PostgreSQL database wal_level value is set to by using the following command:

postgres SHOW wal_level;

If you need to change this value, a restart of the server will be required.

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 at the database system level:

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.

Data Record Format

Data records from PostgreSQL using Logical Replication events will take on the following format:

{
"schema": {
"type": "struct",
"fields": [
{
"type": "struct",
"fields": [
{
"type": "int32",
"optional": false,
"field": "id"
},
{
"type": "string",
"optional": false,
"field": "category"
},
{
"type": "string",
"optional": false,
"field": "product_type"
},
{
"type": "string",
"optional": true,
"field": "product_name"
},
{
"type": "boolean",
"optional": false,
"field": "stock"
},
{
"type": "int32",
"optional": false,
"field": "product_id"
},
{
"type": "string",
"optional": false,
"field": "shipping_address"
},
{
"type": "string",
"optional": false,
"field": "customer_email"
}
],
"optional": true,
"name": "resource.public.orders.Value",
"field": "before"
},
{
"type": "struct",
"fields": [
{
"type": "int32",
"optional": false,
"field": "id"
},
{
"type": "string",
"optional": false,
"field": "category"
},
{
"type": "string",
"optional": false,
"field": "product_type"
},
{
"type": "string",
"optional": true,
"field": "product_name"
},
{
"type": "boolean",
"optional": false,
"field": "stock"
},
{
"type": "int32",
"optional": false,
"field": "product_id"
},
{
"type": "string",
"optional": false,
"field": "shipping_address"
},
{
"type": "string",
"optional": false,
"field": "customer_email"
}
],
"optional": true,
"name": "resource.public.orders.Value",
"field": "after"
},
{
"type": "struct",
"fields": [
{
"type": "string",
"optional": false,
"field": "version"
},
{
"type": "string",
"optional": false,
"field": "connector"
},
{
"type": "string",
"optional": false,
"field": "name"
},
{
"type": "int64",
"optional": false,
"field": "ts_ms"
},
{
"type": "string",
"optional": true,
"name": "io.debezium.data.Enum",
"version": 1,
"parameters": {
"allowed": "true,last,false"
},
"default": "false",
"field": "snapshot"
},
{
"type": "string",
"optional": false,
"field": "db"
},
{
"type": "string",
"optional": false,
"field": "schema"
},
{
"type": "string",
"optional": false,
"field": "table"
},
{
"type": "int64",
"optional": true,
"field": "txId"
},
{
"type": "int64",
"optional": true,
"field": "lsn"
},
{
"type": "int64",
"optional": true,
"field": "xmin"
}
],
"optional": false,
"name": "io.debezium.connector.postgresql.Source",
"field": "source"
},
{
"type": "string",
"optional": false,
"field": "op"
},
{
"type": "int64",
"optional": true,
"field": "ts_ms"
},
{
"type": "struct",
"fields": [
{
"type": "string",
"optional": false,
"field": "id"
},
{
"type": "int64",
"optional": false,
"field": "total_order"
},
{
"type": "int64",
"optional": false,
"field": "data_collection_order"
}
],
"optional": true,
"field": "transaction"
}
],
"optional": false,
"name": "resource.public.orders.Envelope"
},
"payload": {
"before": null,
"after": {
"id": 1,
"category": "camping",
"product_type": "sleeping-bag",
"product_name": "Forte 35 Sleeping Bag — Womens",
"stock": true,
"product_id": 361632,
"shipping_address": "9718 East Virginia Avenue Silver Spring, MD 20901",
"customer_email": "[email protected]"
},
"source": {
"version": "1.2.5.Final",
"connector": "postgresql",
"name": "resource",
"ts_ms": 1650418145246,
"snapshot": "true",
"db": "database",
"schema": "public",
"table": "orders",
"txId": 700,
"lsn": 7851737904,
"xmin": null
},
"op": "r",
"ts_ms": 1650418145248,
"transaction": null
}
}

Pros and Cons

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.