How to restore a Postgres backup that is stored on Amazon S3

Foundations SQL
#1

Hi,

I managed to automate backups of my PostgreSQL database to Amazon’s S3 cloud storage.

For this I used the guide on Gorails: Hourly Production Server Database And File Backups

However, the guide doesn’t tell how to restore these remotely stored backups.

I tried to do a restore with:

sudo su - postgres
psql -1 posplus < production_backup

but that doesn’t do anything.

I guess I have to point the pg restore command somehow to the remote file but I have no idea how to do this.

Has anyone else experience with restoring a PostgreSQL backup file from a remote location?

thanks for your help,

Anthony

#2

heroku pgbackups:restore HEROKU_POSTGRESQL_COLOR_URL "<url to the pgdump on S3>" --app your-heroku-app-name

more info here: https://devcenter.heroku.com/articles/heroku-postgres-import-export

good luck @acandael_acanda!

#3

Thanks,

Joel, my Rails app is not hosted on Heroku but on a VPS of my organisation (http://psrails.ugent.be)

but I’ll definitely check out the documentation, as it might contain useful information on how to do this on a VPS

greetings,

Anthony

#4

I checked the Heroku documentation. Problem is that is assumes the PG Backups add-on, which I don’t have, as my Rails app is not deployed on Heroku, but on a - unmanaged - VPS of my organisation.

thanks anyway,

Anthony

#5

Ah, man I totally glossed over that detail. Sorry about that!