# Migrating from Heroku Postgres to Fly Postgres: A Complete Guide **URL:** \ **Category:** Show & Tell\ **Tags:** docs, grafana, postgres\ **Created:** [March 3, 2022, 11:39pm UTC](https://community.fly.io/t/migrating-from-heroku-postgres-to-fly-postgres-a-complete-guide/4262 "2022-03-03T23:39:35Z")\ **Posts on this page:** 2\ **Page:** 1
**Author:** ![peterpme](https://sea1.discourse-cdn.com/fly1/user_avatar/community.fly.io/peterpme/32/260_2.png) [@peterpme](https://community.fly.io/u/peterpme)\ **Post date:** [March 3, 2022, 11:39pm UTC](https://community.fly.io/t/migrating-from-heroku-postgres-to-fly-postgres-a-complete-guide/4262/1 "2022-03-03T23:39:35Z")
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 ```auto 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! ```auto 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. ```auto # 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](https://github.com/fly-apps/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: ```auto 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: ```auto [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: ```auto fly proxy 5432 -a ``` If you like to use psql, you can use fly’s connect command: ```auto fly postgres connect -a ``` 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](https://fly.io/docs/reference/postgres/#connecting-to-postgres-from-outside-fly) # 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](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 ```auto 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](https://git) There’s a Postgres Dashboard @jerome sent to me. It was over Slack so [I created a gist](https://gist.github.com/peterpme/1f10f5fc9936904baa04573f0d3ab61a). 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](https://fly.io/docs/reference/metrics/#grafana) # 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` ```auto 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: ```auto fly pg connect --app grant pg_read_all_stats TO ; ``` 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! ---
**Author:** ![Brad](https://sea1.discourse-cdn.com/fly1/user_avatar/community.fly.io/brad/32/4429_2.png) [@Brad](https://community.fly.io/u/Brad)\ **Post date:** [October 18, 2022, 9:39pm UTC](https://community.fly.io/t/migrating-from-heroku-postgres-to-fly-postgres-a-complete-guide/4262/2 "2022-10-18T21:39:57Z")
I’m bumping into this problem in some Rails app migration docs I wrote at [Migrate from Heroku · Fly Docs](https://fly.io/docs/rails/getting-started/migrate-from-heroku/#transfer-the-database) If anybody reading this runs into problems migrating their database from Heroku to Fly, you might try those instructions.