Supabase is an incredible Firebase alternative as well as my favorite managed Postgres provider at the moment.

It's incredibly easy to use, but without having access to the server the database is hosted on some people might find creating manual backups confusing. So here's how you can create manual backups of your Supabase database.

How do I create a backup of a Supabase Database?

As previously mentioned, Supabase uses PostgreSQL instances for its databases and thankfully for use Postgres also has some incredibly useful CLI tools. The one we will use today is called pg_dump, which is automatically installed with postgres.

This means that, if you haven't done so already you should install Postgres on your local machine.

On Ubuntu:

sudo apt-get install postgresql-12

On Windows you'll have to run the Postgres Windows Installer. The installer won't add all the Postgres commands to your $PATH, which unfortunately means you'll have to either add them yourself or go find them in their respective folders.

For me, for example, pg_dump was located at C:\Program Files\PostgreSQL\13\bin\pg_dump.

How to use the pg_dump command to backup Supabase

The first thing you'll need to find to backup your Supabase database is your connecton URI. You can find this from the Supabase admin interface by Selecting your database > Going to Settings > Selecting Database from the sidebar > Scrolling all the way to the bottom.

Your string will look something like postgresql://postgres:[YOUR-PASSWORD]@db.[PROJECT-ID].supabase.co:5432/postgres.

Copy your string and then open your terminal.

pg_dump 'postgres://user:[YOUR-PASSWORD]@db.[PROJECT-ID].supabase.co:5432/postgres' > database-dump.sql

The above command will tell pg_dump to connect your remote database using the URI provided and creating a dump of the data in your current folder with the name database-dump.sql.

On Windows, if pg_dump is not part of your PATH, you'll have to run this command after moving to the correct folder:

./pg_dump 'postgres://user:[YOUR-PASSWORD]@db.[PROJECT-ID].supabase.co:5432/postgres' > database-dump.sql

Bonus: Restoring a local backup to a Supabase database

If something goes wrong with your database and you need to restore from a backup the process is really similar!

Simply move to the folder where your backup is located and run:

psql 'postgres://user:[YOUR-PASSWORD]@db.[PROJECT-ID].supabase.co:5432/postgres' < database-dump.sql

The psql command should already be on your machine if you've installed Postgres from the previous steps.