iwantcoding.com
🔥 Daily 👥 Rooms 🏆 Top Log in Sign up

Replication

By · Reviewed & last updated · Part of PostgreSQL Tutorial

Postgres replication ships WAL records from a primary to one or more replicas. Streaming replication is the standard: bytes flow in near real time, replicas are read-only, and failover is a manual or orchestrated promotion. Modern Postgres adds logical replication for cross-version and cross-schema flows.

Streaming + logical replication, plus failover sketch

EXAMPLE
# 1) Primary postgresql.conf
# wal_level             = replica          # 'logical' if you also want logical replication
# max_wal_senders       = 10
# max_replication_slots = 10
# wal_keep_size         = 1GB              # or use a replication slot to avoid retention bugs
# archive_mode          = on
# archive_command       = 'aws s3 cp %p s3://wal/%f --quiet'

# pg_hba.conf — allow replicas to connect with replication privilege
# host replication repl 10.0.0.0/8 scram-sha-256

# 2) Create the replication user on the primary
CREATE USER repl WITH REPLICATION LOGIN PASSWORD 'strong-secret';
SELECT pg_create_physical_replication_slot('replica1');

# 3) On the replica — take a base backup and start it
pg_basebackup -h primary -U repl -D /var/lib/postgresql/data \
              -X stream -P -R -S replica1

# pg_basebackup -R writes a postgresql.auto.conf with primary_conninfo set.
# Add to postgresql.conf on the replica:
# hot_standby = on

# 4) Verify on the primary
SELECT application_name, client_addr, state, sent_lsn, replay_lsn,
       pg_size_pretty(pg_wal_lsn_diff(sent_lsn, replay_lsn)) AS lag
FROM pg_stat_replication;

# 5) Monitor on the replica
SELECT pg_is_in_recovery(), now() - pg_last_xact_replay_timestamp() AS replay_lag;

# 6) Failover — promote the replica when the primary is gone
# On the replica:
SELECT pg_promote(true);                     -- non-blocking, returns true on success
# Now redirect the app via your connection pooler (PgBouncer, pgcat) or DNS.
# Use Patroni/repmgr/Stolon for automated leader election instead of this manual sequence.

# 7) Logical replication — replicate selected tables to a different cluster (or major version)
# On the SOURCE (logical publisher):
ALTER SYSTEM SET wal_level = 'logical';
SELECT pg_reload_conf();
CREATE PUBLICATION shop_pub FOR TABLE orders, customers;

# On the TARGET (subscriber):
CREATE SUBSCRIPTION shop_sub
  CONNECTION 'host=primary user=repl password=strong-secret dbname=shop'
  PUBLICATION shop_pub;

# Verify
SELECT subname, received_lsn, latest_end_lsn FROM pg_stat_subscription;

# 8) Disaster recovery basics
# - Backups + PITR are STILL required. Replication is HA, not DR.
# - Test failover quarterly. A replica you have never promoted does not work.
# - Monitor lag with alerts BOTH on time (seconds_behind) AND on bytes (sent vs replay LSN).

Why it matters

Use a replication slot (not just wal_keep_size) for streaming replicas. Slots tell the primary "keep WAL until this consumer catches up", which prevents the famous failure where a replica falls behind, the primary recycles WAL, and the replica has to be rebuilt from scratch.

Tip: Tweak the snippet with Try it Yourself », then sit the quiz at the bottom of the page.

Example

Example
# postgresql.conf on primary
wal_level = replica
max_wal_senders = 10
# Replica
pg_basebackup -h primary -U replicator -D /var/lib/postgresql/16/main
Try it Yourself »

Discussion

Loading…