Wednesday 25 May 2016

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!

1 comment:

Simon said...

Pro tip: Instead of adding `--app myapp` to the end of Heroku commands, you can use the name of the remote in .git/config. I usually have production and staging versions of apps, with the appropriate names for the remotes in git's config, making it easy to run `heroku do:something -r staging`, etc., with the added bonus that you can then easily alias these commands because they're the same across projects :)