Replication and HA Intro

Introduction

Production databases need high availability (HA) and read scaling. PostgreSQL offers streaming replication (physical, byte-for-byte standby) and logical replication (selective tables, version upgrades). This chapter explains roles—primary, standby, replica—at a concept level, plus how managed clouds and tools like Patroni fit in. You will not build a full cluster in this tutorial; you will know what to search for when one server is not enough.

Prerequisites

Backup and Restore (WAL basics)
Installation and psql

Why Replicate?

Goal	Approach
Failover if primary dies	Standby promoted to primary
Read scaling	Replicas serve SELECT (hot standby)
Near-zero data loss	Synchronous replication (latency tradeoff)
Upgrade major version	Logical replication or dump/restore
Geographic DR	Replica in another region

Compare MySQL primary/replica concepts—PostgreSQL uses WAL shipping instead of MySQL binlog, but the operator goals overlap.

Architecture Sketch

text

Code explanation:

Primary accepts writes and emits WAL records
Standby replays WAL—hot standby allows read queries
Logical replica applies row changes via replication protocol—table-level

Streaming Replication (Physical)

Physical replication copies entire cluster data directory changes through WAL.

Primary side (concept—requires wal_level = replica, max_wal_senders, replication user):

conf

# postgresql.conf (primary)
wal_level = replica
max_wal_senders = 10

Standby is created with pg_basebackup from primary, then follows WAL via primary_conninfo.

On standby you may see:

sql

SELECT pg_is_in_recovery();  -- true on replica

Read-only on standby:

sql

-- Hot standby: SELECT works; writes fail
SELECT COUNT(*) FROM posts;

Failover: promote standby to primary (pg_ctl promote or orchestrator)—apps must point to new primary. Not automatic without Patroni, repmgr, or cloud failover.

Warning

Dev single instance

Your Docker postgres:16 container is a single primary—replication is production/managed-service territory.

Synchronous vs Asynchronous

Mode	Durability	Latency
Asynchronous (common)	Small window of loss if primary fails before WAL ships	Lower
Synchronous	Commit waits for standby ack	Higher write latency

sql

-- Concept: synchronous_standby_names in postgresql.conf
SHOW synchronous_commit;

Managed RDS / Cloud SQL / Neon handle this in console or SLA tiers.

Logical Replication

Replicate specific tables—useful for:

Feeding analytics warehouse
Partial database migration
Major version upgrades (PG 16 → 17)

Publisher (primary):

sql

CREATE PUBLICATION pub_posts FOR TABLE posts;
 
SELECT * FROM pg_publication;

Subscriber (another server):

sql

CREATE SUBSCRIPTION sub_posts
  CONNECTION 'host=primary dbname=hello_demo user=repl password=...'
  PUBLICATION pub_posts;

Code explanation:

Publication defines what to send
Subscription pulls changes on subscriber
Schema must exist on subscriber; DDL is not replicated—manage migrations separately

Read Replicas and Apps

Pattern	Notes
Primary for writes	Single source of truth
Replica for reports	Lag seconds behind—stale reads OK?
Connection routing	PgBouncer, ORM read replica URL, JDBC read-only

Replication lag monitor:

sql

-- On primary: pg_stat_replication
-- On standby: pg_last_wal_receive_lsn(), pg_last_wal_replay_lsn()

Apps must tolerate eventual consistency on replicas.

High Availability Stack (Awareness)

Layer	Examples
Cloud managed	AWS RDS Multi-AZ, Cloud SQL HA, Neon, Supabase
Orchestration	Patroni + etcd/Consul for leader election
Proxy	PgBouncer, HAProxy, cloud load balancers
Backups	`pg_dump` + WAL archiving (PITR)—Backup chapter

Patroni automates failover—promotes healthy replica, updates DNS/service discovery.

PostgreSQL vs MySQL HA (Brief)

	PostgreSQL	MySQL
Change log	WAL	Binlog
Physical replica	Streaming standby	Replica applier
Logical replica	Publication/Subscription	Binlog row events
Common orchestration	Patroni	Orchestrator, Group Replication

When One Server Is Enough

Local learning and small staging
Low traffic apps with good backups
Serverless Postgres with built-in HA (Neon, Supabase)

Move to replication when uptime SLO, read load, or DR requirements demand it.

FAQ

Do I need replication for Docker dev?

No—single container + pg_dump backups suffice.

Hot standby vs cold standby?

Hot accepts SELECT while applying WAL. Cold backup is offline copy—no live reads.

Logical vs physical replication?

Physical = whole cluster, same major version. Logical = table subset, cross-version possible with planning.

How long is replication lag?

Milliseconds to seconds normally—long transactions or network issues increase lag. Monitor pg_stat_replication.

Failover automatic?

Not by default on self-hosted Postgres—use Patroni or cloud HA. Manual promote risks split-brain if old primary still writable.

Read replica for cache?

Use Redis for hot keys; replicas for heavy SELECT reports—not a substitute for query tuning.

Where to learn hands-on?

Official docs: Streaming Replication, Logical Replication.