Thread: wal archiving on a hot-standby server

Hello,
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,
Enrico

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.

--
 Simon Riggs                   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services

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.

On 11/22/11 1:51 AM, Enrico Sirola wrote:
> 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?

I don't believe that function is immutable, since it depends on the
value of pg_current_xlog_location() which will change over time.



--
john r pierce                            N 37, W 122
santa cruz ca                         mid-left coast

Hello John,

Il giorno 22/nov/2011, alle ore 11.04, John R Pierce ha scritto:

> I don't believe that function is immutable, since it depends on the value of pg_current_xlog_location() which will
changeover time. 

oops, I'm afraid you are right!
e.

On Mon, Nov 21, 2011 at 5:58 AM, Enrico Sirola <enrico.sirola@gmail.com> wrote:
> Hello,
> 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
performa PITR also 
> at the replica site.

You can do this 2 different ways; 1 is by adding multiple destinations
into your archive command to send the wal file to multiple
destinations;  here is an example using omnipitr (the -dr are the
remote destinations, we gzip the first for long term archiving)

/opt/OMNIpitr/bin/omnipitr-archive -dr
gzip=db4:/mnt/db/prod/walarchive/ -dr db2:/mnt/db/prod/db2-walarchive/
  "%p"

The other way to do this is to add something into your
archive_cleanup_command of your recovery.conf to archive the files to
the other destination.

Which method you want depends on the version / setup of postgres you
have, and whether you want the slave to be in the chain of the replica
site. (I probably wouldn't, which would make me lean towards something
like omnipitr)

Robert Treat
conjecture: xzilla.net
consulting: omniti.com