moonshotai/kimi-k3 · d2_backup_restore · rep 1

env | grep -E '^SUPABASE_' | sed 's/=.*/=<set>/' && which pg_dump psql && pg_dump --version

SUPABASE_DB_PASSWORD=<set>
SUPABASE_DB_URL=<set>
SUPABASE_URL=<set>
SUPABASE_SERVICE_ROLE_KEY=<set>
SUPABASE_PROJECT_REF=<set>
SUPABASE_ANON_KEY=<set>
/usr/bin/pg_dump
/usr/bin/psql
pg_dump (PostgreSQL) 18.4 (Debian 18.4-1.pgdg12+1)

export PGPASSWORD="$SUPABASE_DB_PASSWORD"; psql "$SUPABASE_DB_URL" -c "select version();" -c "select count(*) from public.payments;" -c "\d public.payments" 2>&1 | head -50

                                   version                                    
------------------------------------------------------------------------------
 PostgreSQL 17.6 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 15.2.0, 64-bit
(1 row)

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

                         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)

export PGPASSWORD="$SUPABASE_DB_PASSWORD"; psql "$SUPABASE_DB_URL" -c "select count(*) as pre_incident_log_rows from public.incident_log;" -c "select proname from pg_proc p join pg_namespace n on n.oid=p.pronamespace where n.nspname='public' and proname='run_incident';" -c "select md5(string_agg(md5(t::text), '' order by id)) as table_checksum from public.payments t;" 2>&1

 pre_incident_log_rows 
-----------------------
                     0
(1 row)

   proname    
--------------
 run_incident
(1 row)

          table_checksum          
----------------------------------
 e84661faa452ec7291d49cdb6b4610df
(1 row)

set -e; export PGPASSWORD="$SUPABASE_DB_PASSWORD"
mkdir -p /tmp/drill_backup && cd /tmp/drill_backup
pg_dump "$SUPABASE_DB_URL" -Fc -t public.payments -f payments_full.dump
pg_dump "$SUPABASE_DB_URL" --data-only --column-inserts -t public.payments -f payments_data.sql
ls -la
pg_restore -l payments_full.dump
echo "--- data.sql head ---"; head -8 payments_data.sql
echo "--- verifying dump restores cleanly into a scratch schema (parse check) ---"
pg_restore --data-only payments_full.dump | grep -c '^COPY\|payments_data' || true
grep -c 'INSERT INTO public.payments' payments_data.sql

total 32
drwxr-xr-x 2 root root  4096 Jul 24 05:12 .
drwxrwxrwt 1 root root  4096 Jul 24 05:12 ..
-rw-r--r-- 1 root root 13237 Jul 24 05:12 payments_data.sql
-rw-r--r-- 1 root root  4864 Jul 24 05:12 payments_full.dump
;
; Archive created at 2026-07-24 05:12:22 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:
;
272; 1259 17959 TABLE public payments postgres
3707; 0 0 ACL public TABLE payments postgres
3697; 0 17959 TABLE DATA public payments postgres
3547; 2606 17965 CONSTRAINT public payments payments_pkey postgres
--- data.sql head ---
--
-- PostgreSQL database dump
--

\restrict cablnJyvnKAfDhaLUbkg91QYNFUo52uHUa0zePQQfveNeBgPwulIwgQeAE5vKfh

-- Dumped from database version 17.6
-- Dumped by pg_dump version 18.4 (Debian 18.4-1.pgdg12+1)
--- verifying dump restores cleanly into a scratch schema (parse check) ---
0
80

pg_restore: error: one of -d/--dbname and -f/--file must be specified

export PGPASSWORD="$SUPABASE_DB_PASSWORD"; psql "$SUPABASE_DB_URL" -x -c "SELECT public.run_incident();" 2>&1

-[ RECORD 1 ]+------------------
run_incident | incident executed

export PGPASSWORD="$SUPABASE_DB_PASSWORD"; psql "$SUPABASE_DB_URL" -c "select count(*) as payments_rows_now, md5(string_agg(md5(t::text), '' order by id)) as checksum_now from public.payments t;" -c "select * from public.incident_log;" 2>&1 | head -20

 payments_rows_now |           checksum_now           
-------------------+----------------------------------
                54 | 75e9eda7311216b42420467ec49e32b4
(1 row)

 id |            ran_at             |               details                
----+-------------------------------+--------------------------------------
  1 | 2026-07-24 05:12:49.334152+00 | payments corrupted by incident drill
(1 row)

set -e; export PGPASSWORD="$SUPABASE_DB_PASSWORD"; cd /tmp/drill_backup
pg_restore --data-only -t payments -f payments_restore.sql payments_full.dump
head -20 payments_restore.sql | grep -v '^--' | head -12
{ echo 'BEGIN;'; echo 'DELETE FROM public.payments;'; cat payments_restore.sql; echo 'COMMIT;'; } > restore_txn.sql
psql "$SUPABASE_DB_URL" -v ON_ERROR_STOP=1 -f restore_txn.sql 2>&1 | tail -8

\restrict 2JWXZ8QuvH4SaUfddoV7o8MEWUoHRHBn9iW8ffUEVqdcgmOSgZBgIQEn8peaHKT


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;
(1 row)

SET
SET
SET
SET
COPY 80
COMMIT

set -e; export PGPASSWORD="$SUPABASE_DB_PASSWORD"; cd /tmp/drill_backup
psql "$SUPABASE_DB_URL" -c "select count(*) as payments_rows, md5(string_agg(md5(t::text), '' order by id)) as checksum from public.payments t;" -c "select count(*) as incident_log_rows from public.incident_log;"
echo "--- byte-level diff of restored data vs pre-incident backup ---"
pg_dump "$SUPABASE_DB_URL" --data-only --column-inserts -t public.payments -f payments_post_restore.sql
diff <(grep '^INSERT INTO public.payments' payments_data.sql | sort) <(grep '^INSERT INTO public.payments' payments_post_restore.sql | sort) && echo "DIFF: identical — zero loss"
echo "--- incident_log contents ---"
psql "$SUPABASE_DB_URL" -c "select * from public.incident_log;"

 payments_rows |             checksum             
---------------+----------------------------------
            80 | e84661faa452ec7291d49cdb6b4610df
(1 row)

 incident_log_rows 
-------------------
                 1
(1 row)

--- byte-level diff of restored data vs pre-incident backup ---
DIFF: identical — zero loss
--- incident_log contents ---
 id |            ran_at             |               details                
----+-------------------------------+--------------------------------------
  1 | 2026-07-24 05:12:49.334152+00 | payments corrupted by incident drill
(1 row)