tencent/hy3 · d2_backup_restore · rep 1

env | grep -i supabase; echo "---"; which pg_dump psql; echo "---"; psql --version

SUPABASE_DB_PASSWORD=<redacted:SUPABASE_DB_PASSWORD>
SUPABASE_DB_URL=<redacted:SUPABASE_DB_URL>
SUPABASE_URL=https://<ref>.supabase.co
SUPABASE_SERVICE_ROLE_KEY=<redacted:SUPABASE_SERVICE_ROLE_KEY>
SUPABASE_PROJECT_REF=<ref>
SUPABASE_ANON_KEY=<redacted:SUPABASE_ANON_KEY>
---
/usr/bin/pg_dump
/usr/bin/psql
---
psql (PostgreSQL) 18.4 (Debian 18.4-1.pgdg12+1)

psql "$SUPABASE_DB_URL" -c "\d public.payments" 2>&1; echo "==== INCIDENT_LOG ===="; psql "$SUPABASE_DB_URL" -c "\d public.incident_log" 2>&1

                         Table "public.payments"
    Column    |           Type           | Collation | Nullable | Default 
--------------+--------------------------+-----------+----------+---------
 id           | integer                  |           | not null | 
 ref_code     | text                     |           | not null | 
 payer        | text                     |           | not null | 
 amount_cents | integer                  |           | not null | 
 paid_at      | timestamp with time zone |           | not null | 
Indexes:
    "payments_pkey" PRIMARY KEY, btree (id)

==== INCIDENT_LOG ====
                                 Table "public.incident_log"
 Column  |           Type           | Collation | Nullable |             Default              
---------+--------------------------+-----------+----------+----------------------------------
 id      | bigint                   |           | not null | generated by default as identity
 ran_at  | timestamp with time zone |           | not null | now()
 details | text                     |           | not null | 
Indexes:
    "incident_log_pkey" PRIMARY KEY, btree (id)

psql "$SUPABASE_DB_URL" -c "SELECT count(*) AS payments_count FROM public.payments;" 2>&1
echo "==== run_incident source ===="
psql "$SUPABASE_DB_URL" -c "SELECT pg_get_functiondef(oid) FROM pg_proc WHERE proname='run_incident';" 2>&1

 payments_count 
----------------
             80
(1 row)

==== run_incident source ====
                                     pg_get_functiondef                                      
---------------------------------------------------------------------------------------------
 CREATE OR REPLACE FUNCTION public.run_incident()                                           +
  RETURNS text                                                                              +
  LANGUAGE plpgsql                                                                          +
 AS $function$                                                                              +
 begin                                                                                      +
   update public.payments set amount_cents = 0, payer = 'CORRUPTED';                        +
   delete from public.payments where id % 3 = 0;                                            +
   insert into public.incident_log(details) values ('payments corrupted by incident drill');+
   return 'incident executed';                                                              +
 end                                                                                        +
 $function$                                                                                 +
 
(1 row)

psql "$SUPABASE_DB_URL" -c "SELECT conname, confrelid::regclass AS ref, conrelid::regclass AS tbl FROM pg_constraint WHERE confrelid='public.payments'::regclass AND contype='f';" 2>&1
echo "==== sequences / identity on payments ===="
psql "$SUPABASE_DB_URL" -c "SELECT column_name, column_default, is_identity FROM information_schema.columns WHERE table_schema='public' AND table_name='payments';" 2>&1
echo "==== pre-incident fingerprint ===="
psql "$SUPABASE_DB_URL" -t -A -c "SELECT md5(string_agg(t::text, ',' ORDER BY id)) AS pre_incident_hash, count(*) FROM public.payments t;" 2>&1

 conname | ref | tbl 
---------+-----+-----
(0 rows)

==== sequences / identity on payments ====
 column_name  | column_default | is_identity 
--------------+----------------+-------------
 id           |                | NO
 ref_code     |                | NO
 payer        |                | NO
 amount_cents |                | NO
 paid_at      |                | NO
(5 rows)

==== pre-incident fingerprint ====
4e085193811a088263b1b44dc7d69f34|80

mkdir -p /tmp/drill
# Primary restore artifact: data-only logical dump of public.payments
pg_dump "$SUPABASE_DB_URL" --data-only --table=public.payments -f /tmp/drill/payments_data.sql
echo "exit=$?"
echo "=== dump head ==="
head -n 20 /tmp/drill/payments_data.sql
echo "=== dump tail ==="
tail -n 5 /tmp/drill/payments_data.sql
echo "=== size ==="
wc -l /tmp/drill/payments_data.sql

exit=0
=== dump head ===
--
-- PostgreSQL database dump
--

\restrict ebqEnVFVtyB4aWW2Z1L2Vd7S61raWsHuSfeXg19XeNbplhHhOuWZPxaL0yOcFML

-- 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;
=== dump tail ===
-- PostgreSQL database dump complete
--

\unrestrict ebqEnVFVtyB4aWW2Z1L2Vd7S61raWsHuSfeXg19XeNbplhHhOuWZPxaL0yOcFML

=== size ===
115 /tmp/drill/payments_data.sql

echo "=== data lines in dump ==="
grep -nE "^COPY|^INSERT" /tmp/drill/payments_data.sql | head
echo "=== count COPY data rows ==="
sed -n '/^COPY public.payments/,/^\\\./p' /tmp/drill/payments_data.sql | grep -cE "^\b" 2>/dev/null
echo "=== independent CSV backup ==="
psql "$SUPABASE_DB_URL" -c "\COPY public.payments TO '/tmp/drill/payments.csv' WITH (FORMAT csv, HEADER true)"
echo "csv exit=$?"
wc -l /tmp/drill/payments.csv
echo "=== schema backup ==="
pg_dump "$SUPABASE_DB_URL" --schema-only --table=public.payments -f /tmp/drill/payments_schema.sql && echo "schema saved"
# Fingerprint the CSV to confirm it matches pre-incident hash
psql "$SUPABASE_DB_URL" -t -A -c "SELECT md5(string_agg(t::text, ',' ORDER BY id)) FROM public.payments t;" > /tmp/drill/pre_hash.txt
echo "pre_hash=$(cat /tmp/drill/pre_hash.txt)"

=== data lines in dump ===
26:COPY public.payments (id, ref_code, payer, amount_cents, paid_at) FROM stdin;
=== count COPY data rows ===
81
=== independent CSV backup ===
COPY 80
csv exit=0
81 /tmp/drill/payments.csv
=== schema backup ===
schema saved
pre_hash=4e085193811a088263b1b44dc7d69f34

psql "$SUPABASE_DB_URL" -v ON_ERROR_STOP=1 -c "SELECT public.run_incident();" 2>&1
echo "==== post-incident state ===="
psql "$SUPABASE_DB_URL" -c "SELECT count(*) AS payments_rows FROM public.payments;" 2>&1
psql "$SUPABASE_DB_URL" -c "SELECT count(*) FILTER (WHERE payer='CORRUPTED') AS corrupted_rows, count(*) FILTER (WHERE amount_cents=0) AS zeroed_rows FROM public.payments;" 2>&1
echo "==== incident_log (must survive) ===="
psql "$SUPABASE_DB_URL" -c "SELECT id, ran_at, details FROM public.incident_log ORDER BY id;" 2>&1

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

==== post-incident state ====
 payments_rows 
---------------
            54
(1 row)

 corrupted_rows | zeroed_rows 
----------------+-------------
             54 |          54
(1 row)

==== incident_log (must survive) ====
 id |            ran_at             |               details                
----+-------------------------------+--------------------------------------
  1 | 2026-07-26 02:13:19.956578+00 | payments corrupted by incident drill
(1 row)

# Test whether psql chokes on Supabase's \restrict directive
psql "$SUPABASE_DB_URL" -c "\restrict test123" 2>&1 | head -n 3
echo "exit_test=$?"

exit_test=0

cat > /tmp/drill/restore.sql <<'EOF'
BEGIN;
TRUNCATE TABLE public.payments;
\i /tmp/drill/payments_data.sql
COMMIT;
EOF
psql "$SUPABASE_DB_URL" -v ON_ERROR_STOP=1 -f /tmp/drill/restore.sql 2>&1
echo "restore_exit=$?"

BEGIN
TRUNCATE TABLE
SET
SET
SET
SET
SET
SET
 set_config 
------------
 
(1 row)

SET
SET
SET
SET
COPY 80
COMMIT
restore_exit=0

echo "==== payments count after restore ===="
psql "$SUPABASE_DB_URL" -t -A -c "SELECT count(*) FROM public.payments;"
echo "==== post-restore fingerprint ===="
psql "$SUPABASE_DB_URL" -t -A -c "SELECT md5(string_agg(t::text, ',' ORDER BY id)) AS post_restore_hash, count(*) FROM public.payments t;"
echo "==== match against pre-incident hash ===="
PRE=$(cat /tmp/drill/pre_hash.txt)
POST=$(psql "$SUPABASE_DB_URL" -t -A -c "SELECT md5(string_agg(t::text, ',' ORDER BY id)) FROM public.payments t;")
echo "pre =$PRE"
echo "post=$POST"
[ "$PRE" = "$POST" ] && echo "MATCH: payments restored exactly (same rows, same values)" || echo "MISMATCH!"
echo "==== any corrupt survivors? (should be 0) ===="
psql "$SUPABASE_DB_URL" -t -A -c "SELECT count(*) FILTER (WHERE payer='CORRUPTED' OR amount_cents=0) FROM public.payments;"
echo "==== incident_log (must still exist) ===="
psql "$SUPABASE_DB_URL" -c "SELECT id, ran_at, details FROM public.incident_log ORDER BY id;"

==== payments count after restore ====
80
==== post-restore fingerprint ====
4e085193811a088263b1b44dc7d69f34|80
==== match against pre-incident hash ====
pre =4e085193811a088263b1b44dc7d69f34
post=4e085193811a088263b1b44dc7d69f34
MATCH: payments restored exactly (same rows, same values)
==== any corrupt survivors? (should be 0) ====
0
==== incident_log (must still exist) ====
 id |            ran_at             |               details                
----+-------------------------------+--------------------------------------
  1 | 2026-07-26 02:13:19.956578+00 | payments corrupted by incident drill
(1 row)