I have a backup sql file from another database that I want to import into Heroku's postgres database. How do you do that?
1
-
This solution will also be relevant for some people: stackoverflow.com/questions/42433414/…Nah– Nah2017-02-24 09:55:14 +00:00Commented Feb 24, 2017 at 9:55
Add a comment
|
5 Answers
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
4 Comments
juanpaco
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.
Bigtrizzy
Make sure you've already created a backup on heroku for the database you're looking to clone!
Ben Wilson
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?
shubham mishra
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
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)
4 Comments
sventechie
Thanks! Everywhere else I looked indicated I had to upload to S3.
Pak
---> 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.
user1400290
I had to use the following command: pg_restore --verbose --clean --no-acl --no-owner -h localhost -U myuser -d mydb latest.dump
karns
Abel, is this the preferred method of backing up a database? What are the limitations?
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'
3 Comments
Some Guy
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.
Dan Kohn
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: 12factor.net
SergeyB
@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.
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
Comments
You must add run in this command. It will work successfully!
heroku pg:psql run --app YOUR_APP_NAME_HERE < updates.sql
