-----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-----


Fri, 13 Aug 2004 16:52:47 +0200
Gaetano Mendola <mendola@bigfoot.com>
hot spare / log shipping   work on
Gaetano Mendola wrote:

>
>         a1) If exist check that is a 16MB file ( the request can
> ~                arrive during the copy ),

I think this will fail under windows: "copy" first sets the file size 
and then transfers the data. I wouldn't rule out that some Unices use 
the same implementation.

>
> ~            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'd concentrate on cold failover: the user (or the OS) must call a 
script to cause a fail-over. The tricky thing are the various partial 
connection losses between master and spare: perhaps the alive file is 
not updated anymore due to a net split, but the master is still alive. 
Unless you are really careful both master and spare could run.

I think SAP DB / MaxDB supports failover - perhaps it would be 
interesting to check their failover scripts.

--
    Manfred

Fri, 13 Aug 2004 19:20:15 +0200
Manfred Spraul <manfred@colorfullife.com>
Re: hot spare / log shipping   work on
Manfred Spraul wrote:

> Gaetano Mendola wrote:
> 
>>
>>         a1) If exist check that is a 16MB file ( the request can
>> ~                arrive during the copy ),
> 
> 
> I think this will fail under windows: "copy" first sets the file size 
> and then transfers the data. I wouldn't rule out that some Unices use 
> the same implementation.

I'm doing this work as "proof of concept", after made it working on
my platform we can even write them in C.



>> ~            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'd concentrate on cold failover: the user (or the OS) must call a 
> script to cause a fail-over. The tricky thing are the various partial 
> connection losses between master and spare: perhaps the alive file is 
> not updated anymore due to a net split, but the master is still alive. 
> Unless you are really careful both master and spare could run.

I agree but as I said I'm doing it as "proof of concept", we can even change
later the way to discovery that master is dead.

I'm facing however to the following problems:

1) Discovery the actual WAL file
	I'm supposing is the last modified file inside the
         pg_xlog directory. If this is not the good method
         may I know how I can know it ?

2) During the recovery phase postmaster ask me for file that will never be
    there like:  00000001.history.
	Actualy if the file does not exist and contain the string history
         instead to wait for him I exit with 1 exit code

	Is this right for you ?



Regards
Gaetano Mendola









Fri, 13 Aug 2004 21:45:36 +0200
Gaetano Mendola <mendola@bigfoot.com>
Re: hot spare / log shipping   work on
Gaetano Mendola <mendola@bigfoot.com> writes:
> I'm facing however to the following problems:

> 1) Discovery the actual WAL file
> 	I'm supposing is the last modified file inside the
>          pg_xlog directory. If this is not the good method
>          may I know how I can know it ?

While that theoretically will work, it leaves a bad taste in my mouth.
I have been thinking of proposing that we add a "pg_current_wal_file()"
function, or some such name, to return the name of the active WAL file.

> 2) During the recovery phase postmaster ask me for file that will never be
>     there like:  00000001.history.
> 	Actualy if the file does not exist and contain the string history
>          instead to wait for him I exit with 1 exit code

I think you can reasonably assume that .history files won't show up
on-the-fly, since they are only created during a PITR recovery operation
on the master.  It's not clear how this whole thing should track such an
operation on the master anyway :-(

			regards, tom lane

Fri, 13 Aug 2004 16:32:59 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: hot spare / log shipping work on 
Tom Lane wrote:

> Gaetano Mendola <mendola@bigfoot.com> writes:
> 
>>I'm facing however to the following problems:
> 
> 
>>1) Discovery the actual WAL file
>>	I'm supposing is the last modified file inside the
>>         pg_xlog directory. If this is not the good method
>>         may I know how I can know it ?
> 
> 
> While that theoretically will work, it leaves a bad taste in my mouth.
> I have been thinking of proposing that we add a "pg_current_wal_file()"
> function, or some such name, to return the name of the active WAL file.

Totally agree, this could help during the process.

Actually I detect the current wal file in this way:

ls -t1p $PGXLOGDIR | grep -v / | head 1

that is an almost "empirical" process, in the first phase
I can live with it but for sure a more robust way is a must.


Regards
Gaetano Mendola








Sat, 14 Aug 2004 00:49:28 +0200
Gaetano Mendola <mendola@bigfoot.com>
Re: hot spare / log shipping work on

I notice that PITR doesn't function correctly on Windows. Has that been
reported elsewhere?

The archive_command parameter %p resolves to a full path containing slashes
rather than backslashes. This is not a Windows file, so any attempt to copy
it fails. There isn't any way to avoid that.

I'm surely not the first to report that? Am I?

[There isn't any way of telling, by default, since the log goes nowhere....]

Is there a PostgreSQL approved way of saying "for Windows port, use
backslashes in pathnames"?

Best Regards, Simon Riggs


Sat, 14 Aug 2004 01:50:23 +0100
"Simon@2ndquadrant.com" <simon@2ndquadrant.com>
PITR on Windows?


Simon@2ndquadrant.com wrote:

>I notice that PITR doesn't function correctly on Windows. Has that been
>reported elsewhere?
>
>The archive_command parameter %p resolves to a full path containing slashes
>rather than backslashes. This is not a Windows file, so any attempt to copy
>it fails. There isn't any way to avoid that.
>
>I'm surely not the first to report that? Am I?
>
>
>  
>

This was fixed several days ago, after lengthy discussion on the -win32 
list, and we have seen reported success, IIRC.

cheers

andrew

Fri, 13 Aug 2004 21:06:19 -0400
Andrew Dunstan <andrew@dunslane.net>
Re: PITR on Windows?
Andrew Dunstan wrote:
> 
> 
> Simon@2ndquadrant.com wrote:
> 
> >I notice that PITR doesn't function correctly on Windows. Has that been
> >reported elsewhere?
> >
> >The archive_command parameter %p resolves to a full path containing slashes
> >rather than backslashes. This is not a Windows file, so any attempt to copy
> >it fails. There isn't any way to avoid that.
> >
> >I'm surely not the first to report that? Am I?
> >
> >
> >  
> >
> 
> This was fixed several days ago, after lengthy discussion on the -win32 
> list, and we have seen reported success, IIRC.

Yes, the fix will be in beta2 or the next pginstaller release.

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

Fri, 13 Aug 2004 23:21:23 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: PITR on Windows?
Bruce Momjian wrote:

 > Andrew Dunstan wrote:
 >
 >>
 >>Simon@2ndquadrant.com wrote:
 >>
 >>
 >>>I notice that PITR doesn't function correctly on Windows. Has that been
 >>>reported elsewhere?
 >>>
 >>>The archive_command parameter %p resolves to a full path containing slashes
 >>>rather than backslashes. This is not a Windows file, so any attempt to copy
 >>>it fails. There isn't any way to avoid that.
 >>>
 >>>I'm surely not the first to report that? Am I?
 >>>
 >>>
 >>>
 >>>
 >>
 >>This was fixed several days ago, after lengthy discussion on the -win32
 >>list, and we have seen reported success, IIRC.
 >
 >
 > Yes, the fix will be in beta2 or the next pginstaller release.

Do you mean that the next pginstaller release will carry a 8.0beta1
with different behaviour that actual 8.0beta1 ?

I hope I mistake you.

Regards
Gaetano Mendola






Sat, 14 Aug 2004 10:08:51 +0200
Gaetano Mendola <mendola@bigfoot.com>
Re: PITR on Windows?


Gaetano Mendola wrote:

> Bruce Momjian wrote:
>
>
> >
> > Yes, the fix will be in beta2 or the next pginstaller release.
>
> Do you mean that the next pginstaller release will carry a 8.0beta1
> with different behaviour that actual 8.0beta1 ?
>
> I hope I mistake you.
>
>
What are you talking about? The installer project doesn't have to sit 
around waiting for the next beta before they include patches that have 
occurred since then. They can package whatever they like, in fact (and 
they have been doing a fantastic job, IMNSHO). After all, their releases 
are beta too. You can run against cvs HEAD and it will be marked 8.0beta1.

cheers

andrew

Sat, 14 Aug 2004 09:32:14 -0400
Andrew Dunstan <andrew@dunslane.net>
Re: PITR on Windows?
Andrew Dunstan wrote:

> 
> 
> Gaetano Mendola wrote:
> 
>> Bruce Momjian wrote:
>>
>>
>> >
>> > Yes, the fix will be in beta2 or the next pginstaller release.
>>
>> Do you mean that the next pginstaller release will carry a 8.0beta1
>> with different behaviour that actual 8.0beta1 ?
>>
>> I hope I mistake you.
>>
>>
> What are you talking about? The installer project doesn't have to sit 
> around waiting for the next beta before they include patches that have 
> occurred since then. They can package whatever they like, in fact (and 
> they have been doing a fantastic job, IMNSHO). After all, their releases 
> are beta too. You can run against cvs HEAD and it will be marked 8.0beta1.

So? I don't like it neither. Am I criticizing their job ?

I think is better have a 8.0beta1 and a 8.0beta1devel, this just in order to
understand which version the users are testing. But of course I'm the last
in this list that can decide about it.

BTW other projects are carrying also a build version in order to distinguish
between two 8.0beta1 versions.

Regards
Gaetano Mendola






Sat, 14 Aug 2004 16:06:46 +0200
Gaetano Mendola <mendola@bigfoot.com>
Re: PITR on Windows?


Gaetano Mendola wrote:

>
> BTW other projects are carrying also a build version in order to 
> distinguish
> between two 8.0beta1 versions.
>
>

Except that we don't officially publish builds, only source sets.

Actually, this might be a reson to chenge to a different SCC system - 
svn at least has a unique tree number for every set (it gets bumped 
after every commit), and I could see some value in exposing that 
information. Then if people report bugs against dev/beta versions, we 
could ask then for the tree number, which would be more helpful than the 
label. Not sure if other systems have this feature - I do know I 
recently looked at svn and decided it wasn't quite stable enough for my 
company's needs - they had a file format change earlier this year, which 
worried me.

cheers

andrew

Sat, 14 Aug 2004 11:09:23 -0400
Andrew Dunstan <andrew@dunslane.net>
Re: PITR on Windows?