Hacker Newsnew | past | comments | ask | show | jobs | submitlogin

Everyone should be running pg_basebackup + pg_receivexlog on a separate machine, preferably at a different data center.

These let you go back to any point in time. If you ran 'delete from orders where id=id', you can restore to the transaction before you ran that command.

http://www.packtpub.com/how-to-postgresql-backup-and-restore... contains more information.

Also, postgresql 9.3 (out in a few months) supports disk page checksums which can detect filesystem failures immediately.



I'm using https://github.com/wal-e/wal-e which ships the logs to S3 with good results


That's good as well!

If you are hosting your database on ec2, using only wal-e means that all of your data is hosted on amazon. If they were to cancel your service, you'd lose your data.

Running pg_basebackup+pg_receivexlog on a different provider is cheap insurance against that.

Be sure to test how fast wal-e can restore your data, btw. Restoring from s3 was significantly slower than restoring from a local disk (in my testing a while ago).


I ship wal logs to s3 for backup, and I do test restores on EC2.

What I've found is that most of the files can be grabbed really quickly, in the 3mb/sec range, but there's always a handful that run at 300k/sec. Running the downloads 8 or more at a time tends to help with that so that we're mostly maxing out the local network.


We use Barman, which is similar, using rsync to ship WAL files to a different server.

http://www.pgbarman.org/




Guidelines | FAQ | Lists | API | Security | Legal | Apply to YC | Contact

Search: