54

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

Some Guy
  • 12,768
  • 22
  • 58
  • 86
  • This solution will also be relevant for some people: http://stackoverflow.com/questions/42433414/heroku-database-restore-issue/42435490#42435490 – Nah Feb 24 '17 at 09:55

5 Answers5

116

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
Muhammad Usama Ashraf
  • 564
  • 1
  • 5
  • 20
SergeyB
  • 9,478
  • 4
  • 33
  • 47
  • 4
    In the first example, if you have more than 1 database on the app, you can specify the non-default (the one that doesn't use `DATABASE_URL`) by doing `heroku pg:psql HEROKU_POSTGRESQL_GREEN_URL --app YOUR_APP_NAME_HERE < updates.sql` as an example. Of course, substitute your own db URL environment variable for the one that I put in this example. – juanpaco Mar 06 '14 at 14:52
  • Make sure you've already created a backup on heroku for the database you're looking to clone! – Bigtrizzy May 26 '20 at 22:36
  • Doesn't this still require uploading to S3 or some local repository? How can one load a local sql dump to a heroku postgres DB? – Ben Wilson Jul 04 '21 at 19:10
  • For me, heroku pg:psql --app APP_NAME < sales_purchase_backup.sql , this failed first expecting amazon s3 url, but when I tried again it worked in Heroku CLI – shubham mishra Aug 02 '21 at 10:46
33

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)

Abel
  • 3,989
  • 32
  • 31
  • 2
    Thanks! Everywhere else I looked indicated I had to upload to S3. – sventechie Oct 19 '15 at 21:04
  • 2
    ---> Connecting to DATABASE_URL The input is a PostgreSQL custom-format dump. Use the pg_restore command-line client to restore this dump to a database. – Pak Nov 25 '16 at 16:08
  • 1
    I had to use the following command: pg_restore --verbose --clean --no-acl --no-owner -h localhost -U myuser -d mydb latest.dump – user1400290 Mar 19 '19 at 10:02
  • Abel, is this the preferred method of backing up a database? What are the limitations? – karns Jan 22 '20 at 14:38
2

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'
Dan Kohn
  • 33,811
  • 9
  • 84
  • 100
  • 1
    Is there a way to do it without having a local database? I have a .sql file with sql statements. It has all the CREATE TABLES and insert datas. I just need a way to send that SQL into Postgres on Heroku. – Some Guy Dec 11 '13 at 05:07
  • 1
    Yes, you can use PgAdmin to connect remotely to your Heroku database and paste your SQL code into it. However, I highly recommend setting up a local database as well so that you can follow the 12 factor principles and be able to debug locally: http://12factor.net/ – Dan Kohn Dec 11 '13 at 11:14
  • @Jeff - check out my answer, you do not need to have a local DB or connect with PgAdmin, you can easily do it via command line. – SergeyB Dec 11 '13 at 15:26
2

Django local db import on Heroku on windows

create backup

pg_dump -U postgres -d hawkishfinance > C:\Users\Fauzan\Projects\hawkishfinance.sql

dump on server

heroku pg:psql --app hawkishfinance < hawkishfinance.sql
Fauzan Khan
  • 79
  • 3
-1

You must add run in this command. It will work successfully!

heroku pg:psql run --app YOUR_APP_NAME_HERE < updates.sql
Suraj Rao
  • 29,388
  • 11
  • 94
  • 103
Justin Ouattara
  • 1
  • 1