postgresqlherokudatabase-migrationheroku-postgresrailway.js

Migrate PostgreSQL database from Heroku to Railway

I am trying to find out how to migrate my PostgreSQL database easily from Heroku to Railway. I have tried using heroku pg:backups:capture --app APP_NAME and heroku pg:backups:download with pg_restore and no success.

I had the error

pg_restore: error: corrupt tar header found in PGDMP (expected 0, computed 19471) file position 512
Solution

  • TL;DR:

    heroku login
heroku run 'pg_dump $DATABASE_URL' > <filename.sql> --app <heroku-app-name>
PGPASSWORD=$PGPASSWORD psql -h $PGHOST -U $PGUSER -p $PGPORT -d $PGDATABASE -f <filename.sql>

    Details:

    If you logged in successfully after running heroku login, $DATABASE_URL is read from your Heroku environment, so no need to insert the database url by hand.

    <filename.sql>: specify any filename you like. You will use it later to import the database.

    <heroku-app-name>: The name of your Heroku (backend) app with the Postgres database.

    Provision a new PostgreSQL database in your railway project and grab $PGPASSWORD, $PGHOST, $PGUSER, $PGPORT, and $PGDATABASE from the Variables tab.

    This is what it should look like

    Example (with dummy credentials):

    heroku login
heroku run 'pg_dump $DATABASE_URL' > mydatabasebackup.sql --app my-heroku-backend
PGPASSWORD=hjUasj8hasA6ahsjJaf3ash psql -h containers-us-west-15.railway.app -U postgres -p 6473 -W -F t -d railway mydatabasebackup.sql