How to Download Logical Backups in Supabase With Physical Backups Enabled?

[monicakh]

This is a copy of a troubleshooting article on Supabase's docs site. It may be missing some details from the original. View the original article.

When Point-in-Time Recovery (PITR) or physical backups are enabled, Supabase no longer generates the downloadable logical backup file (backup.gz). This is expected, as the backup pipeline switches entirely to physical backups. You can still download backups via the Supabase CLI db dump command or using pg_dump.

For step-by-step instructions using the Supabase CLI, check out the Backup database using the CLI guide.

You can also learn more about how backups work in Supabase here: Database Backups.

[mdibaiee]

@monicakh the linked documentation pages do not have any mention of a way to download physical backups or restoring them. There is no mention of pgdump in those pages and supabase db dump does not seem to accept a backup either, only seems to work on the current database state

[monicakh]

Hey @mdibaiee, sorry about the confusion there. supabase db dump is essentially a wrapper around pg_dump: both give you the same kind of logical backup, just the difference supabase db dump handles the connection details automatically for your project.

Also, just to clarify: there’s currently no way to download physical backups. These are low-level, byte-for-byte snapshots of the database’s internal state, they’re designed for PITR and can only be restored within our managed infrastructure: https://supabase.com/blog/restore-to-a-new-project. For this reason, we've created the above troubleshooting guide to advise users on how to download backups to keep an offsite copy when on physical backups, you’ll want to rely on logical backups instead with supabase db dump. This will give you a SQL file representing the schema and data at the time of the dump, and you can restore it later using psql:

psql \
  --single-transaction \
  --variable ON_ERROR_STOP=1 \
  --file roles.sql \
  --file schema.sql \
  --command 'SET session_replication_role = replica' \
  --file data.sql \
  --dbname [CONNECTION_STRING]

If anything in that flow is blocking you, please open a ticket and we’ll help out directly.

[albertocubeddu]

@monicakh using the supabase db dump.

Dumping data from remote database...
pg_dump: error: Dumping the contents of table "assessments_voice" failed: PQgetCopyData() failed.
pg_dump: detail: Error message from server: SSL connection has been closed unexpectedly
pg_dump: detail: Command was: COPY "public"."assessments_voice" ("id", <>) TO stdout;
error running container: exit 1
Try rerunning the command with --debug to troubleshoot the error.
supabase db dump exited with status 1
Downloading an atomic production snapshot (attempt 3/3)...
Initialising login role...
Dumping data from remote database...
pg_dump: error: Dumping the contents of table "assessments_prequalification" failed: PQgetCopyData() failed.
pg_dump: detail: Error message from server: SSL connection has been closed unexpectedly
pg_dump: detail: Command was: COPY "public"."assessments_prequalification" (<>) TO stdout;