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 ---------------------------(end of broadcast)--------------------------- TIP 8: explain analyze is your friend