Replication and High Availability

Streaming Replication

A standby server keeps a copy of the primary by applying a continuous stream of write-ahead log (WAL) records. The result is a hot standby that can also serve read-only queries.

Building a Standby

pg_basebackup -h primary -D /var/lib/postgresql/standby -R

The -R flag writes connection settings and places a standby.signal marker so the node boots in recovery mode.

Failover

SELECT pg_promote();   -- run on the standby

Promotion turns the standby into the new primary. Tools like repmgr automate the switch with minimal downtime.

Logical Replication

Logical replication copies changes row by row between publishing and subscribing databases. It works across PostgreSQL versions and even into other database systems.

Key Points

• Streaming replication gives you a hot standby.
• pg_basebackup builds a standby quickly.
• pg_promote completes a manual failover.
• Logical replication is flexible across versions and vendors.