Replication ensures high availability and read scaling.
Streaming Replication¶
# PostgreSQL Replication
# Replica:
pg_basebackup -h primary -U replicator -D /var/lib/postgresql/16/main -P
# primary_conninfo='host=primary user=replicator'
Logical Replication¶
-- Primary
CREATE PUBLICATION my_pub FOR TABLE users, orders;
-- Subscriber
CREATE SUBSCRIPTION my_sub
CONNECTION 'host=primary dbname=mydb'
PUBLICATION my_pub;
Monitoring¶
SELECT client_addr, state, sent_lsn, replay_lsn FROM pg_stat_replication;
SELECT now()-pg_last_xact_replay_timestamp() AS lag;
- Streaming — HA, read replicas, entire DB
- Logical — selective tables, cross-version
Production Recommendations¶
For automatic failover in production, use a tool like Patroni or repmgr, which detects primary server failure and automatically promotes a replica. Without automatic failover, manual intervention is required and can take minutes to hours.
Streaming replication is asynchronous by default — the replica can be several transactions behind the primary. For critical systems, set up synchronous replication (synchronous_commit = on), but expect higher write latency. Logical replication allows selective replication of specific tables, which is useful for data warehouse feeding or cross-version migrations. Monitor replication lag using pg_stat_replication and set up alerting when lag exceeds a defined threshold.
Replication for HA¶
Streaming for HA, logical for selective replication.