Re: Basic question on recovery and disk snapshotting

On Sat, Apr 27, 2013 at 10:40 AM, Yang Zhang <yanghatespam(at)gmail(dot)com> wrote:

> On Sat, Apr 27, 2013 at 4:25 AM, Jov <amutu(at)amutu(dot)com> wrote:
> > Are you sure the EBS snapshot is consistent? if the snapshot is not
> > consistent,enven on the same volume,you will have prolbems with your
> backup.
>
> I think so. EBS gives you "point-in-time consistent snapshots"
> (https://aws.amazon.com/ebs/) but maybe you're using the term
> differently.
>

I would not trust any data that I care about based on the description on
that page. They mention "consistent" once and "atomic" not at all. Which
is not to say it won't work.

This thread seems to indicate the file system on top of the EBS volume
would need to be quiescent in order for the snapshot to be reliable:

https://forums.aws.amazon.com/message.jspa?messageID=108940

Although I don't think that strength of consistency would actually be
needed. As long as the snapshot reflects all writes that were, at the time
the snapshot was initiated, reported back to PG as being successfully
synced, and contained no writes which were done after the snapshot was
reported as complete, that should be consistent enough for PG. Unless the
file system itself got scrambled.

> Even so, it's impossible to take snapshots of two different volumes at
> exactly the same time so they won't be consistent with each other,
> hence my question.
>
> My question really boils down to: if we're interested in using COW
> snapshotting (a common feature of modern filesystems and hosting
> environments), would we necessarily need to ensure the data and
> pg_xlog are on the same snapshotted volume?

That would certainly make it easier. But it shouldn't be necessary, as
long as the xlog snapshot is taken after the cluster snapshot, and also as
long as no xlog files which were written to after the last completed
checkpoint prior to the cluster snapshot got recycled before the xlog
snapshot. As long as the snapshots run quickly and promptly one after the
other, this should not be a problem, but you should certainly validate that
a snapshot collection has all the xlogs it needs before accepting it as
being good. If you find some necessary xlog files are missing, you can
turn up wal_keep_segments and try again.

> If not, how should we be
> taking the snapshots - should we be using pg_start_backup() and then
> taking the snapshot of one before the other? (What order?) What if
> we have tablespaces, do we take snapshots of those, followed by the
> cluster directory, followed by pg_xlog?
>

First the cluster directory (where "pg_control" is), then tablespaces, then
pg_xlog. pg_start_backup() shouldn't be necessary, unless you are running
with full_page_writes off. But it won't hurt, and if you don't use
pg_start_backup you should probably run a checkpoint of your own
immediately before starting.

> I read through
> http://www.postgresql.org/docs/9.1/static/continuous-archiving.html
> and it doesn't touch on these questions.
>

Your goal seems to be to *avoid* continuous archiving, so I wouldn't expect
that part of the docs to touch on your issues. But see the section
"Standalone Hot Backups" which would allow you to use snapshots for the
cluster "copy" part, and normal archiving for just the xlogs. The volume
of pg_xlog should be fairly small, so this seems to me like an attractive
option.

If you really don't want to use archiving, even just during the duration of
the cluster snapshotting, then this is the part that addresses your
questions:

http://www.postgresql.org/docs/9.1/static/backup-file.html

Cheers,

Jeff