pg_standby for 8.2 (with last restart point) - Mailing list pgsql-hackers

Hi all,<br /><br />    For PG versions < 8.3 (specifically 8.2) I wanted the %r parameter to be substituted by the
lastrestart point, just as the recovery code does in > 8.3. I assumed there would be objections to it (else it would
havealready been there in 8.2.x), so started looking for workarounds. After a few ideas, I settled with using the
outputof pg_controldata.<br /><br />    Here's what I have done: I execute pg_controldata and parse it's output to
extractthe same information as xlog.c provides for %r in versions > 8.3. Then I rebuild the XLog filename, just like
xlog.c,and emit it from the script. All this is done in a perl script (at the end of this mail).<br /><br />    My next
stepis: use this script in the restore_command to provide the %r parameter to pg_standby, like so:<br /><br
/>restore_command= 'pg_standby -c -d -s 5 -w 0 -t /tmp/pg_standby.trigger.5433 ../wal_archive/ %f %p `perl
/home/gurjeet/dev/last_restart_point.pl`2>> pg_standby.log'<br /><br />    I have tested this script using the
followingrestore_command, on a HEAD version:<br /><br />restore_command = 'echo before `perl
/home/gurjeet/dev/last_restart_point.pl`>> pg_standby.log && pg_standby -c -d -s 5 -w 0 -t
/tmp/pg_standby.trigger.5433../wal_archive/ %f %p %r 2>> pg_standby.log && echo after `perl
/home/gurjeet/dev/last_restart_point.pl`>> pg_standby.log'<br /><br />    Using the above restore_command, I can
seethat my script is able to detect the change in the restart point (%r) just as soon as the server updates it. Here's
asnippet:<br /><br /><snip><br />...<br />Keep archive history    : 000000010000000100000021 and later<br />
runningrestore     : OK<br />after 000000010000000100000021<br />before 000000010000000100000045<br /><br />Trigger
file       : /tmp/pg_standby.trigger.5433<br />Waiting for WAL file    : 000000010000000100000047<br />WAL file
path      : ../wal_archive//000000010000000100000047<br /> Restoring to...     : pg_xlog/RECOVERYXLOG<br />Sleep
interval     : 5 seconds<br />Max wait interval   : 0 forever<br />Command for restore : cp
"../wal_archive//000000010000000100000047""pg_xlog/RECOVERYXLOG"<br /> Keep archive history    :
000000010000000100000045and later<br />running restore     : OK<br />removing
"../wal_archive//000000010000000100000025"<br/>removing "../wal_archive//00000001000000010000002D"<br /> removing
"../wal_archive//000000010000000100000031"<br/>...<br /><./snip><br /><br /><br />    So, is this a safe way of
extractingthe last restart point for PG < 8.3? Or would it be possible to make PG<8.3 provide this %r through
somepatch?<br /><br />Best regards,<br />Gurjeet.<br /><br />Here's the perl script:<br /><br /><script><br />my
@text= `pg_controldata .`; # here . represents the PGDATA, since the server is executing here.<br />my $line;<br /><br
/>my$time_line_id;<br /> my $redo_log_id;<br />my $redo_rec_off;<br />my $wal_seg_bytes;<br />my $redo_log_seg;<br
/><br/>foreach $line ( @text )<br />{<br />    $line = mychomp( $line );<br /><br />    if( $line =~ m/Latest
checkpoint'sTimeLineID:\s*(([0-9])+)/ )<br />     {<br />        # decimal number<br />        $time_line_id = 0 +
$1;<br/>    }<br />    if( $line =~ m/Latest checkpoint's REDO location:\s*(([0-9]|[A-F])+)\/(([0-9]|[A-F])+)/ )<br
/>   {<br />        # hexadecimal numbers<br />         $redo_log_id = $1;<br />        $redo_rec_off = $3;<br />   
}<br/><br />    if( $line =~ m/Bytes per WAL segment:\s*([0-9]+)/ )<br />    {<br />        # decimal number<br
/>       $wal_seg_bytes = $1;<br />    }<br />}<br /><br />$redo_log_seg = sprintf( "%d", hex( $redo_rec_off ) /
$wal_seg_bytes);<br /><br />print "" . sprintf( "%08X%08X%08X", $time_line_id, $redo_log_id, $redo_log_seg ) . "\n";<br
/><br/># Wrapper around Perl's chomp function<br />sub mychomp<br />{<br />    my ( $tmp ) = @_;<br />     chomp( $tmp
);<br/>    return $tmp;<br />}<br /><br /></script><br /><br /><br />-- <br />gurjeet[.singh]@EnterpriseDB.com<br
/>singh.gurjeet@{gmail | hotmail | indiatimes | yahoo }.com<br /><br />EnterpriseDB <a
href="http://www.enterprisedb.com">http://www.enterprisedb.com</a><br/><br />Mail sent from my BlackLaptop device