Re: Cascade streaming replication + wal_files - Pgsql 9.2

On Thu, 7 Jul 2016, 9:52 a.m. Sameer Kumar, <sameer(dot)kumar(at)ashnik(dot)com> wrote:

>
>
> On Thu, 7 Jul 2016, 9:51 a.m. Patrick B, <patrickbakerbr(at)gmail(dot)com> wrote:
>
>>
>>>
>>> On Thu, 7 Jul 2016, 9:20 a.m. Patrick B, <patrickbakerbr(at)gmail(dot)com>
>>> wrote:
>>>
>>>> Hi guys,
>>>>
>>>> I got two slaves using Postgresql 9.2.
>>>>
>>>> slave01 - Streaming replication from master
>>>> slave02 - nothing.. new server
>>>>
>>>> is it possible to get slave02 replicating from slave01 (Cascade
>>>> streaming replication) this can be done with streaming ?
>>>>
>>>
>>> Yes, a cascaded standby can be setup with Streaming Replication in v9.2
>>> and above.
>>>
>>> and also slave01 sending the wal_files to that new slave02?
>>>>
>>>
>>> What exactly do you mean here by "sending the wal_files"??
>>>
>>>
>>>> So it would be on slave01
>>>>
>>>> archive_mode = ok
>>>> archive_command = ....
>>>>
>>>
>>> This will have no effect on a standby server. archive_command will be
>>> neglected
>>>
>>> This is possible only in v9.5 onward when archive_mode is set to always.
>>>
>>> wal_level = hot_standby
>>>> max_wal_senders = 2
>>>> wal_keep_segments = 128
>>>>
>>>> Is that right?
>>>>
>>>
>>> If you want to set up archiving from slave01, checkout pg_receivexlog.
>>>
>>>
>>> https://www.postgresql.org/docs/9.2/static/app-pgreceivexlog.html
>>>
>>> It might be helpful as it does something similar to archiving but using
>>> streaming protocol (so I think it should work even in cascaded mode). But
>>> since it works using streaming protocol, max_wal_senders on slave01 will be
>>> 2(what you have set now) + 1 (for pg_reveivexlog)
>>>
>>>
>>
>> ok.. got a little confused now.
>>
>
> I thought I did my best to explain :)
>
>
>
>> the only way to archive the wal_files from a slave into another slave on
>> PostgreSQL 9.2, is by using pg_reveivexlog?
>>
>
> Yes. Though I have never tried this myself but going by the theory, it
> should work.
>

I think with pg_receivexlog, in v9.2 you will not be able to get timeline
switch information. So while the wal_file received using pg_receivexlog can
be used for replication or even for PITR, complex recovery or continuing
replication after promotion of slave01 will not be possible. Untill v9.2
this timeline switch info is recorded only in archives generated by
archive_mode (on). But pg_receivexlog is creating archives only from live
WAL using wal_sender.

Though you may still use archive_command on slave01. When your master goes
down, slave01 (if you choose to) is promoted to be a master. Then
archive_command set in slave01 will be effective and will send the archives
to slave02 or whatever you have set in archive_command. But as long as
slave01 is standby you will have to rely on pg_receivexlog (running on
slave02) and by doing that you will miss timeline switch info.

--
> --
> Best Regards
> Sameer Kumar | DB Solution Architect
> *ASHNIK PTE. LTD.*
>
> 101 Cecil Street, #11-11 Tong Eng Building, Singapore 069 533
>
> T: +65 6438 3504 | M: +65 8110 0350 | www.ashnik.com
>
--
--
Best Regards
Sameer Kumar | DB Solution Architect
*ASHNIK PTE. LTD.*

101 Cecil Street, #11-11 Tong Eng Building, Singapore 069 533

T: +65 6438 3504 | M: +65 8110 0350 | www.ashnik.com