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
Replication for HA¶
Streaming for HA, logical for selective replication.
postgresqlreplicationha