Backups [database & storage] without supabase-cli

[ethicnology]

Database backups are well documented, but since the project evolve over time, postgres user cannot be a superuser anymore, which generate errors while restoring backups using standard tools such as pg_dump, pg_restore and psql.

1. How to deal with these errors related to constrained postgres user ?
2. If you back up only public schema, how do you deal with triggers for new_profile on auth or even storage related stuff ?
3. How to back up storage data + policies ?
4. How to restore all this information in a fresh supabase project ?

CONSTRAINT: the solution must work in hosted and self-hosted environment

Database

Backup

pg_dump -h xxxxx.pooler.supabase.com -p 5432 -d postgres -U postgres.xxxxx --schema-only > backup.sql

Restore

psql -h xxxxx.pooler.supabase.com -p 5432 -d postgres -U postgres.xxxxx < backup.sql

[tristdrum]

Would love to know the answer too

[djshubs]

Same here. I would like to know what are the steps to do backup and restore.

[theranajayant]

Same here. Reading 3rd thread, no answer for this.

[ethicnology]

@kiwicopple Could we have your opinion on the topic?

[ethicnology]

@theranajayant @tristdrum @djshubs @neiled

This is my procedure to backup supabase instance with classic tools.
I'm using only auth + postgres + storage in supabase, no other features will be covered.

1. Backup all schema using psql / pg_dump. The most import schema (IMO) are: auth,public,storage

• auth (contains your users)
• public (contains your data)
• storage (contains your buckets and links to objects)

2. Export all buckets using rclone sync (you have to rclone config before)

The re-import process is the opposite, using psql / pg_restore, it works well with data but, you could be blocked for schema as supabase protect superuser role. You can also re-upload all buckets you saved using rclone or the Studio.

I'm sharing with you the script I'm using to backup schemas. You can select which schema you want to include in your backup but also specify if you want --schema-only or --data-only. You can give it a try:

You can set up environment variables (export DB_PASS='mysecretpassword') or pass the params, as follows:

./pg_backup.sh --DB_HOST=<region>.pooler.supabase.com --DB_PORT=6543 --DB_NAME=postgres --DB_USER=postgres.<id> --DIR=./backup

Gist to the script

If you have any tips, please share

[igorlanko]

Could you share your rclone config? I'm constantly getting:

https response error StatusCode: 403, RequestID: , HostID: , api error SignatureDoesNotMatch: The request signature we calculated does not match the signature you provided. Check your key and signing method.

when trying any operations with Supabase Storage via S3 config in rclone config.

[baptiste-mnh]

Same here, it works for me with boto3 or the AWS CLI.
Probably something wrong with the rclone config ?

[supabase]
type = s3
provider = Other
region = eu-central-1
access_key_id = XXXXXXXXXXXXXX
secret_access_key = XXXXXXXXXXXXXXXXXXXXXXXXXX
endpoint = https://xxxxxxxxxxxxxxx.supabase.co/storage/v1/s3
signature_version = 4