Re: pg_dump --snapshot

On Mon, May 6, 2013 at 10:02 PM, Simon Riggs <simon(at)2ndquadrant(dot)com> wrote:
> 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.

I think it would be pretty sweet but we don't have the infrastructure
for it. We would need to retain enough information in the WAL log (or
somewhere else) to reverse the records.

> 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.

This is similar to "flashback query". And I think you're right that to
be comparable with Oracle's features we would need some option to
specify the snapshot based on time or wal position. And fwiw I think
it could still be read-write in consistent-read or serializable mode.
If you tried to update any records that had been updated since you
would get a serialization failure.

So I just did some research. It seems Oracle's equivalent of pg_dump
"expdp" does use flashback internally to guarantee consistency in some
cases which is perhaps analogous to how pg_dump uses snapshots to
synchronize multiple sessions (though it sounds like Oracle uses it
for cases that just work in Postgres).

But more interestingly expdp does in fact have a user option to
specify a timestamp or scn (analogous to wal position) and use
flashback query to dump the data at that point in time. That's a
pretty clear a parallel to what you propose here.

--
greg