Without giving too much backstory, I had a web application connected to fly.io's postgres offering. Fly.io admits that this is not a managed database and I decided I wanted a managed database on a major cloud provider's infrastructure, with all of the niceties that come along with that.
Assumptions
I am assuming that you have a local installation of PostgreSQL and its cli utilities. Installation is out of scope for this post, but specifically, you should have pg_dump
and pg_restore
in your path, which you can verify with
which pg_dump && which pg_restore
I am also assuming that you have created a Digital Ocean (or other) database already.
Part 1: Exporting Data From Fly Postgres
First things first, we need to export our data from our current database in Fly.io. To do this, we need to proxy a connection to our database server using the flyctl proxy command. First, verify or identify the name of your database application in Fly by running
flyctl apps list
This should help you identify the name of the postgres app. Once we have this, we need to proxy a connection to the app by running
flyctl proxy 15432:5432 -a <pg-app-name>
I am proxying the connection to local port 15432
instead of the postgres default of 5432
because I already have a pg service running locally via docker. With this connection established, in a new terminal tab, we can run
pg_dump -h localhost -p 15432 -U <user> -Fc <db> > <backup.pgsql>
You can consult some relevant documentation in Digital Ocean for a detailed breakdown of the flags but the gist is that we are connecting to localhost:15432 and dumping the output into a file we named backup.pgsql
. You can obtain the user and password via the connection string that your fly app is using. This should have been set when you configured the app's connection to postgres via flyctl pg attach
. If you don't readily have this information, you can obtain it by SSHing into the application and running a printenv
to discover the DATABASE_URL
. Once the pg_dump
was successful, we can move onto the import.
Part 2: Importing Data To Digital Ocean
With the pg_dump
complete and our snapshot ready to restore, run
pg_restore -d <do_connection_string> <path/to/backup>
Your digital ocean connection string is readily accessible in the Digital Ocean dashboard. Take care to wrap the connection string in quotes. With any luck, you should now have restored your data to your new DO database. From here, we can update our application.
Part 3: Updating Application Servers
Take precautions with your approach if your application is in production, but we can now run
flyctl secrets set DATABASE_URL="<do-db-connection-string>"
which will update your application server to now point to your DO database. You can now spin down the fly postgres machines. This path would apply to other managed database providers as well as a restore workflow within a single database, but I just wanted to highlight some of the nuances I encountered specifically migrating off of Fly.io. Hope it helps!
Top comments (0)