Recently we had requirement in one of the project to migrate Heroku PostgreSQL database to SoftLayer (dedicated server) psql database.

One major issue we faced during migration i.e. import Heroku pg database to SoftLayer database using pg_restore is the version conflict between the dump file that Heroku generates (for psql backups) and the old version 8.4. of PostGreSQL that SoftLayer instance has.

We upgraded PostgreSQL to version 9.1 on SoftLayer. Remember to upgrade version above Heroku dump file or use same version but not below versions as pg_restore doesn't handle backward compatibility properly for dump files from Heroku. We were then smoothly able to import dump file to softlayer psql database, except for few minor permissions issues.

Here are the steps for this migration process in case if its helpful for your project or in case if you face similar issues

  1. Download pg database from Heroku. This will download the dump file that pg-backups takes on Heroku.

  2. scp it to server SoftLayer, lets say in /home directory

  3. Create database using command (inside app repo) RAILS_ENV=production rake db:create. This will create empty database (say for example "project_name_production") in postgresql database. Please make sure not to use pg_restore command to create database using --create --dbname options.

  4. Use this command to import dump file

pg_restore -i -h localhost -U postgres file_name.dump -d database_name --no-owner

Note that we need to use --no-owner option as otherwise pg_restore issues ALTER OWNER or SET SESSION AUTHORIZATION statements to set ownership of created schema elements. This option is important while importing DB dump.

  1. Test. You can also login to pgsql prompt to check database on terminal.