Migrate from Heroku-Postgresql

Overview

Migrating from Heroku is quick and easy using standard Postgres database archival and recovery tools. In this guide, we walk you through the process of:

1. Installing postgres with the bundled pg_dump and pg_restore tools
2. Archiving your Herkou-Postgresql database
3. Restoring your database to bit.io

Installing pg_dump and pg_restore

Postgres comes bundled with a number of tools including pg_dump and pg_restore. We can use pg_dump to archive a Heroku-Postgres database and pg_restore to restore the database as a bit.io database. You can read more about the general usage of pg_dump and pg_restore here.

Postgres installation varies by system:

Max OSX:

brew install postgres

Debian and Ubuntu:

sudo apt install postgres

Fedora and Red Hat:

sudo dnf install postgresql

Windows:

Download the official postgres installer from the official postgres docs and follow the provided prompts/instructions.

Archiving your Heroku-Postgres Database

On heroku.com, navigate to the "Settings" for your Heroku-Postgres resource and copy the "URI" field (blue highlight) under database credentials.

Using the "URI" field, run pg_dump with the following options as shown below:

• --no-privileges - bit.io provides streamlined database permissions, similar to Google-docs, that supersede lower-level Postgres permissions. This option tells the utility to not export existing Postgres permissions that are not applicable on bit.io and would otherwise cause error messages when restored to bit.io. You can read more about bit.io sharing and permissions in our getting started guide.
• --format custom - created a compressed archive that is compatible with pg_restore.
• --file - specify a file name for the archive on the machine running the command.

pg_dump --no-privileges --format custom --file heroku_archive \
postgres://youruserid:[email protected]:5432/yourdatabaseid

You should now have a file containing an archive of your Heroku-Postgres database.

Restoring Your Database to bit.io

Next, create an account on bit.io and create a free database. From your database page, click the connect tag and copy your bit.io Postgres connection string.

Finally, use pg_restore with the --no-owner option (ownership is superseded by bit.io managed permissions and sharing, as discussed above) and your bit.io Postgres connection string to restore your database from the archive file.

pg_restore --no-owner \
-d postgresql://yourusername:[email protected]/yourdatabasename \
"heroku_archive"

From there, you can use the bit.io connect tab to get credentials for any existing applications or clients that connect to your database.

🚧

Need Help?

If you run into issues not covered in this guide, please reach out to us for help at [email protected]!