How To Create Backup From PostgreSQL on Fly.io

Before we start, this is the version I tested it with:

> fly version
fly v0.2.12

> SELECT version();
PostgreSQL 14.6 (Debian 14.6-1.pgdg110+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 10.2.1-6) 10.2.1 20210110, 64-bit

The first step is to proxy the remote port 5432 to a local port. Since I have already a local PostgreSQL instance running, I will forward it to port 5433. If you don’t have any port-conflicts, keep the port 5432.

Run the following command:

fly proxy 5433:5432 -a <your-fly-instance>

The next step is copy the database wth pg_dump. I go the following error when I tried it:

pg_dump: error: server version: 14.6 (Debian 14.6-1.pgdg110+1); pg_dump version: 15.4

pg_dump: error: aborting because of server version mismatch

Seems the version has to be the same. My solution was to use a docker image to run the exact postgres version.

docker run --net=host --rm -i postgres:14.6 pg_dump -h host.docker.internal -p 5433 -U postgres -d <your-db-name> > db_dump..sql

A few notes for future me:

• This command runs a single commands and deletes the image afterward (the --rm flag)
• The --net=host flags forces docker to use the local network stack,that way the docker instance can connect to port 5433 on my host machine
• host.docker.internal is a special URL which resolves to the local network (afaik, take this with grain of salt, my Docker knowledge is limited)[0]

[0] https://docs.docker.com/desktop/networking/#i-want-to-connect-from-a-container-to-a-service-on-the-host