Re: pg_dump --snapshot

On 6 May 2013 16:02, Andrew Dunstan <andrew(at)dunslane(dot)net> wrote:
>
> On 05/06/2013 10:56 AM, Simon Riggs wrote:
>>
>> Patch to allow pg_dump to use a snapshot exported with an explicit
>> pg_export_snapshot() for when precise timing of the snapshot is
>> important.
>>
>> This overrides the internally generated snapshot in parallel pg_dump.
>>

> Could you be a bit more expansive about the use case, please?

Sure.

Exported snapshots allow you to coordinate a number of actions
together, so they all see a common view of the database. So this patch
allows a very general approach to this, much more so than pg_dump
allows currently since the exact timing of the snapshot is not
controlled by the user.

For example, you may wish to create a sequence of materialized views
all using the same snapshot, while at the same time dumping the
database, then follow that by dumping the mat views also. Or you may
wish to dump the database at the exact moment between two tasks,
rather than wait for pg_dump to startup before commencing the next
task.

The original pg_snapclone code from 2008 allowed an export of the
snapshot and then an import of the snapshot explicitly into pg_dump in
much the same way shown here. This patch would be the only aspect
missing from that original feature set: explicit control. (Thanks for
following up to get it all into production code).

Logical replication would be one of the users of this also, but it is
a standalone feature which complements the existing capabilities.

--
Simon Riggs http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services