Recently we had requirement in Mamasan project to migrate Heroku PostGreSQL database to SoftLayer (dedicated server) psql database

One major issue we faced during migration (import of DB to SoftLayer DB) using "pg_restore" is the version conflict between the dump file that Heroku creates (for pgsql 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 pgsql database (except few minor permissions issues.)

Here is the step by step process for this migration process (in case its helpful for your project or in case if you face similar issues)

  1. Download DB from Heroku (this will download the dump file that pg-backups takes on Heroku)

  2. SCP it to server SoftLayer (probably in /home directory)

  3. Create DB using command (on repo) RAILS_ENV=production rake db:create. This will create empty database (say for example "mamasan_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 dabase_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 important DB dump.

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