[DX] Figure out how to make a local backup

[ykdojo]

Description

Now that we have more questions in our production database (ref: #232), we should figure out how to make a local backup.

Current plan: follow this.

[aakashraj01]

Hey @ykdojo Can you assign me this issue under Hacktoberfest 2022 label? I would love to contribute in this issue

[ykdojo]

Update:

I tried the methods described in the blog post, but it didn't quite work - I got a connection error.

I used pgAdmin and it seems like I was able to get a backup, but I had trouble restoring the production/dev databases from the backups, too.

[aakashraj01]

Update:

I tried the methods described in the blog post, but it didn't quite work - I got a connection error.

I used pgAdmin and it seems like I was able to get a backup, but I had trouble restoring the production/dev databases from the backups, too.

I also tried but error is coming always.

[CerealPlayer]

Hey @ykdojo I managed to make this work on my copy of defaang db in Supa bootstraped with supabase.sql file.

Instead of using the connection string (which errors because it needs a tls certificate, it could still be done), I used the pooling connection string, which is below the connection string in the settings panel. Pooling must be enabled.

Worth noting I run pg_dump in a Postgres docker container, much easier to setup than installing Postgres locally.

[ykdojo]

@CerealPlayer sounds good. Were you able to restore a DB from that?

[CerealPlayer]

Yes! it generated a dump.sql with all the info I had stored in my copy of defaang.

[ykdojo]

How did you upload it back to Supabase? That was another part I had trouble with.

[CerealPlayer]

This helps. The best approach would be to pg_dump certain tables that we know are critical and then pg_restore only those, since a full pg_dump would backup all schemas, and supabase doesn't support creating an empty database yet.

[ykdojo]

The best approach would be to pg_dump certain tables that we know are critical and then pg_restore only those, since a full pg_dump would backup all schemas, and supabase doesn't support creating an empty database yet.

Got it. Backing up a database and restoring it seems like a pretty basic feature to have, though (I remember I had it in Heroku when I was using it).