Andrew Mercer
on this page

PostgreSQL Replication

PostgreSQL supports two distinct replication mechanisms that solve different problems — physical (streaming) replication for HA/read-scaling of a whole cluster, and logical replication for replicating specific tables, often across major versions or into a different schema shape. This doc covers both, plus base backups for point-in-time recovery. See Overview: WAL first if the WAL isn't already familiar — both replication types are built on it.

Unlike Galera (see the Galera Cluster docs), standard PostgreSQL replication is not multi-master — one primary accepts writes, standbys are read-only (or logically independent, in the logical case). For a multi-master/horizontally-sharded setup, see HA with Citus instead.

Physical (Streaming) Replication

The standby continuously receives and replays the primary's WAL stream, producing a byte-for-byte copy of the whole cluster (every database, every table).

1. Configure the Primary

# postgresql.conf on the primary
wal_level = replica
max_wal_senders = 10
wal_keep_size = 1GB
  • wal_level = replica (or logical, which is a superset — see below) enables enough WAL detail for a standby to replay
  • max_wal_senders caps how many concurrent replication connections (standbys + backup tools) can attach
  • wal_keep_size retains extra WAL on disk so a temporarily-lagging standby doesn't fall so far behind the primary recycles WAL it still needs — a replication slot (below) is the more robust way to guarantee this

Add a pg_hba.conf entry allowing the standby to connect using the special replication pseudo-database:

# pg_hba.conf on the primary
host    replication     replicator      10.0.0.0/24            scram-sha-256

Create a dedicated replication role rather than reusing an admin account:

CREATE ROLE replicator WITH REPLICATION LOGIN ENCRYPTED PASSWORD '...';

2. Take a Base Backup Onto the Standby

pg_basebackup -h primary_host -D /var/lib/pgsql/data -U replicator -P -v -R -X stream
  • -R writes the standby connection info directly into the data directory, so the standby knows how to find the primary on startup
  • -X stream streams WAL generated during the backup alongside the base copy, avoiding a gap between backup completion and replication start
  • -P -v show progress — worth having on for anything but a trivially small database

3. Start the Standby

Starting PostgreSQL against a data directory containing a standby.signal file (created automatically by -R above) puts it into standby mode automatically — no separate "enable standby" step needed on modern versions (12+).

4. Verify Replication Status

On the primary:

SELECT client_addr, state, sync_state, replay_lag FROM pg_stat_replication;

On the standby:

SELECT pg_is_in_recovery();  -- true means this is a standby
SELECT now() - pg_last_xact_replay_timestamp() AS replication_delay;

Replication Slots

A physical replication slot tells the primary "don't recycle WAL this standby hasn't consumed yet," even if the standby disconnects for a while. Without a slot, a standby that falls too far behind can end up needing a full re-pg_basebackup instead of just catching up.

-- on the primary
SELECT pg_create_physical_replication_slot('standby1_slot');

Then point the standby at it (in its primary_conninfo, or via -S with pg_basebackup). The trade-off: an unused or forgotten slot will let WAL grow unbounded on the primary until disk fills up — monitor pg_replication_slots for slots that aren't actively being consumed.

SELECT slot_name, active, pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)) AS retained_wal
FROM pg_replication_slots;

Synchronous vs. Asynchronous Replication

By default, replication is asynchronous — the primary commits and returns to the client before the standby has confirmed receipt. This is the right default for most setups (better commit latency, and a lagging standby never blocks writes on the primary), but it means a small amount of data loss is possible if the primary fails before the standby catches up.

To require at least one standby to confirm before commit:

# postgresql.conf on the primary
synchronous_standby_names = 'standby1'

This trades commit latency (every write now waits on network round-trip to the standby) for a stronger durability guarantee (zero data loss on primary failure, as long as the synchronous standby is up). Only turn this on if you've actually thought through the availability trade-off — a synchronous standby that goes down will stall writes on the primary unless synchronous_standby_names lists multiple candidates.

Failover / Promotion

PostgreSQL itself does not automate failover — promoting a standby to primary is a manual (or externally-orchestrated) action:

pg_ctl promote -D /var/lib/pgsql/data

or, on systemd-managed installs:

touch /var/lib/pgsql/data/promote.signal

After promotion, the old primary (if it comes back) does not automatically become a standby of the new primary — it needs to be reconfigured (or rebuilt via pg_rewind if its WAL history has diverged) before rejoining as a standby, or you risk a split-brain with two nodes both accepting writes.

Automating Failover

Since PostgreSQL doesn't do this itself, common tools layered on top: - Patroni — the most widely used option today; uses a distributed consensus store (etcd, Consul, or ZooKeeper) to manage leader election and automate promotion - repmgr — older, simpler, more manual-friendly; good fit if you want visibility into each step rather than full automation - pgpool-II — connection pooling plus basic failover/load-balancing, often paired with one of the above rather than used alone for failover logic

None of these are covered in depth here since they weren't in the source notes for this section — flagging them so you know what exists if HA failover becomes a requirement rather than manual promotion.

Logical Replication

Where physical replication copies the entire cluster byte-for-byte, logical replication replicates individual tables (or sets of tables) at the row level, decoded into logical changes rather than raw WAL bytes. This allows: - Replicating a subset of tables, not the whole cluster - Replicating between different major PostgreSQL versions (used for online major-version upgrades) - A subscriber with a different schema (e.g. extra local indexes, or a table with fewer columns) - Multiple publishers into one subscriber (something physical replication can't do at all)

1. Enable Logical Decoding on the Publisher

# postgresql.conf
wal_level = logical

2. Create a Publication

-- on the publisher, connected to the source database
CREATE PUBLICATION my_pub FOR TABLE users, orders;
-- or, for every table in the database:
CREATE PUBLICATION my_pub FOR ALL TABLES;

3. Create a Subscription

-- on the subscriber, connected to the target database (schema must already exist there)
CREATE SUBSCRIPTION my_sub
  CONNECTION 'host=publisher_host dbname=source_db user=replicator password=...'
  PUBLICATION my_pub;

This performs an initial data copy of the published tables, then streams ongoing changes. Unlike physical replication, the subscriber database is fully writable independently — logical replication doesn't make it read-only, which also means it's on you to avoid conflicting writes on replicated tables if you don't want divergence.

Monitoring Logical Replication

-- on the publisher
SELECT * FROM pg_stat_replication;
SELECT * FROM pg_replication_slots WHERE slot_type = 'logical';

-- on the subscriber
SELECT * FROM pg_stat_subscription;

Base Backups and Point-in-Time Recovery

pg_basebackup (shown above for standby provisioning) doubles as the foundation for PITR: combined with continuous WAL archiving, it lets you restore to any point in time, not just the moment the backup was taken.

# postgresql.conf
archive_mode = on
archive_command = 'cp %p /path/to/wal_archive/%f'

In production, archive_command should ship WAL somewhere durable and off-box (object storage, a dedicated backup host) rather than a local cp — and tools like pgBackRest or WAL-G exist specifically to manage this properly (compression, retention, parallel restore) rather than hand-rolling it. Not covered in depth here, but worth knowing this is a solved problem rather than something to script from scratch.

This is a different concern from the logical pg_dump snapshots in Administration: Backup and Restore — a pg_dump gives you one static point in time; WAL archiving plus a base backup gives you any point in time within your retention window.

  • Overview: WAL — the mechanism both replication types build on
  • Administration — pg_hba.conf and firewall setup referenced above
  • HA with Citus — for multi-master/sharded write scaling, which neither replication type above provides on its own