How can I import a .sql file into my Heroku postgr

Question

I have a backup sql file from another database that I want to import into Heroku's postgres database. How do you do that?

Answer 1

Load the SQL into a local Postgres instance and make sure it's correct. Then dump the data using the directions here: https://devcenter.heroku.com/articles/heroku-postgres-import-export

Finally, upload the dump to a public web server (like S3) and restore to Heroku like this:

heroku pgbackups:restore DATABASE 'https://s3.amazonaws.com/me/items/3H0q/mydb.dump'

Answer 2

This is how you do it:

heroku pg:psql --app YOUR_APP_NAME_HERE < updates.sql

And if you want to restore your production into staging (assuming both are heroku postgres DBs):

heroku pgbackups:restore YOUR_STAGING_DATABASE_NAME `heroku pgbackups:url --app YOUR_PRODUCTION_APP_NAME` --app YOUR_STAGING_APP_NAME --confirm YOUR_STAGING_APP_NAME

Make sure to preserve the special single quotes around "heroku pgbackups:url --app YOUR_PRODUCTION_APP_NAME".

-------- HEROKU TOOLBELT UPDATE --------

Heroku has recently updated their toolbelt so the old commands are no longer valid (see this link for more info). Below is the new version of the restore command.

heroku pg:backups restore `heroku pg:backups public-url -a YOUR_PRODUCTION_APP_NAME` YOUR_STAGING_DATABASE_NAME --app YOUR_STAGING_APP_NAME --confirm YOUR_STAGING_APP_NAME

Answer 3

Making backup file:

pg_dump -U USERNAME DATABASE --no-owner --no-acl -f backup.sql 

Restoring from sql file to heroku :

heroku pg:psql --app APPNAME < backup.sql 

(Bonus) Deleting all tables from heroku app database (example):

heroku pg:reset --app APPNAME HEROKU_POSTGRESQL_ROSE

get DATABASE_URL from posgresql heroku panel (psql line)