hot spare / log shipping work on

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

Hi all,
I'm having some spare time and I'm testing what Tom Lane was
suggesting:

===========================================================================
Tom Lane wrote:
1. You set up WAL archiving on the master, and arrange to ship copies of
completed segment files to the slave.

2. You take an on-line backup (ie, tar dump) on the master, and restore
it on the slave.

3. You set up a recover.conf file with the restore_command being some
kind of shell script that knows where to look for the shipped-over
segment files, and also has a provision for being signaled to stop
tracking the shipped-over segments and come alive.

4. You start the postmaster on the slave. It will try to recover. Each
time it asks the restore_command script for another segment file, the
script will sleep until that segment file is available, then return it.

5. When the master dies, you signal the restore_command script that it's
time to come alive. It now returns "no such file" to the patiently
waiting postmaster, and within seconds you have a live database on the
slave.
===========================================================================

How I'm expanding the point above:

1) This is the easy part and the task can be accomplished with a simple:

cp %p /mnt/server/archivedir/%f

2) Easy task

3+4) I already wrote the restore_command that do the trick, it take 3
~ parameters: <source> <target> <partial_directory>

~ The partial_directory will contain the partial_segment shipped each
~ minute, and a file "alive" that is "touch"ed periodically

~ The script when called perform these tasks:
a) Check if the file requested exist

a1) If exist check that is a 16MB file ( the request can
~ arrive during the copy ), if is not 16MB sleep for
~ 1 second and retry. This is done for 20 try, after
~ this time out the script exit with a nonzero return time.
~ When the file reach a size of 16MB ( or is already a 16MB
~ file then it's copied with: cp <source> <target>

~ a2) If the file not exist this mean that is not yet recycled and
~ is a partial file present on the partial directory,
~ check if the "alive" file is older then 2 minutes.
~ a21) If the file is older than 2 minutes I assume that
~ the master is dead: I move the partial WAL file
~ present in the partial directory to the <target>
~ directory, and I exit returning a 0 ( the asked file
~ was the partial ). If the partial file do not exist
~ this mean that in the previous call I already moved the
~ partial file and then I have to exit with a nonzero value.

a22) If the file is newer than 2 minutes I assume that
~ the master is alive and I sleep for 5 seconds and I
~ restart from the point a)

5) If the master dies the daemon ( a running shell script ) that is running on
~ the master will not touch the "alive" file.
~ If the master is alive the daemon copy the current WAL file in the <partial
~ directory> with the name <current_name>.tmp and after the copy:
~ mv <current_name>.tmp <current_name>.partial

Do you see any pitfall on it ?
I think in an hour I'll test it and I let you know.

Regard
Gaetano Mendola

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.4 (MingW32)
Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org

iD8DBQFBHNW+7UpzwH2SGd4RAsMBAJ9diSsgG3y6rnueWbZLOvjzko07OwCdGaxE
f8mwC9A4sDJ8nN+XhcUKjP8=
=9SrG
-----END PGP SIGNATURE-----