Importing a postgresql dump to Heroku

Question

I have a java app and postgresql database to go with it that is running on Heroku. I can push my app just fine, but what about the DB contents? I have exported a full dump from the database, but I don't know how I could import that.

By googling, you can find about db:push which is a limited rubygem, not pushing all the stuff needed. I have sequences, bigint datatypes etc. I also tried importing using heroku pg:psql --app MYAPP < db_all.out which just connects and stops, and going to heroku pg:psql --app MYAPP and issuing \i db_all.out complaints about permissions.

How should I do it?

Answer 1

You can run the pg_restore command from your local machine using the credentials given by heroku pg:credentials HEROKU_POSTGRESQL_<COLOR>.

Answer 2

To help others who still stumble upon this issue, what works for me is hgmnz's answer, but with a few modifications.

To be more precise:

1. Create a dump from the source PostgreSQL database

   $ PGPASSWORD=YOUR_PG_PASSWORD pg_dump -Fc --no-acl --no-owner -h localhost -U YOUR_PG_USER YOUR_DB_NAME > YOUR_DB_NAME.dump
   

2. Get the Heroku Postgres credentials for your heroku app

   $ heroku pg:credentials:url -a YOUR_APP_NAME
   

3. Attempt to import PostgreSQL dump to Heroku using the credentials above

   $ pg_restore --verbose --clean --no-acl --no-owner -h HOSTNAME -U USER -d DATABASE -p PORT PATH/TO/YOUR_DB_NAME.dump --password
   

4. Enter the password received from the Heroku Postgres credentials
5. It should then import the dump successfully

Answer 3

This is very simple and had worked for me:

heroku pg:psql -a {YOUR_APP} -f {YOUR_DUMP_PATH}

I find this a better way then using the standard input syntax (like in the OP example), since it uses an option given by the Heroku command itself.

You may want to check if your dump file it's OK before submitting (in my case it wasn't).