Can't import to heroku postgres database from dump

Question

Sorry if it is a duplicate, but I tried to find an answer here, and nothing helped.

So I've read heroku articles like this and this. I was able to save a dump file, which I've created with pg:backups capture command. Uploaded it to s3 and tried to restore it with:

heroku pg:backups restore DATABASE 'https://s3-eu-west-1.amazonaws.com/somebucket/uploads/tmp/b011.dump'

But it just do not work! In console it logs:

Unknown database: https://s3-eu-west-1.amazonaws.com/somebucket/uploads/tmp/b011.dump. Valid options are: DATABASE_URL, HEROKU_POSTGRESQL_SILVER_URL

Tried listed options instead of DATABASE, but with the same result. Also I've tried other hosting, but with the same result, again. I also tried to restore it from other app, like this:

heroku pg:backups restore myapp::b001 HEROKU_POSTGRESQL_SILVER --app myapp-cedar

But it logs with Backup oncampus::b001 not found. However, command heroku pg:backups --app myapp shows that it is present.

=== Backups
ID    Backup Time                Status                              Size    Database
----  -------------------------  ----------------------------------  ------  --------
b001  2015-03-13 18:10:14 +0000  Finished 2015-03-13 18:10:22 +0000  9.71MB  ORANGE

Don't know what to do now. Just hope someone will help me.

Try: heroku pg:backups restore HEROKU_POSTGRESQL_SILVER 'https://s3-eu-west-1.amazonaws.com/somebucket/uploads/tmp/b011.dump' and make sure that the file on amazon is accesible from outside. — cristian
– cristian, Commented Mar 14, 2015 at 22:49
I'm having this exact same issues. It seems Heroku has depracated the PG:Backups Add On and have built the functionality into their standard Postgres functionality. See new documentation here: devcenter.heroku.com/articles/heroku-postgres-backups. They don't provide documentation from importing via direct URL. — Justin Thiele
– Justin Thiele, Commented Mar 14, 2015 at 23:52
@cristian Tried that. Also checked if it is accessible through private session. — Danny Ocean
– Danny Ocean, Commented Mar 16, 2015 at 10:13
@JustinThiele if it is impossible to import via direct URL, how can I restore DB from dump file, that I have locally? — Danny Ocean
– Danny Ocean, Commented Mar 16, 2015 at 10:15
The method described in this gist worked for me: gist.github.com/anhkind/e4bcde6a7bbd9f080cd6 (note the difference between pg:backups and pgbackups) — Florian Kugler
– Florian Kugler, Commented Mar 16, 2015 at 15:17

geemus · Accepted Answer · 2015-03-17 20:54:30Z

10

+50

The order of arguments to the command is significant. In the first example above, you have heroku pg:backups restore DATABASE 'https://s3-eu-west-1.amazonaws.com/somebucket/uploads/tmp/b011.dump', but the command expects the reference FIRST and the db to load into second, which would give heroku pg:backups restore 'https://s3-eu-west-1.amazonaws.com/somebucket/uploads/tmp/b011.dump' DATABASE instead. I think in the new stuff ID may be preferred to URL, but URL ought to work as long as that URL is accessible. Hope that helps, otherwise let me know and we can try some other stuff.

answered Mar 17, 2015 at 20:54

geemus

2,54217 silver badges22 bronze badges

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

1 Comment

geemus Over a year ago

Awesome. A really easy detail to overlook, and unfortunately in this case the error message doesn't give much help. Glad to hear that got you back on track! I'll mention to the Heroku Postgres team that the new docs don't cover import from URL.

Jameel Mohammed · Accepted Answer · 2015-03-23 11:43:51Z

prepare two sh files as below
backup.sh

NOWDATE=`date +%Y-%m-%d`
BACKUPNAME=$NOWDATE.dump
export PGPASSWORD='<password>'
echo “Creating backup of database  to $BACKUPNAME”
/usr/bin/pg_dump --host <urhostname> --port 5432 --username "<user>" --role "<role>" --no-password  --format tar --blobs --verbose --file "./$BACKUPNAME" "dbname"
echo “Succesfully created database backup”
echo “Uploading backup to Amazon S3 bucket…”
s3cmd put $BACKUPNAME s3://path/to/$BACKUPNAME
echo “Successfully uploaded backup to S3″
echo “Deleting backup file…”
rm $BACKUPNAME
echo “Done”

restore.sh

NOWDATE=`date +%Y-%m-%d`
BACKUPNAME=$NOWDATE.dump
echo “downloading the file $BACKUPNAME”
s3cmd get s3://path/to/$BACKUPNAME
echo “Succesfully downloaded”
echo “Will restore it Please wait “
export PGPASSWORD='<password>'
pg_restore --host <host> --port 5432 --username "<user>" --dbname "<databasename>" --role "<role>" --no-password  --verbose "./$BACKUPNAME"
echo “restoring the file $BACKUPNAME”
echo “Deleting backup file…”
rm -r $BACKUPNAME

echo “Done”

you need to configure s3cmd credentials hope it helps!

Jose Paez · Accepted Answer · 2020-08-23 01:28:06Z

0

geemus correctly solved Danny Ocean's problem.

The order does affect and the DATABASE_URL goes at the end not at the beginning.

Just wanted to leave the link to this answer which I believe could help other users with a similar problem.

answered Aug 23, 2020 at 1:28

Jose Paez

8531 gold badge12 silver badges19 bronze badges

Collectives™ on Stack Overflow

Can't import to heroku postgres database from dump

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related