On Sat, Apr 27, 2013 at 10:40 AM, Yang Zhang <yanghatespam@xxxxxxxxx> wrote:
On Sat, Apr 27, 2013 at 4:25 AM, Jov <amutu@xxxxxxxxx> wrote:I think so. EBS gives you "point-in-time consistent snapshots"
> 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.
(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:
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:
Cheers,
Jeff
