Обсуждение: Best approach for multi-database system
Hi! Using async replication between geographically database servers. I've some 9.3 and some 9.4 servers, so doesn't matter the version (the only reason I've not migrated all to 9.4 yet is because I'm short on time). I've experienced some synchronization failures between Master-Slave servers (for example, if connection is lost for 4 to 6 hours, the number of wall keep segments may not suffice). I've created some bash scripts that will reacreate the master-slave using rsync, which seems to be farily easy - but really slow. Depending on server size (130Gb for example) take really long on a 5Mbps link - in some cases, all night long. This is the first approach: all databases in one server, and if I loose the sync between master and slave, all data need to be transfered between server and slave and is really slow. The other approach I've tested is to split each database in one cluster. By using the second approach, I can resynch each server separeted (for example 15Gb instead 130Gb). This approach have the problema that it is needed to configure and maintain another cluster for each database. Is there a best practice for multi-database systems with async replication to avoid such "resync" problems? Thanks for your orientation, -- Atenciosamente, Edson Carlos Ericksson Richter
On 3/10/2016 1:41 PM, Edson Richter wrote: > Hi! > > Using async replication between geographically database servers. I've > some 9.3 and some 9.4 servers, so doesn't matter the version (the only > reason I've not migrated all to 9.4 yet is because I'm short on time). > > I've experienced some synchronization failures between Master-Slave > servers (for example, if connection is lost for 4 to 6 hours, the number > of wall keep segments may not suffice). > I've created some bash scripts that will reacreate the master-slave > using rsync, which seems to be farily easy - but really slow. > Depending on server size (130Gb for example) take really long on a 5Mbps > link - in some cases, all night long. > > This is the first approach: all databases in one server, and if I loose > the sync between master and slave, all data need to be transfered > between server and slave and is really slow. > > The other approach I've tested is to split each database in one cluster. > By using the second approach, I can resynch each server separeted (for > example 15Gb instead 130Gb). > This approach have the problema that it is needed to configure and > maintain another cluster for each database. > > Is there a best practice for multi-database systems with async > replication to avoid such "resync" problems? > > > Thanks for your orientation, > I wrote about my setup here: http://www.postgresql.org/message-id/548F3954.8040501@squeakycode.net Would be happy to answer questions. -Andy
Interesting. This logicel (only remove WAL files after been applied) should be available in PostgreSQL by default. I mean, to have an option wheter every 15 minutes a master server query slave for completeness, or slave notify master last successfully imported WAL. Atenciosamente, Edson Carlos Ericksson Richter Em 10/03/2016 16:56, Andy Colson escreveu: > On 3/10/2016 1:41 PM, Edson Richter wrote: >> Hi! >> >> Using async replication between geographically database servers. I've >> some 9.3 and some 9.4 servers, so doesn't matter the version (the only >> reason I've not migrated all to 9.4 yet is because I'm short on time). >> >> I've experienced some synchronization failures between Master-Slave >> servers (for example, if connection is lost for 4 to 6 hours, the number >> of wall keep segments may not suffice). >> I've created some bash scripts that will reacreate the master-slave >> using rsync, which seems to be farily easy - but really slow. >> Depending on server size (130Gb for example) take really long on a 5Mbps >> link - in some cases, all night long. >> >> This is the first approach: all databases in one server, and if I loose >> the sync between master and slave, all data need to be transfered >> between server and slave and is really slow. >> >> The other approach I've tested is to split each database in one cluster. >> By using the second approach, I can resynch each server separeted (for >> example 15Gb instead 130Gb). >> This approach have the problema that it is needed to configure and >> maintain another cluster for each database. >> >> Is there a best practice for multi-database systems with async >> replication to avoid such "resync" problems? >> >> >> Thanks for your orientation, >> > > I wrote about my setup here: > > http://www.postgresql.org/message-id/548F3954.8040501@squeakycode.net > > Would be happy to answer questions. > > -Andy > > >
please don't top post. > Em 10/03/2016 16:56, Andy Colson escreveu: >> On 3/10/2016 1:41 PM, Edson Richter wrote: >>> Hi! >>> >>> Using async replication between geographically database servers. I've >>> some 9.3 and some 9.4 servers, so doesn't matter the version (the only >>> reason I've not migrated all to 9.4 yet is because I'm short on time). >>> >>> I've experienced some synchronization failures between Master-Slave >>> servers (for example, if connection is lost for 4 to 6 hours, the number >>> of wall keep segments may not suffice). >>> I've created some bash scripts that will reacreate the master-slave >>> using rsync, which seems to be farily easy - but really slow. >>> Depending on server size (130Gb for example) take really long on a 5Mbps >>> link - in some cases, all night long. >>> >>> This is the first approach: all databases in one server, and if I loose >>> the sync between master and slave, all data need to be transfered >>> between server and slave and is really slow. >>> >>> The other approach I've tested is to split each database in one cluster. >>> By using the second approach, I can resynch each server separeted (for >>> example 15Gb instead 130Gb). >>> This approach have the problema that it is needed to configure and >>> maintain another cluster for each database. >>> >>> Is there a best practice for multi-database systems with async >>> replication to avoid such "resync" problems? >>> >>> >>> Thanks for your orientation, >>> >> >> I wrote about my setup here: >> >> http://www.postgresql.org/message-id/548F3954.8040501@squeakycode.net >> >> Would be happy to answer questions. >> >> -Andy >> >> >> > > > On 3/10/2016 2:14 PM, Edson Richter wrote: > Interesting. This logicel (only remove WAL files after been applied) > should be available in PostgreSQL by default. > I mean, to have an option wheter every 15 minutes a master server query > slave for completeness, or slave notify master last successfully > imported WAL. > > > Atenciosamente, > > Edson Carlos Ericksson Richter > It is. Starting in 9.4 you can use replication slots. -Andy
Em 10/03/2016 17:18, Andy Colson escreveu: > please don't top post. > > Em 10/03/2016 16:56, Andy Colson escreveu: >>> On 3/10/2016 1:41 PM, Edson Richter wrote: >>>> Hi! >>>> >>>> Using async replication between geographically database servers. I've >>>> some 9.3 and some 9.4 servers, so doesn't matter the version (the only >>>> reason I've not migrated all to 9.4 yet is because I'm short on time). >>>> >>>> I've experienced some synchronization failures between Master-Slave >>>> servers (for example, if connection is lost for 4 to 6 hours, the >>>> number >>>> of wall keep segments may not suffice). >>>> I've created some bash scripts that will reacreate the master-slave >>>> using rsync, which seems to be farily easy - but really slow. >>>> Depending on server size (130Gb for example) take really long on a >>>> 5Mbps >>>> link - in some cases, all night long. >>>> >>>> This is the first approach: all databases in one server, and if I >>>> loose >>>> the sync between master and slave, all data need to be transfered >>>> between server and slave and is really slow. >>>> >>>> The other approach I've tested is to split each database in one >>>> cluster. >>>> By using the second approach, I can resynch each server separeted (for >>>> example 15Gb instead 130Gb). >>>> This approach have the problema that it is needed to configure and >>>> maintain another cluster for each database. >>>> >>>> Is there a best practice for multi-database systems with async >>>> replication to avoid such "resync" problems? >>>> >>>> >>>> Thanks for your orientation, >>>> >>> >>> I wrote about my setup here: >>> >>> http://www.postgresql.org/message-id/548F3954.8040501@squeakycode.net >>> >>> Would be happy to answer questions. >>> >>> -Andy >>> >>> >>> >> >> >> > > On 3/10/2016 2:14 PM, Edson Richter wrote: > > Interesting. This logicel (only remove WAL files after been applied) > > should be available in PostgreSQL by default. > > I mean, to have an option wheter every 15 minutes a master server query > > slave for completeness, or slave notify master last successfully > > imported WAL. > > > > > > Atenciosamente, > > > > Edson Carlos Ericksson Richter > > > > > It is. Starting in 9.4 you can use replication slots. > > -Andy > > Sorry for toppost. This is the only list I've this rule, and sometimes I just forget... I'll learn about replication slots. Thanks for the tip. Regards, Edson Richter