Skip site navigation (1) Skip section navigation (2)

Re: PITR Phase 2 - Design Planning

From: Richard Huxton <dev(at)archonet(dot)com>
To: Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us>,Simon Riggs <simon(at)2ndquadrant(dot)com>
Cc: 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 09:38:45
Message-ID: 200404271038.45601.dev@archonet.com (view raw or flat)
Thread:
Lists: pgsql-hackers
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

In response to

Responses

pgsql-hackers by date

Next:From: Jochem van DietenDate: 2004-04-27 11:41:58
Subject: Re: Usability, MySQL, Postgresql.org, gborg, contrib, etc.
Previous:From: Thomas HallgrenDate: 2004-04-27 09:02:31
Subject: Re: Pl/Java and GCJ

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group