PostgreSQL has two kinds of replication. Streaming (physical) replication copies the entire cluster byte for byte to a read-only standby. Logical replication sends row changes for selected tables to another PostgreSQL server, which stays a normal, writable database. That makes logical replication the tool for selective replication, data consolidation, and major version upgrades with almost no downtime.

Physical vs logical

Streaming (physical) Logical
Replicates Whole cluster Chosen tables (or all tables in a database)
Subscriber Read-only standby Normal writable database
Across major versions No Yes
DDL (schema changes) Replicated Not replicated
Sequences Replicated Not replicated (before PG 18; copy values manually)
Use for HA, failover, read replicas Upgrades, migrations, partial copies, consolidation

See PostgreSQL streaming replication.

1. Configure the publisher

# postgresql.conf on the publisher
wal_level = logical
max_replication_slots = 10
max_wal_senders = 10
sudo systemctl restart postgresql

Create a replication user, and allow the subscriber’s IP in pg_hba.conf:

CREATE ROLE repl LOGIN REPLICATION PASSWORD 'StrongReplPass';
GRANT SELECT ON ALL TABLES IN SCHEMA public TO repl;
# pg_hba.conf
host  app  repl  203.0.113.20/32  scram-sha-256

Reload with SELECT pg_reload_conf();, and open port 5432 only for the subscriber’s IP. See PostgreSQL remote connections.

2. Every table needs a replica identity

UPDATEs and DELETEs need a way to identify rows: normally the primary key. Tables without one fail to replicate updates. Add a primary key, or set ALTER TABLE t REPLICA IDENTITY FULL; (slower, since it compares whole rows).

3. Create a publication

-- on the publisher, in database "app"
CREATE PUBLICATION app_pub FOR ALL TABLES;
-- or selected tables:
CREATE PUBLICATION orders_pub FOR TABLE orders, order_items;
-- PG 15+: filter rows and columns
CREATE PUBLICATION eu_orders FOR TABLE orders (id, total, region) WHERE (region = 'eu');

4. Prepare the subscriber schema

Logical replication doesn’t create tables. Copy the schema first:

pg_dump -h publisher.example.com -U postgres --schema-only app | psql -d app

See pg_dump and pg_restore.

5. Create the subscription

-- on the subscriber
CREATE SUBSCRIPTION app_sub
  CONNECTION 'host=publisher.example.com dbname=app user=repl password=StrongReplPass'
  PUBLICATION app_pub;

This creates a replication slot on the publisher, copies the existing data of every table (initial sync), then streams changes continuously.

6. Monitor

-- subscriber
SELECT subname, received_lsn, latest_end_lsn, last_msg_receipt_time FROM pg_stat_subscription;
SELECT srrelid::regclass, srsubstate FROM pg_subscription_rel;   -- r = ready

-- publisher
SELECT slot_name, active, pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), confirmed_flush_lsn)) AS lag
FROM pg_replication_slots;

Watch inactive slots. If a subscriber goes away, its slot makes the publisher keep WAL forever, until the disk fills. Drop unused slots (SELECT pg_drop_replication_slot('name');), or set max_slot_wal_keep_size. See PostgreSQL monitoring.

Schema changes

DDL isn’t replicated. To add a column: add it on the subscriber first, then on the publisher. For new tables: create on both, then ALTER PUBLICATION app_pub ADD TABLE new_table; (unless it’s FOR ALL TABLES), and run ALTER SUBSCRIPTION app_sub REFRESH PUBLICATION; on the subscriber.

Conflicts

The subscriber is writable. If it already contains a row that replication tries to insert (a unique violation), replication stops, and logs an error. Fix the data, or skip the transaction (ALTER SUBSCRIPTION … SKIP (lsn = …) in PG 15+). Avoid writing to replicated tables on the subscriber.

Zero-downtime major upgrade

  1. Build a new server with the new PostgreSQL version. Copy the schema.
  2. Publish all tables on the old server; subscribe from the new one.
  3. Wait for the initial sync and for lag to reach zero.
  4. Sync sequence values (SELECT setval(…) on the new server, from the old values).
  5. Briefly stop writes, confirm zero lag, and switch the application’s connection string.
  6. Drop the subscription, and keep the old server for rollback for a while.

Downtime is limited to the switch itself. See upgrading PostgreSQL major versions.

Quick reference

Step Command
Enable wal_level = logical + restart
Publish CREATE PUBLICATION p FOR ALL TABLES;
Subscribe CREATE SUBSCRIPTION s CONNECTION '…' PUBLICATION p;
New tables ALTER SUBSCRIPTION s REFRESH PUBLICATION;
Status pg_stat_subscription, pg_replication_slots
Pause ALTER SUBSCRIPTION s DISABLE;

Related: database replication explained and pgBackRest backups.

  • PostgreSQL
  • Replication
  • Database