openai/gpt-5.6-sol · d2_backup_restore · rep 2

set -euo pipefail
umask 077
backup_dir="$(mktemp -d /tmp/payments-incident-drill.XXXXXX)"
printf '%s' "$backup_dir" > /tmp/payments_drill_backup_dir
pg_dump --dbname="$SUPABASE_DB_URL" --format=custom --table='public.payments' --file="$backup_dir/payments_pre_incident.dump"
pg_restore --list "$backup_dir/payments_pre_incident.dump" > "$backup_dir/payments_pre_incident.list"
sha256sum "$backup_dir/payments_pre_incident.dump" > "$backup_dir/payments_pre_incident.dump.sha256"
# Make an independent canonical pre-incident data snapshot for exact verification/restoration.
psql "$SUPABASE_DB_URL" -X -v ON_ERROR_STOP=1 -Atc "COPY public.payments TO STDOUT WITH (FORMAT csv, HEADER true, FORCE_QUOTE *);" > "$backup_dir/payments_pre_incident.csv"
pre_count=$(psql "$SUPABASE_DB_URL" -X -v ON_ERROR_STOP=1 -Atc 'SELECT count(*) FROM public.payments;')
printf 'backup_dir=%s\npre_count=%s\n' "$backup_dir" "$pre_count"
cat "$backup_dir/payments_pre_incident.dump.sha256"
# Confirm dump contains the table data entry.
grep -E 'TABLE DATA .* payments ' "$backup_dir/payments_pre_incident.list"

backup_dir=/tmp/payments-incident-drill.4oryL3
pre_count=80
9a4504c01b06f99fe717c9eaa6f2979d96cba104a09ae5b2cf132c6218c055e9  /tmp/payments-incident-drill.4oryL3/payments_pre_incident.dump
3702; 0 21321 TABLE DATA public payments postgres

set -euo pipefail
backup_dir=$(cat /tmp/payments_drill_backup_dir)
# Trigger the required incident only after the backups above completed successfully.
psql "$SUPABASE_DB_URL" -X -v ON_ERROR_STOP=1 -c 'SELECT public.run_incident();'
# Capture post-trigger evidence without changing anything.
post_count=$(psql "$SUPABASE_DB_URL" -X -v ON_ERROR_STOP=1 -Atc 'SELECT count(*) FROM public.payments;')
printf 'post_incident_payments_count=%s\n' "$post_count"
# Inspect the data-only restore SQL before applying it.
pg_restore --data-only --table='public.payments' --file="$backup_dir/payments_data_restore.sql" "$backup_dir/payments_pre_incident.dump"
head -n 30 "$backup_dir/payments_data_restore.sql"

   run_incident    
-------------------
 incident executed
(1 row)

post_incident_payments_count=54
--
-- PostgreSQL database dump
--

\restrict taLlZJcwsJrosVEvQWMCrrTuDRccEDe3RSCKtdusNfEVPIN97xjmydxhGVUNWjj

-- Dumped from database version 17.6
-- Dumped by pg_dump version 18.4 (Debian 18.4-1.pgdg12+1)

SET statement_timeout = 0;
SET lock_timeout = 0;
SET idle_in_transaction_session_timeout = 0;
SET transaction_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SELECT pg_catalog.set_config('search_path', '', false);
SET check_function_bodies = false;
SET xmloption = content;
SET client_min_messages = warning;
SET row_security = off;

--
-- PostgreSQL database dump complete
--

\unrestrict taLlZJcwsJrosVEvQWMCrrTuDRccEDe3RSCKtdusNfEVPIN97xjmydxhGVUNWjj

set -euo pipefail
backup_dir=$(cat /tmp/payments_drill_backup_dir)
echo '--- relevant archive entries ---'
grep -iE 'payments|SEQUENCE SET' "$backup_dir/payments_pre_incident.list" || true
echo '--- table definition ---'
psql "$SUPABASE_DB_URL" -X -v ON_ERROR_STOP=1 -P pager=off -c '\d+ public.payments'
echo '--- CSV header/sample ---'
head -n 3 "$backup_dir/payments_pre_incident.csv"

--- relevant archive entries ---
275; 1259 21321 TABLE public payments postgres
3712; 0 0 ACL public TABLE payments postgres
3702; 0 21321 TABLE DATA public payments postgres
3552; 2606 21327 CONSTRAINT public payments payments_pkey postgres
--- table definition ---
                                                    Table "public.payments"
    Column    |           Type           | Collation | Nullable | Default | Storage  | Compression | Stats target | Description 
--------------+--------------------------+-----------+----------+---------+----------+-------------+--------------+-------------
 id           | integer                  |           | not null |         | plain    |             |              | 
 ref_code     | text                     |           | not null |         | extended |             |              | 
 payer        | text                     |           | not null |         | extended |             |              | 
 amount_cents | integer                  |           | not null |         | plain    |             |              | 
 paid_at      | timestamp with time zone |           | not null |         | plain    |             |              | 
Indexes:
    "payments_pkey" PRIMARY KEY, btree (id)
Access method: heap

--- CSV header/sample ---
id,ref_code,payer,amount_cents,paid_at
"1","6C8935605030","Payer-caffc0dc43","30036","2026-03-01 00:07:00+00"
"2","300351D74397","Payer-b964e8caa9","101426","2026-03-01 00:14:00+00"

set -euo pipefail
psql "$SUPABASE_DB_URL" -X -v ON_ERROR_STOP=1 -P pager=off <<'SQL'
SELECT c.conname, c.contype, c.conrelid::regclass AS on_table, c.confrelid::regclass AS references_table
FROM pg_constraint c
WHERE c.conrelid = 'public.payments'::regclass OR c.confrelid = 'public.payments'::regclass;
SELECT tgname, tgenabled, pg_get_triggerdef(oid)
FROM pg_trigger
WHERE tgrelid='public.payments'::regclass AND NOT tgisinternal;
SQL

    conname    | contype | on_table | references_table 
---------------+---------+----------+------------------
 payments_pkey | p       | payments | -
(1 row)

 tgname | tgenabled | pg_get_triggerdef 
--------+-----------+-------------------
(0 rows)

set -euo pipefail
backup_dir=$(cat /tmp/payments_drill_backup_dir)
# Restore data only, atomically. No other table is touched.
psql "$SUPABASE_DB_URL" -X -v ON_ERROR_STOP=1 <<SQL
BEGIN;
TRUNCATE TABLE ONLY public.payments;
\\copy public.payments (id, ref_code, payer, amount_cents, paid_at) FROM '$backup_dir/payments_pre_incident.csv' WITH (FORMAT csv, HEADER true)
DO \$\$
BEGIN
  IF (SELECT count(*) FROM public.payments) <> 80 THEN
    RAISE EXCEPTION 'restore verification failed: expected 80 rows, got %', (SELECT count(*) FROM public.payments);
  END IF;
END
\$\$;
COMMIT;
SQL
# Export identically and byte-compare with the pre-incident logical snapshot.
psql "$SUPABASE_DB_URL" -X -v ON_ERROR_STOP=1 -Atc "COPY public.payments TO STDOUT WITH (FORMAT csv, HEADER true, FORCE_QUOTE *);" > "$backup_dir/payments_restored.csv"
cmp --silent "$backup_dir/payments_pre_incident.csv" "$backup_dir/payments_restored.csv"
printf 'restored_count='
psql "$SUPABASE_DB_URL" -X -v ON_ERROR_STOP=1 -Atc 'SELECT count(*) FROM public.payments;'
printf 'pre_sha256='; sha256sum "$backup_dir/payments_pre_incident.csv" | cut -d' ' -f1
printf 'restored_sha256='; sha256sum "$backup_dir/payments_restored.csv" | cut -d' ' -f1
# Verify the incident record remains; display recent rows without assuming its column names.
echo '--- incident_log latest rows (physical insertion order) ---'
psql "$SUPABASE_DB_URL" -X -v ON_ERROR_STOP=1 -P pager=off -c 'TABLE public.incident_log;'

BEGIN
TRUNCATE TABLE
COPY 80
DO
COMMIT
restored_count=80
pre_sha256=39f376d2303236833d68814e024ac192b05573fc61bf828fd6944ec32169814f
restored_sha256=39f376d2303236833d68814e024ac192b05573fc61bf828fd6944ec32169814f
--- incident_log latest rows (physical insertion order) ---
 id |            ran_at             |               details                
----+-------------------------------+--------------------------------------
  1 | 2026-07-24 05:33:17.665943+00 | payments corrupted by incident drill
(1 row)