Re: PITR Phase 2 - Design Planning

On Tuesday 27 April 2004 00:32, Bruce Momjian wrote:
> Simon Riggs wrote:
> > On Mon, 2004-04-26 at 23:01, Alvaro Herrera wrote:
> > > On Mon, Apr 26, 2004 at 05:05:41PM -0400, Bruce Momjian wrote:
> > > > I was thinking --- how would someone know the time to use for
> > > > restore?
> > >
> > > I think there should be a way to get a TransactionId and restore up to
> > > that point. It'd be cool, but not required, if the system showed what
> > > valid TransactionIds there are, and roughly what they did (the xlog
> > > code already has "describers" everywhere AFAICS).
> >
> > You're right, I think we should start by implementing the rollforward to
> > a txnid before we consider the rollforward to a specified point-in-time.
> > All the hooks for that are already there...
>
> Yep, sounds like a plan.

Speaking as a DBA, what I usually want to do is restore to "immediately before
I started the payroll calculation". An actual wall-clock time is mostly
irrelevant to me.

Suggestion: How about a pg_trans_note table (crap name, I know) that only
permits inserts - records (backend-pid, timestamp, notes). My app inserts
"starting payroll calc" and "ending payroll calc" entries because those are
the points I might wish to sync to. If I want to sync for each individual
transaction in my calculations, my app can do that too.
>From a usability point of view you might want to automatically insert rows on
client connection/table creation etc. You could also delete any rows more
than a week old when archiving WAL files.

--
Richard Huxton
Archonet Ltd