I wrote a guide on restore-testing a Supabase Postgres backup, because a truncated dump, a dump taken with the wrong role and a job that half-failed all look the same as a good backup sitting in a bucket.
The manual version takes about fifteen minutes and never connects to production:
- Start a throwaway Postgres in Docker that matches your Supabase major version, bound to 127.0.0.1 and with a fresh random password, and check readiness over TCP rather than the Unix socket.
- Create stand-ins for what a Supabase project provides (the anon, authenticated and service_role roles and the auth.uid() / auth.jwt() helpers). Without them, a table with
default auth.uid()never gets created, and pg_restore carries on to the end anyway. - Restore with
--no-owner --no-privileges, then sort the expected sandbox errors from the real ones. - Check the table count, that tables that should have rows aren’t empty, and
max(created_at)to catch a job that has quietly stopped running.
It also covers running the same loop as one command with the open-source backupdrill CLI (MIT, I work on it), what a passed test does and doesn’t prove, and how often to run it.