export Postgres backups

Questions / Help
1

Hello, is there a way to run something to extract a Postgresql backup, we would like to run a pg_dump every x number of hours?

Thanks

2 Likes
2

There’s not a great way to schedule jobs on Fly.io apps right now. Your best bet would be to install cron on a separate app (or a VM you run elsewhere) and have it do the pg_dump for you.

1 Like
3

how would you pg_dump the postgres database on fly.io?
I can’t find the documentation for this.

4

set up a tunnel to the database, us pg_dump locally while connecting to that database. Connecting remotely is described here; External Connections · Fly Docs

2 Likes
5

excuse me please, but could you elaborate a bit more on how to create a tunnel to the database? The linked documentation does not mention this.

6

You are right, in this guide (Private Networking · Fly Docs) go to the section “Private Network VPN” and this will allow you to connect to the PostgreSQL DB, and then you just instruct pg_dump to dump database with a .internal hostname.

7

You can also run fly proxy 15432:5432 -a <pg-app-name>, then connect to localhost:15432.

8

Thanks kurt! I am trying to connect to postgres with psql -h localhost -p 15432 but it’s prompting for user/password. What would that be?

9

When you created your Postgres cluster (via flyctl) it gave you a username & a password to connect to your database, as well as a postgres://user:password@host/dbname-like URL that you may be using to connect to it from your apps. You can find it there as well.

Unfortunately for security reasons, we cannot access (and thus give you) your Postgres credentials. But again, if you have some app writing data to it, you have these somewhere!

Cheers

10

See the link to my post: Backup and Restore PostgreSQL in fly.io, step by step

11

I trying this but the process can’t be finished!
fly proxy 15432:5432 -a <database-app-name>
Do you know why?

12

Hi… This is normal behavior, actually: it stays running, continuously passing packets back and forth across the reaches of the Wireguard tunnel…

Proxying local port 15432 to remote [pg-app-name.internal]:5432

Once you see the above message, you should be able to connect.

(In a different terminal window / tab.)

13

From How To to Questions / Help

14

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.