Re: replication using WAL archives

Robert Treat wrote:
> On Thu, 2004-10-21 at 02:44, Iain wrote:
>
>>Hi,
>>
>>I thought I read something about this in relation to v8, but I can't
>>find any reference to it now... is it (or will it be) possible to do
>>master-slave style database replication by transmitting log files to the
>>standby server and having it process them?
>>
>
>
> I'm not certain if this is 8.0, but some folks have created a working
> version against the 8.0 code that will do something like this. Search
> the pgsql-hacker mail list archives for more information on it.

I sent a post on hackers, I put it here:

=======================================================================
Hi all,
I seen that Eric Kerin did the work suggested by Tom about
how to use the PITR in order to have an hot spare postgres,
writing a C program.

I did the same writing 2 shell scripts, one of them perform
the restore the other one deliver the partial filled wal and
check if the postmaster is alive ( check if the pid process
still exist ).

With these two scripts I'm able to have an hot spare installation,
and the spare one go alive when the first postmaster dies.

How test it:

1) Master node:
modify postgresql.conf using:

~ archive_command = 'cp %p /mnt/server/archivedir/%f'

~ launch postgres and perform a backup as doc

~ http://developer.postgresql.org/docs/postgres/backup-online.html

suggest to do

launch the script:

partial_wal_deliver.sh <PID> /mnt/server/partialdir <pg_xlog path>

~ this script will delivery each 10 seconds the "current" wal file,
~ and touch the "alive" file in order to notify the spare node that
~ the master node is up and running

2) Spare node:
create a recovery.conf with the line:

~ restore_command = 'restore.sh /mnt/server/archivedir/%f %p /mnt/server/partialdir'

~ replace the content of data directory with the backup performed at point 1,
~ remove any file present in the pg_xlog directory ( leaving there the archive_status
~ directory ) and remove the postmaster.pid file ( this is necessary if you are running
~ the spare postgres on the same hw ).

~ launch the postmaster, the restore will continue till the "alive" file present in the
~ /mnt/server/partialdir directory is not updated for 60 seconds ( you can modify this
~ values inside the restore.sh script ).

Be sure that restore.sh and all directories involved are accessible

Let me know.

This is a first step, of course, as Eric Kerin did, is better port these script
in C and make it more robust.

Postgres can help this process, as suggested by Tom creating a pg_current_wal()
or even better having two new GUC parameters: archive_current_wal_command and
archive_current_wal_delay.

I problem I discover during the tests is that if you shut down the spare node
and the restore_command is still waiting for a file then the postmaster will never
exit :-(
==========================================================================

I hope that is clear.

Regards
Gaetano Mendola