Обсуждение: Cascade streaming replication + wal_files - Pgsql 9.2
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 ? and also slave01 sending the wal_files to that new slave02?
So it would be on slave01
archive_mode = ok
archive_command = ....
wal_level = hot_standby
max_wal_senders = 2
wal_keep_segments = 128
Is that right?
Cheers guys!
Patrick
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 masterslave02 - nothing.. new serveris 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 slave01archive_mode = okarchive_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_standbymax_wal_senders = 2wal_keep_segments = 128Is that right?
If you want to set up archiving from slave01, checkout pg_receivexlog.
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 masterslave02 - nothing.. new serveris 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 slave01archive_mode = okarchive_command = ....This will have no effect on a standby server. archive_command will be neglectedThis is possible only in v9.5 onward when archive_mode is set to always.wal_level = hot_standbymax_wal_senders = 2wal_keep_segments = 128Is that right?If you want to set up archiving from slave01, checkout pg_receivexlog.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 masterslave02 - nothing.. new serveris 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 slave01archive_mode = okarchive_command = ....This will have no effect on a standby server. archive_command will be neglectedThis is possible only in v9.5 onward when archive_mode is set to always.wal_level = hot_standbymax_wal_senders = 2wal_keep_segments = 128Is that right?If you want to set up archiving from slave01, checkout pg_receivexlog.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
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.
so.. is or isn't possible a slave to archive de wal_files?
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 masterslave02 - nothing.. new serveris 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 slave01archive_mode = okarchive_command = ....This will have no effect on a standby server. archive_command will be neglectedThis is possible only in v9.5 onward when archive_mode is set to always.wal_level = hot_standbymax_wal_senders = 2wal_keep_segments = 128Is that right?If you want to set up archiving from slave01, checkout pg_receivexlog.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 RegardsSameer Kumar | DB Solution ArchitectASHNIK 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 slave01slave02 does not existsThe 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 slave025. restore/join the files6. start postgres on the slave02 slave7. 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 slave01slave02 does not existsThe 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 slave025. restore/join the files6. start postgres on the slave02 slave7. restore the DB using the wal_files from slave01Given 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 EC23. 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 slave01slave02 does not existsThe 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 slave025. restore/join the files6. start postgres on the slave02 slave7. restore the DB using the wal_files from slave01Given 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 VPCok. so a RSYNC would grab the wal-files from the current folder on the slave01 server, and send them to slave02.easyQuestion:
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 lol1. Copy the archives generated on master to a shared location or may be copy it to S3 bucketas 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 EC23. pg_receivexlog running on EC2 to copy WAL from slave01this 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
2016-07-07 15:19 GMT+12:00 Sameer Kumar <sameer.kumar@ashnik.com>:
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.
oh ok.... thanks for the tip!
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 slave01slave02 does not existsThe 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.
I've just checked and is indeed a VPC with 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 slave025. restore/join the files6. start postgres on the slave02 slave7. restore the DB using the wal_files from slave01Given 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
Nope.. The EC2 server has to be updated with latest transaction. There will be more ideas around here, that's why we need it updated .
B. If you need latest data and transaction before EC2 server is promoted as master, take a look at DMSEverything else we are discussing/discussed is not the best way and involves workaroundYou 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 VPCok. so a RSYNC would grab the wal-files from the current folder on the slave01 server, and send them to slave02.easyQuestion:
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 lol1. Copy the archives generated on master to a shared location or may be copy it to S3 bucketas 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 EC23. pg_receivexlog running on EC2 to copy WAL from slave01this 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.
ok cool.
To finalize:
- slave01 can't archive the wal_files by itself with postgres 9.2. archive_mode and archive_command will be there for nothing if setteled to on or a command(archive_command).
However, I still didn't understand this part. https://www.postgresql.org/docs/9.2/static/warm-standby.html#CASCADING-REPLICATION
Says it is possible: "The cascading replication feature allows a standby server to accept replication connections and stream WAL records to other standbys, acting as a relay. This can be used to reduce the number of direct connections to the master and also to minimize inter-site bandwidth overheads."
- EC2 server can be promoted to a master server at any time, with or without the archives (Just a base backup would be enough)
- slave01 is storing the wal_files from master, and EC2 server will copy them from there(slave01).. using RSYNC or something else.
- in the future, EC2 server will be a streaming replication server replicating the DB from slave01... nothing has to be done here as STREAMING CASCADING is already allowed on 9.2.
- If EC2 server in the future will need to get the wal_files via streaming from slave01, it will have to use the pg_receivexlog tool.
On Thu, Jul 7, 2016 at 11:37 AM Patrick B <patrickbakerbr@gmail.com> wrote:
2016-07-07 15:19 GMT+12:00 Sameer Kumar <sameer.kumar@ashnik.com>: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.oh ok.... thanks for the tip!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 slave01slave02 does not existsThe 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.I've just checked and is indeed a VPC with 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 slave025. restore/join the files6. start postgres on the slave02 slave7. restore the DB using the wal_files from slave01Given 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 restoredNope.. The EC2 server has to be updated with latest transaction. There will be more ideas around here, that's why we need it updated .B. If you need latest data and transaction before EC2 server is promoted as master, take a look at DMSEverything else we are discussing/discussed is not the best way and involves workaroundYou 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 VPCok. so a RSYNC would grab the wal-files from the current folder on the slave01 server, and send them to slave02.easyQuestion:
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 lol1. Copy the archives generated on master to a shared location or may be copy it to S3 bucketas 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 EC23. pg_receivexlog running on EC2 to copy WAL from slave01this 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.ok cool.To finalize:- slave01 can't archive the wal_files by itself with postgres 9.2. archive_mode and archive_command will be there for nothing if setteled to on or a command(archive_command).However, I still didn't understand this part. https://www.postgresql.org/docs/9.2/static/warm-standby.html#CASCADING-REPLICATION
Says it is possible: "The cascading replication feature allows a standby server to accept replication connections and stream WAL records to other standbys, acting as a relay. This can be used to reduce the number of direct connections to the master and also to minimize inter-site bandwidth overheads."
*STREAM* WAL Records to other standby is not really same as *ARCHIVE* WAL Records to other standby
- EC2 server can be promoted to a master server at any time, with or without the archives (Just a base backup would be enough)
Make sure you include WAL files in pg_basebackup (option -x -Xs )
- slave01 is storing the wal_files from master, and EC2 server will copy them from there(slave01).. using RSYNC or something else.- in the future, EC2 server will be a streaming replication server replicating the DB from slave01... nothing has to be done here as STREAMING CASCADING is already allowed on 9.2.
Sounds ok untill you face a network outage between your data center and EC2 site. With that, if slave01 has not kept enough WAL (wal_keep_segment) you may run out of WAL files and EC2 standby will go out of sync and this where you may have to restore the WALs from datacenter to EC2
you can use restore_command along so that any missing WAL is pulled by EC2 to catchup.
restore_command='scp myuser@slave02.local-datacenter.com:/arch_dir/%f %p'
VPC should allow this to happen.
- If EC2 server in the future will need to get the wal_files via streaming from slave01, it will have to use the pg_receivexlog tool.
I would not suggest this as best approach. Best would be to use DMS if that has some challenges, then you can use resotre_command in recovery.conf (as suggested above) to sync up the EC2 standby with in-premise servers
--
--
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
Hi guys, (I'm sending this email here because it has the same subject, wal_files)
I'm setting up a new slave server, using Postgres 9.2. This new slave server I'll call: New_slave.
I ran this command, from the new_slave server. It will connects to my other slave and copy the DB.
ssh postgres@slave05 'pg_basebackup --pgdata=- --format=tar --label=new_slave --progress --host=localhost --port=5432 --username=replicator --xlog' | tar -x --no-same-owner
That took me 10 days, as I'm copying +2TB. Note that I'm not using --xlog-method=stream, but I was copying the wal_files manually to the new_slave server. By the time the command above (pg_basebackup) finished, there was 1TB of wal_files.
I started to recovery the Database, setting the recovery.conf.recovery_command.
and then I got this error:
WAL segment `../wal_archive/000000020000171B000000D9` not found
So.. shit.. I don't know what happened, but I checked the master server and actually that file wasn't shipped into the new_server. I don't know why.
Questions:
1 - Do you have any recommendations? I'll have to perform that again.
2 - If I use pg_basebackup with -xlog-stream, do I have to have wal_shippment to the new_slave? Or the basebackup command would take care of them?
3 - Instead of using pg_basebackup like that, should I dump into local disk(on the slave05 server) and then copy to the new_slave?Using the pg_basebackup with stream should be?ssh postgres@slave05 'pg_basebackup --pgdata=- --format=tar --label=new_slave --xlog-method=stream --progress --host=localhost --port=5432 --username=replicator --xlog' | tar -x --no-same-owner
Cheers
On 07/15/2016 04:16 AM, Patrick B wrote: > Hi guys,/*(I'm sending this email here because it has the same subject, > wal_files)*/ > > I'm setting up a new slave server, using Postgres 9.2. This new slave > server I'll call: New_slave. > > I ran this command, from the new_slave server. It will connects to my > other slave and copy the DB. > > ssh postgres@slave05 'pg_basebackup --pgdata=- --format=tar > --label=new_slave --progress --host=localhost --port=5432 > --username=replicator --xlog' | tar -x --no-same-owner > > > > That took me 10 days, as I'm copying +2TB. Note that I'm not > using *--xlog-method=stream*, but I was copying the wal_files manually > to the new_slave server. By the time the command above (pg_basebackup) > finished, there was 1TB of wal_files. > > I started to recovery the Database, setting the > recovery.conf.recovery_command. > > and then I got this error: > > WAL segment `../wal_archive/000000020000171B000000D9` not found > > > So.. shit.. I don't know what happened, but I checked the master server > and actually that file wasn't shipped into the new_server. I don't know why. > > > > *Questions:* > 1 - Do you have any recommendations? I'll have to perform that again. > > 2 - If I use pg_basebackup with -xlog-stream, do I have to have > wal_shippment to the new_slave? Or the basebackup command would take > care of them? > > *Using the pg_basebackup with stream should be?* > > ssh postgres@slave05 'pg_basebackup --pgdata=- --format=tar > --label=new_slave --xlog-method=stream --progress > --host=localhost --port=5432 --username=replicator --xlog' | tar > -x --no-same-owner > > > 3 - Instead of using pg_basebackup like that, should I dump into local > disk(on the slave05 server) and then copy to the new_slave? That has been suggested: https://www.postgresql.org/message-id/f72572aa-fd8c-87e8-69d8-bfc7e25f05d1%402ndquadrant.com > > Cheers -- Adrian Klaver adrian.klaver@aklaver.com