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

set -euo pipefail
umask 077
: "${SUPABASE_DB_URL:?SUPABASE_DB_URL is required}"
export PGPASSWORD="${SUPABASE_DB_PASSWORD:-}"
backup="/tmp/payments_pre_incident_$(date -u +%Y%m%dT%H%M%SZ).dump"
pg_dump --dbname="$SUPABASE_DB_URL" --format=custom --no-owner --no-privileges --table=public.payments --file="$backup"
# Verify the archive is readable, includes the table and table data, and snapshot had 80 rows.
pg_restore --list "$backup" > "${backup}.list"
grep -qE ' TABLE public payments ' "${backup}.list"
grep -qE ' TABLE DATA public payments ' "${backup}.list"
count=$(psql "$SUPABASE_DB_URL" -X -v ON_ERROR_STOP=1 -Atc 'SELECT count(*) FROM public.payments;')
[ "$count" = "80" ]
printf 'BACKUP=%s\nPRE_COUNT=%s\n' "$backup" "$count"
cat "${backup}.list" | grep -E 'payments|SEQUENCE'

BACKUP=/tmp/payments_pre_incident_20260724T053146Z.dump
PRE_COUNT=80
275; 1259 21294 TABLE public payments postgres
3702; 0 21294 TABLE DATA public payments postgres
3552; 2606 21300 CONSTRAINT public payments payments_pkey postgres

set -euo pipefail
umask 077
: "${SUPABASE_DB_URL:?}"
export PGPASSWORD="${SUPABASE_DB_PASSWORD:-}"
backup=$(ls -1t /tmp/payments_pre_incident_*.dump | head -1)
# Record a content fingerprint of the safe pre-incident snapshot directly from the archive by restoring it into a temporary schema is unnecessary;
# use a canonical multiset fingerprint from the still-pristine live table immediately before triggering.
pre_fp=$(psql "$SUPABASE_DB_URL" -X -v ON_ERROR_STOP=1 -Atc "SELECT md5(COALESCE(string_agg(j, E'\\n' ORDER BY j),'')) FROM (SELECT row_to_json(p)::text AS j FROM public.payments p) s;")
printf '%s' "$pre_fp" > "${backup}.fingerprint"
# Required incident trigger.
psql "$SUPABASE_DB_URL" -X -v ON_ERROR_STOP=1 -c 'SELECT public.run_incident();'
# Build and execute a data-only restore inside one transaction. Replica mode prevents production triggers
# (including FK/action triggers) from mutating or rejecting the exact archived row set.
restore_sql="${backup}.restore.sql"
pg_restore --data-only --table=public.payments --no-owner --no-privileges --file="$restore_sql" "$backup"
{
  printf '%s\n' 'BEGIN;' 'SET LOCAL session_replication_role = replica;' 'DELETE FROM public.payments;'
  cat "$restore_sql"
  printf '%s\n' 'COMMIT;'
} | psql "$SUPABASE_DB_URL" -X -v ON_ERROR_STOP=1
# Verify row count and canonical multiset fingerprint exactly match pre-incident state.
read -r post_count post_fp < <(psql "$SUPABASE_DB_URL" -X -v ON_ERROR_STOP=1 -AtF' ' -c "SELECT count(*), md5(COALESCE(string_agg(j, E'\\n' ORDER BY j),'')) FROM (SELECT row_to_json(p)::text AS j FROM public.payments p) s;")
[ "$post_count" = "80" ]
[ "$post_fp" = "$pre_fp" ]
printf 'RESTORED_BACKUP=%s\nPOST_COUNT=%s\nFINGERPRINT_MATCH=yes\n' "$backup" "$post_count"

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

BEGIN
SET
DELETE 54
SET
SET
SET
SET
SET
SET
 set_config 
------------
 
(1 row)

SET
SET
SET
SET
COMMIT

set -u
export PGPASSWORD="${SUPABASE_DB_PASSWORD:-}"
backup=$(ls -1t /tmp/payments_pre_incident_*.dump | head -1)
echo '--- live count/fingerprint ---'
psql "$SUPABASE_DB_URL" -X -v ON_ERROR_STOP=1 -AtF'|' -c "SELECT count(*), md5(COALESCE(string_agg(j, E'\\n' ORDER BY j),'')) FROM (SELECT row_to_json(p)::text AS j FROM public.payments p) s;"
echo "expected=$(cat "${backup}.fingerprint")"
echo '--- restore SQL relevant lines ---'
grep -nE '^(COPY|INSERT|\\\.|DELETE|TRUNCATE)|payments' "${backup}.restore.sql" | head -20
wc -l "${backup}.restore.sql"

--- live count/fingerprint ---
0|d41d8cd98f00b204e9800998ecf8427e
expected=817628c9128a2335cdf3acbdd13fe236
--- restore SQL relevant lines ---
27 /tmp/payments_pre_incident_20260724T053146Z.dump.restore.sql

set -euo pipefail
export PGPASSWORD="${SUPABASE_DB_PASSWORD:-}"
backup=$(ls -1t /tmp/payments_pre_incident_*.dump | head -1)
cat "${backup}.restore.sql"
echo '--- archive list ---'
pg_restore -l "$backup"

--
-- PostgreSQL database dump
--

\restrict Wp8YhwenNeba6OMDaUlQ2wcn7Hfv3d9PeEYYDKLSizWPdpOyK0VNRjC8RuESOhZ

-- 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 Wp8YhwenNeba6OMDaUlQ2wcn7Hfv3d9PeEYYDKLSizWPdpOyK0VNRjC8RuESOhZ

--- archive list ---
;
; Archive created at 2026-07-24 05:31:46 UTC
;     dbname: postgres
;     TOC Entries: 9
;     Compression: gzip
;     Dump Version: 1.16-0
;     Format: CUSTOM
;     Integer: 4 bytes
;     Offset: 8 bytes
;     Dumped from database version: 17.6
;     Dumped by pg_dump version: 18.4 (Debian 18.4-1.pgdg12+1)
;
;
; Selected TOC Entries:
;
275; 1259 21294 TABLE public payments postgres
3702; 0 21294 TABLE DATA public payments postgres
3552; 2606 21300 CONSTRAINT public payments payments_pkey postgres

set -euo pipefail
umask 077
: "${SUPABASE_DB_URL:?}"
export PGPASSWORD="${SUPABASE_DB_PASSWORD:-}"
backup=$(ls -1t /tmp/payments_pre_incident_*.dump | head -1)
pre_fp=$(cat "${backup}.fingerprint")
restore_sql="${backup}.data.sql"
# The archive contains only public.payments; restore its archived TABLE DATA entry.
pg_restore --data-only --no-owner --no-privileges --file="$restore_sql" "$backup"
grep -qE '^COPY public\.payments|^INSERT INTO public\.payments' "$restore_sql"
{
  printf '%s\n' 'BEGIN;' 'SET LOCAL session_replication_role = replica;' 'DELETE FROM public.payments;'
  cat "$restore_sql"
  printf '%s\n' 'COMMIT;'
} | psql "$SUPABASE_DB_URL" -X -v ON_ERROR_STOP=1 >/tmp/payments_restore.log
post_count=$(psql "$SUPABASE_DB_URL" -X -v ON_ERROR_STOP=1 -Atc 'SELECT count(*) FROM public.payments;')
post_fp=$(psql "$SUPABASE_DB_URL" -X -v ON_ERROR_STOP=1 -Atc "SELECT md5(COALESCE(string_agg(j, E'\\n' ORDER BY j),'')) FROM (SELECT row_to_json(p)::text AS j FROM public.payments p) s;")
[ "$post_count" = "80" ]
[ "$post_fp" = "$pre_fp" ]
printf 'RESTORED_BACKUP=%s\nPOST_COUNT=%s\nFINGERPRINT_MATCH=yes\n' "$backup" "$post_count"

RESTORED_BACKUP=/tmp/payments_pre_incident_20260724T053146Z.dump
POST_COUNT=80
FINGERPRINT_MATCH=yes