How to backup and restore Fly.io Postgres DB

[nelsonic]

We have a Database Error that is causing our MVP to fail catastrophically: dwyl/mvp#281
We need to:

• backup the DB
• run it on localhost and
• determine how to resolve the issue.

Starting point: https://fly.io/docs/rails/the-basics/backup-and-restoring-data/

[nelsonic]

This guide only tells us how to backup/restore on Fly.io.
It doesn't give us a way to export the data off the platform. 💭

[nelsonic]

Daily backup snapshots are not being created. 😢
Need to investigate why ... 🔍

[nelsonic]

Reading: https://www.advantch.com/blog/automate-postgres-database-backups-on-fly-dot-io/

[nelsonic]

Backups are working:

fly volumes snapshots list vol_2n0l9vlnypor635d

You should see something similar to:

Snapshots
ID                    	SIZE    	CREATED AT
vs_50bygMmomApnXf6wmL 	62392863	22 hours ago
vs_QnL5Q9ZlZ6mwyh7Dy0Y	60940180	1 day ago
vs_0w0ym5D9D61ngC91VVl	59556750	2 days ago
vs_zQenbPLJLM3YlcMNaDB	59471566	3 days ago
vs_7zQy75mamR4nNf950xk	59379026	4 days ago
vs_ywgVbONANRPxXCGX15M	61236290	5 days ago

[nelsonic]

installing DBeaver to attempt to connect to the Postgres DB directly: https://dbeaver.io/download/

brew install --cask dbeaver-community

[nelsonic]

Created a new cluster off the snapshot:

fly postgres create --snapshot-id vs_50bygMmomApnXf6wmL

Output:

? Choose an app name (leave blank to generate one): debugging-281
? Select Organization: dwyl-mvp (dwyl-mvp)
? Select region: London, United Kingdom (lhr)
? Select configuration: Development - Single node, 1x shared CPU, 256MB RAM, 1GB disk
Creating postgres cluster in organization dwyl-mvp
Creating app...
Setting secrets on app debugging-281...
Restoring 1 of 1 machines with image flyio/postgres:14.4
Waiting for machine to start...
Machine 5683605ade058e is created
==> Monitoring health checks
  Waiting for 5683605ade058e to become healthy (started, 3/3)

Postgres cluster debugging-281 created
  Username:    postgres
  Password:    nHcQaJGbTfZCqFO
  Hostname:    debugging-281.internal
  Proxy port:  5432
  Postgres port:  5433
  Connection string: postgres://postgres:nHcQaJGbTfZCqFO@debugging-281.internal:5432

Save your credentials in a secure place -- you won't be able to see them again!

Connect to postgres
Any app within the dwyl-mvp organization can connect to this Postgres using the above connection string

[nelsonic]

Connect to the DB locally:

flyctl postgres connect -a debugging-281

[nelsonic]

Port forwarding:

flyctl proxy 5432 -a debugging-281

proxy works but cannot connect form DBeaver ...

reading: https://community.fly.io/t/connecting-to-fly-database-from-local-elixir-application/3231

[nelsonic]

Working:

With the config:

and proxy running:

flyctl proxy 5434:5432 -a debugging-281

[nelsonic]

Attach the mvp app to the debugging-281 DB and see if it works. ⏳

[nelsonic]

https://fly.io/docs/postgres/managing/attach-detach/

flyctl postgres detach --app <app-name> <postgres-app-name>

e.g:

flyctl postgres detach --app mvp mvp-db

Prompt:

? Select the attachment that you would like to detach (Database will remain intact):   [Use arrows to move, type to filter]
> PG Database: mvp, PG User: mvp, Environment variable: DATABASE_URL

but get the error:

Error error running user-delete: 500: ERROR: role "mvp" cannot be dropped because some objects depend on it (SQLSTATE 2BP01)

[nelsonic]

Fly.io completely lost our data. Ref: dwyl/mvp#449 (comment) 🔥
So this issue is void. 😢

GOTO: #90 🧑‍💻