Backup, Replication and Availability

Jr Codex DBMS Notes

Level: Advanced Prerequisites: Chapter 2: Checkpoints and Crash Recovery Time to complete: ~20 minutes

Backups Are Not Replicas
Kinds of Backup
Point-in-Time Recovery
Replication
Synchronous vs Asynchronous
Failover and Its Hazards
Summary & Next Steps

1. Backups Are Not Replicas

The Distinction That Costs Companies Their Data
─────────────────────────────────────────
  REPLICATION protects against HARDWARE failure.
    A machine dies; another has the same data.

  BACKUP protects against LOGICAL failure.
    Someone runs DELETE FROM orders with no WHERE.
    A bad migration drops a column. Ransomware
    encrypts the volume.

  In every one of those cases, replication
  FAITHFULLY AND INSTANTLY COPIES THE DAMAGE to
  every replica.

  You need both. They are not alternatives.
─────────────────────────────────────────

The Recovery Objectives
─────────────────────────────────────────
  RPO  Recovery Point Objective
       How much data may you lose?
       "At most 5 minutes."

  RTO  Recovery Time Objective
       How long may recovery take?
       "Back online within 1 hour."

  Every design below is a different point on the
  RPO/RTO plane, at a different cost. Decide these
  two numbers before choosing an architecture.
─────────────────────────────────────────

2. Kinds of Backup

Three Types
─────────────────────────────────────────
  LOGICAL
    A dump of SQL statements or data.
    pg_dump, mysqldump.
    + portable across versions and platforms;
      selective (one table)
    - SLOW to restore on large databases — it
      re-executes every insert and rebuilds every
      index

  PHYSICAL
    A copy of the data files themselves.
    pg_basebackup, Percona XtraBackup.
    + fast to restore; the basis for PITR
    - version- and platform-specific; all or
      nothing

  SNAPSHOT
    A filesystem or storage-layer snapshot.
    LVM, ZFS, EBS.
    + near-instant to take
    - must be CRASH-CONSISTENT: the database must
      be able to recover from it as though from a
      crash (Chapter 2), so the snapshot must be
      atomic across all volumes
─────────────────────────────────────────

# Logical
pg_dump -Fc -d mydb -f mydb.dump          # custom format: compressed, parallel restore
pg_restore -d mydb_restored -j 4 mydb.dump
 
# Physical, and the basis of PITR
pg_basebackup -D /backup/base -Ft -z -P --wal-method=stream

FULL / INCREMENTAL / DIFFERENTIAL
─────────────────────────────────────────
  FULL          everything
  DIFFERENTIAL  changes since the last FULL
  INCREMENTAL   changes since the last backup of
                any kind

  A common schedule: weekly full, daily
  differential, continuous WAL archiving.

  Restore cost rises as you rely on more
  increments — which is a real RTO consideration,
  not a detail.
─────────────────────────────────────────

3. Point-in-Time Recovery

The single most valuable reliability feature, and it falls straight out of Chapter 1's log.

How It Works
─────────────────────────────────────────
  1. Take a PHYSICAL base backup on Sunday.
  2. ARCHIVE every WAL segment continuously
     thereafter.

  Now you can restore to ANY INSTANT:
    - restore the base backup
    - replay archived WAL up to the chosen moment
    - stop exactly there

  "Restore the database to 14:22:59, one second
   before the bad migration ran."
─────────────────────────────────────────

# postgresql.conf
archive_mode = on
archive_command = 'test ! -f /archive/%f && cp %p /archive/%f'

# recovery target, set at restore time
restore_command = 'cp /archive/%f %p'
recovery_target_time = '2026-09-02 14:22:59'
recovery_target_action = 'promote'

Why It Changes the Risk Picture
─────────────────────────────────────────
  Nightly backups alone mean up to 24 hours of
  data loss on a logical failure.

  PITR means seconds — because the log already
  records every change (Chapter 1), and archiving
  it costs almost nothing beyond storage.

  If you implement one thing from this chapter,
  implement WAL archiving.
─────────────────────────────────────────

The Rule Nobody Follows Until It Is Too Late
─────────────────────────────────────────
  AN UNTESTED BACKUP IS NOT A BACKUP.

  Restore it, on a schedule, to a real machine, and
  verify the data. Common failures found only by
  testing:

    - the archive_command silently failing for
      weeks
    - a missing WAL segment breaking the chain
    - a restore that takes 14 hours against a
      1-hour RTO
    - nobody knowing the procedure
─────────────────────────────────────────

4. Replication

The Two Models
─────────────────────────────────────────
  PHYSICAL (streaming)
    Ship the WAL to a replica, which replays it.
    A byte-identical copy.
    + low overhead, replicas are read-only and
      exactly consistent
    - same version and platform; ALL or nothing

  LOGICAL
    Ship row-level changes as decoded events.
    + selective tables; cross-version; the target
      can differ in schema or even be another
      system
    - higher overhead; needs replica identity
      (usually a primary key)
─────────────────────────────────────────

Topologies
─────────────────────────────────────────
  PRIMARY-REPLICA
    One writer, many read-only replicas.
    Simple, and the correct default.
    Read scaling is easy; write scaling is not.

  MULTI-PRIMARY
    Several writable nodes.
    Requires CONFLICT RESOLUTION — two nodes may
    update the same row simultaneously, and
    someone must decide who wins.
    Avoid unless you have a specific need and have
    read Module 9.
─────────────────────────────────────────

-- Logical replication in PostgreSQL: publish on the primary...
CREATE PUBLICATION orders_pub FOR TABLE orders, order_items;
 
-- ...subscribe on the target.
CREATE SUBSCRIPTION orders_sub
    CONNECTION 'host=primary dbname=mydb'
    PUBLICATION orders_pub;

5. Synchronous vs Asynchronous

The Trade
─────────────────────────────────────────
  ASYNCHRONOUS
    The primary commits and acknowledges the client
    immediately; the replica catches up.
    + no added write latency
    - a primary failure loses whatever had not
      shipped. RPO > 0.

  SYNCHRONOUS
    The primary waits for the replica to confirm
    before acknowledging.
    + RPO = 0 for a single node loss
    - every commit pays a network round trip
    - if the replica is DOWN, writes on the primary
      BLOCK — availability is now coupled to the
      replica
─────────────────────────────────────────

-- PostgreSQL: choose per-transaction, which is the useful part.
SET synchronous_commit = 'remote_apply';   -- strongest: replica has APPLIED it
SET synchronous_commit = 'on';             -- replica has FLUSHED it
SET synchronous_commit = 'local';          -- primary only; do not wait for replicas
SET synchronous_commit = 'off';            -- do not even wait locally (Chapter 1)

The Practical Configuration
─────────────────────────────────────────
  Use SYNCHRONOUS for the transactions that matter
  — payments, orders — and ASYNCHRONOUS for the
  rest.

  Setting it per transaction means you pay the
  round trip only where the data justifies it,
  rather than choosing one global answer that is
  wrong for half your workload.

  And with synchronous replication, configure at
  least TWO candidate replicas, so one being down
  does not halt all writes.
─────────────────────────────────────────

6. Failover and Its Hazards

The Sequence
─────────────────────────────────────────
  1. DETECT the primary is unreachable
  2. PROMOTE a replica to primary
  3. REDIRECT clients to it
  4. FENCE the old primary so it cannot accept
     writes if it returns
─────────────────────────────────────────

SPLIT-BRAIN
─────────────────────────────────────────
  The failure mode that destroys data.

  The old primary was not dead — only unreachable
  (a network partition). It keeps accepting writes.
  The new primary also accepts writes.

  Two divergent histories. When the partition
  heals, there is no correct merge: both sets of
  writes were acknowledged to clients.

  PREVENTION
    - QUORUM: a node only serves as primary if it
      can see a majority. A minority partition
      demotes itself.
    - FENCING / STONITH: forcibly isolate or power
      off the old primary before promoting.
    - Never fail over automatically without one of
      these.
─────────────────────────────────────────

REPLICATION LAG
─────────────────────────────────────────
  Asynchronous replicas are behind. So:

    write to primary, then immediately read from a
    replica ──► the write may not be there yet

  This surprises users: they save a form and the
  next page shows the old value.

  FIXES
    - route reads-after-write to the PRIMARY
    - track the write's LSN and wait for the
      replica to reach it
    - accept it where staleness is harmless
      (listings, analytics)
─────────────────────────────────────────

-- Monitor lag before it becomes an incident.
SELECT client_addr, state,
       pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn) AS lag_bytes,
       replay_lag
FROM pg_stat_replication;

7. Summary & Next Steps

Key Takeaways

Replication protects against hardware failure and faithfully copies logical damage; backups protect against logical failure — you need both, and they are not substitutes.
Point-in-time recovery falls directly out of the write-ahead log and reduces potential data loss from a day to seconds, making WAL archiving the highest-value reliability measure.
An untested backup is not a backup: silent archive failures, broken WAL chains and unmeetable restore times are found only by rehearsing the restore.
Synchronous replication gives RPO zero at the cost of a round trip and couples availability to the replica; setting it per transaction pays that cost only where it is justified.

Module 8 Complete — What's Next

You now understand how a database survives both a crash and a catastrophe. Module 9 asks what happens when one machine is no longer enough — and what relational guarantees you have to give up to get there.

Concept Check

Why does having three replicas not protect you from DELETE FROM orders with no WHERE?
What does WAL archiving add beyond a nightly physical backup, and why is it nearly free?
What is split-brain, and why is automatic failover without quorum or fencing dangerous?

Next Module

→ Module 9: Beyond Relational

Jr Codex — 1-on-1 Personalized Coaching | Back to Module Index | Back to DBMS Index