How to back up and restore a Supabase database with pgAdmin 4

This guide is for people who already use pgAdmin to look after a Supabase project and want a backup file they can restore. You end up with one custom-format file holding the public schema: tables, rows, indexes, functions, row level security policies and grants. The Backup dialog runs pg_dump and the Restore dialog runs pg_restore, so each step also shows the command pgAdmin builds from your choices.

The dialog labels come from pgAdmin 4 v9.18, released September 17, 2026, which ships pg_dump and pg_restore 18.4. We ran the exact commands these dialogs build, with those 18.4 binaries, against Docker containers of Supabase’s own Postgres image, supabase/postgres:17.11.0.004, not against a hosted project. The test schema was small but typical: a notes table with 1,000 rows, an owner column that defaults to auth.uid() and a row level security policy; a profiles table with a foreign key to auth.users; a plain function; a security definer function owned by a role we created; and notes switched on for Realtime. Every error message on this page is copied from those runs.

Step 1: Connect pgAdmin to Supabase

In the Supabase dashboard, open the project and click Connect at the top of the page. Pick a connection method and open its parameters to see the host, port, database and user separately. Two methods work for pgAdmin, and the choice depends on your network:

• Direct connection. Host db.<project-ref>.supabase.co, port 5432, user postgres. Supabase’s docs list pg_dump, backups and restores under this one. It only works over IPv6, unless the project has the IPv4 add-on.
• Session pooler. A shared pooler host that works over IPv4, also port 5432, user postgres.<project-ref>. Supabase suggests it for GUI tools because it works from any IPv4 network, and its own pgAdmin page uses it. Copy the host from the dialog: it contains a pooler index you can’t guess from the region.

Don’t use the transaction pooler on port 6543. Supabase built it for short serverless queries, and session settings don’t survive between transactions there.

In pgAdmin, right-click Servers and choose Register → Server...

If you don’t remember the database password, reset it under Database Settings in the Supabase dashboard. SSL mode is set to prefer by default, which Supabase warns can fall back to an unencrypted connection; require refuses to. To verify the server as well, download the certificate from the same Database Settings page, add a Root certificate parameter pointing at it, and set SSL mode to verify-full.

Step 2: Check which pg_dump pgAdmin will use

pg_dump refuses to dump a server newer than itself. With an old client the backup stops before writing anything:

pg_dump: error: aborting because of server version mismatch
pg_dump: detail: server version: 17.11; pg_dump version: 16.15

Supabase projects run Postgres 17, or 15 if they haven’t been upgraded yet. To see yours, open the Query Tool on the project and run select version();.

pgAdmin does not use whatever pg_dump is on your PATH. Open File → Preferences → Paths → Binary paths. The PostgreSQL Binary Path grid has one row per major version. pgAdmin uses the row that matches the server, PostgreSQL 17 for Supabase; if that row is empty, it uses the row marked Set as default. The validate button next to a path lists the version of each utility it finds there.

The macOS and Windows installers ship their own copies (18.4 in pgAdmin 9.18, per its release notes) and register them as the default, which is new enough for Supabase. The Linux packages point at /usr/bin, so you get whichever postgresql-client your distribution installed. If pgAdmin can’t find a binary at all, the dialog stops with Utility not found and asks you to correct the Binary Path in the Preferences.

Step 3: Back up the public schema

In the Object Explorer, expand your server, then Databases → postgres → Schemas, right-click public and choose Backup... The dialog opens as “Backup (Schema: public)”. Most defaults are already right:

Custom is the format to keep. It is compressed by default, it is what pg_restore and pgAdmin’s Restore dialog read, and it lets you restore single tables later. Plain gives you a SQL script instead, which pgAdmin restores through psql with none of the restore options below.

The Owner switch makes no difference in this dialog. pg_dump ignores it for archive formats, and we checked: a Custom file made with the switch on still carried every OWNER TO statement. That decision belongs in the Restore dialog. Privileges is different: switching it on really does strip every GRANT and REVOKE from the file, so leave it off.

If your app uses more schemas than public, right-click the postgres database instead, open the Objects tab and tick each schema you created. pgAdmin adds one --schema per tick.

Click Backup. The job runs in the background; on the Processes tab, More details opens the process watcher with the log and the command. Without the path to the binary, pgAdmin 9.18 runs this:

pg_dump --file "/Users/you/backups/myapp-2026-10-16.backup" --host "aws-0-us-east-1.pooler.supabase.com" --port "5432" --username "postgres.abcdefghijklmnop" --no-password --format=c --large-objects --verbose --schema "public"

--large-objects comes from the Blobs switch, which is on by default, and --verbose from Verbose messages on the Options tab. The password never appears: pgAdmin passes it in the PGPASSWORD environment variable. Since 9.18 the database name travels the same way, in PGDATABASE, as a security fix. Older versions show "postgres" at the end of the command instead.

One gap in 9.18: the SSL mode from the Parameters tab only reaches pg_dump and pg_restore when a client certificate, key and root certificate are all set. Otherwise they connect with libpq’s default, prefer. A change merged into pgAdmin on October 13, 2026 passes every connection parameter to these tools. Until a release includes it, the terminal version sets the mode itself:

read -rs PGPASSWORD && export PGPASSWORD   # hidden prompt, stays out of shell history
export PGDATABASE=postgres PGSSLMODE=require
pg_dump --file "myapp-2026-10-16.backup" \
  --host "aws-0-us-east-1.pooler.supabase.com" --port "5432" \
  --username "postgres.abcdefghijklmnop" --no-password \
  --format=c --large-objects --verbose --schema "public"
unset PGPASSWORD

Why not back up the whole database?

Right-clicking the database and leaving the Objects tab alone also works, and the file is valid. The trouble starts at restore time. Most of a Supabase database belongs to Supabase: the auth, storage, realtime, extensions, graphql and vault schemas, among others. A new project already has them, and the postgres role you restore as isn’t allowed to own or change much of what is in them. When we restored a whole-database file into a fresh Supabase database, with the restore options from step 4, pg_restore reported 139 errors. Any real problem would have been buried in that list. Supabase’s own CLI leaves the same schemas out of its schema dumps.

Backing up public also means the file does not contain these, so plan for them separately:

• Auth users. They live in the auth schema. Supabase’s migration guide moves them with the Supabase CLI. Triggers or policies you added to auth or storage tables have to be recreated separately too, which the same guide points out.
• Storage files. They are not in the database at all; storage.objects only holds their metadata. How to back them up.
• Roles you created. Roles belong to the server, not to a schema. Supabase’s migration guide exports them with supabase db dump --role-only; for a handful, writing the CREATE ROLE statements by hand is quicker.
• Realtime. Which tables send changes to Realtime is stored in the supabase_realtime publication, which sits outside any schema. Turn those tables on again under Database → Publications in the new project; query 5 in step 5 lists them.
• Encrypted data. Vault secrets live in the vault schema, so they are not in the file, and every project has its own encryption root key. If you encrypt data with Vault or pgsodium, this method won’t bring it over in a readable state; Supabase’s migration guide covers what has to move with it.
• Everything outside Postgres. Edge Functions, Auth settings, API keys and secrets.

Step 4: Restore into a new project

Restore into a new, empty Supabase project, never over the one you backed up. If your tables use extensions beyond Supabase’s defaults, such as vector, enable them in the new project first, along with Database Webhooks if you used them. If you created roles of your own, create them in the new project too, before the restore, so the grants to them succeed. Register the new project in pgAdmin as in step 1. It has its own project ref, so the host or username changes.

Expand the new server, right-click Databases → postgres and choose Restore...

Owner on means pg_restore skips every ALTER ... OWNER TO, so whatever it creates belongs to the role you connected as, postgres. Without it, pg_restore tries to hand each object back to its original owner, which fails for roles you can’t switch to, such as supabase_admin. Leave Privileges off so the file’s GRANT and REVOKE statements run; step 5 shows the kind that still goes missing.

Owner on changes nothing when postgres owned everything in the first place, which Supabase’s docs describe as the usual case. Check before you restore: query 4 in step 5 lists the owner of every table, view and function, so run it on the old project first. Anything owned by another role matters most if it is a security definer function or a view, because both run with their owner’s rights, and on Supabase postgres bypasses row level security. In our test, a security definer function owned by a role without that right counted 0 rows in the old project and all 1,000 after the restore. Set those owners back before the new project takes traffic:

-- the role must exist in the new project (see above)
alter function public.count_all_notes() owner to report_reader;

Single transaction and Exit on error sound safer, but the very first statement in the file is expected to fail (see below). We tried both: each stopped at that statement and left the new project empty. Clean before restore drops objects before recreating them, which you don’t need in an empty project and don’t want in Supabase’s public schema; the Supabase restore guide explains what it breaks.

pgAdmin 9.18 runs:

pg_restore --host "aws-0-us-east-1.pooler.supabase.com" --port "5432" --username "postgres.qrstuvwxyzabcdef" --no-password --dbname= --no-owner --verbose "/Users/you/backups/myapp-2026-10-16.backup"

The empty --dbname= is deliberate. pg_restore needs the option to restore into a database at all, and the real name comes from PGDATABASE, the same security fix as in the backup. We ran it with the name set that way and it restored into postgres as expected.

Restoring to your own machine instead

A local copy is useful for looking at old data without touching a project. A stock Postgres image is the wrong target on its own: it has no auth schema and no anon or authenticated roles. In our test the notes table was never created, because its owner column defaults to auth.uid(), and its 1,000 rows went with it. Only one of two tables came back. The restore drill guide shows how to stub those pieces into a stock image from a terminal. From pgAdmin, Supabase’s own image is simpler:

docker run -d --name supabase-restore \
  -e POSTGRES_PASSWORD=scratch \
  -p 127.0.0.1:54329:5432 \
  supabase/postgres:17.11.0.004

# wait until the STATUS column says (healthy)
docker ps --filter name=supabase-restore

Register it in pgAdmin with host 127.0.0.1, port 54329, user postgres and the password above, then restore exactly as for a new project. You get the same errors as a new project does. Remove it with docker rm -f supabase-restore when you are done.

Expected errors and real ones

pgAdmin will tell you the restore failed. The process watcher ends with Failed (exit code: 1). and the last log line is pg_restore: warning: errors ignored on restore: 5. pg_restore exits with 1 whenever it skipped an error, and pgAdmin treats any non-zero exit as a failure, so this alone tells you little. Read the errors. Ours, restoring a schema with a profiles table that references auth.users:

Anything else that names one of your own tables, functions or policies is a real failure. Compare it with the source before you trust the copy.

Step 5: Check that the restore worked

Run these five queries in the Query Tool on the old project and on the new one, and compare the results side by side. The first one counts rows exactly instead of reading planner estimates.

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

-- 2. Row level security switched on, and how many policies each table has
select c.relname as table_name, c.relrowsecurity as rls_enabled,
       count(p.policyname) as policies
from pg_class c
left join pg_policies p
  on p.schemaname = 'public' and p.tablename = c.relname
where c.relnamespace = 'public'::regnamespace and c.relkind in ('r', 'p')
group by 1, 2
order by 1;

-- 3. Foreign keys
select conrelid::regclass as table_name, conname, convalidated
from pg_constraint
where connamespace = 'public'::regnamespace and contype = 'f'
order by 1, 2;

-- 4. Owner and grants of the schema and of every table, view, sequence and function
select 'public' as name, 'schema' as kind,
       pg_get_userbyid(n.nspowner) as owner, n.nspacl::text as grants
from pg_namespace n
where n.nspname = 'public'
union all
select c.relname, c.relkind::text,
       pg_get_userbyid(c.relowner), c.relacl::text
from pg_class c
where c.relnamespace = 'public'::regnamespace
  and c.relkind in ('r', 'p', 'v', 'm', 'S', 'f')
union all
select p.oid::regprocedure::text,
       case when p.prosecdef then 'function, security definer' else 'function' end,
       pg_get_userbyid(p.proowner), p.proacl::text
from pg_proc p
where p.pronamespace = 'public'::regnamespace
order by 1;

-- 5. Tables that send changes to Realtime
select tablename
from pg_publication_tables
where pubname = 'supabase_realtime' and schemaname = 'public'
order by 1;

In our test the first two matched: 1,000 notes, one profile, row level security on both tables, the policy in place, and the identity sequence continuing at 1,001. The third came back empty on the new side, which is the missing foreign key from the error list.

The fourth is the one people miss. Each letter in the grants column is a privilege: r select, a insert, w update, d delete, X execute. In the source we had taken update and delete on notes away from anon (anon=arDxtm), and revoked everything on profiles and execute on one function. After the restore, with Privileges restored, anon had all of it back (anon=arwdDxtm). The reason is that a new Supabase project grants anon, authenticated and service_role everything on each table and function created in public, through default privileges, at the moment it is created. The backup file only records grants relative to Postgres’s own defaults, so it never says “take this away from anon”: by Postgres’s defaults, anon never had it. The same goes for anything you revoked from authenticated. Row level security policies still decide which rows those roles see, but they don’t stop anyone calling a function, and they don’t apply to TRUNCATE or to tables without RLS. Run your REVOKE statements again and repeat query 4 until both sides match. The row whose kind is schema covers public itself, where a revoked USAGE comes back the same way. The owner column catches the security definer problem from step 4, and query 5 came back empty on the new side until we re-enabled Realtime for notes.

A backup you have never restored is a guess

Steps 4 and 5 prove that today’s file restores. Next week’s file is a different file, made after schema changes you haven’t thought about yet, and nobody repeats a manual restore every week for long. If you want to keep doing it by hand, the restore drill guide does the same check from a terminal, against a throwaway Docker container.

FAQ