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
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
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
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.
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.
You can also run fly proxy 15432:5432 -a <pg-app-name>, then connect to localhost:15432.
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?
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