How to backup Supabase database?

 

Step 1: Set Up Environment Variables

 

Before you begin, ensure you have the necessary environment variables. These will include your Supabase project URL, the anon key, and the service role key. Typically, these can be stored in a .env file for easy access:

SUPABASE_URL=https://your-project-ref.supabase.co
SUPABASE_ANON_KEY=your-anonymous-api-key
SUPABASE_SERVICE_ROLE_KEY=your-service-role-key

 

Step 2: Install pg_dump

 

Supabase uses PostgreSQL as its database, so you will need to use pg_dump to back up your database. If you haven't installed it already, do so using a package manager.

For MacOS, you can use Homebrew:

brew install postgresql

For Windows, you may download PostgreSQL from the official website and ensure pg_dump is in your PATH.

 

Step 3: Connect to the Supabase Database

 

To connect to your Supabase database, you'll need your database URL, user, and password. You can find these details in your Supabase project dashboard under the "Settings" and then "API" tab.

Set up your connection string as follows:

PG_CONNECTION_STRING=postgresql://postgres:[your_password]@db.[project_ref].supabase.co:5432/postgres

Make sure to replace [your_password] and [project_ref] with your actual database password and project reference.

 

Step 4: Create a Backup

 

Execute the following command in your terminal to create a backup of your Supabase database:

pg_dump $PG_CONNECTION_STRING --format=custom --file=supabase-backup.dump

This will create a supabase-backup.dump file in your current directory. The --format=custom option specifies a custom archive suitable for use with tools like pg_restore.

 

Step 5: Automate Backups (Optional)

 

You might want to automate your backups using a cron job or a similar task scheduler. Here's an example of a script you could run daily with cron on a Unix-based system:

#!/bin/bash
timestamp=$(date +"%Y-%m-%d_%H-%M-%S")
pg_dump $PG_CONNECTION_STRING --format=custom --file=backups/supabase-backup-$timestamp.dump

To schedule this script, you could add a cron job by using crontab -e and inserting a line like:

0 2 * * * /path/to/backup_script.sh

This sets the script to run daily at 2 AM.

 

Step 6: Store Backups Safely

 

Ensure your backups are stored safely, possibly in a different physical location or cloud storage to avoid data loss due to failures at the primary site. You can use AWS S3, Google Cloud Storage, or any other cloud provider. Here's an example using AWS CLI to upload your backup:

aws s3 cp supabase-backup.dump s3://your-bucket-name/backups/

Don't forget to configure your AWS CLI with aws configure before running this command.

 

Step 7: Restore a Backup (Optional)

 

To restore a backup, use the pg_restore command. Make sure your database is ready to accept the restore:

pg_restore --clean --no-owner --dbname=postgresql://postgres:[your_password]@db.[project_ref].supabase.co:5432/postgres supabase-backup.dump

Modify the connection string and file path as needed for your environment. The --clean option cleans (drops) database objects before recreating them, which ensures the restore process is clean.

 

Conclusion

 

By following these steps, you'll ensure that your Supabase database is regularly backed up and can be restored when needed. Remember to keep your backups in a secure location and test your restore process periodically to confirm it works as expected.