Migrating from Heroku Postgres to Fly Postgres: A Complete Guide

How To
, ,
1

Hi everyone,

We recently successfully migrated away from Heroku Postgres to Fly Postgres. Here are the steps, start to finish:

  • Setup
  • Migrating Database
  • Updating Connection Options
  • Updating Deployment Migrations
  • Accessing locally
  • Setting up pg_stats
  • Setting up grafana
  • Setting up pg-hero

Setup

To create your postgres database, run the following command. Make sure you update all values appropriately. Keep the initial cluster at 1

fly pg create --name your_db --organization your-org --region your-region --vm-size your-vm-size --volume-size your-volume-size --initial-cluster-size 1

Once the operation is complete, attach this new database, under a different environment variable like FLY_DATABASE_URL to your running fly app.

The reason you’re setting up a different environment variable is because you don’t want it to affect your current app, but you still want the extras this command runs.

fly pg attach will create a new user, set up roles and return a database url when complete.

Make sure you save that database url!

fly pg attach --variable-name FLY_DATABASE_URL --database-name your-db -a your-app --postgres-app your-db

Next, the migration will require your user to have CREATE DB access since it’ll try to recreate the Heroku db name. You can decide to rename the database later.

# Connect to psql
fly pg connect --app your-app

# add CREATEDB permissions
alter user your-user WITH CREATEDB; # don't forget semi

# This confirms that your user has the role:
\du+

Migrating Database

Fly has a simple app you can run that will migrate your database over directly: postgres-migrator

Setting up the migrator app instructions are straight forward. There’s a PR as of 3/3/21 with some updated instructions.

You’ll have to set 2 environment variables:

  • SOURCE_DATABASE_URL your old Heroku postgres database
  • TARGET_DATABASE_URL your new Fly postgres database. You can leave off the database name and the dump will create it for you.

Once this is provisioned, you should be able to run the migration script:

fly ssh console -C "migrate" -a postgres-migration

The script can take anywhere from a couple minutes to hours depending on the size of your database. It’ll emit “All Done!” when it’s done. If you have less than 10gb of data, it should only take a couple minutes.

You might run into some errors. Most of the time, they should be ok. Make sure you look through the logs and confirm you don’t need to make a change.

If you have certain extension errors you need to install, we didn’t have to do that so you’re on your own.

Updating Connection Options

Since Heroku Postgres has a publicly-accessible URL, there have always been extra SSL settings you had to add to your ORM config or connection string.

If you had ?sslmode=true&pgsslmode=require in your connection string, remove it. There shouldn’t be any extra params.

If you had extra config params like ssl: true, rejectUnauthorized: false make sure you remove those too.

Fly Postgres doesn’t need SSL since its shielded from the public internet, which means all the fancy things Heroku requires us to do are no longer necessary.

Make sure you have a PR or deployment ready with these changes. We did a lot of testing before hand on staging databases.

Updating Deployment Migrations

We previously ran migrations by connecting to the publicly accessible database url and running the migrations command over the wire. We updated our deployment script by adding a deploy param to our fly.toml file:

[deploy]
  release_command = "./node_modules/typeorm/cli.js migration:run"

Accessing Locally

If you’re like me, you use something like Postico or another Postgres GUI. Similarly to your ORM config, you’ve had to add special settings and params to your URL to be able to connect. Those no longer work.

In order to connect to your Postgres database, you’ll have to start a fly proxy:

fly proxy 5432 -a <postgres-app-name>

If you like to use psql, you can use fly’s connect command:

fly postgres connect -a <postgres-app-name>

For the connection url, make sure you replace the hostname, something like @top2.internal.localhost.yourapp (something like that) and replace it with @localhost

The @internal.localhost hostname is internal to Fly and isn’t necessary when proxying.

You can read about other connection options here

Setting up pg-stats

pg_stats is where the metrics from Heroku Postgres came from. I’ll admit these commands look a little scary, but trust me, it all worked out fine!

Stolon is tool to manage high availability Postgres. You can learn more about it here https://github.com/sorintlab/stolon. I’ve never used or heard of this before so don’t feel bad if you haven’t either.

The breakdown of the commands are as follows:

  • Connect to your-db
  • export vars
  • Update stolon to include pg_stat_statements
  • Restart your postgres database (instant)
  • Verify that pg_stat_statements shows up under pgParameters
  • Run connect in the same console to connect to psql
  • Add the extension
fly ssh console -a your-db

export $(cat /data/.env | xargs)

stolonctl update --patch "{ \"pgParameters\" : {\"shared_preload_libraries\": \"pg_stat_statements\" } }"

# restarts postgres
su stolon -c "pg_ctl -D /data/postgres restart"

# verifies the pg params
stolonctl spec

connect
CREATE EXTENSION pg_stat_statements
\q

With this, you’ll be able to collect some really great metrics with Grafana and pg-hero

Setting up Grafana

Grafana is a cool-looking Dashboard. Fly has a 1st class integration via Prometheus. The metrics are fun to look at. Don’t go over board or you might under resource your app like I accidently did!

Setting up Grafana is straight forward, just follow these instructions provided by [fly/grafana](https://git

There’s a Postgres Dashboard @jerome sent to me. It was over Slack so I created a gist. Credit goes to him, not me!

Click “Raw” on the Gist, then go to the + in Grafana and import via URL. Viola, you have metrics.

You can learn more about Fly, Prometheus & Grafana here

Setting up pg-hero

pg-hero is a performance dashboard for Postgres. It’ll show you any connections, long running queries, suggestions & more

  • Create a fly.toml with the code snippet below
  • Set DATABASE_URL, PGHERO_USERNAME, PGHERO_PASSWORD as environment variables via fly secrets set 
app = "pg-hero"

kill_signal = "SIGINT"
kill_timeout = 5
processes = []

[build]
  image = "ankane/pghero"

[env]
  PORT = "8080"

[experimental]
  allowed_public_ports = []
  auto_rollback = true

[[services]]
  http_checks = []
  internal_port = 8080
  processes = ["app"]
  protocol = "tcp"
  script_checks = []

  [services.concurrency]
    hard_limit = 25
    soft_limit = 20
    type = "connections"

  [[services.ports]]
    handlers = ["http"]
    port = 80

  [[services.ports]]
    handlers = ["tls", "http"]
    port = 443

  [[services.tcp_checks]]
    grace_period = "1s"
    interval = "15s"
    restart_limit = 0
    timeout = "2s"

You’ll also have to expose pg_stats to your pg-hero instance:

fly pg connect --app <pg-app-name>

grant pg_read_all_stats TO <whatever-user-pg-hero-is-using-to-connect>;

Don’t forget the semi!

Big thanks to @kurt, @shaun, @jerome, @danwetherald for helping us get here! This was a huge under-taking and everyone has been so helpful!

19 Likes
2

I’m bumping into this problem in some Rails app migration docs I wrote at Migrate from Heroku · Fly Docs

If anybody reading this runs into problems migrating their database from Heroku to Fly, you might try those instructions.

1 Like