How to Backup Supabase Database

In this post and video, we'll dive into the specifics of creating a local PostgreSQL backup for your Supabase database.
I'll share with you the command you need to use to both create and restore a Supabase backup.

What is Supabase?

Before getting started, let's introduce Supabase!

Supabase is an open-source Firebase alternative, providing developers with a toolkit to build dynamic web and mobile applications.
It offers a PostgreSQL database, authentication services, real-time subscriptions, and auto-generated APIs. One of its key components is the PostgreSQL database, renowned for its robustness and reliability.

In this article we'll focus on how to back up the Supabase PostgreSQL database, which by the way is a provider we like a lot at SimpleBackups.

When do you need a Supabase backup?

While Supabase offers its own backup solutions, relying solely on these can be risky.

Here’s why an external backup is crucial:

Redundancy: Having an external backup ensures that if something goes wrong with Supabase's internal backups, your data is still safe.
Control: External backups give you more control over backup frequency, retention policies, and recovery strategies.
Compliance: For certain applications, regulations may require external backups to meet data protection standards.

We'll develop a straightforward script to back up your PostgreSQL database.
If you want to automate the process, you can use SimpleBackups for Supabase to schedule your backups and store them on Amazon S3, Google Cloud Storage, or any other cloud storage provider.
If you feel like coding it yourself, you can check out our guide on how to automate PostgreSQL backups.

With that being said, let's get started!

How to backup Supabase database

In order to backup your Supabase database, you'll need to use the pg_dump command.

Make sure you have postgres installed on your machine.

If not, you can install it by following the instructions on the official website.

You can also check our article on how to install PostgreSQL on Docker.

For Ubuntu, simply install it via the command line:

sudo apt-get install postgresql-15

Backup your Supabase database with pg_dump

While we won't go deep into how pg_dump works, here's a quick overview of the command.

For those who wants to understand moreabout pg_dump, we wrote an complete guide on how to backup PostgreSQL database that goes into more details.

The format of a Supabase PostgreSQL connection string is similar to a standard PostgreSQL connection string.
It typically includes the following components:

postgresql://myuser:[email protected]:5432/mydatabase

Remember to replace myuser, mypassword, db.myproject.supabase.co, and mydatabase with your actual database credentials and details. Also, ensure that your connection string is kept secure and not exposed in your application code or any public repositories.

Now, let's execute the pg_dump command to backup your Supabase database:

Using credentials:

pg_dump --inserts --column-inserts --username=myuser --host=db.myproject.supabase.co --port=5432 mydatabase > database-dump.sql

Or using the connection string:

pg_dump 'postgresql://myuser:[email protected]:5432/mydatabase > database-dump.sql

This command will connect to your Supabase database and create a dump file called database-dump.sql in your current directory.

Restore your Supabase database with pg_restore

Now that you have a backup of your Supabase database, you can restore Supabase using the methods shown below.

Using psql:

psql -U username -d dbname < database-dump.sql

Or, using pg_restore:

pg_restore -U username -d dbname -1 database-dump.sql

If you want to restore your Supabase backup on to Supabase itself, just your the propler connection string and credentials.

psql 'postgresql://myuser:[email protected]:5432/mydatabase' < database-dump.sql

And that's it! You now know how to backup and restore your Supabase database.