Streaming Replication Best Practices

One synchronous partner max unless you understand latency. These rules keep physical replication reliable under failover and load spikes.

How to Use This List

• Apply during cluster design and every standby addition.
• Verify each item after Patroni config changes or OS upgrades.
• Use as a pre-failover drill checklist.
• Pair with Grafana alerts on byte lag and inactive slots.

A - Topology and Slots

• One physical replication slot per long-lived standby. Slots prevent WAL loss during outages.
• Name slots after hostname or Patroni member. Operators know which consumer to fix.
• Set max_slot_wal_keep_size as a disk safety valve. Complement, not replace, slot hygiene.
• Limit cascade depth to two hops. Extra hops multiply lag and failure domains.
• Direct-primary standbys for failover candidates. Cascading edges stay read-only unless runbook says otherwise.

B - Sync and Durability

• At most one synchronous partner in production. More partners need explicit WAN latency budget.
• Match application_name to synchronous_standby_names. Misnamed standbys never join quorum.
• Document behavior when sync partner is down. Patroni should demote sync or ops relax quorum quickly.
• Avoid global synchronous_commit = off. Scope durability downgrades to batch roles only.
• Prefer remote_write over remote_apply unless apps read from standby post-commit. Lower commit latency.

C - Monitoring and Alerts

• Alert on pg_stat_replication byte lag, not only time lag. Idle workloads hide delay in LSN gap.
• Page when last sync standby stops streaming. Writes may stall under sync mode.
• Alert on inactive slots retaining > 512 MiB WAL. Orphan slots fill disks.
• Monitor WAL generation rate on primary. Spikes predict replication pressure.
• Track Patroni leader role separately from replication lag. Clients can be on wrong host while lag looks fine.

D - Security and Networking

• Use scram-sha-256 for replication users in pg_hba.conf. No plaintext passwords in conf files.
• TLS on replication (sslmode=verify-full in primary_conninfo). Encrypt cross-AZ and cross-region links.
• Dedicated replication user with minimal privileges. REPLICATION login only, no superuser.
• Separate monitoring role for health queries. No superuser for cron checks.

E - Operations and Drills

• Rehearse standby promotion quarterly. Measure RTO with app reconnect tests.
• Verify PgBouncer or HAProxy routes to new primary after promotion. Pool does not fix DNS by itself.
• Reseed standby from pg_basebackup after wal_status = lost. Incremental catch-up cannot fix missing WAL.
• Keep primary and standby major versions aligned. Physical replication does not cross major versions.

FAQs

Related

• Streaming Replication Basics - setup walkthrough
• Synchronous vs Asynchronous - RPO trade-offs
• Replication Slots - retention mechanics
• HA Best Practices - failover drills

Stack versions: This page was written for PostgreSQL 18.4 (stable 18, maintenance 17), pgvector 0.8+, PgBouncer 1.x, Patroni 3.x, and PostGIS 3.5+. 636f64656775696465732e696f7c6367696f313037307c323032363037