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.
heroku pg:backups capture --app myapp curl -o latest.dump `heroku pg:backups public-url --app myapp`
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:
We also need a target database:
Then to import the database (back on the command line):
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.
pg_restore --verbose --clean --no-acl --no-owner -h localhost -U importermydatabase latest.dump
Back in postgres as a superuser, switch to the new database and assign the correct ownership:
\c mydatabase REASSIGN OWNED BY importer TO myuser;