DBAtlas · Database operations atlas

Set up a logical replication publication and subscription

PostgreSQLReplication

Command

-- Source (publisher): CREATE PUBLICATION pub_customer FOR TABLE customer; -- Target (subscriber): CREATE SUBSCRIPTION sub_customer CONNECTION 'host=primary_host dbname=appdb user=replicator password=secret' PUBLICATION pub_customer;

What it does

Starts table-level logical replication by creating a publication for specific tables on the source and a subscription that connects to it on the target.

When to use it

For version upgrades, replicating selected tables to another server, or one-way data distribution.

Caution

Requires PostgreSQL 10+. The target tables' schema must already exist; the subscription performs the initial COPY automatically. wal_level must be logical (requires a restart). DDL changes and sequence values are not replicated; apply schema changes on both sides separately and sync sequences with setval at cutover.

Open in DBAtlas →

Related