Update on PITR

A brief update on PITR status:

I've completed successful unit testing of the PostgreSQL client-side
code for the XLogArchive API. Just about to start moving on to pg_arch
and the archiver side code. At this rate, I should have a
system-testable set of patches in around 2 weeks time for the first
phase of PITR. I'll release the code as soon as I can: I'm conscious
that other work may be updating xlog code also and I'm sure the
committers will want some thorough checks before acceptance.

Quick update on overall plan:
Phase 1: xlog archiving api, with functional pg_arch utility
- ETA mid-April
- changes so far to xlog.c, xlog.h, guc.c
- will allow rollforward along extended archive history till-end of
logs, diskspace permitting

Phase 2: add code to control recovery (to a point-in-time)
- should be there by mid-May, though may yield more quickly
- will allow rollforward along extended archive history to point in
time, diskspace permitting

Phase 3: various additional tweaks, suggestions & better documentation
- early June (following some time travelling in May)
- will allow rollforward along extended archive history to point in
time, even when logs > available disk

Is this all still OK for 7.5? (My attempts at cataloguing changes has
fallen by the wayside in concentrating on the more important task of
PITR.) Do we have a planned freeze month yet?

...Those with a wry sense of humour may be interested to know that I
recently lost my hard-drive on my main business workstation. Not
uncommon, I grant you, but in the circumstances I didn't really need the
extra recovery practice...

Best Regards, Simon Riggs

"Simon Riggs" <simon(at)2ndquadrant(dot)com> writes:
> [ expecting to finish PITR by early June ]

> Is this all still OK for 7.5? (My attempts at cataloguing changes has
> fallen by the wayside in concentrating on the more important task of
> PITR.) Do we have a planned freeze month yet?

There's not really a plan at the moment, but I had June in the back of
my head as a good time; it looks to me like the Windows port will be
stable enough for beta in another month or two, and it'd be good if
PITR were ready to go by then.

Is your timeline based on the assumption of doing all the work yourself?
If so, how about farming out some of it? I'd be willing to contribute
some effort to PITR. (It's been made clear to me that Red Hat really
wants PITR in 7.5 ;-))

regards, tom lane

Tom Lane wrote:
> "Simon Riggs" <simon(at)2ndquadrant(dot)com> writes:
> > [ expecting to finish PITR by early June ]
>
> > Is this all still OK for 7.5? (My attempts at cataloguing changes has
> > fallen by the wayside in concentrating on the more important task of
> > PITR.) Do we have a planned freeze month yet?
>
> There's not really a plan at the moment, but I had June in the back of
> my head as a good time; it looks to me like the Windows port will be
> stable enough for beta in another month or two, and it'd be good if
> PITR were ready to go by then.
>
> Is your timeline based on the assumption of doing all the work yourself?
> If so, how about farming out some of it? I'd be willing to contribute
> some effort to PITR. (It's been made clear to me that Red Hat really
> wants PITR in 7.5 ;-))

Agreed! Lets see if we can assemble a team to start coding PITR.

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

* Tom Lane (tgl(at)sss(dot)pgh(dot)pa(dot)us) wrote:
> Is your timeline based on the assumption of doing all the work yourself?
> If so, how about farming out some of it? I'd be willing to contribute
> some effort to PITR. (It's been made clear to me that Red Hat really
> wants PITR in 7.5 ;-))

Hey, us Debian folks really want it too. ;)

Stephen

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Hi,

On Wed, 31 Mar 2004, Tom Lane wrote:

<snip>
> I'd be willing to contribute some effort to PITR. (It's been made clear
> to me that Red Hat really wants PITR in 7.5 ;-))

Wow! That's exciting news :-) Does Red Hat also want some more enterprise
features? ;-)

I've been using PostgreSQL since... about 5 years... The great improvement
since then really impresses me.

Regards,
- --
Devrim GUNDUZ
devrim(at)gunduz(dot)org devrim(dot)gunduz(at)linux(dot)org(dot)tr
http://www.TDMSoft.com
http://www.gunduz.org
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.1 (GNU/Linux)

iD8DBQFAaxEytl86P3SPfQ4RAkisAKDoa8yXf9a68TqBabO6uipPwbihxgCdEJoo
0tkna1hIXkCWFsGcINl+gWs=
=bNf3
-----END PGP SIGNATURE-----

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Hi,

On Wed, 31 Mar 2004, Tom Lane wrote:

> > Is this all still OK for 7.5? (My attempts at cataloguing changes has
> > fallen by the wayside in concentrating on the more important task of
> > PITR.) Do we have a planned freeze month yet?
>
> There's not really a plan at the moment, but I had June in the back of
> my head as a good time; it looks to me like the Windows port will be
> stable enough for beta in another month or two, and it'd be good if
> PITR were ready to go by then.

BTW... PITR, Windows port, possibly Tablespaces and more... Does the
core team intend to use 8.0 instead of 7.5?

Regards,
- --
Devrim GUNDUZ
devrim(at)gunduz(dot)org devrim(dot)gunduz(at)linux(dot)org(dot)tr
http://www.TDMSoft.com
http://www.gunduz.org
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.1 (GNU/Linux)

iD8DBQFAaxprtl86P3SPfQ4RAqDKAKDmLHQS3KJDlNdhKkIJEzCCjzKk0gCeLQiX
zOpH7jHB9qpJeLvXnm2/+n8=
=DCvJ
-----END PGP SIGNATURE-----

Devrim GUNDUZ <devrim(at)gunduz(dot)org> writes:
> BTW... PITR, Windows port, possibly Tablespaces and more... Does the
> core team intend to use 8.0 instead of 7.5?

It's premature to have that discussion yet, IMHO. When we get close
to beta and know what the feature list will look like, we can think
about what to call it ...

regards, tom lane

On Wed, 31 Mar 2004, Tom Lane wrote:

> Devrim GUNDUZ <devrim(at)gunduz(dot)org> writes:
> > BTW... PITR, Windows port, possibly Tablespaces and more... Does the
> > core team intend to use 8.0 instead of 7.5?
>
> It's premature to have that discussion yet, IMHO. When we get close
> to beta and know what the feature list will look like, we can think
> about what to call it ...

Agreed ... all of the above are still, in my mind, considered 'wish list
items' for the next release, up until they are actually committed ... but,
I do agree that several of them should warrant a major jump, so let's see
what we can get into place before 1st of June ...

----
Marc G. Fournier Hub.Org Networking Services (http://www.hub.org)
Email: scrappy(at)hub(dot)org Yahoo!: yscrappy ICQ: 7615664

>Bruce Momjian wrote
> Tom Lane wrote:
> > "Simon Riggs" <simon(at)2ndquadrant(dot)com> writes:
> > > [ expecting to finish PITR by early June ]
> >
> > > Is this all still OK for 7.5? (My attempts at cataloguing
> changes has
> > > fallen by the wayside in concentrating on the more
> important task of
> > > PITR.) Do we have a planned freeze month yet?
> >
> > There's not really a plan at the moment, but I had June in
> the back of
> > my head as a good time; it looks to me like the Windows port will be
> > stable enough for beta in another month or two, and it'd be good if
> > PITR were ready to go by then.
> >
> > Is your timeline based on the assumption of doing all the
> work yourself?
> > If so, how about farming out some of it? I'd be willing to
> contribute
> > some effort to PITR. (It's been made clear to me that Red
> Hat really
> > wants PITR in 7.5 ;-))
>
> Agreed! Lets see if we can assemble a team to start coding PITR.
>

Thank you all for your offers of help. Yes, is the short answer; we
should be able to cut enough code on independent work streams to get
this system testable by end April.

As I say, I started coding some time back and am well into what I've
called Phase 1, so its probably best for me to complete that. You guys
will be contributing by looking at my code anyhow, so your goodwill is
certainly going to be called in, don't worry. There isn't anything too
hairy code wise anyhow, if I'm honest. For clarity, this will give the
facility to archive xlogs beyond their current short lifetime in the
recycling method currently used.

Phase 2 is definitely another matter...There doesn't seem to be any
dependency that I can see for that.... I called it Phase 2 because, yes,
I did make the assumption that I was doing it all myself, but I did set
off on this journey as a team effort and I welcome that still...
I described this piece of work earlier as:
Phase 2: add code to control recovery (to a point-in-time)
- will allow rollforward along extended archive history to point in
time, diskspace permitting

In my earlier summary of all design contributions there was the idea for
a postmaster command line switch which would make rollforward recovery
stop at the appropriate place. Two switches were discussed:
i) roll forward to point in time. This sounds relatively easy...recovery
is already there, all you have to do is stop it at the right place...but
I haven't looked into the exact mechanism of reading the xlog headers
etc.. [There's also a few bits of work to do there in terms of putting
in hooks for the command mechanism.
Something like postmaster -R "2004/12/10 19:37:04" as a loose example

ii) roll forward on all available logs, but shutdown at end, leaving pg
in a recovery-pending state (still). This then gives the DBA a chance to
do either a) switch in a new batch of xlogs, allowing an infinite
sequence of xlogs to be applied one batch at a time, or b) keep a "hot
standby" system continually primed and ready to startup should the
primary go down.

Neither of those looks too hard to me, so should be able to be done by
about mid-April when I'm thinking to have finished XLogArchive API. As I
say there's no particular dependency on the XLogArchive API stuff all
working, since they can both be tested independently, though we must put
them together for system testing.

Further tasks (what I had thought of as "Phase 3", but again these can
be started now...)
- what to do should a cancel CTRL-C be issued during recovery..what
state is the database left in?
- How do you say "this is taking to long, I really need my database up
now, whatever state its in" (when recovery is grinding away, not before
you start it or after it has failed, which is where you would use
pg_resetxlog)....
- can you change your mind on that once its up and you see what a mess
its in! i.e. put it back into recovery? what would that take - saving
clogs? an optional "trial recovery" mode?
- how would we monitor a lengthy recovery? watch for "starting recovery
of log XXX" messages and do some math to work out the finish time, or is
there a better way?
- is it possible to have parallel recovery processes active
simultaneously for faster recovery?? can we work anything into the
design now that would allow that to be added later?

What I think is really important is a very well coordinated test plan.
Perhaps even more importantly a test plan not written by me, since I
might make some dangerous assumptions in writing it. Having a written
test plan would allow us to cover all the edge cases that PITR is
designed to recover from. It will be pretty hard for most production
users of PostgreSQL to fully test PITR, though of course many will "kick
the tyres" shall we say, to confirm a full implementation. Many of the
tests are not easily automatable, so we can't just dream up some more
regression tests. A written plan would then allow coordinated testing to
occur across platforms, so a QNX user may spot something that also
effects Solaris etc.. Is that anything any large open source outfit
(commercial or non-commerical) would be able to contribute the time of a
test analyst for 10 days to complete? ;) ...I didn't get a huge response
from the community when last I requested assistance in that area,
assuming my post got through.

Overall, I'm confident that we're getting close to this becoming fully
real. The main issues historically were that the xlogs didn't contain
all that was required for full recovery, but those challenges have been
solved already by J.R. and Patrick(subject to full system testing).

Best Regards,

Simon Riggs
2nd Quadrant

> Is your timeline based on the assumption of doing all the work yourself?
> If so, how about farming out some of it? I'd be willing to contribute
> some effort to PITR. (It's been made clear to me that Red Hat really
> wants PITR in 7.5 ;-))

What is RedHat's interest in PostgreSQL? Last time I heard they weren't
interested in their database product anymore. Why do they care about
the PostgreSQL project?

Of course, it's awesome that they are - but why? What's their plan?

Chris

Christopher Kings-Lynne wrote:
> > Is your timeline based on the assumption of doing all the work yourself?
> > If so, how about farming out some of it? I'd be willing to contribute
> > some effort to PITR. (It's been made clear to me that Red Hat really
> > wants PITR in 7.5 ;-))
>
> What is RedHat's interest in PostgreSQL? Last time I heard they weren't
> interested in their database product anymore. Why do they care about
> the PostgreSQL project?
>
> Of course, it's awesome that they are - but why? What's their plan?
>

SRA wants PITR too, as does everyone else. :-)

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

On Thu, 1 Apr 2004, Christopher Kings-Lynne wrote:

> > Is your timeline based on the assumption of doing all the work yourself?
> > If so, how about farming out some of it? I'd be willing to contribute
> > some effort to PITR. (It's been made clear to me that Red Hat really
> > wants PITR in 7.5 ;-))
>
> What is RedHat's interest in PostgreSQL? Last time I heard they weren't
> interested in their database product anymore. Why do they care about
> the PostgreSQL project?

Just speculation, but I'd go with end goal being to be able to dump Oracle
altogether, once PostgreSQL actually has all the various enterprise
features ...

----
Marc G. Fournier Hub.Org Networking Services (http://www.hub.org)
Email: scrappy(at)hub(dot)org Yahoo!: yscrappy ICQ: 7615664

scrappy(at)postgresql(dot)org ("Marc G. Fournier") writes:
> On Thu, 1 Apr 2004, Christopher Kings-Lynne wrote:
>> > Is your timeline based on the assumption of doing all the work
>> > yourself? If so, how about farming out some of it? I'd be
>> > willing to contribute some effort to PITR. (It's been made clear
>> > to me that Red Hat really wants PITR in 7.5 ;-))
>>
>> What is RedHat's interest in PostgreSQL? Last time I heard they
>> weren't interested in their database product anymore. Why do they
>> care about the PostgreSQL project?
>
> Just speculation, but I'd go with end goal being to be able to dump
> Oracle altogether, once PostgreSQL actually has all the various
> enterprise features ...

I understood that they were using Oracle Financials and possibly
Oracle Manufacturing for their internal systems; I seriously doubt
that Oracle will be adding support for PostgreSQL in those products
:-).

That being said, I'm sure it is in their interests to be able to
promote database options that do not mandate sending one penny to any
other vendors.

That was pretty clearly a major reason behind RHAT promoting GNOME
over KDE, in that "business" use of the latter would mandate sending
more money to a little company in Europe than RHAT would receive in
selling 50 'boxed sets.'
--
"cbbrowne","@","cbbrowne.com"
http://cbbrowne.com/info/wp.html
Rules of the Evil Overlord #145. "My dungeon cell decor will not
feature exposed pipes. While they add to the gloomy atmosphere, they
are good conductors of vibrations and a lot of prisoners know Morse
code." <http://www.eviloverlord.com/>

I may be completely missing the point here, but it looks to me as though
the PITR archival mechanism is also most of a native replication
facility. Is there anyone reason this couldn't be extended to
replication, and if so, is anyone planning on using it as such?

My memory is fuzzy on this point, but I seem to recall that this is
(was?) how replication is more-or-less done for many of the big
commercial RDBMS.

jrogers(at)neopolitan(dot)com

On Fri, 1 Apr 2004, J. Andrew Rogers wrote:

>
> I may be completely missing the point here, but it looks to me as though
> the PITR archival mechanism is also most of a native replication
> facility. Is there anyone reason this couldn't be extended to
> replication, and if so, is anyone planning on using it as such?
>
> My memory is fuzzy on this point, but I seem to recall that this is
> (was?) how replication is more-or-less done for many of the big
> commercial RDBMS.

Logfile shipping is one replication option some commercial vendors offer.
It doesn't solve all problems however. It is mostly used for hot backups
in my experience.

Gavin

Oops! jrogers(at)neopolitan(dot)com ("J. Andrew Rogers") was seen spray-painting on a wall:
> I may be completely missing the point here, but it looks to me as
> though the PITR archival mechanism is also most of a native
> replication facility. Is there anyone reason this couldn't be
> extended to replication, and if so, is anyone planning on using it
> as such?
>
> My memory is fuzzy on this point, but I seem to recall that this is
> (was?) how replication is more-or-less done for many of the big
> commercial RDBMS.

What isn't clear to me at this point is whether PITR is set up to
allow the database to remain "live and accessible" while updates are
being loaded in.

If that's NOT the case, then it is in no way a meaningful replacement
for replication, vastly useful though it may be.

At those times I have seen Oracle(tm) archive logs being applied to
support PITR-related functionality, the DB hasn't been "up and
running." I suspect that may have changed by now, which would
certainly be handy for replication.
--
(format nil "~S(at)~S" "cbbrowne" "ntlug.org")
http://cbbrowne.com/info/multiplexor.html
As of next Tuesday, all terminal input will be line-at-a-time.
Please update your programs.

"J. Andrew Rogers" <jrogers(at)neopolitan(dot)com> writes:

> I may be completely missing the point here, but it looks to me as though
> the PITR archival mechanism is also most of a native replication
> facility. Is there anyone reason this couldn't be extended to
> replication, and if so, is anyone planning on using it as such?
>
> My memory is fuzzy on this point, but I seem to recall that this is
> (was?) how replication is more-or-less done for many of the big
> commercial RDBMS.

Well "replication" is one of those things that means different things to
different people. IMHO, this is one of the simpler, more reliable, mechanisms
and would be nice to have. And you're right that it shouldn't require much
more work, in fact it's probably easier than a lot of other cases that PITR
requires.

For a long time Oracle has supported this mechanism for running warm standby
databases. Basically you maintain a second database and every time an archived
log is finished you ship it over and immediately "restore" it on the standby
machine. Whenever you have a failure you can quickly fail over to the standby
database which is all ready to go and up-to-date within minutes of your
failure.

Since 8i Oracle has also supported a more advanced version where you can open
the standby database in read-only mode. This makes it useful for running batch
reports and so on. In postgres this wouldn't work nearly so well since even
read-only queries require write access to the database in postgres. Perhaps
it's not nearly so urgent since running long-running batch queries on a busy
system in postgres doesn't pose all the same dangers it does in Oracle (the
dreaded "snapshot too old" error) -- though there are other analogous dangers
(fsm settings being too small unexpectedly).

--
greg

> Greg Stark
> "J. Andrew Rogers" <jrogers(at)neopolitan(dot)com> writes:
>
> > I may be completely missing the point here, but it looks to
> me as though
> > the PITR archival mechanism is also most of a native replication
> > facility. Is there anyone reason this couldn't be extended to
> > replication, and if so, is anyone planning on using it as such?
> >
> > My memory is fuzzy on this point, but I seem to recall that this is
> > (was?) how replication is more-or-less done for many of the big
> > commercial RDBMS.

You're right...it is the basis of a log shipping replication facility.
I'm keen no to get too far ahead of ourselves. Let's eat the mammoth one
bite at a time....or one patch at a time.

> Well "replication" is one of those things that means
> different things to
> different people. IMHO, this is one of the simpler, more
> reliable, mechanisms
> and would be nice to have. And you're right that it shouldn't
> require much
> more work, in fact it's probably easier than a lot of other
> cases that PITR
> requires.

I agree. PITR is intended initially as a recovery mechanism. Replication
has other purposes as well, such as locating data close to where it is
required (in master-multi-slave replication scenarios), which is
certainly not an objective or even a likely end point of the PITR work.
The PostgreSQL community is lucky enough to have some very competent
people working on those other approaches and I would recommend everybody
checks out that work.

> For a long time Oracle has supported this mechanism for
> running warm standby
> databases. Basically you maintain a second database and every
> time an archived
> log is finished you ship it over and immediately "restore" it
> on the standby
> machine. Whenever you have a failure you can quickly fail
> over to the standby
> database which is all ready to go and up-to-date within
> minutes of your
> failure.

This facility is one of the intended features, if all goes well. But it
is an advanced feature, not the bread and butter.

> Since 8i Oracle has also supported a more advanced version
> where you can open
> the standby database in read-only mode.

This mode requires more thought, but is certainly possible, in time.

Best Regards

Simon Riggs