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.

Answer 1

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.

Answer 2

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!