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