Cascade streaming replication + wal_files - Pgsql 9.2

On 7/6/2016 6:18 PM, Patrick B wrote:

is it possible to get slave02 replicating from slave01 (Cascade
streaming replication) this can be done with streaming ? and also
slave01 sending the wal_files to that new slave02?

https://www.postgresql.org/docs/9.2/static/warm-standby.html#CASCADING-REPLICATION

--
john r pierce, recycling bits in santa cruz

Thanks!

Someone from another list told me that wouldn't be possible on the 9.2
version, that's why I asked even that I read that doc...

cheers

On 7/6/2016 6:30 PM, Patrick B wrote:

Someone from another list told me that wouldn't be possible on the 9.2
version, that's why I asked even that I read that doc...

it was new in 9.2, 9.1 didn't support cascading.

--
john r pierce, recycling bits in santa cruz

Yep.. thanks for the help!

On Thu, 7 Jul 2016, 9:20 a.m. Patrick B, <patrickbakerbr@gmail.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)

Cheers guys!
Patrick

--
--
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

On Thu, 7 Jul 2016, 9:20 a.m. Patrick B, <patrickbakerbr@gmail.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.

the only way to archive the wal_files from a slave into another slave on
PostgreSQL 9.2, is by using pg_reveivexlog?

On Thu, 7 Jul 2016, 9:51 a.m. Patrick B, <patrickbakerbr@gmail.com> wrote:

On Thu, 7 Jul 2016, 9:20 a.m. Patrick B, <patrickbakerbr@gmail.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.

--

--
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

ok.. cool... cheers mate!

Patrick

On 7/6/2016 6:52 PM, Sameer Kumar wrote:

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.

a slave doesn't generate wal_files at all.

if your master is keeping a wal_archive, slaves, including cascaded
streaming slaves, can all use that same archive as their wal source,
these are only used for catchup when streaming is interrupted.

--
john r pierce, recycling bits in santa cruz

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

On Thu, 7 Jul 2016, 9:51 a.m. Patrick B, <patrickbakerbr@gmail.com> wrote:

On Thu, 7 Jul 2016, 9:20 a.m. Patrick B, <patrickbakerbr@gmail.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

ok...

I'll archive the wal_files locally on slave01, and then send them to
slave02 every 10 minutes, for example. This will be done by rsync or
something else.

I'll have still the problem with timeline, and slave02 won't be able to
become a master in the future.

Is that right?

On Thu, 7 Jul 2016, 9:59 a.m. John R Pierce, <pierce@hogranch.com> wrote:

On 7/6/2016 6:52 PM, Sameer Kumar wrote:

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.

a slave doesn't generate wal_files at all.

if your master is keeping a wal_archive, slaves, including cascaded
streaming slaves, can all use that same archive as their wal source, these
are only used for catchup when streaming is interrupted.

But the slave can send wal to a wal_receiver and I think pg_receivexlog
will be nothing less than a wal_receiver. I will hold back from any further
comments unless I have tried it.

But a good question here is, why do you want to do that? Why Archive from
standby to cascaded standby? If it is only to catchup, the archives from
master are good enough

--
john r pierce, recycling bits in santa cruz

--

--
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

On 7/6/2016 7:10 PM, Patrick B wrote:

I'll archive the wal_files locally on slave01, and then send them to
slave02 every 10 minutes, for example. This will be done by rsync or
something else.

I'll have still the problem with timeline, and slave02 won't be able
to become a master in the future.

the master should archive to a file server (NFS or whatever). all slaves
should use this wal archive as a 'catch up source' when they resume
after downtime. [*]

master /also/ sends streaming replication to any direct attached slaves,
and those slaves can send streaming replication to any cascaded slaves,
repeat.

copying WAL archives around via rsync just seems cray-cray to me.

[*] in the latest version, there are 'replication slots' which provides
a completely different mechanism for this catch-up that doesn't require
WAL archiving

--
john r pierce, recycling bits in santa cruz

On Thu, 7 Jul 2016, 10:10 a.m. Patrick B, <patrickbakerbr@gmail.com> wrote:

ok...

I'll archive the wal_files locally on slave01,

How? I assume you are planning on doing this with scp/rsync in
archive_command in upstream master.

and then send them to slave02 every 10 minutes, for example.

Sounds good. But why? Is it just to have redundunt archives?
On your upstream master you can also use a custom script which inturn would
be scp'ing the wals to 2 or 3 servers for archiving purposes

This will be done by rsync or something else.

I'll have still the problem with timeline, and slave02 won't be able to
become a master in the future.

No, you should not have a problem. Since rsync will also copy the timeline
related files to slave02

Is that right?

--
--
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

The slave02 server will be a copy of the DB into Amazon. There will be a
migration to Amazon in the future, and the company's managers want a copy
of the current DB there to test and do all the stuffs they need (migrating
to 9.5, too).

slave01 is already working as a streaming replication server.
The master server sends the wal_files to slave01 by archive_command.

The plan below isn't my idea, I would do different but isn't my call:

*Current scenario:*

master stores wal_files into slave01
slave02 does not exists

*The plan is:*

1. setup slave02 at amazon EC2 (just for testing and future master server
for devs)
2. setup postgres on slave02 (9.2)
3. pg_basebackup will be run from slave01. This will split the base in
files of 50GB each (example)
4. Send the splitted files from slave01 to slave02
5. restore/join the files
6. start postgres on the slave02 slave
7. restore the DB using the wal_files from slave01

Question:

Is possible to make slave01 archive the wal_files? can just be a
archive_command and archive_mode = on?

On Thu, 7 Jul 2016, 10:32 a.m. Patrick B, <patrickbakerbr@gmail.com> wrote:

The slave02 server will be a copy of the DB into Amazon. There will be a
migration to Amazon in the future, and the company's managers want a copy
of the current DB there to test and do all the stuffs they need (migrating
to 9.5, too).

Have you checked out Amazon's DMS?

slave01 is already working as a streaming replication server.
The master server sends the wal_files to slave01 by archive_command.

The plan below isn't my idea, I would do different but isn't my call:

Been there :)

*Current scenario:*

master stores wal_files into slave01
slave02 does not exists

*The plan is:*

1. setup slave02 at amazon EC2 (just for testing and future master server
for devs)

Is it EC2 Classic? Or is it EC2 Virtual Pvt Cloud (VPC)?

2. setup postgres on slave02 (9.2)
3. pg_basebackup will be run from slave01. This will split the base in
files of 50GB each (example)
4. Send the splitted files from slave01 to slave02
5. restore/join the files
6. start postgres on the slave02 slave
7. restore the DB using the wal_files from slave01

Given that slave02 is a standby, how do you plan on doing your regression
testing? It will be just a read only database.

You can restore the wal_file by specifying resotre_command to copy from the
archive generated by the master (rsync or scp to pull from your in-premise
setup to EC2). This would be fairly simple if you are using VPC

Question:

Is possible to make slave01 archive the wal_files?

If you really can not just live with archive generated on master itself,
you need to try the options discussed up thread.
1. Copy the archives generated on master to a shared location or may be
copy it to S3 bucket
2. Archive generated on master is rsync (schedule basis) to EC2
3. pg_receivexlog running on EC2 to copy WAL from slave01

can just be a archive_command and archive_mode = on?

Setting these parameters on slave02 will have not any effect.

--

--
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

2016-07-07 14:55 GMT+12:00 Sameer Kumar <sameer.kumar@ashnik.com>:

On Thu, 7 Jul 2016, 10:32 a.m. Patrick B, <patrickbakerbr@gmail.com>
wrote:

The slave02 server will be a copy of the DB into Amazon. There will be a
migration to Amazon in the future, and the company's managers want a copy
of the current DB there to test and do all the stuffs they need (migrating
to 9.5, too).

Have you checked out Amazon's DMS?

Like I said.. it's gonna be a test server. Does not need to be powerful or
to use tools.. a EC2 would be enough.

slave01 is already working as a streaming replication server.
The master server sends the wal_files to slave01 by archive_command.

The plan below isn't my idea, I would do different but isn't my call:

Been there :)

*Current scenario:*

master stores wal_files into slave01
slave02 does not exists

*The plan is:*

1. setup slave02 at amazon EC2 (just for testing and future master server
for devs)

Is it EC2 Classic? Or is it EC2 Virtual Pvt Cloud (VPC)?

Have no idea lol - I believe it will be classic....

2. setup postgres on slave02 (9.2)
3. pg_basebackup will be run from slave01. This will split the base in
files of 50GB each (example)
4. Send the splitted files from slave01 to slave02
5. restore/join the files
6. start postgres on the slave02 slave
7. restore the DB using the wal_files from slave01

Given that slave02 is a standby, how do you plan on doing your regression
testing? It will be just a read only database.

hmmm... do u mean by this, that I won't be able to turn slave02 as a master?

You can restore the wal_file by specifying resotre_command to copy from
the archive generated by the master (rsync or scp to pull from your
in-premise setup to EC2). This would be fairly simple if you are using VPC

ok. so a RSYNC would grab the wal-files from the current folder on the
slave01 server, and send them to slave02.
easy

Question:

Is possible to make slave01 archive the wal_files?

If you really can not just live with archive generated on master itself,
you need to try the options discussed up thread.

I'd prefer, but I can't lol

1. Copy the archives generated on master to a shared location or may be
copy it to S3 bucket

as i said, the servers will be migrated to amazon, any change now will not
be approved.

2. Archive generated on master is rsync (schedule basis) to EC2
3. pg_receivexlog running on EC2 to copy WAL from slave01

this is not needed, as the wal_files will be sent by RSYNC from slave01.

can just be a archive_command and archive_mode = on?

Setting these parameters on slave02 will have not any effect.

slave01* *not* slave02.

With this scenario, slave02 will be able to turn up to a master server in
the future?

On Thu, Jul 7, 2016 at 11:02 AM Patrick B <patrickbakerbr@gmail.com> wrote:

2016-07-07 14:55 GMT+12:00 Sameer Kumar <sameer.kumar@ashnik.com>:

On Thu, 7 Jul 2016, 10:32 a.m. Patrick B, <patrickbakerbr@gmail.com>
wrote:

The slave02 server will be a copy of the DB into Amazon. There will be a
migration to Amazon in the future, and the company's managers want a copy
of the current DB there to test and do all the stuffs they need (migrating
to 9.5, too).

Have you checked out Amazon's DMS?

Like I said.. it's gonna be a test server. Does not need to be powerful or
to use tools.. a EC2 would be enough.

DMS is Database Migration Service from Amazon. :)
It allows you to setup a hybrid architecture like what you are planning to
have.

slave01 is already working as a streaming replication server.
The master server sends the wal_files to slave01 by archive_command.

The plan below isn't my idea, I would do different but isn't my call:

Been there :)

*Current scenario:*

master stores wal_files into slave01
slave02 does not exists

*The plan is:*

1. setup slave02 at amazon EC2 (just for testing and future master
server for devs)

Is it EC2 Classic? Or is it EC2 Virtual Pvt Cloud (VPC)?

Have no idea lol - I believe it will be classic....

Since you plan to use this for Production later on, better to use VPC. It
also ensures a fixed IP.

2. setup postgres on slave02 (9.2)
3. pg_basebackup will be run from slave01. This will split the base in
files of 50GB each (example)
4. Send the splitted files from slave01 to slave02
5. restore/join the files
6. start postgres on the slave02 slave
7. restore the DB using the wal_files from slave01

Given that slave02 is a standby, how do you plan on doing your regression
testing? It will be just a read only database.

hmmm... do u mean by this, that I won't be able to turn slave02 as a
master?

No, that's is not what I mean. I think you are mixing it up.

Slave02, with or without archives can always be promoted. I never saw that
you will be doing a promotion.

Anyways, for your case, AWS DMS looks like the best option. I suggest that
you explore that.

If I understood it right, your aim is to create a stand alone test DB
server restored using backup of slave01 and recovered to latest point in
time using archives. Now getting these archives to EC2 is you challenge and
hence you are thinking of ways to send archives from slave01 to EC2.

A. Can you not just start the server with the backup itself or do you
really need to start EC2 stand alone server with latest transaction? if not
then just drop the whole idea of getting the archives restored

B. If you need latest data and transaction before EC2 server is promoted as
master, take a look at DMS

Everything else we are discussing/discussed is not the best way and
involves workaround

You can restore the wal_file by specifying resotre_command to copy from
the archive generated by the master (rsync or scp to pull from your
in-premise setup to EC2). This would be fairly simple if you are using VPC

ok. so a RSYNC would grab the wal-files from the current folder on the
slave01 server, and send them to slave02.
easy

Question:

Is possible to make slave01 archive the wal_files?

If you really can not just live with archive generated on master itself,
you need to try the options discussed up thread.

I'd prefer, but I can't lol

1. Copy the archives generated on master to a shared location or may be
copy it to S3 bucket

as i said, the servers will be migrated to amazon, any change now will not
be approved.

2. Archive generated on master is rsync (schedule basis) to EC2
3. pg_receivexlog running on EC2 to copy WAL from slave01

this is not needed, as the wal_files will be sent by RSYNC from slave01.

can just be a archive_command and archive_mode = on?

Setting these parameters on slave02 will have not any effect.

slave01* *not* slave02.

yep. typo.

With this scenario, slave02 will be able to turn up to a master server in
the future?

Yes. Infact, slave02 (I now prefer to call it EC2 instance) can be promoted
to master even without archives. I assume you don't intend to run it (EC2)
as a slave/standby for very long and will promote it to become a standalone
test DB server. With that, I would not much worry about archives or
timeline switch.
--
--
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