Subject: Re: [GENERAL] wal archiving on a hot-standby server
From: Enrico Sirola <[email-masked]>
To: Simon Riggs <[email-masked]>
Cc: "PostgreSQL General (list)" <[email-masked]>
Date: Tue, 22 Nov 2011 10:51:16 +0100
List-Id: <pgsql-general.postgresql.org>

Hello Simon,

Il giorno 21/nov/2011, alle ore 15.47, Simon Riggs ha scritto:

> On Mon, Nov 21, 2011 at 10:58 AM, Enrico Sirola <[email-masked]> wrote:
> 
>> is it possible to archive the WAL files received by a hot-standby server? In noticed nothing about this on the pgsql docs. The idea is to archive logs in two locations, at the primary site and at the replica site (over a wan) in order to be able to perform a PITR also at the replica site.
>> Thanks a lot for your help,
> 
> Not directly, but you can arrange this yourself.
> 
> Cascading replication is a feature in PG 9.2, released next year.

oh, thanks a lot for the info. By the way, in order to keep an eye on the streamed wal activity, I ended up in writing the following functions (discovering the correct multiplier was a pain). I'd like a code review if someone is available - for what I understood about WALs the functions should return the amount of bytes on the WALs since cluster initialization. Here's the code:

create or replace function last_xlog_receive_bytes()
returns int8
as
$$
   select cast(cast( 'x' || lpad(split_part(
                                 pg_last_xlog_receive_location(), '/', 1),
                                 8, '0')
                    as bit(32))
               as int8) * 16*1024*1024*254 +
          cast(cast( 'x' || lpad(split_part(
                                 pg_last_xlog_receive_location(), '/', 2),
                                 8, '0')
                    as bit(32))
               as int8)
$$
language sql immutable strict;

create or replace function last_xlog_replay_bytes()
returns int8
as
$$
   select cast(cast( 'x' || lpad(split_part(
                                 pg_last_xlog_replay_location(), '/', 1),
                                 8, '0')
                    as bit(32))
               as int8) * 16*1024*1024*254 +
          cast(cast( 'x' || lpad(split_part(
                                 pg_last_xlog_replay_location(), '/', 2),
                                 8, '0')
                    as bit(32))
               as int8)
$$
language sql immutable strict;

create or replace function current_xlog_bytes()
returns int8
as
$$
   select cast(cast( 'x' || lpad(split_part(
                                 pg_current_xlog_location(), '/', 1),
                                 8, '0')
                    as bit(32))
               as int8) * 16*1024*1024*254 +
          cast(cast( 'x' || lpad(split_part(
                                 pg_current_xlog_location(), '/', 2),
                                 8, '0')
                    as bit(32))
               as int8)
$$
language sql immutable strict;

Any comment?
Thanks,
e.