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(dot)sirola(at)gmail(dot)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 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.