WAL -> Replication

Mike Biamonte wrote:

Caveat: I'm not a pg hacker, I apologize
in advance if this is a dumb question, but
it has been nagging at me, and I don't
know who else to ask.

If the WAL is a record of all transactions,
and if the checkpoint process can be managed
tightly, is it possible to copy the WAL
files from a master DB and use them to keep
a slave DB in sync? This seems like it
would be an easy way to slave a backup system
without additional load on the primary....

WAL files are kept only until an fsync(), checkpoint, then reused.
Also, the info is tied to direct locations in the file. You could do
this for hot backup, but it would require quite bit of coding to make it
work.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

On Thu, 25 Apr 2002, Bruce Momjian wrote:

WAL files are kept only until an fsync(), checkpoint, then reused.

One could keep them longer though, if one really wanted to.

Also, the info is tied to direct locations in the file. You could do
this for hot backup, but it would require quite bit of coding to make it
work.

That's kind of too bad, since log shipping is a very popular method of
backup and replication.

Not that I'm volunteering to fix this. :-)

cjs
--
Curt Sampson <cjs@cynic.net> +81 90 7737 2974 http://www.netbsd.org
Don't you know, in this new Dark Age, we're all light. --XTC

On Fri, 2002-04-26 at 07:38, Curt Sampson wrote:

On Thu, 25 Apr 2002, Bruce Momjian wrote:

WAL files are kept only until an fsync(), checkpoint, then reused.

One could keep them longer though, if one really wanted to.

Also, the info is tied to direct locations in the file. You could do
this for hot backup, but it would require quite bit of coding to make it
work.

That's kind of too bad, since log shipping is a very popular method of
backup and replication.

Now again from my just aquired DB2 knowledge:

DB2 can run in two modes

1) similar to ours, where logs are reused after checkpoints/commits
allow it.

2) with log archiving: logs are never reused, but when system determines
it no longer needs them, it will hand said log over to archiving process
that will archive it (usually do a backup to some other place and then
delete it). This mode is used when online backup and restore
functionality is desired. This is something that could be interesting
for 24x7 reliability.

-----------------
Hannu

Hannu Krosing <hannu@tm.ee> writes:

DB2 can run in two modes
1) similar to ours, where logs are reused after checkpoints/commits
allow it.
2) with log archiving: logs are never reused, but when system determines
it no longer needs them, it will hand said log over to archiving process
that will archive it (usually do a backup to some other place and then
delete it).

There is in fact the skeleton of support in xlog.c for passing unwanted
log segments over to an archiver, rather than recycling them. So far
no one's done anything with the facility. I think the main problem is
the one Bruce cited: because the WAL representation is tied to physical
tuple locations and so forth, it's only useful to a slave that has an
*exact* duplicate of the master's entire database cluster. That's not
useless, but it's pretty restrictive.

It could be useful for incremental backup, though I'm not sure how
efficient it is for the purpose. WAL logs tend to be pretty voluminous.
At the very least you'd probably want enough smarts in the archiver to
strip out the page-image records.

regards, tom lane

Tom Lane wrote:

Hannu Krosing <hannu@tm.ee> writes:

DB2 can run in two modes
1) similar to ours, where logs are reused after checkpoints/commits
allow it.
2) with log archiving: logs are never reused, but when system determines
it no longer needs them, it will hand said log over to archiving process
that will archive it (usually do a backup to some other place and then
delete it).

There is in fact the skeleton of support in xlog.c for passing unwanted
log segments over to an archiver, rather than recycling them. So far
no one's done anything with the facility. I think the main problem is
the one Bruce cited: because the WAL representation is tied to physical
tuple locations and so forth, it's only useful to a slave that has an
*exact* duplicate of the master's entire database cluster. That's not
useless, but it's pretty restrictive.

It could be useful for incremental backup, though I'm not sure how
efficient it is for the purpose. WAL logs tend to be pretty voluminous.
At the very least you'd probably want enough smarts in the archiver to
strip out the page-image records.

Yes, I think the bottom line is that we would need to add some things to
the WAL file to make archiving the logs work, for either point-in-time
recovery, or replication, both of which we need.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

On Fri, 2002-04-26 at 19:41, Tom Lane wrote:

Hannu Krosing <hannu@tm.ee> writes:

DB2 can run in two modes
1) similar to ours, where logs are reused after checkpoints/commits
allow it.
2) with log archiving: logs are never reused, but when system determines
it no longer needs them, it will hand said log over to archiving process
that will archive it (usually do a backup to some other place and then
delete it).

There is in fact the skeleton of support in xlog.c for passing unwanted
log segments over to an archiver, rather than recycling them. So far
no one's done anything with the facility. I think the main problem is
the one Bruce cited: because the WAL representation is tied to physical
tuple locations and so forth, it's only useful to a slave that has an
*exact* duplicate of the master's entire database cluster. That's not
useless, but it's pretty restrictive.

It is probably the fastest way to creating functionality for a hot spare
database.

If we could ship the log changes even earlier than whole logs are
complete, we can get near-realtime backup server.

It could be useful for incremental backup, though I'm not sure how
efficient it is for the purpose. WAL logs tend to be pretty voluminous.

But if they contain enough repeated data they should compress quite
well.

At the very least you'd probably want enough smarts in the archiver to
strip out the page-image records.

If we aim for ability to restore the last known good state and not any
point of time in between, the archiving can be just playing back the
logs over sparse files + keeping record (bitmap or list) of pages that
have been updated and thus are really present in the file. Then doing
full restore would be just restoring some point of time online backup
plus copying over changed pages.

----------------
Hannu