Re: PITR Phase 2 - Design Planning

From: Simon Riggs <simon(at)2ndquadrant(dot)com>
To: Richard Huxton <dev(at)archonet(dot)com>
Cc: Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us>, Alvaro Herrera <alvherre(at)dcc(dot)uchile(dot)cl>, pgsql-hackers(at)postgresql(dot)org
Subject: Re: PITR Phase 2 - Design Planning
Date: 2004-04-27 20:05:18
Message-ID: 1083096318.3018.279.camel@stromboli
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

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

In response to

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Joshua D. Drake 2004-04-27 20:14:08 Re: Usability, MySQL, Postgresql.org, gborg, contrib,
Previous Message Simon Riggs 2004-04-27 20:01:30 Re: PITR Phase 2 - Design Planning