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. Commented 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? Commented 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. Commented 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! Commented 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. Commented Apr 28, 2015 at 11:50

0

Reset to default

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.