PostgreSQL Operations, HA, Replication, and Recovery

PostgreSQL availability is built from database mechanics plus an external management layer. PostgreSQL knows how to write WAL, stream WAL, recover from WAL, serve hot standbys, and promote a standby. A production HA system also needs leader election, fencing, connection routing, backup orchestration, monitoring, restore testing, and human-safe failover procedures.

Command Examples

psql -d <database> -c "SELECT pid, state, wait_event_type, wait_event, now() - query_start AS age, query FROM pg_stat_activity ORDER BY query_start NULLS LAST LIMIT 20;"
psql -d <database> -c "SELECT * FROM pg_stat_replication;"
psql -d <database> -c "SELECT slot_name, active, restart_lsn, wal_status FROM pg_replication_slots;"
psql -d <database> -c "SELECT * FROM pg_stat_archiver;"
psql -d <database> -c "SELECT checkpoints_timed, checkpoints_req, buffers_checkpoint FROM pg_stat_checkpointer;"
psql -d <database> -c "SELECT wal_records, wal_fpi, wal_bytes FROM pg_stat_wal;"
psql -d <database> -c "SELECT pg_is_in_recovery(), pg_last_wal_receive_lsn(), pg_last_wal_replay_lsn(), now() - pg_last_xact_replay_timestamp() AS replay_delay;"
psql -d <database> -c "SELECT subname, subenabled, subfailover FROM pg_subscription;"

Example output and meaning:

These checks separate live sessions, standby state, WAL retention, archive health, checkpoint pressure, and WAL generation. Pair them with host metrics for CPU, memory, IO, filesystem fullness, and cgroup limits.

Managed HA Model

PostgreSQL does not become highly available just because it has a replica. A managed HA design needs clear ownership for:

The dangerous failure is split brain: two writable primaries accepting divergent writes. Promotion must be paired with a plan to stop or fence the old primary before it can accept writes again. After promotion, PostgreSQL creates a new timeline, and remaining standbys must follow the right timeline or be rebuilt.

Think in RPO and RTO:

• RPO: how much committed data can be lost.
• RTO: how long the service can be unavailable.
• Asynchronous replication: better write availability and latency, possible data loss on failover.
• Synchronous replication: lower data-loss risk, higher write latency and possible write unavailability when required standbys are missing.

Synchronous replication is not one setting with one meaning. synchronous_commit controls how far a commit waits: local WAL flush, standby receipt, standby flush, or standby apply. Waiting for remote_apply gives stronger read-after-write behavior from synchronous standbys than waiting only for receipt, but it costs more latency. Quorum synchronous replication can require any selected number of standbys rather than one named standby. Design these settings around business RPO/RTO, not around the word “synchronous” by itself.

HA Failover

Failover is a state transition, not just a command. The system must decide that the old primary is no longer allowed to accept writes, promote exactly one standby, route clients to it, and make the remaining replicas follow the new timeline.

flowchart LR
  Detect[Detect primary failure] --> Fence[Fence old primary]
  Fence --> Choose[Choose promotion candidate]
  Choose --> Promote[Promote standby]
  Promote --> Route[Move writer route]
  Route --> Repoint[Repoint or rebuild replicas]
  Repoint --> Validate[Validate writes, WAL archive, backups]

Failover sequence:

1. Detect primary failure with more than one signal: database checks, host checks, storage checks, replication state, and client impact.
2. Fence the old primary. Stop PostgreSQL, remove write access, power off the node, detach storage, delete the Pod, or otherwise prove it cannot accept writes.
3. Choose the promotion candidate by replay position, health, availability zone, synchronous state, and operational policy.
4. Promote the candidate with the HA manager, pg_ctl promote, or SELECT pg_promote();.
5. Wait for the promoted server to leave recovery and accept writes.
6. Move client routing: VIP, DNS, load balancer, PgBouncer target, Kubernetes Service, or cloud endpoint.
7. Repoint or rebuild other standbys so they follow the promoted primary’s timeline.
8. Validate application writes, replication, backup archiving, monitoring, and connection pool behavior.
9. Decide whether the old primary can be rewound with pg_rewind or must be rebuilt from a fresh base backup.

Failover hazards:

Planned switchover is different from emergency failover. In a switchover, stop or drain writes, let the candidate catch up, promote cleanly, move routing, and demote or rewind the old primary. Emergency failover prioritizes restoring service, but it still needs fencing and a written decision about acceptable data loss.

Useful checks around promotion:

SELECT pg_is_in_recovery();
SELECT pg_last_wal_receive_lsn(), pg_last_wal_replay_lsn();
SELECT now() - pg_last_xact_replay_timestamp() AS replay_delay;
SELECT pg_promote(wait => true);
SELECT timeline_id FROM pg_control_checkpoint();

Replication

Physical streaming replication sends WAL from primary to standby. A hot standby can serve read-only queries while replaying WAL, but it is still a copy of the primary’s physical cluster, not an independent writable database.

Key pieces:

Physical replication properties:

• It replicates the whole cluster at the storage/WAL level, not selected tables.
• Primary and standby must be compatible at the binary storage level, so physical replication is not a general major-version upgrade method.
• Standbys are read-only unless promoted.
• Cascading replication can reduce primary fan-out by streaming from a standby to downstream standbys.
• Physical replication slots protect standbys from missing WAL, but an inactive slot can retain WAL until disk fills unless guarded by monitoring and max_slot_wal_keep_size.

Replication lag has multiple meanings:

• send lag: primary has not sent WAL yet,
• write or flush lag: standby received WAL but has not persisted it,
• replay lag: standby has not applied WAL yet,
• visibility lag: read-only queries on standby see older data.

Use pg_stat_replication on the primary, pg_stat_wal_receiver on the standby, and LSN differences to locate the lag boundary. Large sent_lsn gaps often point at primary pressure, network delay, or standby IO/replay limits.

Logical replication is different. It publishes row changes from selected tables and subscriptions apply them elsewhere. It is useful for migrations, selective replication, version transitions, reporting copies, partial fan-out, and some shard-movement workflows, but it has different DDL, sequence, conflict, and identity constraints than physical replication.

Logical replication model:

Logical replication caveats:

• DDL is not automatically replicated in the same way as table data. Schema changes need an explicit rollout plan.
• Sequences need separate handling because sequence state is not the same as ordinary table rows.
• Tables generally need primary keys or appropriate replica identity for efficient updates and deletes.
• Conflicts can happen if the subscriber is writable or if multiple sources write overlapping rows.
• Initial table copy can be large and needs capacity for table sync workers, WAL retention, and subscriber writes.
• Logical slots can retain catalog tuples and WAL; stale slots are both storage and vacuum risks.

Logical failover is a newer operational detail worth planning explicitly. Logical replication slots can be configured for failover so they synchronize to physical standbys. Before promoting a standby that should preserve logical subscribers, verify that the required logical slots exist on the standby and are ready, because slot synchronization is asynchronous.

Replication design examples:

Replication incident workflow:

1. Check whether the primary is producing WAL: pg_stat_wal, write load, checkpoints, and pg_wal usage.
2. Check sender state on the primary: pg_stat_replication, sent_lsn, write_lsn, flush_lsn, replay_lsn.
3. Check receiver state on the standby: pg_stat_wal_receiver, pg_last_wal_receive_lsn(), pg_last_wal_replay_lsn().
4. Check replication slots: inactive slots, restart_lsn, wal_status, safe_wal_size, catalog_xmin, and invalidation reason.
5. Check network and standby IO before assuming PostgreSQL settings are wrong.
6. If a standby fell too far behind and required WAL is gone, rebuild it from a new base backup instead of trying to skip missing WAL.

Sharding

Sharding splits data across multiple PostgreSQL clusters or nodes so one logical application dataset is physically distributed. Native PostgreSQL gives you building blocks for partitioning, foreign tables, logical replication, and routing, but it does not provide transparent automatic distributed SQL sharding by itself. If you need that, use a purpose-built extension or distributed database layer and design around its failure modes.

Do not confuse these terms:

Shard key choice is the main design decision. A good shard key keeps common transactions on one shard, spreads writes evenly, and matches tenant or data-ownership boundaries. A bad shard key creates hot shards, cross-shard joins, global uniqueness problems, and painful resharding.

Sharding design questions:

• What is the shard key: tenant, organization, user, account, region, time, hash, or composite key?
• Can the most important transactions execute on one shard?
• What must be globally unique, and can IDs be generated without a central bottleneck?
• Which queries need fan-out across shards, and how slow or stale can those results be?
• How are schema migrations rolled across shards without breaking application compatibility?
• How are backups, PITR, restores, and legal deletes performed for one shard or one tenant?
• How are shard moves, split/merge operations, and rebalancing tested?
• What happens when one shard is down but others are healthy?

Common PostgreSQL sharding patterns:

Sharding operational rules:

• Keep a shard map with versioning. The application and operations team must know which shard owns each key.
• Prefer idempotent retry behavior. Cross-shard partial failures are normal in distributed systems.
• Avoid cross-shard transactions unless the platform explicitly supports the semantics you need and you have tested failure cases.
• Use per-shard backups plus shard-map backups. A restore without the correct shard map can put tenants on the wrong data.
• Monitor each shard separately and also monitor fleet-level skew: largest shard, hottest shard, slowest shard, and most lagged replica.
• Plan resharding before it is urgent. Emergency shard splits are much harder than routine, rehearsed movement.
• Keep schema versions compatible during rolling migrations. Application code may talk to shards at different migration points.

Backups and Restore

Replicas are not backups. They can faithfully replicate deletion, corruption, bad migrations, and application bugs. A backup strategy should include:

For PITR, you need a base backup and every WAL segment from that backup through the recovery target. Missing one required WAL segment breaks the chain. archive_command or archive_library must be monitored as a production write path, because failed archiving can silently destroy recovery objectives and fill pg_wal.

Logical dump boundaries matter. pg_dump backs up one database, not the whole cluster. Cluster-global objects such as roles and tablespaces require pg_dumpall --globals-only or equivalent infrastructure-as-code. A logical dump is useful, but it is not PITR and it cannot replace physical backups for whole-cluster recovery objectives.

Physical backup verification is also not the same as restore testing. pg_basebackup can create a backup manifest, and pg_verifybackup can check a plain-format base backup against that manifest. That catches many file-level problems, but the PostgreSQL documentation still warns that only a real restore proves the backup can be used by a running server and application.

Restore drills should prove:

1. You can find the intended base backup.
2. You can retrieve every required WAL segment.
3. You can recover to a named time, LSN, restore point, or latest consistent state.
4. Applications can authenticate and use the restored database.
5. The restored database is not accidentally connected to production integrations.

WAL and Checkpoints

WAL is the write-ahead log used for crash recovery, replication, and PITR. Data pages can be written later because WAL records describe the changes needed to recover.

Operational WAL checks:

• pg_wal filesystem usage,
• failed or slow archiving in pg_stat_archiver,
• inactive replication slots retaining old WAL,
• WAL generation spikes in pg_stat_wal,
• checkpoint frequency in pg_stat_checkpointer,
• replica replay lag and recovery conflicts.

Frequent requested checkpoints can mean max_wal_size is too low for the write workload. Very infrequent checkpoints can increase crash recovery time. Full-page writes, bulk changes, index builds, vacuum behavior, and checkpoint timing all affect WAL volume.

High CPU

High PostgreSQL CPU is usually one of these shapes:

Start with active sessions and wait events before tuning. If the server is CPU-bound on useful work, reduce work: better indexes, better predicates, fixed row estimates, less chatty queries, cached results, or more efficient schema design. If CPU is from connection churn, PgBouncer usually helps more than raising max_connections.

Locks, Timeouts, and Long Transactions

Lock waits can look like CPU, memory, or application latency incidents. Use pg_stat_activity, pg_locks, and pg_blocking_pids() to separate “doing work” from “waiting for another transaction.”

Lock wait evidence path:

flowchart LR
  Symptom[Slow query or request timeout] --> Activity[pg_stat_activity wait_event]
  Activity --> Blocking[pg_blocking_pids]
  Blocking --> Locks[pg_locks relation and mode]
  Locks --> Tx[blocking transaction age and query]
  Tx --> Decision{Safe to cancel or terminate?}
  Decision -->|Cancel statement| Cancel[pg_cancel_backend]
  Decision -->|Terminate session| Terminate[pg_terminate_backend]
  Decision -->|Not safe| App[Coordinate app owner and preserve evidence]

SELECT blocked.pid AS blocked_pid,
       blocked.query AS blocked_query,
       pg_blocking_pids(blocked.pid) AS blocking_pids,
       blocked.wait_event_type,
       blocked.wait_event
FROM pg_stat_activity AS blocked
WHERE cardinality(pg_blocking_pids(blocked.pid)) > 0;

Important guardrails:

• statement_timeout limits runaway statements when set at an appropriate role, database, or application level.
• lock_timeout fails fast when a statement waits too long to acquire a lock.
• idle_in_transaction_session_timeout terminates sessions that sit idle inside a transaction, which can otherwise hold locks and prevent vacuum cleanup.
• transaction_timeout limits total transaction duration, but be careful with middleware and connection poolers.

Do not set every timeout globally without testing. Poolers, migration tools, maintenance jobs, and long analytical queries may need different limits.

Lock triage guardrails:

High RAM

PostgreSQL memory is not one pool. Important consumers include:

The common mistake is setting work_mem as if it were global. A value that is safe for five sessions can be dangerous for hundreds of sessions running multi-sort queries. High RAM incidents often combine too many backends, high work_mem, temp tables, hash joins, prepared statement caches, and container memory limits.

High RAM workflow:

1. Check whether memory is PostgreSQL RSS, OS cache, tmpfs, kernel slab, or cgroup accounting.
2. Count active and idle backends.
3. Look for temp file creation and spilled sorts/hashes.
4. Check work_mem, maintenance_work_mem, shared_buffers, and connection counts together.
5. Inspect long transactions and idle-in-transaction sessions that hold resources and block vacuum.
6. Use PgBouncer or app pool limits to cap backend count before raising memory settings.

Study Cards

References

• PostgreSQL High Availability, Load Balancing, and Replication
• PostgreSQL Log-Shipping Standby Servers
• PostgreSQL Replication Configuration
• PostgreSQL Backup and Restore
• PostgreSQL Continuous Archiving and PITR
• PostgreSQL Resource Consumption
• PostgreSQL Write Ahead Log Configuration
• PostgreSQL Monitoring Statistics
• PostgreSQL pg_stat_statements
• PostgreSQL Logical Replication
• PostgreSQL Logical Replication Failover
• PostgreSQL Table Partitioning
• postgres_fdw

psql -c "select pg_is_in_recovery(), now() - pg_last_xact_replay_timestamp()"

pg_is_in_recovery | ?column?
-------------------+----------
f                 |

psql -p 6432 pgbouncer -c "show pools"

database | user | cl_active | cl_waiting | sv_active | sv_idle
app      | app  | 120       | 18         | 20        | 0

psql -p 6432 pgbouncer -c "reconnect"

RECONNECT