On Sat, 31 Oct 2009, Magnus Hagander wrote:
As long as EBS snapshots are atomic (which I think they are, but don't know for sure), you can just do the snapshot, no need to do anything extra. PostgreSQL will then go into normal crash recovery when you start up another instance on the snapshot
They're not really atomic, but when combined with the xfs-freeze capability you can make them good enough for this purpose. See http://developer.amazonwebservices.com/connect/message.jspa?messageID=109005 for more details, the relevant bit is:
"When your call to ec2-create-snapshot returns, any future writes to the volume will not be reflected in the snapshot. However, that doesn't guarantee that your snapshot will be consistent, due to write caching inside your instance. In order to guarantee a consistent snapshot, you need to quiesce the file system running on top of your volume, sync the information in the cache to disk and then initiate the snapshot.
Some file systems (such as XFS) provide this hook, others (such as EXT3) do not. For file systems that do not, you can use dmsetup (man dmsetup) to suspend and resume the file system."
While this should work fine as Magnus describes, using the normal crash recovery mechanism, your whole database could stop for the amount of time this takes for this to execute. I don't know how fast the EC2 snapshots are, but since it looks like they're even computing deltas for them I wouldn't expect it to be instant.
This approach is fine if you want a snapshot and you don't care that the database is going to be unresponsive for a bit while you take it. If you want to do this the right way, and not take that hit, you really should be setting up PostgreSQL PITR and take the snapshot inside a pg_start_backup block instead. That will flush the important things that can't be in the write cache first, and as long as you grab the incremental WAL files when you're done as a second step you shouldn't need to quiesce the file system in the middle.
-- * Greg Smith gsmith@xxxxxxxxxxxxx http://www.gregsmith.com Baltimore, MD -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
