Migrate PostrgreSQL Database From Heroku or Fly.io To Hetzner VM

Export of Database in General

The first step is to export your existing database. Generally speaking, this involves SSHing into your server and doing a pg_dump. I'll explore below how this is done with Fly.io and with Heroku but here's the general step:

Log in to your VM via SSH using the following command:

ssh user@source-vm-ip

Normally this would look something like this, if you've added your SSH Key to your Server account (E.g. on Hetzner):

ssh root@123.114.221.231

This should get you to your root directory of your Server. Once you're in, you can use the pg_dump command to export your database into a SQL file. Make sure to replace dbname with the actual name of your database and filename.sql with your preferred output file name:

pg_dump dbname > your-backup.sql

This command will create a dump file containing all the data from your specified database.

Fly.io

Here's how you can create a pg_dump backup file using Fly's CLI.

Go to your application's fly console:

fly ssh console -a your-application

Get the database name, login, and password:

echo $DATABASE_URL

It will show the string in the following format:

postgres://some_login:some_password@some-address.internal:5432/some_db_name?sslmode=disable

Go to the DB machine console:

fly ssh console -a your-app-db

FYI: You can find the exact names of your machines on your fly.io dashboard.

Make a dump, using the credentials you got from the DATABASE_URL:

pg_dump --username=some_login --dbname=some_login --port=5432 --host=top2.nearest.of.some_db_name.internal > ./your-backup.sql
-> PW: some_login

You may have to experiment a bit, but the required fields should all be in your db url.

Make sftp connection to the DB machine

fly ssh sftp shell -a your-app-db

Download the dump using SFTP session:

get ./your-backup.sql

Now import the database to your target server, as outlined here!

Heroku

Capture a database backup from Heroku using your heroku cli:

heroku pg:backups:capture -a your-app-name

Download the database from Heroku:

heroku pg:backups:download -a your-app-name

The database will get downloaded as latest.dump file on your computer.

Import latest.dump to Postgres(your local database):

pg_restore --verbose --clean --no-acl --no-owner -h localhost -U your-username -d your-database-name latest.dump

This should give your your backup.sql file, which you can now use to import to another server, as outlined here!