Re: pg_dump --snapshot - Mailing list pgsql-hackers

On 6 May 2013 19:48, Greg Stark <stark@mit.edu> wrote:
> On Mon, May 6, 2013 at 6:58 PM, Simon Riggs <simon@2ndquadrant.com> wrote:
>> In any case, "flashback database" is one of the most requested
>> features I know of... the ability to dump the database as it appeared
>> in the past *after* that point has passed.
>
> Fwiw that's not what flashback database does. It rolls back the whole
> database to that earlier point in time. it's equivalent to running
> recovery but backwards in time.
>
> Obviously if you had the ability to dump the database as of an earlier
> point in time you could do a complete dump and then a complete restore
> and effectively have accomplished the same thing.

OK, so you know Oracle. So then you also know that there are actually
a number of related features all called "flashback <something>", all
interrelated. What I meant by using their term was just a broad
reference to that capability, not an exact 1:1 match. Most people
requesting this have not asked for it by that name.

At the database level, it rolls back the whole kaboodle. Not what I
meant at all and I would expect people to start twitching at the
prospect.

The feature we have in PG9.2+ is the ability to set a transaction
snapshot to a snapshot that existed in the database at some point,
invoked by some external reference to it. The external reference is
the piece of information that must be specified by the user to allow
the database to look backwards. At the moment we can only specify a
snapshot from a currently running transaction, i.e. the recent past. I
foresee a feature that will allow us to look back further, possibly
with some restrictions, though certainly read only. There is a wide
stretch of water between current state and making that work, but the
existence of an ability to specify an external reference to a snapshot
is pivotal to that future capability and I would not wish to see that
capability removed.

This patch only allows pg_dump to use the existing API. As an example,
we would use it like this.

Session 1:
BEGIN; SELECT pg_export_snapshot(); --returns a textual reference to
the internally held snapshotpg_export_snapshot
--------------------000004F6-1
(1 row)

Session 2 -- some other user of the same snapshot
pg_dump --snapshot '000004F6-1' database1

Session 3 -- some other user of the same snapshot
e.g.
pg_dump --snapshot '000004F6-1' database2
some other programs etc..

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