pg_restore finished fine. One of my tables wasn't there.

A restore test on a real 105-table Supabase database failed a few weeks ago. The tool doing it restores in a strict mode, so it stopped on the first error: a function with a parameter defaulting to auth.uid(). Fixing that was quick. The interesting part came when I reproduced it locally with plain pg_restore, the way most people test a backup by hand. There it didn't stop at all. It ran to the end, and one table simply wasn't there.

Here's why, how to reproduce it in two minutes, and the few lines that prevent it.

The setup

Supabase gives you auth.uid(), a function that returns the id of the logged-in user. It's everywhere in Supabase schemas, and a very natural place to use it is a column default:

create table public.notes (
  id    serial primary key,
  owner uuid default auth.uid(),
  body  text
);

Insert a row from the client and owner fills itself in. Nice.

Now take a normal backup of your application schema:

pg_dump --format=custom --schema=public -f app.dump "$SUPABASE_DB_URL"

and restore it somewhere that isn't Supabase, which is exactly what you do when you test a backup: a throwaway postgres container.

What goes wrong

Plain Postgres has no auth schema. So when pg_restore gets to CREATE TABLE public.notes (... default auth.uid() ...), the statement fails. The table isn't created. A few lines later the COPY into public.notes fails too, because there's no table to copy into. And then pg_restore keeps going, because by default it reports errors and carries on.

You end up with every other table, all their rows, and no notes.

The error lines are in the output, but they're easy to wave through. Anyone who has restored a Supabase dump into vanilla Postgres has learned to expect a pile of complaints about roles and schemas that "only exist on Supabase", and the tempting rule is to ignore everything mentioning auth. I had written roughly that rule into my own notes. It's wrong for this case.

Reproduce it

Two containers, one pretending to be Supabase:

docker run -d --name src -e POSTGRES_PASSWORD=pw postgres:17
docker run -d --name rt  -e POSTGRES_PASSWORD=pw postgres:17
# wait a few seconds for both to start

docker exec -i src psql -U postgres <<'SQL'
create schema auth;
create function auth.uid() returns uuid language sql stable as $$ select null::uuid $$;
create table public.notes (id serial primary key, owner uuid default auth.uid(), body text);
insert into public.notes (body) select 'n' || g from generate_series(1, 500) g;
create table public.plain (id int);
insert into public.plain select generate_series(1, 100);
SQL

docker exec src pg_dump -U postgres --format=custom --schema=public -f /tmp/app.dump postgres
docker cp src:/tmp/app.dump . && docker cp app.dump rt:/tmp/app.dump

docker exec rt pg_restore -U postgres --no-owner --no-privileges -d postgres /tmp/app.dump
docker exec rt psql -U postgres -c "select to_regclass('public.notes'), (select count(*) from public.plain)"

What I get:

pg_restore: error: could not execute query: ERROR:  schema "auth" does not exist
pg_restore: error: could not execute query: ERROR:  relation "public.notes" does not exist
...
 to_regclass | count
-------------+-------
             |   100

plain restored with all 100 rows. notes doesn't exist.

The same thing happens to a function whose parameter defaults to auth.uid(), like create function is_admin(p_user_id uuid default auth.uid()), which is the one that tripped the strict restore in the first place.

The fix

Before restoring, give the sandbox cheap stand-ins for what Supabase would have provided:

create role anon;
create role authenticated;
create role service_role;
create schema auth;
create function auth.uid()   returns uuid  language sql stable as 'select null::uuid';
create function auth.role()  returns text  language sql stable as 'select null::text';
create function auth.email() returns text  language sql stable as 'select null::text';
create function auth.jwt()   returns jsonb language sql stable as 'select null::jsonb';

Run the same restore again and notes comes back with all 500 rows.

Two details matter here.

First, don't create auth.users. An empty users table would make every foreign key into it fail in a way that looks like your data is broken, when really the sandbox just doesn't have your users. Errors about auth.users are the ones that genuinely are sandbox noise.

Second, change what you ignore. The useful rule isn't "ignore anything mentioning auth". It's: errors about auth.users are expected, and an error saying a role or an auth function doesn't exist means a stub is missing and something didn't restore.

The check that would have caught it anyway

Count things. After any test restore, compare the number of tables and the row counts of your two or three most important tables against production:

select count(*) from information_schema.tables where table_schema = 'public';
select count(*) from public.notes;

A restore that "worked" but is missing a table fails this in five seconds. The exit code won't tell you much here: pg_restore returns non-zero on any error, including the harmless ones every Supabase restore produces, so most people learn to ignore it.

Where this ended up

I build a small backup tool for Supabase, and restore-testing is the part I care most about, so this bug got a proper fix: the drill sandbox now creates these stand-ins before every restore and is strict about which errors count as noise. The CLI is MIT and does the whole drill locally if you want it: github.com/backupdrill/cli.

If you'd rather do it by hand, the step-by-step version is in this restore-testing guide, and a stricter two-pass restore that separates schema-and-data errors from index and constraint errors is in the manual backup guide.

Either way, test the restore, and count what comes back.