5 ms·
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 t
by joevandyk 13y ago
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/book 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.
- jordanthoms 13y agoI'm using https://github.com/wal-e/wal-e https://github.com/wal-e/wal-e which ships the logs to S3 with good results
- joevandyk 13y agoThat'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).
- wiredfool 13y agoI 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.
- scraplab 13y agoWe use Barman, which is similar, using rsync to ship WAL files to a different server. http://www.pgbarman.org/ http://www.pgbarman.org/