Restore Postgres DB in fly.io - PG version mismatch issue workaround

Problem

Restoring Postgres databases in Fly.io is super easy; we just need to run the below command in the Fly console as mentioned in the Fly.io Heroku migration documentation.

pg_dump -Fc --no-acl --no-owner -d $HEROKU_DATABASE_URL | pg_restore --verbose --clean --no-acl --no-owner -d $DATABASE_URL

But sometimes we might get a version mismatch error because the fly DB Postgres version and the Postgres DB version that host in the other service are different.

pg_dump: server version: 13.10; pg_dump version: 14.7
pg_dump: aborting because of server version mismatch

Workaround

As a workaround to fix this version mismatch issue, instead of running pg_dump and pg_restore in the web app, we can take a DB dump and restore it to Fly DB from the local environment.

In the below steps, I explain how to restore the DB dump in the Fly database.

If Postgres app is already there in Fly.io, skip to Step 2.

1. Create a Postgres DB

Create a Postgres DB on the fly by running the following command:

fly pg create --name <app name>

E.g.,

fly pg create --name myapp-db

Output be something like this,

Creating postgres cluster myapp-db in organization personal
Creating app...
Setting secrets...
Provisioning 1 of 1 machines with image flyio/postgres-flex:15.2
Waiting for machine to start...
Machine 5683004b797d8e is created
==> Monitoring health checks
  Waiting for 5683004b797d8e to become healthy (started, 3/3)

Postgres cluster myapp-db created
  Username:    postgres
  Password:    ***************
  Hostname:    myapp-db.internal
  Flycast:     fdaa:0:2e26:0:1::b6
  Proxy port:  5432
  Postgres port:  5433
  Connection string: postgres://postgres:********@myapp-db.flycast:5432

Save your credentials in a secure place -- you won't be able to see them again!

Connect to postgres
Any app within the organization can connect to this Postgres using the above connection string

Now that you've set up Postgres, here's what you need to understand: https://fly.io/docs/postgres/getting-started/what-you-should-know/

Store the Postgres cluster credentials somewhere, we will use these credentials later.

2. Connect to a remote Postgres DB

flyctl proxy 5434:5432 -a <DB app name>

In this case,

flyctl proxy 5434:5432 -a myapp-db

This will establish a proxy forwarding to the localhost on port 5434.

3. Create a new DB

Skip this step if Postgres cluster already has a database with the app’s name.

In a new terminal, run the following command:

  psql -h localhost -p 5434 -U <User name from credential>

In this case,

  psql -h localhost -p 5434 -U postgres

Enter the password (from the credentials) then, in the psql console, run the following command:

CREATE DATABASE <web app name>;

E.g.,

CREATE DATABASE myapp;

This will create the DB and ensure that the DB name is the same as the app name.

4. Restore Dump to DB

Open a terminal in the path where the PG dump file is saved and run the following command:

pg_restore -v -d postgresql://<user>:<password@localhost:5434/<database_name> <  <pg dump file path>

E.g.,

pg_restore -v -d postgresql://postgres:***************@localhost:5434/myapp < filename.dump

This will restore the records in the database.

5. Attaching a Fly app

Skip this step if the app is attached to a Postgres cluster.

Ensure that the fly web app doesn’t have a secret key with the name DATABASE_URL, if a secret with that name exists, then unset that secret by running the following command:

 fly secrets unset DATABASE_URL -a <web app name>

In this case,

 fly secrets unset DATABASE_URL -a myapp

This will remove the DATABASE_URL secret from the web app.

To attach the fly app to the Postgres cluster, run the following command:

flyctl postgres attach <DB app name> --app <web app name>

In this case,

flyctl postgres attach myapp-db --app myapp

Running this command will add DATABASE_URL secret to the web app.

Now, when we access the site we can see all the data there, and we can also verify the records from the rails console.