Supabase: Creating Local Backups

How to download your database from your hosted Supabase project.

The following is a guide for explaining the steps needed to download a copy of your Supabase projects database, schema or data. Supabase itself performs scheduled backups, which you can read up on here.

Prerequisites

The following guide is Mac (silicon) based. However most steps should remain nearly the same between systems.

To get started:

• Log into your Supabase account.

• Navigate to your project, then to Project Settings→ Configuration (Database) → Connection String (psql). You will be using configuration data here later.

• Find your project’s database password (will be needed later).

Installing Basic Postgres Tooling

We do not need a full instance of Postgres to perform the backup, but we do need the core tooling. This is available via the libpq library. You can find more information on this library here.

First, update and optionally upgrade homebrew:

brew update
brew upgrade

Once this completes, use homebrew to install libpq:

brew install libpq

Once finished, you will need to update your environment because brew does not install these tools in any regular location (keg-only).

Might Work

One option is to use symlink via:

brew link --force libpq

However, for myself, this did not work, so to undo this, drop the force flag and change the subcommand to unlink:

brew unlink libpq

If I sort this out later I will update.

Did Work

What DID work however is the following (which is part of the final output from brew):

 echo 'export PATH="/opt/homebrew/opt/libpq/bin:$PATH"' >> ~/.zshrc

Then update your terminal environment via:

source ~/.zshrc

From here, you can test that you can reach the various tools by entering pg_dump, pg_restore, etc.

Backing Up Your Projects Database

Now that the tooling is present, we need to build the correct connection string for the various tools. For this post, we are focusing only on creating the local backup.

In your Supabase projects settings you navigated towards earlier, you will see a screen like this:

Database settings for your project, with project name and id redacted.

The provided connection string for PSQL is nearly exactly what you need. We simply need to update the command and tweak any options needed. Given the above (minus the id), here are three backup options. I navigated to ~/Downloads before running these commands:

Schema and Data

pg_dump -h aws-0-us-west-1.pooler.supabase.com -p 6543 -d postgres -U postgres.<your_project_id> supabase_dump_backup.sql

Schema Only

pg_dump -h aws-0-us-west-1.pooler.supabase.com -p 6543 -d postgres -U postgres.<your_project_id> --schema-only > supabase_schema_backup.sql

Data Only

pg_dump -h aws-0-us-west-1.pooler.supabase.com -p 6543 -d postgres -U postgres.<your_project_id> --data-only -Fc > supabase_data_backup.dump

Alternatively, to get an uncompressed readable version, drop the -Fc flag. That flag is to help provide a compressed and more convenient version for later usage in pg_restore.

Do be mindful if you have customer/real data in these backups and keep them in a safe place and also delete if unneeded.