On Tue, Feb 5, 2013 at 5:07 PM, Joe Conway <mail at joeconway.com> wrote:
> What is the recommended way to capture a "snapshot" of an entire slony
> cluster at a point in time, such that it is possible to restore the
> cluster to that point in time (without having to drop/resubscribe nodes)?
>
> We can stop activity on the cluster while taking the snapshot, but
> ideally would like to avoid having to dump every node independently. We
> will have both slony-managed and non-slony-managed tables (although the
> non-slony-managed tables should also be in sync across the nodes).
>
> The ultimate goal it to provide a way to reset the cluster to a known
> state in order to facilitate testing.
>
> Pointers, ideas, URLs appreciated.

There's a built-in tool for dumping the configuration of a cluster:
http://git.postgresql.org/gitweb/?p=slony1-engine.git;a=blob;f=tools/slonikconfdump.sh

I'm pretty sure that's not what you have in mind; I think you're
thinking not just about the slony configuration, but about the
combination of all the nodes and their cross-database state.

If all of the databases are located on reasonably sophisticated disk
array hardware, then you could probably use the disk array's
capabilities to generate snapshots of the states of the respective
databases, perhaps complete with deduplication of data via
copy-on-write cloning.

I'd think that's the only particularly reasonable way to do this
without doing a bunch of pg_dumps.

Similar could be done on Linux using LVM, copying via dd, or doing
something filesystem-specific using btrfs (btrfs subvolume snapshot)
or XFS (xfs_copy).

On FreeBSD (perhaps other BSDs), ZFS has a "zfs clone" command that is
analogous.

It's probably also conceivable to use PITR to this end, checking the
end XID on each database node, and setting things up to recover to
that point.  But I rather expect that filesystem/disk array snapshots
will be the easiest way to do this.