3

I'm new to AWS and PostgreSQL admin and have been poring over the docs trying to come up with a good backup strategy for a fairly simple setup.

I have a single Amazon Web Services EC2 instance that will run a web app and a PostgreSQL 9.2.7 database. My setup is pretty similar to this question, but I'm considering a different approach. Because I must encrypt the data, I'm storing it on a separate EBS volume formatted with XFS. I expect the database will be fairly small (a few GB) for a while. I want to be able to restore up with next to no data loss, so continuous archiving seems the way to go.

It looks like I can ship the WAL files directly to S3, probably using s3cmd (see this code).

But what about base backups? The PostgreSQL docs recommend using a file-based tool like tar. I had been planning to create an EBS snapshot as the base backup using a sequence something like this:

pg_start_backup
sync
freeze XFS
create snapshot
unfreeze XFS
pg_stop_backup

The PostgreSQL docs do talk about "consistent snapshots" here, but not in conjunction with continuous archiving. Is there any reason to avoid snapshots as a way of getting a base backup? Wouldn't restoring an EBS volume from a snapshot be faster than creating an empty EBS volume and untarring a large file? It is a bit of a management headache since the base is not stored with the WAL files...

Also, if the snapshot is the way to go, do I need the freeze/unfreeze the XFS volume? For reasons I don't understand, the continuous archiving docs actually advise against stopping database writes during a base backup: "It is neither necessary nor desirable to stop normal operation of the database while you do this."

Improve this question
6
  • 1
    Use pg_basebackup. It's simple and easy. The docs are correct: you don't need to freeze. If you freeze, you don't need to use pg_start_backup() and pg_stop_backup() - their purpose is to allow a backup to be taken at the file system level without requiring a consistent snapshot to be taken.
    – Craig Ringer
    Jun 18, 2014 at 5:54
  • Thanks @CraigRinger. I glossed over the mention of pg_basebackup when I read section 24.3.2. Does this mean snapshots + continuous archiving won't work, or just that snapshots are overkill if one can use pg_base_backup instead?
    – Mark Berry
    Jun 18, 2014 at 14:41
  • 1
    EBS snapshots are not useful if you use pg_basebackup or pg_start_backup/rsync/pg_stop_backup. Freezing xfs will actively stop them working because they must write a checkpoint to finish the backup. Freeze and snapshot or use the live backup tools. pg_basebackup -X stream makes it a no brainer.
    – Craig Ringer
    Jun 18, 2014 at 15:09
  • 1
    If you want an easy system to work with, I would look at wal-e for shipping WAL logs to S3. Works great and is fairly easy to set up. Remember to rest a restore!
    – Trygve Laugstøl
    Jun 28, 2014 at 10:51
  • 1
    Don't use s3cmd ever for anything automated The author had made a horrible mistake where the s3cmd returned 0 status when the DNS couldn't resolve amazon.com. See the bug here.
    – Antti Haapala
    Apr 28, 2015 at 11:50

0

Reset to default

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Browse other questions tagged or ask your own question.