If you're setting up an automated backup for a Supabase project, the first connection string you meet is the wrong one for the job. This post explains why, and which one to use instead.
The symptom
You copy the connection string from Settings → Database in the Supabase dashboard, plug it into your backup script or CI job, and run pg_dump. Then one of these happens:
- The dump hangs until it times out.
- It dies partway through with an error that mentions prepared statements, or a setting that "does not exist", or nothing recognisable at all.
- It works once from your laptop and fails in CI, or the other way around.
The credentials are fine. The network is fine. The port is the problem.
What's actually going on
The prominent connection option in the dashboard is the transaction pooler, on port 6543. Supabase's pooler is PgBouncer, and in transaction mode it works like this: your client connects, runs a statement, and the pooler is free to hand the next statement to a different backend connection. No single server session belongs to you for the length of your connection.
That's exactly what you want for a web app making thousands of short queries. It is exactly what pg_dump cannot tolerate. A dump is not a pile of independent statements — pg_dump sets session-level state, takes locks, and reads a consistent snapshot across the whole run. It expects one real PostgreSQL session, start to finish. Through the transaction pooler, that assumption breaks, and the failure mode is rarely a clean "not supported" message. It's a hang, a half-finished dump, or an error pointing at something else entirely.
This is the trap: the error never says "wrong pooler mode", so people burn hours on firewalls, passwords, and SSL settings that were never the issue.
The fix: session pooler, port 5432
In the same dashboard panel (Settings → Database → Connection string), switch to the session pooler. It looks like this:
postgres://postgres.<your-project-ref>:<password>@aws-0-<your-region>.pooler.supabase.com:5432/postgres
Two details worth double-checking:
- Port 5432, not 6543. Same host family, different mode.
-
The username includes your project ref:
postgres.<your-project-ref>, not justpostgres. The pooler needs the ref to route you to the right project.
Session mode gives you a dedicated backend connection for as long as you're connected, which is precisely the session behaviour pg_dump assumes. As a bonus, the session pooler is reachable over IPv4, which the direct connection host (db.<your-ref>.supabase.co) is not on the Free plan — that host is IPv6-only, a separate trap if your dumps run from GitHub Actions or anywhere else without IPv6.
A minimal working dump command
pg_dump "$SUPABASE_DB_URL" \
--format=custom \
--no-owner \
--no-privileges \
--file=backup-$(date +%F).dump
Custom format (--format=custom) is worth defaulting to: the file is compressed, and pg_restore can selectively restore individual tables from it later — which is what you'll want on the bad day, rather than replaying one giant SQL file.
Verify the dump is real before you trust the pipeline:
pg_restore --list backup-$(date +%F).dump | head -20
If that prints your tables, the dump is structurally sound. (Actually test-restoring it into a throwaway database is the stronger check, but that's a topic of its own.)
Quick checklist
If your Supabase backup is failing and you landed here from a search:
- Are you connecting on port 6543? → Switch to the session pooler on 5432.
- Is your username
postgres.<project-ref>? → Barepostgreswon't route correctly through the pooler. - Does the same string work locally but not in CI? → Check whether you're using the direct host (
db.<ref>.supabase.co) in one of them; on Free it's IPv6-only. - Does the dump start but produce a file
pg_restore --listcan't read? → It was probably interrupted mid-stream; re-run over the session pooler and compare.
None of this is Supabase being broken. The transaction pooler is the right default for application traffic — it's just the wrong tool for a job that needs a whole session. One port number is the entire fix, but it's an expensive one to discover by debugging.
I learned this (and four other traps like it) while building a weekly pg_dump → Cloudflare R2 backup for my own Free-plan project. The minimal setup is free on GitHub as supabase-free-backup-lite; if you want the hardened version — encryption, per-dump validation, restore drills, failure alerts — I packaged it as the Supabase Free-Tier Backup Kit.
Top comments (1)
Nice that your workflow pins postgresql-client-17, that one bites a lot of people. I think the install step breaks first though: ubuntu-latest is still 24.04 and noble dropped the
awsclipackage, so apt should error out before pg_dump even runs. The runner already has AWS CLI v2, so I'd just removeawsclifrom that line.