Thread: How-To Consitent EC2 Snapshots with Postgres

Hi all,

I am fairly new to postgres admin so please bear with me.

I am looking for a way to create a consitent EC2 snapshot of a postgres DB that is running in a Debian Lenny instance, XFS file system on a EBS . I was following Eric Hammons posts http://ec2ebs-mysql.notlong.com/  http://alestic.com/2009/09/ec2-consistent-snapshot on creating Snapshost for MySQL and would like to port his efforts to Postgres.

I am really looking just for some documentation of this whole area since I am sitting here a bit in the blue ;) (e.g. I heard of LVM but do not know it and not sure if this is the way to go)

Thanks a bunch! Ben

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@gregsmith.com http://www.gregsmith.com Baltimore, MD