PostgreSQL logical replication (publication / subscription)

Table-level logical replication between two PostgreSQL clusters: wal_level=logical, replica identity checks, publication and subscription, initial sync, monitoring, conflict handling, and manual sequence synchronization. Also covers the final cutover steps that keep downtime to seconds for cross-version migrations (major upgrade, server replacement).

Logical replication is not an HA or backup method: for an exact copy of the whole cluster and failover use streaming replication or Patroni, and for backups pgBackRest. An in-place major version upgrade on the same server (pg_upgrade) is in pg-major-upgrade.

Topology: 2 servers: publisher + subscriber; the publisher can be a single server, a streaming replication setup or a Patroni cluster

Scenarios

• Continuous replication (reporting, integration, partial copy) — Selected tables flow continuously to another cluster (reporting server, data warehouse feed, integration between services). The subscriber stays writable.
• Migration: new server / new major version + cutover — Move to a new server or a new major version with seconds of downtime: initial copy and continuous sync, then a short write freeze and the cutover.

Before you start

• PostgreSQL … is running on the publisher and the … database exists.
• The Patroni cluster … is healthy and … is currently the Leader; hot_standby_feedback = on on the replicas (pg-patroni default).
• Standby … is connected to the primary through a physical replication slot (primary_slot_name = …, the slot option in pg-streaming). Slot synchronization does not work without this slot.
• PostgreSQL … has been installed on the subscriber with pg-install and is running; the … builds of the extensions used on the publisher (PostGIS etc.) are installed.
• The subscriber can reach the publisher's …HAProxy/VIP address (…:…)…port …… (firewall / security group).
• What is the current wal_level on the publisher? If it is not logical, changing it requires a restart: plan a maintenance window.
• The tables to be published have a primary key or a suitable unique index. Otherwise UPDATE/DELETE fail on the publisher: add a PK to the listed tables or define a replica identity (step below).
• Logical replication does not replicate DDL (CREATE/ALTER TABLE), sequence values or large objects. While replication runs, schema changes will be applied manually, first on the subscriber and then on the publisher; the team has been informed.
• The publisher has extra disk headroom for WAL: if the subscriber stops, the slot retains WAL (limit: … GB).

Phases of the guide

• Preparing the publisher — Verify that the publisher is the …Leader…primary… · Write the logical replication settings · Write the logical replication settings to Patroni · Restart the publisher · Restart the Patroni members · Create the replication user …
• Replica identity — List the tables without a PK · Define a replica identity for tables without a PK
• Publication — Create the publication
• Preparing the subscriber — Write the subscriber worker settings · Restart the subscriber · Write the publisher password to .pgpass · Create the database and the schema · Transfer roles and privileges
• Subscription and initial sync — Create the subscription · Wait for the initial sync to finish · Compare the row counts · Live change test
• Preserving the slot on failover — Define the slot as permanent in Patroni · Verify that the slot is visible on the replicas · Primary: define the synchronized standby slots · Standby: enable slot synchronization · Point the subscription to the new primary after a failover · Rebuild the subscription after a failover
• Monitoring — Subscription status (subscriber) · Slot lag (publisher)
• Conflict handling — Find the error · Fix the conflicting row on the subscriber · Skip the conflicting transaction
• Synchronizing sequences — Transfer sequence values from the publisher to the subscriber
• Cutover — Stop writes on the publisher · Verify that the last changes were transferred · Synchronize the sequences one last time · Drop the subscription · Gather statistics · Point the applications to the subscriber …
• Maintenance operations — Adding a new table · Schema change (ALTER TABLE) · Dropping a subscription (when the publisher is unreachable)