Exporting a postgres database from Heroku and importing to local install

Continuing with my efforts to learn some basic, useful postgres admin commands it’s time to look at importing and exporting data. We are going to export a postgres database from Heroku and import it to a local postgres install for development.

I’m assuming the Heroku toolbelt and postgres are installed locally and myuser is already created. I’ve written some very basic pointers to (local dev) postgres installation and administration already.

We are going to export the database used in myapp and import it locally to mydatabase to be owned by myuser. Brace thyself.

Export from Heroku

This is the easy bit.

heroku pg:backups capture --app myapp
curl -o latest.dump `heroku pg:backups public-url --app myapp`

Boom.

Import to local

We are going to use the pg_restore command, but that needs to import as a postgres superuser. It will also prompt for a password, even if the user is set up for peer authentication (as per my last post) so we’re going to create an importer user with superuser powers. There is probably a better way to do this, but life is short…

Logged in to postgres as a superuser:

CREATE USER importer WITH PASSWORD 'mypassword';
ALTER USER importer WITH SUPERUSER;

We also need a target database:

CREATE DATABASE mydatabase;

Then to import the database (back on the command line):

pg_restore --verbose --clean --no-acl --no-owner -h localhost -U importer -d mydatabase latest.dump

This will throw some errors when the DROP commands in the Heroku export fail. This seems to be ok, but check nothing else has gone wrong. There is probably a way to have Heroku export the database without the drop statements to eliminate these messages.

Back in postgres as a superuser, switch to the new database and assign the correct ownership:

\c mydatabase
REASSIGN OWNED BY importer TO myuser;

Done!