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."

  • 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 '14 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 '14 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 '14 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 '14 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 '15 at 11:50

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.