Supabase → encrypted AWS backups: repeated restore failures while building with ChatGPT/Codex. Are we overcomplicating this?
retained text
I’m a project owner, not a database engineer. I’ve been using ChatGPT for guidance/review and Codex for implementation to build an independent, encrypted backup system for my Supabase database using AWS. The goal is to recover our application data, Auth records, database roles, permissions and RLS policies reliably. Storage files and full managed-service recovery are separate concerns; we haven’t established those yet. An encrypted backup exists in AWS, and verification/decryption works. **The part we still cannot complete is an actual restoration of that backup.** Our setup uses AWS S3/KMS and CodeBuild, with restoration into an isolated, disposable database using a pinned Supabase/Linux PostgreSQL 17.6 runtime. These tests do not restore over production. We’ve gone through several rounds of failures and corrections: * Some early blockers were defects in our own retention checks and role-comparison code. * Those were corrected, and role restoration now passes its internal comparisons. * Subsequent attempts fail during database content restoration. * Our diagnostic code initially removed so much detail to protect private information that we couldn’t identify the underlying error. Several rounds of work have gone into correcting that. **Latest actual restoration result:** * Archive verification and decryption passed. * Role/bootstrap restoration and internal role/grantor comparisons passed. * Database bootstrap and extension setup passed. * `pg_restore` failed during content restoration with exit status **1**. * The retained diagnostic points near **TOC entry 4046 — EVENT TRIGGER**, but this is only a candidate location. * We still don’t have the actual SQLSTATE or a confirmed failing statement. * Final content/security comparisons and recovery probes weren’t reached. * Cleanup completed, and production and the original backup remain unchanged. One potential lead is the event-trigger ownership handling in [Supautils v3.4.0](https://github.com/supabase/supautils/blob/v3.4.0/src/supautils.c). However, we haven’t established that those checks caused this failure or even applied to the failed session. The latest local change adds correlated PostgreSQL JSON server logs so we can obtain the real SQLSTATE and source location while keeping private SQL and data inside the disposable environment. That change hasn’t been tested in the hosted restoration yet. We now have **903 passing local tests**, plus **14 fresh native PostgreSQL checkpoints** for the latest diagnostic work. But the native tests use stock PostgreSQL on Windows, which clearly doesn’t prove that our actual backup restores on the Supabase/Linux runtime. Honestly, I’m frustrated. I keep getting reports that tests passed, followed by another failed restore and another diagnostic change. I’m relying on the AI tools for technical decisions, and I’m concerned we’ve built too much custom machinery around something that should have a more established approach. For people who have successfully implemented and tested independent Supabase backups: 1. Have you encountered event-trigger or Supautils ownership issues when restoring into a disposable Supabase/Postgres instance? 2. What exact evidence should we collect next to identify the failure without exposing user data? 3. What backup/restore approach do you use to preserve application data, Auth records and security behavior? 4. Does this sound like a restore-environment or implementation problem, and what would you simplify first? I’m looking for an experienced sanity check and a practical path to a demonstrably working restore. I understand nobody can diagnose the exact cause without the actual error.
document fetched 2026-10-20 00:37 · canonical https://reddit.com/r/aws/comments/1xabc8c/supabase_encrypted_aws_backups_repeated_restore