Restoring a local database on fly.io postgres instance

Questions / Help
1

Hello, I’ve just started playing around with Fly.io and I’m really liking it so far, I’ve migrated a phoenix app and would also like to restore my development database to production, is this possible?

Cheers

2 Likes
2

Hey there,

and would also like to restore my development database to production,

Could you elaborate on this a bit more?

3

Of course Shaun,
So I’ve been developing an application locally (including adding data to my database), and rather than having to add that data all again, was wondering if i can restore a pg_dump of the local data to the postgres instance i created using the fly postgres create command.
I guess on a more general level I’m wondering if it’s possible to restore a backed up database to a fly postgres instance, including backing up a fly postgres instance, and restoring it

4

I guess on a more general level I’m wondering if it’s possible to restore a backed up database to a fly postgres instance

Yep! As long as you’ve set up a Wireguard tunnel, this will be easy to accomplish. If you haven’t yet, here’s a step-by-step guide: Private Networking · Fly Docs

Once you’ve provisioned your Postgres via fly postgres create, you’ll be provided with a connection string that you can use to connect. From here, it should just be a matter of issuing a pg_restore.

Something like this should work:

pg_restore -v -d [FLY_POSTGRES_URI] < [path-to-dump-file]

including backing up a fly postgres instance, and restoring it

Here’s some information going over the PG restore process: Multi-region Postgres (Legacy) · Fly Docs

Let me know if you have any other questions!

3 Likes
5

Awesome Shaun, I’ll give that a shot, thanks for the quick response

6

there is easier way to accomplish this: Postgres on Fly · Fly Docs

7

Thanks @shaun,
the only issue I have now is that the pg_restore command imports the tables into the posgres database and not into the databse of my choosing. When I go into the psql shell I can see the tables under postgres database now but they should go into the database of my app that I’ve created.
Previously I’ve defined this in the -d parameter but this is now used up by my FLY_POSTGRES_URI

8

@flov Maybe try specifying the database name at the end of your URI.

FLY_POSTGRES_URI =postgres://<user>:<password>@<hostname>:<port>/<db-name>

9

@shaun Thank you, that did the trick :sweat_smile: I can’t believe I haven’t tried this myself.

1 Like
10

This blog shows another solution:

Assuming we have some basic knowledge of psql, summary:
Use fly proxy command from fly proxy · Fly Docs to link localhost:xxxx to my fly db at port yyyy.
Then just use psql functions to log into the db (if you need to create roles or databases).
Or use things like psql <db-name> < <my_dump_file>.sql -h localhost -p <port> -U my_username to populate my db.

1 Like