pg_standby for 8.2 (with last restart point)

Hi all,

For PG versions < 8.3 (specifically 8.2) I wanted the %r parameter to be
substituted by the last restart point, just as the recovery code does in >
8.3. I assumed there would be objections to it (else it would have already
been there in 8.2.x), so started looking for workarounds. After a few ideas,
I settled with using the output of pg_controldata.

Here's what I have done: I execute pg_controldata and parse it's output
to extract the 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).

My next step is: use this script in the restore_command to provide the
%r parameter to pg_standby, like so:

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'

I have tested this script using the following restore_command, on a HEAD
version:

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'

Using the above restore_command, I can see that my script is able to
detect the change in the restart point (%r) just as soon as the server
updates it. Here's a snippet:

<snip>
...
Keep archive history : 000000010000000100000021 and later
running restore : OK
after 000000010000000100000021
before 000000010000000100000045

Trigger file : /tmp/pg_standby.trigger.5433
Waiting for WAL file : 000000010000000100000047
WAL file path : ../wal_archive//000000010000000100000047
Restoring to... : pg_xlog/RECOVERYXLOG
Sleep interval : 5 seconds
Max wait interval : 0 forever
Command for restore : cp "../wal_archive//000000010000000100000047"
"pg_xlog/RECOVERYXLOG"
Keep archive history : 000000010000000100000045 and later
running restore : OK
removing "../wal_archive//000000010000000100000025"
removing "../wal_archive//00000001000000010000002D"
removing "../wal_archive//000000010000000100000031"
...
<./snip>

So, is this a safe way of extracting the last restart point for PG < 8.3?
Or would it be possible to make PG<8.3 provide this %r through some patch?

Best regards,
Gurjeet.

Here's the perl script:

<script>
my @text = `pg_controldata .`; # here . represents the PGDATA, since the
server is executing here.
my $line;

my $time_line_id;
my $redo_log_id;
my $redo_rec_off;
my $wal_seg_bytes;
my $redo_log_seg;

foreach $line ( @text )
{
$line = mychomp( $line );

if( $line =~ m/Latest checkpoint's TimeLineID:\s*(([0-9])+)/ )
{
# decimal number
$time_line_id = 0 + $1;
}
if( $line =~ m/Latest checkpoint's REDO
location:\s*(([0-9]|[A-F])+)\/(([0-9]|[A-F])+)/ )
{
# hexadecimal numbers
$redo_log_id = $1;
$redo_rec_off = $3;
}

if( $line =~ m/Bytes per WAL segment:\s*([0-9]+)/ )
{
# decimal number
$wal_seg_bytes = $1;
}
}

$redo_log_seg = sprintf( "%d", hex( $redo_rec_off ) / $wal_seg_bytes );

print "" . sprintf( "%08X%08X%08X", $time_line_id, $redo_log_id,
$redo_log_seg ) . "\n";

# Wrapper around Perl's chomp function
sub mychomp
{
my ( $tmp ) = @_;
chomp( $tmp );
return $tmp;
}

</script>

--
gurjeet[(dot)singh](at)EnterpriseDB(dot)com
singh(dot)gurjeet(at){ gmail | hotmail | indiatimes | yahoo }.com

EnterpriseDB http://www.enterprisedb.com

Mail sent from my BlackLaptop device