How to backup and restore Supabase Postgres database

Supabase is an open source alternative to Firebase. They provide lot of features like auth, storage, serverless functions, database, etc out of box.

And one interesting thing about them is that they've build many service on top of the Postgres. They also have a generous free plan as well.

Let's see how to do backup and restore the database in Supabase.

Backup database using pg_dump

We'll be using pg_dump to perform a backup operation.

Make sure that you're using the same version of Postgres locally and also in Supabase platform

Get the connection string from your Supabase dashboard

pg_dump "postgresql://<username>:<password>@aws-0-us-east-1.pooler.supabase.com:5432/postgres?sslmode=require" -F c -b -v -f <backup_file_name>.dump

Restore database using pg_restore

We'll be using pg_restore which we'll be able to restore the database using the dump that we've created using pg_dump and it comes preinstalled when you install the Postgres server locally.

Just run the following command:

pg_restore -d "postgresql://<username>:<password>@aws-0-us-east-1.pooler.supabase.com:5432/postgres?sslmode=require" <backup_file_name>.dump

d or --dbname 👉 database name (and also connection string as well)

How do I backup and restore only public schema?

If you prefer to backup and restore only a specific schema (like "public") you can do so using -n argement

pg_dump "postgresql://<username>:<password>@aws-0-us-east-1.pooler.supabase.com:5432/postgres?sslmode=require" -n public -F c -b -v -f <backup_file_name>.dump

pg_restore -d "postgresql://<username>:<password>@aws-0-us-east-1.pooler.supabase.com:5432/postgres?sslmode=require" <backup_file_name>.dump

That's pretty much it.

Happy backing up!