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.
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.
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!
In order to backup your Supabase database, you'll need to use the
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
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:
Remember to replace
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:
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.
Now that you have a backup of your Supabase database, you can restore Supabase using the methods shown below.
psql -U username -d dbname < database-dump.sql
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.
Want to trust your Supabase backups are running well without hastle?
Try SimpleBackups Now →
Free 7-day trial. No credit card required.
Have a question? Need help getting started?
Get in touch via chat or at [email protected]