Re: wal archiving on a hot-standby server

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 <enrico.sirola@gmail.com> 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
beable 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
thefollowing 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.