Re: PITR Phase 2 - Design Planning

On Tue, 2004-04-27 at 10:38, Richard Huxton wrote:
> 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.

Not sure I like you're implementation, but the overall idea is great.

I'd suggest extending the CHECKPOINT command so you can say:
CHECKPOINT <text message>
e.g. CHECKPOINT 'starting payroll Feb04';
(I'm sure some other DBMS does this...head spinning can;t recall...)
the text could just appear in the xlog record data packet...

That could then be used as the target recovery point.

Best Regards, Simon Riggs