Run Failure, Corruption, Restore, Failover, and Performance Drills—and Defend the Design

SET ROLE servicehub_cap_owner;CREATE TABLE IF NOT EXISTS ops.ch24_drill_log (  drill_id bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,  drill_name text NOT NULL,  started_at timestamptz NOT NULL,  detected_at timestamptz,  recovered_at timestamptz,  expected_result text NOT NULL,  observed_result text,  data_correct boolean,  rpo_seconds numeric,  rto_seconds numeric,  notes text);RESET ROLE;

date -upg_ctl -D "$CH24_PRIMARY" stop -m immediate# Observe the process is down, then restart.pg_ctl -D "$CH24_PRIMARY" \  -l "$CH24_ROOT/primary.log" \  -o "-p 55480 -c listen_addresses=127.0.0.1" starttail -n 80 "$CH24_ROOT/primary.log"

Get-Date -AsUTCpg_ctl.exe -D $env:CH24_PRIMARY stop -m immediatepg_ctl.exe -D $env:CH24_PRIMARY `  -l (Join-Path $env:CH24_ROOT "primary.log") `  -o "-p 55480 -c listen_addresses=127.0.0.1" startGet-Content (Join-Path $env:CH24_ROOT "primary.log") -Tail 80

SELECT pg_is_in_recovery();SELECT count(*) AS orders,       count(*) FILTER (WHERE status NOT IN         ('queued','assigned','in_progress','completed','cancelled'))         AS invalid_statusFROM app.work_orders;SELECT conname,convalidatedFROM pg_constraintWHERE conrelid='app.work_orders'::regclassORDER BY conname;

BEGIN;UPDATE app.work_ordersSET note = note || '-lock-drill'WHERE tenant_id = (  SELECT tenant_id FROM app.tenants WHERE tenant_code='tenant-a')AND work_order_id = (  SELECT min(work_order_id)  FROM app.work_orders  WHERE tenant_id = (    SELECT tenant_id FROM app.tenants WHERE tenant_code='tenant-a'  ));-- Keep this transaction open until diagnosis is complete.

SET lock_timeout='3s';UPDATE app.work_ordersSET amount = amount + 0.01WHERE tenant_id = (  SELECT tenant_id FROM app.tenants WHERE tenant_code='tenant-a')AND work_order_id = (  SELECT min(work_order_id)  FROM app.work_orders  WHERE tenant_id = (    SELECT tenant_id FROM app.tenants WHERE tenant_code='tenant-a'  ));

SELECT a.pid,a.state,a.wait_event_type,a.wait_event,       now()-a.xact_start AS xact_age,       pg_blocking_pids(a.pid) AS blocking_pids,       a.query_idFROM pg_stat_activity AS aWHERE a.datname=current_database()ORDER BY a.xact_start NULLS LAST;

SELECT pg_wal_replay_pause();SELECT pg_get_wal_replay_pause_state(),       pg_last_wal_receive_lsn(),       pg_last_wal_replay_lsn();

INSERT INTO app.work_orders(tenant_id,customer_id,external_ref,status,scheduled_at,amount,note,priority)SELECT c.tenant_id,c.customer_id,       'LAG-' || g,       'queued',       clock_timestamp() + g * INTERVAL '1 second',       25.00,'lag-drill',3FROM app.customers AS cCROSS JOIN generate_series(1,1000) AS gWHERE c.tenant_id=(SELECT tenant_id FROM app.tenants WHERE tenant_code='tenant-a')  AND c.customer_id=(SELECT min(customer_id) FROM app.customers                     WHERE tenant_id=c.tenant_id)ON CONFLICT (tenant_id,external_ref) DO NOTHING;SELECT application_name,state,       sent_lsn,write_lsn,flush_lsn,replay_lsn,       pg_size_pretty(pg_wal_lsn_diff(sent_lsn,replay_lsn)) AS replay_byte_lagFROM pg_stat_replication;SELECT slot_name,active,restart_lsn,wal_status,safe_wal_sizeFROM pg_replication_slotsWHERE slot_name='ch24_standby_slot';

SELECT pg_wal_replay_resume();SELECT pg_get_wal_replay_pause_state(),       pg_last_wal_receive_lsn(),       pg_last_wal_replay_lsn();

SELECT pg_create_restore_point('ch24_drill_before_delete');SELECT count(*) AS before_rowsFROM app.work_ordersWHERE status='completed';DELETE FROM app.work_ordersWHERE status='completed'  AND created_at < TIMESTAMPTZ '2026-09-01 00:00+00';SELECT count(*) AS after_rowsFROM app.work_ordersWHERE status='completed';

CORRUPT="$CH24_ROOT/corrupt_backup"rm -rf "$CORRUPT"cp -a "$CH24_BACKUP" "$CORRUPT"REL_PATH="$(psql -h 127.0.0.1 -p 55480 -U postgres \  -d servicehub_capstone -Atc \  "SELECT pg_relation_filepath('app.work_orders');")"python3 - "$CORRUPT/$REL_PATH" <<'PY'from pathlib import Pathimport sysp=Path(sys.argv[1])with p.open("r+b") as f:    f.seek(min(9000, max(0, p.stat().st_size-1)))    b=f.read(1)    f.seek(-1,1)    f.write(bytes([(b[0] ^ 0x01) if b else 0x01]))PYpg_verifybackup "$CORRUPT" || true

$corrupt = Join-Path $env:CH24_ROOT "corrupt_backup"if (Test-Path $corrupt) { Remove-Item -Recurse -Force $corrupt }Copy-Item -Recurse $env:CH24_BACKUP $corrupt$rel = psql.exe -h 127.0.0.1 -p 55480 -U postgres `  -d servicehub_capstone -Atc `  "SELECT pg_relation_filepath('app.work_orders');"$target = Join-Path $corrupt $rel.Trim()$bytes = [IO.File]::ReadAllBytes($target)$offset = [Math]::Min(9000, $bytes.Length - 1)$bytes[$offset] = $bytes[$offset] -bxor 1[IO.File]::WriteAllBytes($target,$bytes)pg_verifybackup.exe $corrupt

# Add temporarily to the standby's postgresql.conf:shared_buffers = 'not-a-size'

pg_ctl -D "$CH24_STANDBY" stop -m fast# After adding the invalid line:pg_ctl -D "$CH24_STANDBY" \  -l "$CH24_ROOT/standby-config-error.log" \  -o "-p 55481" start || truetail -n 60 "$CH24_ROOT/standby-config-error.log"# Remove/comment the invalid line, then:pg_ctl -D "$CH24_STANDBY" \  -l "$CH24_ROOT/standby.log" \  -o "-p 55481" start

for i in $(seq 1 8); do  psql -h 127.0.0.1 -p 55480 -U postgres \    -d servicehub_capstone -c "      SELECT tenant_id,status,count(*),sum(amount)      FROM app.work_orders      GROUP BY tenant_id,status      ORDER BY tenant_id,status;" \    >/dev/null &donewait

$jobs = 1..8 | ForEach-Object {  Start-Job -ScriptBlock {    psql.exe -h 127.0.0.1 -p 55480 -U postgres `      -d servicehub_capstone -c `      "SELECT tenant_id,status,count(*),sum(amount)       FROM app.work_orders       GROUP BY tenant_id,status       ORDER BY tenant_id,status;" | Out-Null  }}$jobs | Wait-Job | Receive-Job | Out-Null$jobs | Remove-Job

SELECT state,count(*) AS sessionsFROM pg_stat_activityWHERE backend_type='client backend'GROUP BY state;SELECT wait_event_type,wait_event,count(*) AS backendsFROM pg_stat_activityWHERE state='active'GROUP BY wait_event_type,wait_eventORDER BY backends DESC;SELECT numbackends,deadlocks,temp_bytes,blk_read_time,blk_write_timeFROM pg_stat_databaseWHERE datname=current_database();

START RTO CLOCK[ ] Stop application writes / routing to primary[ ] Fence old primary so it cannot accept writes[ ] Record standby replay position and expected RPO impact[ ] Promote standby[ ] Verify pg_is_in_recovery() = false[ ] Run one business read/write invariant[ ] Reroute clients[ ] Verify new writes only on promoted node[ ] Keep old primary isolated[ ] Record timeline / rebuild planSTOP RTO CLOCKPass:- data correctness accepted- measured RPO <= 60 s target- measured RTO <= 15 min targetIf not met:- record exact step/time that breached the SLO; do not relabel the target.

SELECT current_database(),version();SELECT count(*) AS invalid_constraintsFROM pg_constraintWHERE connamespace='app'::regnamespace  AND NOT convalidated;SELECT count(*) AS invalid_indexesFROM pg_indexWHERE NOT indisvalid  AND indrelid IN (    SELECT oid FROM pg_class    WHERE relnamespace='app'::regnamespace  );SELECT relname,n_live_tup,n_dead_tup,last_autovacuum,last_autoanalyzeFROM pg_stat_user_tablesWHERE schemaname='app'ORDER BY relname;SELECT archived_count,failed_count,last_archived_time,last_failed_timeFROM pg_stat_archiver;SELECT slot_name,active,wal_status,safe_wal_sizeFROM pg_replication_slotsORDER BY slot_name;

-- Export ops.ch24_drill_log before destruction.SELECT * FROM ops.ch24_drill_log ORDER BY drill_id;-- Run the slot cleanup only on the node that owns the slot,-- only while it is isolated from application write routing.SELECT pg_drop_replication_slot('ch24_standby_slot')WHERE EXISTS (  SELECT 1  FROM pg_replication_slots  WHERE slot_name='ch24_standby_slot'    AND NOT active);

pg_ctl -D "$CH24_RESTORE" stop -m fast 2>/dev/null || truepg_ctl -D "$CH24_STANDBY" stop -m fast 2>/dev/null || truepg_ctl -D "$CH24_PRIMARY" stop -m fast 2>/dev/null || truerm -rf "$CH24_ROOT"

pg_ctl.exe -D $env:CH24_RESTORE stop -m fast 2>$nullpg_ctl.exe -D $env:CH24_STANDBY stop -m fast 2>$nullpg_ctl.exe -D $env:CH24_PRIMARY stop -m fast 2>$nullRemove-Item -Recurse -Force $env:CH24_ROOT