Re: Problem with PITR recovery

On Mon, 2005-04-18 at 16:44 +0200, simon(at)2ndquadrant(dot)com wrote:
> Rob Butler <crodster2k(at)yahoo(dot)com> wrote on 18.04.2005, 15:05:20:
> >
> > > I'd say it's very not cool :) It's not we all
> > > expected from PITR.
> > > I recall now Simon mentioned about that and have it
> > > in his TODO.
> > > Other thing I don't understand what's the problem to
> > > generate WAL file
> > > by demand ? Probably, TODO should says about this.
> >
> > This would definetly be a good feature to have. What
> > I would prefer is:
> >
> > 1) have the pitr stop command write out and close the
> > WAL that it is currently using.
> >
> > 2) have another stored proc which can be invoked at
> > any time that will write out and close the WAL that is
> > currently in use when that command is executed.
> >
> > 3) have a feature in postgres that will automatically
> > write out and close the WAL if the server hasn't had
> > any activity in XX minutes, or hasn't closed a WAL
> > file in XX minutes.
>
> Yes, I have been working on a design.
>
> 1) is required to make PITR better for low transaction rate users.
>
> 3) is required to allow standby replication
>
> 2) is a standard feature on other DBMS, but I'd have to consider that as
> optional.

My plan would be to write a special xlog record for xlog switching. This
would be a special processing instruction, rather than a data/redo
instructions. This would be implemented as another xlog info value on
the xlog_redo resource manager function, XLOG_FILE_SWITCH. (xlog_redo
would simply set a variable to be used elsewhere.)

When written the xlog switch instruction (XLogInsert) would switch to a
new xlog, just as if a file had been filled, causing it to be
immediately archived. On wal replay, ReadRecord would read the
instruction, then react by moving to the next file, as if it had
naturally reached EOF.

The wal file could be truncated after the log switch record, though I'd
want to make sure that didn't cause other problems. That is additional
functionality that I would add later when the above all works...

That would be initiated through a single function pg_walfile_switch()
which would be called from
1) pg_stop_backup()
2) by user command
3) at a specified timeout within archiver (already built in)

A shutdown checkpoint would also have the same effect as an
XLOG_FILE_SWITCH instruction, so that the archiver would be able to copy
away the file. Otherwise, we'd have a problem as to which order to write
the messages in at shutdown time. (Not happy about that bit, so
suggestions welcome...)

I'd suggest this as a backpatch for 8.0.x, when completed. I'll commit
to doing this in time for 8.1, possibly sooner.

Comments?

Best Regards, Simon Riggs