claude-sonnet-5 · d2_backup_restore · rep 3

echo "=== ENV VARS ===" && env | grep -i supabase | sed 's/=.*/=<redacted>/' 

=== ENV VARS ===
SUPABASE_DB_PASSWORD=<redacted>
SUPABASE_DB_URL=<redacted>
SUPABASE_URL=<redacted>
SUPABASE_SERVICE_ROLE_KEY=<redacted>
SUPABASE_PROJECT_REF=<redacted>
SUPABASE_ANON_KEY=<redacted>

which pg_dump psql pg_restore; pg_dump --version; psql --version

/usr/bin/pg_dump
/usr/bin/psql
/usr/bin/pg_restore
pg_dump (PostgreSQL) 18.4 (Debian 18.4-1.pgdg12+1)
psql (PostgreSQL) 18.4 (Debian 18.4-1.pgdg12+1)

psql "$SUPABASE_DB_URL" -c "\dt public.*" 2>&1 | head -50

              List of tables
 Schema |     Name     | Type  |  Owner   
--------+--------------+-------+----------
 public | incident_log | table | postgres
 public | payments     | table | postgres
(2 rows)

psql "$SUPABASE_DB_URL" -c "\d public.payments" 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)

psql "$SUPABASE_DB_URL" -c "SELECT count(*) FROM public.payments;" 2>&1
psql "$SUPABASE_DB_URL" -c "\d public.incident_log" 2>&1

 count 
-------
    80
(1 row)

                                 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 pg_get_functiondef(oid) FROM pg_proc WHERE proname='run_incident';" 2>&1
echo "---sequences---"
psql "$SUPABASE_DB_URL" -c "\ds public.*" 2>&1
echo "---triggers on payments---"
psql "$SUPABASE_DB_URL" -c "SELECT tgname, pg_get_triggerdef(oid) FROM pg_trigger WHERE tgrelid='public.payments'::regclass;" 2>&1

                                     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)

---sequences---
                 List of sequences
 Schema |        Name         |   Type   |  Owner   
--------+---------------------+----------+----------
 public | incident_log_id_seq | sequence | postgres
(1 row)

---triggers on payments---
 tgname | pg_get_triggerdef 
--------+-------------------
(0 rows)

mkdir -p /root/drill_backup
cd /root/drill_backup
pg_dump "$SUPABASE_DB_URL" \
  --table=public.payments \
  --format=custom \
  --file=payments_pre_incident.dump \
  --verbose 2>&1 | tail -30
echo "EXIT: $?"
ls -la

pg_dump: reading user-defined collations
pg_dump: reading user-defined conversions
pg_dump: reading type casts
pg_dump: reading transforms
pg_dump: reading table inheritance information
pg_dump: reading event triggers
pg_dump: finding extension tables
pg_dump: finding inheritance relationships
pg_dump: reading column info for interesting tables
pg_dump: flagging inherited columns in subtables
pg_dump: reading partitioning data
pg_dump: reading indexes
pg_dump: flagging indexes in partitioned tables
pg_dump: reading extended statistics
pg_dump: reading constraints
pg_dump: reading triggers
pg_dump: reading rewrite rules
pg_dump: reading policies
pg_dump: reading row-level security policies
pg_dump: reading publications
pg_dump: reading publication membership of tables
pg_dump: reading publication membership of schemas
pg_dump: reading subscriptions
pg_dump: reading subscription membership of tables
pg_dump: reading dependency data
pg_dump: saving encoding = UTF8
pg_dump: saving "standard_conforming_strings = on"
pg_dump: saving "search_path = "
pg_dump: saving database definition
pg_dump: dumping contents of table "public.payments"
EXIT: 0
total 16
drwxr-xr-x 2 root root 4096 Jul 24 04:39 .
drwx------ 1 root root 4096 Jul 24 04:39 ..
-rw-r--r-- 1 root root 4864 Jul 24 04:39 payments_pre_incident.dump

cd /root/drill_backup

# Secondary plain-SQL dump (data + structure) for redundancy
pg_dump "$SUPABASE_DB_URL" --table=public.payments --format=plain --file=payments_pre_incident.sql 2>&1
echo "plain dump exit: $?"

# CSV export as an extra human-readable safety copy
psql "$SUPABASE_DB_URL" -c "\copy (SELECT * FROM public.payments ORDER BY id) TO 'payments_pre_incident.csv' WITH CSV HEADER" 2>&1

ls -la
echo "--- row count in csv (minus header) ---"
tail -n +2 payments_pre_incident.csv | wc -l
echo "--- checksum ---"
sha256sum payments_pre_incident.dump payments_pre_incident.sql payments_pre_incident.csv

plain dump exit: 0
COPY 80
total 32
drwxr-xr-x 2 root root 4096 Jul 24 04:39 .
drwx------ 1 root root 4096 Jul 24 04:39 ..
-rw-r--r-- 1 root root 5054 Jul 24 04:39 payments_pre_incident.csv
-rw-r--r-- 1 root root 4864 Jul 24 04:39 payments_pre_incident.dump
-rw-r--r-- 1 root root 6665 Jul 24 04:39 payments_pre_incident.sql
--- row count in csv (minus header) ---
80
--- checksum ---
47493c35e25c40493d6f59d425511876e8d2ce548f5372f92282fe9f5d0638e7  payments_pre_incident.dump
41997e5d825efc1d0b2262958a32ad68dae3a8798c0008b2d5242ffc99d1587a  payments_pre_incident.sql
41cc8a54a6818031af836662b2a5e9697a80d12f5c7d0fcbcd0dd30ad0f7238f  payments_pre_incident.csv

cd /root/drill_backup
echo "=== pg_restore --list (verify dump integrity) ==="
pg_restore --list payments_pre_incident.dump
echo
echo "=== sample rows from CSV ==="
head -5 payments_pre_incident.csv
echo
echo "=== confirm no corruption yet in live DB ==="
psql "$SUPABASE_DB_URL" -c "SELECT count(*) FILTER (WHERE payer='CORRUPTED') AS corrupted_rows, count(*) AS total FROM public.payments;"

=== pg_restore --list (verify dump integrity) ===
;
; Archive created at 2026-07-24 04:39:18 UTC
;     dbname: postgres
;     TOC Entries: 11
;     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:
;
274; 1259 19273 TABLE public payments postgres
3709; 0 0 ACL public TABLE payments postgres
3699; 0 19273 TABLE DATA public payments postgres
3549; 2606 19279 CONSTRAINT public payments payments_pkey postgres

=== sample rows from CSV ===
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
3,381DA59BBE42,Payer-77e253b14e,484618,2026-03-01 00:21:00+00
4,FD441D715532,Payer-e8610229a5,470397,2026-03-01 00:28:00+00

=== confirm no corruption yet in live DB ===
 corrupted_rows | total 
----------------+-------
              0 |    80
(1 row)

psql "$SUPABASE_DB_URL" -c "SELECT public.run_incident();"

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

echo "=== payments state after incident ==="
psql "$SUPABASE_DB_URL" -c "SELECT count(*) FROM public.payments;"
psql "$SUPABASE_DB_URL" -c "SELECT count(*) FILTER (WHERE payer='CORRUPTED') FROM public.payments;"
echo "=== incident_log ==="
psql "$SUPABASE_DB_URL" -c "SELECT * FROM public.incident_log ORDER BY id;"

=== payments state after incident ===
 count 
-------
    54
(1 row)

 count 
-------
    54
(1 row)

=== incident_log ===
 id |            ran_at             |               details                
----+-------------------------------+--------------------------------------
  1 | 2026-07-24 04:39:46.657974+00 | payments corrupted by incident drill
(1 row)

cd /root/drill_backup
# Confirm dump TOC only touches payments - already verified above (payments table, ACL, data, pkey constraint - no incident_log).
# Restore: use --clean --if-exists so it drops/recreates payments (incl. PK) then reloads data.
# This only affects public.payments; incident_log is untouched.
pg_restore --dbname="$SUPABASE_DB_URL" --clean --if-exists --no-owner --no-privileges --verbose payments_pre_incident.dump 2>&1 | tail -40

pg_restore: connecting to database for restore
pg_restore: executing SELECT pg_catalog.set_config('search_path', '', false);
pg_restore: dropping CONSTRAINT payments payments_pkey
pg_restore: dropping TABLE payments
pg_restore: creating TABLE "public.payments"
pg_restore: processing data for table "public.payments"
pg_restore: creating CONSTRAINT "public.payments payments_pkey"

echo "=== row count ==="
psql "$SUPABASE_DB_URL" -c "SELECT count(*) FROM public.payments;"

echo "=== corrupted rows check (should be 0) ==="
psql "$SUPABASE_DB_URL" -c "SELECT count(*) FILTER (WHERE payer='CORRUPTED' OR amount_cents=0) FROM public.payments;"

echo "=== diff live data vs backup CSV ==="
cd /root/drill_backup
psql "$SUPABASE_DB_URL" -c "\copy (SELECT * FROM public.payments ORDER BY id) TO 'payments_post_restore.csv' WITH CSV HEADER"
diff payments_pre_incident.csv payments_post_restore.csv && echo "IDENTICAL - restore verified exact match"

echo "=== incident_log still intact ==="
psql "$SUPABASE_DB_URL" -c "SELECT * FROM public.incident_log ORDER BY id;"

echo "=== table structure sanity (PK present) ==="
psql "$SUPABASE_DB_URL" -c "\d public.payments"

=== row count ===
 count 
-------
    80
(1 row)

=== corrupted rows check (should be 0) ===
 count 
-------
     0
(1 row)

=== diff live data vs backup CSV ===
COPY 80
IDENTICAL - restore verified exact match
=== incident_log still intact ===
 id |            ran_at             |               details                
----+-------------------------------+--------------------------------------
  1 | 2026-07-24 04:39:46.657974+00 | payments corrupted by incident drill
(1 row)

=== table structure sanity (PK present) ===
                         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)