How-To Consitent EC2 Snapshots with Postgres

On Sat, Oct 31, 2009 at 15:44, saturon <gsaturon@gmail.com> wrote:

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/*&lt;http://ec2ebs-mysql.notlong.com/&gt;
*http://alestic.com/2009/09/ec2-consistent-snapshot*&lt;http://alestic.com/2009/09/ec2-consistent-snapshot&gt;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)

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. Note that this requires that all your PostgreSQL
data (including the data directory, xlog and *all* tablespaces) are on the
same EBS volume - I'm pretty sure they can't do atomic snapshots across
multiple volumes.

--
Magnus Hagander
Me: http://www.hagander.net/
Work: http://www.redpill-linpro.com/

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