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
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
-W is force ask for password, ignoring the one that's already in the command. So this isn't required. -F t I think this sets the file format? But it's already specified as .sql in the filename so this also isn't required?