Restore a Supabase dump outside Supabase, and check what came back

Updated 31 October 2026 · run with pg_dump 17.6, postgres:17 and supabase/postgres:17.6.1.178

Supabase's own backups restore into the same project. To know your own copy works, you have to restore it somewhere else: a container on your laptop, a CI job, another Postgres. That is where these errors show up. If what you have is the dashboard's downloaded backup rather than a pg_dump of your own, Supabase's guide to restoring a downloaded backup locally covers it with the Supabase CLI.

One dump, five restores

We built a small database in the supabase/postgres:17.6.1.178 image, so you can rerun every step. It has one table of 5,000 orders with row-level security and one policy. It has the supabase_vault extension, which the image installs as hosted projects do. And it has a function that sets log_min_messages, like realtime.list_changes on a hosted project. We dumped it as postgres with pg_dump 17.6, the role and tool you would use through the session pooler, and restored the same file five ways.

-- as supabase_admin: what a hosted project has and the bare image does not
create function realtime.list_changes_demo() returns int language sql
  set log_min_messages to 'fatal' as $$ select 1 $$;

-- as postgres: the app's table
create table public.orders (
  id bigint generated always as identity primary key,
  user_id uuid not null,
  total_cents int not null,
  created_at timestamptz not null default now()
);
alter table public.orders enable row level security;
create policy "own orders" on public.orders for select to authenticated using (user_id = auth.uid());
insert into public.orders (user_id, total_cents)
select gen_random_uuid(), (random() * 10000)::int from generate_series(1, 5000);

Look at the second row. The usual advice for a Supabase restore is to add --no-owner --no-privileges, and it does cut the noise from 130 errors to 4. The policy still fails, the exit code is still 1, and the output is now short enough to skim past.

The setup that restores with no errors

1. Dump through the session pooler

pg_dump "$SUPABASE_SESSION_POOLER_URL" -Fc -f backup.dump

Copy the session pooler string from Connect in your project's dashboard. The direct host, db.<ref>.supabase.co, has only an IPv6 address unless you pay for the IPv4 add-on, and most CI runners cannot reach it. Your pg_dump has to be the project's major version or newer: 17 for a project on 17.

2. Start the image

docker run -d --name restore-test -e POSTGRES_PASSWORD=restore-test supabase/postgres:17.6.1.178
docker exec restore-test pg_isready -h localhost -U postgres

Run the second line until it prints accepting connections. The image runs its own setup before that, which took under 10 seconds for us. Use a tag with your project's major version.

3. Restore into a new database, as supabase_admin

docker cp backup.dump restore-test:/tmp/backup.dump
docker exec restore-test psql -U supabase_admin -d postgres -c 'create database restore_test'
docker exec restore-test pg_restore -U supabase_admin -d restore_test /tmp/backup.dump

No output means no errors. Check the exit code anyway (echo $?), because pg_restore keeps going after an error and only says so at the end.

4. Compare what came back

Run this in the restored database and in your project, and compare the two outputs line by line:

select table_schema, table_name,
       (xpath('/row/c/text()',
              query_to_xml(format('select count(*) as c from %I.%I', table_schema, table_name),
                           false, true, '')))[1]::text::bigint as row_count
from information_schema.tables
where table_type = 'BASE TABLE'
  and table_schema in ('public', 'auth', 'storage')
order by 1, 2;

select schemaname, tablename, policyname from pg_policies order by 1, 2, 3;

select extname, extversion from pg_extension order by 1;

The first query counts every row exactly, table by table. The second lists your policies, which is the check that catches the plain-Postgres restore above. Your project keeps taking writes after the dump, so expect its busiest tables to be a few rows ahead.

What each error means

role "supabase_admin" does not exist

A Supabase dump names Supabase's own roles as the owners of its schemas and functions, and in its grants. Plain Postgres has none of them. In our test, 122 of the 130 errors named a missing role: dashboard_user 55 times, supabase_admin 38, anon 13, supabase_auth_admin 12, and a few others.

extension "supabase_vault" is not available

pg_restore: error: could not execute query: ERROR:  extension "supabase_vault" is not available
DETAIL:  Could not open extension control file "/usr/share/postgresql/17/extension/supabase_vault.control": No such file or directory.
HINT:  The extension must first be installed on the system where PostgreSQL is running.

Supabase builds this extension into its image. Hosted projects have it installed (ours did), so their dumps ask for it, and stock Postgres images do not ship it. We hit this error in September, restoring a hosted project's dump into postgres:17.

role "authenticated" does not exist, on a policy

pg_restore: error: could not execute query: ERROR:  role "authenticated" does not exist
Command was: CREATE POLICY "own orders" ON public.orders FOR SELECT TO authenticated USING ((user_id = auth.uid()));

Policies name roles. --no-owner skips ownership and --no-privileges skips grants, and neither touches a policy. The table keeps row-level security turned on with no policy left, so every role without BYPASSRLS reads zero rows from it. Count the rows as postgres, which owns the table in the restore, and you see all 5,000.

permission denied to set parameter "log_min_messages"

pg_restore: error: could not execute query: ERROR:  permission denied to set parameter "log_min_messages"
Command was: CREATE FUNCTION realtime.list_changes_demo() RETURNS integer
    LANGUAGE sql
    SET log_min_messages TO 'fatal'
    AS $$ select 1 $$;

Only a superuser can create a function that sets log_min_messages. In the Supabase image, postgres is not a superuser and supabase_admin is. Restoring as postgres gave 52 more errors with it, mostly must be able to SET ROLE on one of Supabase's roles (24) and permission denied to change default privileges (18). We hit this one in September too, restoring a hosted project's dump into this image as postgres.

schema "auth" already exists

The image's own postgres database already has auth, storage, realtime, vault and four more schemas, plus rows in auth.schema_migrations. Restoring into it gave 45 errors, one of them a failed COPY on a duplicate key. A new database starts empty and avoids all 45.

Doing it every week

A restore you ran once tells you about that day's dump. The GitHub Actions guide runs the dump nightly and this restore weekly, in your own repository. SafeGrd's Fire Drills run the same restore on a schedule, in this image and as supabase_admin. Each drill keeps a signed record of the tables and row counts it found. The record does not include policies, so compare pg_policies yourself after a migration that changes them.

Postgres behavior from the documentation for pg_restore and CREATE POLICY. Restores run on 31 October 2026 on macOS with Docker 29.