PostgreSQL Backup & Replication Guide: WAL Basics to Production‑Ready HA, DR, and Recovery

Core Concepts

Backup, high‑availability (HA), disaster‑recovery (DR) and change‑data‑capture (CDC) are distinct capabilities. Backup answers “what to do when data is lost”. HA answers “what to do when the primary fails”. DR answers “what to do when an entire availability zone is lost”. CDC answers “how to stream data changes to other systems”. Mixing them without clear boundaries yields a solution that looks complete on paper but fails in real incidents.

Order‑System Requirements

Core transaction loss tolerance: RPO <= 5s Failover time: RTO <= 60s Read‑only queries may tolerate slight lag (asynchronous replicas)

Audit, data‑warehouse and messaging need change capture without reading the primary WAL directly

Accidental deletions or bad scripts require point‑in‑time recovery (PITR)

Typical production layout splits the system into four layers:

Transaction primary layer – guarantees write durability.

Read‑only replica layer – offloads reporting and analytics.

Backup & archive layer – stores base backups and WAL for PITR.

Logical distribution layer – streams changes to risk control, data‑warehouse, search, etc.

WAL, LSN, Checkpoints and Timelines

All backup, recovery and replication in PostgreSQL revolve around the Write‑Ahead Log (WAL). A transaction is considered committed once its WAL record is flushed to disk, not when the data page is written.

Key LSN markers used for monitoring: sent_lsn – position sent by the primary. write_lsn – position written to the replica’s WAL. flush_lsn – position flushed to disk on the replica. replay_lsn – position replayed on the replica.

Checkpoints consolidate dirty pages. Disabling full_page_writes to reduce WAL size is a common mistake; it removes the safety net for crash‑consistent page recovery. Prefer tuning checkpoint frequency, storage latency and wal_compression instead.

After a failover the new primary creates a new WAL timeline. The old primary must be aligned to this timeline before re‑joining, otherwise divergent timelines cause a split‑brain situation.

Synchronous Commit Options

synchronous_commit = off

– returns before WAL is flushed locally; risk of losing recent transactions on crash; suitable for non‑critical logs. synchronous_commit = on – waits for local WAL flush (and optional sync replica); higher latency but much smaller loss window; default for core transactions. synchronous_commit = remote_apply – waits until the replica has replayed the transaction; highest latency; used only when read‑your‑writes consistency is required.

In practice most teams keep the default on and downgrade only specific non‑critical statements with SET LOCAL synchronous_commit = off.

Essential PostgreSQL Parameters

# postgresql.conf
wal_level = replica
archive_mode = on
archive_command = 'pgbackrest --stanza=orders archive-push %p'
max_wal_senders = 16
max_replication_slots = 16
wal_keep_size = '4GB'
max_slot_wal_keep_size = '32GB'
archive_timeout = '60s'
hot_standby = on
wal_compression = zstd
checkpoint_timeout = '15min'
checkpoint_completion_target = 0.9
max_wal_size = '32GB'
min_wal_size = '4GB'
max_standby_streaming_delay = '30s'
hot_standby_feedback = on

Key meanings: wal_level controls how much information is kept in WAL ( replica for physical replication, logical for logical decoding). archive_mode / archive_command enable continuous WAL archiving for PITR. max_wal_senders and max_replication_slots limit concurrent WAL streams. wal_keep_size provides a safety buffer for short network hiccups. wal_compression reduces WAL volume, especially for write‑intensive workloads.

Backup Strategies

Logical Backup (pg_dump / pg_dumpall)

Pros: per‑schema/table export, good for migrations, testing, audit.

Cons: not incremental, slower for large databases, cannot be combined with WAL for PITR, unsuitable for sub‑second recovery.

Physical Backup + Continuous WAL Archiving (recommended)

Periodic base backups (e.g., nightly).

Real‑time WAL archiving.

Backup verification.

Regular recovery drills.

This enables full‑cluster restores, point‑in‑time recovery, and works seamlessly with HA replicas.

Streaming Replication (Physical)

Provides online availability but does not replace a backup system; it cannot recover from logical data corruption or accidental deletes.

Logical Replication (CDC)

Ideal for zero‑downtime version upgrades, partial table sync, and streaming order changes to downstream systems.

Limitations: DDL is not replicated, sequence state is not synced, large transactions increase lag, subscribers should be read‑only.

Publication & Subscription Example

ALTER SYSTEM SET wal_level = logical;
SELECT pg_reload_conf();
CREATE PUBLICATION pub_order_core FOR TABLE orders, order_items, payment_record;

CREATE SUBSCRIPTION sub_order_core
  CONNECTION 'host=10.10.1.12 port=5432 dbname=orders user=replicator password=replace_me'
  PUBLICATION pub_order_core
  WITH (copy_data = true, create_slot = true, enabled = true);

Common Pitfalls

DDL changes are not automatically applied on the subscriber.

Sequence values diverge unless explicitly synchronized.

Large transactions block replication; split them into smaller batches.

Debezium + Kafka for Change Streaming

{
  "name": "orders-cdc",
  "config": {
    "connector.class": "io.debezium.connector.postgresql.PostgresConnector",
    "database.hostname": "order-db-rw",
    "database.port": "5432",
    "database.user": "debezium",
    "database.password": "replace_me",
    "database.dbname": "orders",
    "database.server.name": "order_db",
    "slot.name": "debezium_orders",
    "publication.name": "dbz_orders_pub",
    "plugin.name": "pgoutput",
    "table.include.list": "public.orders,public.order_items,public.payment_record",
    "tombstones.on.delete": "false",
    "snapshot.mode": "initial"
  }
}

Debezium decouples downstream systems (search, risk, analytics) from the primary, but consumers must handle idempotency, ordering and compensation.

High‑Concurrency Engineering Considerations

Use a connection pool or cloud‑native proxy (PgBouncer, RDS Proxy) and enforce connection limits.

Route long‑running reporting queries to a data‑warehouse; keep read‑only replicas free for OLTP.

Split large transactions, use idempotent batch processing, and avoid monolithic writes.

Schedule backup jobs off‑peak and preferably pull base backups from replicas to avoid I/O contention.

Kubernetes Deployment Tips

Manage topology with a PostgreSQL operator (Patroni, CrunchyData, etc.).

Persist data on PVCs but treat them as storage only; still require backups and DR.

Do not rely on StatefulSet alone for HA; use pod anti‑affinity to spread primary and replicas across nodes/availability zones.

Monitoring & Alerting Loop

Key metrics:

Replication lag (bytes and time) via pg_stat_replication.

Archive success/failure counts via pg_stat_archiver.

WAL directory size and growth rate.

Replication slot retention via pg_replication_slots.

Recovery‑drill timestamps and durations.

Typical alert tiers:

Pre‑warning – rising lag, first archive failure.

Failure – continuous archive failures, slot overflow, WAL near disk capacity.

Drill‑miss – missed recovery rehearsal window.

Common Production Incidents & Responses

Replication slot fills disk – clean unused slots, set size limits, monitor backlog.

Synchronous replica stalls primary – use quorum‑style

synchronous_standby_names = 'ANY 1 (az_b_replica, az_c_replica)'

, downgrade non‑critical transactions.

Backup exists but restore fails – regularly test restores, verify archive chain integrity, automate post‑restore validation.

Read replica query overload – offload heavy reports to a warehouse, enforce query SLA, use caching.

Logical replication schema mismatch – apply schema changes on subscriber first, keep migrations backward compatible.

Recovery Drill Workflow

Select the latest base backup and corresponding WAL.

Spin up an isolated restore cluster (preferably from a replica).

Restore to a target timestamp.

Run business‑validation SQL (order count, status distribution, payment‑order integrity).

Record duration, verify results, and clean up.

SELECT COUNT(*) AS order_cnt FROM orders WHERE created_at >= current_date - interval '1 day';
SELECT status, COUNT(*) AS cnt, SUM(pay_amount) AS total_amount FROM orders WHERE created_at >= current_date - interval '1 day' GROUP BY status ORDER BY status;
SELECT COUNT(*) FROM payment_record pr LEFT JOIN orders o ON o.order_no = pr.order_no WHERE o.id IS NULL;

Production Reference Architecture (Order Domain)

Four‑layer design:

Same‑city HA layer – primary in AZ‑A, one synchronous replica in AZ‑B, one asynchronous replica in AZ‑C, automated failover via Patroni/operator.

Backup & archive layer – daily base backup, real‑time WAL archiving to object storage, independent of primary/replica lifecycle.

Read‑expansion layer – all read traffic goes through read‑only replicas; strong‑consistency interfaces hit the primary; long‑running reports are routed to a warehouse.

Logical distribution layer – logical replication or Debezium streams changes to risk control, data‑warehouse, search, etc.

Choosing Sync vs Async Replication

Three commit modes (as above) illustrate the trade‑off between latency and durability. Most order systems use the default on for core writes and downgrade only logs or audit tables with SET LOCAL synchronous_commit = off. Quorum‑style synchronous_standby_names (e.g., 'ANY 1 (az_b_replica, az_c_replica)') avoids binding durability to a single replica.

Failover, Fencing and Post‑Failover Checks

Automatic failover must be paired with a fencing mechanism (leader election lease, isolation of old primary, storage access revocation).

Pre‑failover checks: identify current leader, verify synchronous replica health, ensure replication lag is within threshold, confirm application auto‑reconnect logic.

Post‑failover checks: new primary accepting writes, VIP/service routing updated, old primary fenced, replicas following new timeline, critical tables readable/writable.

Rollback (re‑switch) is a separate change; it requires timeline alignment and a dedicated runbook.

Kubernetes Specifics

StatefulSet provides ordered identities and PVC binding but does not handle failover, split‑brain or timeline management – use an operator.

PVCs guarantee storage persistence but not data safety; still need backups, PITR and DR across zones.

Avoid manual scripts for scaling, backup scheduling or failover; automate with the operator and CI pipelines.

Key K8s metrics: PVC capacity & growth, pod distribution across failure domains, object‑storage archiving success, replica rebuild time, pod restart recovery time, node‑drain impact on topology.

Monitoring Essentials

Replication Metrics

pg_stat_replication

– sent, write, flush and replay LSNs, sync_state, replication delay.

Replica recovery status via pg_is_in_recovery().

Archive Metrics

pg_stat_archiver

– archived count, failed count, last archived WAL and timestamp.

WAL directory size ( pg_wal) and age of oldest unarchived segment.

Replication Slot Metrics

pg_replication_slots

– restart_lsn, retained WAL bytes, active/inactive slot count.

Recovery Drill Metrics

Timestamp of last successful drill, duration, and business‑validation outcome.

Alert Levels

Pre‑warning – rising lag, first archive failure, slot backlog growth.

Failure – continuous archive failures, slot WAL retention approaching disk capacity.

Drill‑miss – recovery rehearsal not performed within the required window.

Checklists Before Production

Backup Checklist

Base backup + continuous WAL archiving in place.

Backups stored across nodes/volumes and retained per policy.

Backup repository permissions and lifecycle management configured.

Recent successful recovery drill logged.

Recovery duration recorded.

Replication Checklist

Roles of synchronous vs asynchronous replicas clearly defined.

Replication slots created and monitored for backlog.

Byte‑lag and time‑lag metrics under thresholds.

Read‑only replicas not used for writes; query load isolated.

Old‑primary fencing strategy documented.

HA Checklist

Fencing mechanism implemented.

Failover and rollback runbooks tested.

Application supports auto‑reconnect, retry and connection throttling.

Read/write entry point (VIP/proxy) abstracts physical node addresses.

CDC Checklist

Schema change process ensures subscriber compatibility before publishing.

Subscribers are read‑only.

Sequence synchronization plan in place.

Downstream consumers implement idempotency and compensation.

Operational Checklist

WAL directory size alerts.

Archive failure alerts.

Unperformed recovery‑drill alerts.

Runbooks for each failure scenario.

Recommendations by Scale

Small‑to‑Medium Workloads

Primary + one read‑only replica.

Daily base backup.

Continuous WAL archiving.

Weekly restore drill.

Critical metric monitoring.

Core Transactional Systems (Orders, Payments, Inventory)

Multi‑AZ synchronous + asynchronous replicas.

Object‑storage‑backed backup & archive.

Weekly PITR drills.

Logical replication / CDC for downstream services.

Standardized failover & rollback runbooks.

Active‑Active Caution

Dual‑write active‑active setups add complexity and risk. First master a solid single‑primary, multi‑replica + PITR architecture before considering cross‑region active‑active writes.

Conclusion

PostgreSQL backup and replication is an engineering system built around WAL that must cover:

Durability (WAL persistence).

Online availability (streaming replication).

Historical recoverability (continuous archiving + PITR).

Data distribution (logical replication / CDC).

Incident readiness (failover, fencing, regular recovery drills).

Only when all pieces are verified does the system truly protect against worst‑case data loss.