A WAL Recovery Walkthrough

=====================================================================
  MINUTE 0 — STOP THE BLEEDING
=====================================================================

The single most important action, and the one most often skipped.
Every transaction that runs from now on makes the situation harder.

# Stop the application. Whichever applies:
sudo systemctl stop myapp

# If you cannot stop the app quickly, cut it off at the database instead:
psql -U postgres -c "REVOKE CONNECT ON DATABASE shop FROM app_user;"
psql -U postgres -c "
  SELECT pg_terminate_backend(pid)
  FROM pg_stat_activity
  WHERE datname = 'shop' AND usename = 'app_user';"

# Announce it. Recovery under the assumption that nobody else is
# 'just trying something' is much safer.

=====================================================================
  MINUTE 2 — ESTABLISH EXACTLY WHAT HAPPENED AND WHEN
=====================================================================

You need a timestamp accurate to the second. Guessing costs you
extra recovery attempts.

# The PostgreSQL log, if log_min_duration_statement or log_statement is on:
sudo grep -n "DELETE FROM orders" /var/log/postgresql/postgresql-2026-09-23.log

# 2026-09-23 14:35:12.431 IST [28471] app_user@shop LOG:
#     duration: 8241.117 ms  statement: DELETE FROM orders;
#                     ^ it committed at roughly 14:35:20

-- If you have an audit table (see the triggers lesson), it is authoritative:
SELECT min(changed_at) AS first_delete,
       max(changed_at) AS last_delete,
       count(*)        AS rows_deleted
FROM orders_audit
WHERE operation = 'DELETE'
  AND changed_at > now() - interval '1 hour';

-- first_delete = 2026-09-23 14:35:12.431+05:30

-- Confirm the damage and its boundary:
SELECT count(*) FROM orders;                      -- 0
SELECT count(*) FROM order_items;                 -- also gone? (ON DELETE CASCADE)

-- DECISION: recover to 14:35:10 — two seconds before the statement started.

=====================================================================
  MINUTE 5 — PRESERVE EVERYTHING BEFORE CHANGING ANYTHING
=====================================================================

!! A recovery attempt can destroy the current state.
!! The current state may contain data committed AFTER the incident
!! that you will want to merge back in later.

df -h                                   # confirm there is room for a full copy

sudo systemctl stop postgresql

STAMP=$(date +%F_%H%M)
sudo cp -a /var/lib/postgresql/16/main \
           /var/lib/postgresql/16/main.broken.$STAMP

# The WAL in pg_wal may not be archived yet and holds the most recent
# transactions. Copy it separately:
sudo mkdir -p /backup/pg_wal_rescue_$STAMP
sudo cp -a /var/lib/postgresql/16/main/pg_wal/* /backup/pg_wal_rescue_$STAMP/

# Force any unarchived segments into the archive so recovery can reach
# right up to the incident:
sudo ls /var/lib/postgresql/16/main/pg_wal/ | head

=====================================================================
  MINUTE 10 — CHECK THE RECOVERY CHAIN IS COMPLETE
=====================================================================

Verify BEFORE starting, not halfway through.

# Is the base backup there, and when was it taken?
ls -lh /backup/base_2026-09-21/
cat /backup/base_2026-09-21/TAKEN_AT        # 2026-09-21T02:00:11+05:30

# Are the WAL segments continuous from then until now?
ls /archive/ | sort | head -3
ls /archive/ | sort | tail -3
ls /archive/ | wc -l

# Segment names increment in hex. A gap means recovery WILL stop there.
# A quick sanity check for an obvious hole:
ls /archive/ | sort | awk '{print}' | uniq | wc -l

# Confirm the archive covers past 14:35 on Wednesday:
ls -l --time-style=full-iso /archive/ | tail -5

=====================================================================
  MINUTE 15 — RESTORE THE BASE BACKUP (into a SEPARATE directory)
=====================================================================

sudo mkdir -p /var/lib/postgresql/16/recovery
sudo chown postgres:postgres /var/lib/postgresql/16/recovery
sudo chmod 700 /var/lib/postgresql/16/recovery

sudo -u postgres tar -xzf /backup/base_2026-09-21/base.tar.gz \
    -C /var/lib/postgresql/16/recovery

sudo -u postgres tar -xzf /backup/base_2026-09-21/pg_wal.tar.gz \
    -C /var/lib/postgresql/16/recovery/pg_wal

# Expect this to take a while on a large database. It is a file copy.
du -sh /var/lib/postgresql/16/recovery

# =====================================================================
#   MINUTE 25 — CONFIGURE THE RECOVERY TARGET
# =====================================================================

sudo -u postgres tee -a /var/lib/postgresql/16/recovery/postgresql.conf > /dev/null <<'EOF'

# --- recovery ---
restore_command        = 'cp /archive/%f %p'
recovery_target_time   = '2026-09-23 14:35:10+05:30'
recovery_target_action = 'pause'
EOF

# Never let this instance be reached by the application by accident:
sudo -u postgres sed -i "s/^#*port.*/port = 5433/" \
    /var/lib/postgresql/16/recovery/postgresql.conf

sudo -u postgres touch /var/lib/postgresql/16/recovery/recovery.signal

# =====================================================================
#   MINUTE 30 — RUN THE RECOVERY
# =====================================================================

sudo -u postgres /usr/lib/postgresql/16/bin/pg_ctl \
    -D /var/lib/postgresql/16/recovery \
    -l /tmp/recovery_$STAMP.log start

tail -f /tmp/recovery_$STAMP.log

Expected progress:

LOG:  starting point-in-time recovery to 2026-09-23 14:35:10+05:30
LOG:  restored log file "000000010000000000000041" from archive
LOG:  restored log file "000000010000000000000042" from archive
...                                    (this is the slow part)
LOG:  recovery stopping before commit of transaction 48573922,
      time 2026-09-23 14:35:12.431+05:30
LOG:  pausing at the end of recovery
HINT: Execute pg_wal_replay_resume() to promote.

If instead you see:
  FATAL: could not restore file "0000000100000000000000XX"
      -> a GAP in the archive. Recovery cannot pass it.
         The best achievable target is just before that segment.

-- =====================================================================
--   MINUTE 50 — VERIFY. DO NOT SKIP. DO NOT RUSH.
-- =====================================================================
-- The instance is paused and READ-ONLY. Nothing is committed yet.

psql -p 5433 -U postgres -d shop

-- 1. Is the deleted data back?
SELECT count(*) FROM orders;                 -- 1204118  (expected ~1.2M)

-- 2. Is it recent enough? Did we get Tuesday and Wednesday morning's work?
SELECT max(placed_at) FROM orders;           -- 2026-09-23 14:33:47+05:30

-- 3. Did related data come back consistently?
SELECT count(*) FROM order_items;
SELECT count(*) FROM orders o
WHERE NOT EXISTS (SELECT 1 FROM order_items i WHERE i.order_id = o.id);

-- 4. Spot-check something a human can recognise:
SELECT id, customer_id, total, placed_at
FROM orders ORDER BY id DESC LIMIT 10;

-- 5. How much legitimate work falls in the gap between our target
--    and the deletion? (Here: about 2 seconds. Acceptable.)

=====================================================================
  THE DECISION POINT
=====================================================================

TOO FAR BACK (missing hours of good work)?
    -> stop the instance, RAISE recovery_target_time, delete the
       recovery directory, restore the base backup again, retry.

TOO FAR FORWARD (the DELETE is included — orders is empty)?
    -> stop, LOWER recovery_target_time, retry.

LOOKS RIGHT?
    -> promote.

This retry loop is only possible because we recovered into a separate
directory and preserved the original. That decision, made at minute 5,
is what makes a wrong guess cheap.

-- =====================================================================
--   MINUTE 55 — PROMOTE
-- =====================================================================

SELECT pg_wal_replay_resume();
SELECT pg_is_in_recovery();            -- false  = writable, new timeline

# =====================================================================
#   MINUTE 60 — SWITCH OVER, THEN IMMEDIATELY RE-PROTECT
# =====================================================================

sudo -u postgres /usr/lib/postgresql/16/bin/pg_ctl \
    -D /var/lib/postgresql/16/recovery stop

sudo mv /var/lib/postgresql/16/main /var/lib/postgresql/16/main.old.$STAMP
sudo mv /var/lib/postgresql/16/recovery /var/lib/postgresql/16/main

# Put the port back to 5432 and remove the recovery settings you appended.
sudo -u postgres sed -i "s/^port = 5433/port = 5432/" \
    /var/lib/postgresql/16/main/postgresql.conf

sudo systemctl start postgresql

# 1. Statistics did NOT survive. Without this the site will be slow.
psql -U postgres -d shop -c "ANALYZE;"

# 2. NEW TIMELINE -> the old base backup is no longer a valid chain.
#    Until this finishes you have NO working recovery.
pg_basebackup -D /backup/base_$(date +%F) -Ft -z -Xs -P

# 3. Confirm archiving is working again on the new timeline:
psql -U postgres -c "SELECT * FROM pg_stat_archiver;"

# 4. Restore application access and restart it.
psql -U postgres -c "GRANT CONNECT ON DATABASE shop TO app_user;"
sudo systemctl start myapp

=====================================================================
  AFTERWARDS
=====================================================================

  * Keep main.broken.* and main.old.* for several days. There may be
    transactions committed between 14:35:10 and the shutdown that
    someone wants recovered by hand from the broken copy.

  * Write down: what happened, the timeline, what was lost
    (here: ~2 seconds of orders, plus everything between the incident
    and the shutdown), and what would have prevented it.

  * Fix the cause, not just the symptom:
      - the deployment script that ran an unguarded DELETE
      - the application role having DELETE rights it did not need
      - no restore point taken before the deployment
      - six minutes of writes after the incident before anyone noticed