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?

hgmnz · Accepted Answer · 2012-08-09 21:23:29Z

14

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

answered Aug 9, 2012 at 21:23

hgmnz

13.3k4 gold badges39 silver badges41 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

eis Over a year ago

Thank you! In practice, this was the command that was needed:

pg_restore --verbose --clean --no-acl --no-owner -h <HOSTNAME> -U <USER> -d <DATABASE> -p <PORT> <FILEPATH> password=<PASS>

lollercoaster Over a year ago

In Postgres 9.4, I found I needed:

PGPASSWORD=<PASS> pg_restore --verbose --clean --no-acl --no-owner -h <HOSTNAME> -U <USER> -d <DATABASE> -p <PORT> <FILEPATH>

Coherent Over a year ago

Thanks for this! Can you clarify whether I am entering the database info for your local postgres or for the heroku credentials that you get from pg:credentials? Thanks!

eis Over a year ago

@Coherent we're talking about heroku database here

Zaim Ramlan · Accepted Answer · 2021-01-22 13:07:32Z

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:

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

Get the Heroku Postgres credentials for your heroku app
```
$ heroku pg:credentials:url -a YOUR_APP_NAME
```

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

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

pdechery · Accepted Answer · 2022-02-21 13:05:10Z

1

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).

answered Feb 21, 2022 at 13:05

pdechery

215 bronze badges

Collectives™ on Stack Overflow

Importing a postgresql dump to Heroku

3 Answers 3

4 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

4 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related