Обсуждение: synch streaming replication question
Hi All
I have a three node cluster using streaming replication configured as follows:
One synch node and one asynch node.
If my synch node is down the master node is hung until that node is brought back on line.
Two questions:
1. Is this the expected action on my master? It makes sense if it is since the master is waiting for an ack from the synch node.
2. Is there anyway to configure my cluster so I have a synch node but not have my master node “HANG” if the synch node is down?
Regards
John Wiencek
On Tue, Feb 23, 2016 at 6:43 AM, John Wiencek <jwiencek3@comcast.net> wrote: > I have a three node cluster using streaming replication configured as > follows: > One synch node and one asynch node. > If my synch node is down the master node is hung until that node is brought > back on line. > > Two questions: > 1. Is this the expected action on my master? It makes sense if it is since > the master is waiting for an ack from the synch node. Yes, when synchronous_commit = on, the default values that you should have, the master will wait from standby the confirmation that the WAL record for the commit has been flushed; http://www.postgresql.org/docs/devel/static/runtime-config-replication.html#RUNTIME-CONFIG-REPLICATION-MASTER > 2. Is there anyway to configure my cluster so I have a synch node but not > have my master node “HANG” if the synch node is down? You can do that at transaction level for example by disabling synchronous_commit. Regards, -- Michael
On Tue, Feb 23, 2016 at 3:09 PM, Michael Paquier <michael.paquier@gmail.com> wrote: > On Tue, Feb 23, 2016 at 6:43 AM, John Wiencek <jwiencek3@comcast.net> wrote: > >> I have a three node cluster using streaming replication configured as >> follows: >> One synch node and one asynch node. >> If my synch node is down the master node is hung until that node is brought >> back on line. >> >> Two questions: >> 1. Is this the expected action on my master? It makes sense if it is since >> the master is waiting for an ack from the synch node. > > Yes, when synchronous_commit = on, the default values that you should > have, the master will wait from standby the confirmation that the WAL > record for the commit has been flushed; > http://www.postgresql.org/docs/devel/static/runtime-config-replication.html#RUNTIME-CONFIG-REPLICATION-MASTER > >> 2. Is there anyway to configure my cluster so I have a synch node but not >> have my master node “HANG” if the synch node is down? > > You can do that at transaction level for example by disabling > synchronous_commit. Or you could configure both of your standbys as synchronous standbys. Only one of them will actually be a synchronous standby at a time, and the other one will take over that role if the first one is down, so your system won't hang but you'll still have the sync standby guarantee. -- Thomas Munro http://www.enterprisedb.com
How do I configure both standbys as ³synch² nodes? Do I list both nodenames or ip addresesses in the postgresql.conf synchronous_standyby_names entry? My current entery looks like this: synchronous_standby_names = 'keypg2,*' John On 2/22/16, 8:25 PM, "Thomas Munro" <thomas.munro@enterprisedb.com> wrote: >On Tue, Feb 23, 2016 at 3:09 PM, Michael Paquier ><michael.paquier@gmail.com> wrote: >> On Tue, Feb 23, 2016 at 6:43 AM, John Wiencek <jwiencek3@comcast.net> >>wrote: >> >>> I have a three node cluster using streaming replication configured as >>> follows: >>> One synch node and one asynch node. >>> If my synch node is down the master node is hung until that node is >>>brought >>> back on line. >>> >>> Two questions: >>> 1. Is this the expected action on my master? It makes sense if it is >>>since >>> the master is waiting for an ack from the synch node. >> >> Yes, when synchronous_commit = on, the default values that you should >> have, the master will wait from standby the confirmation that the WAL >> record for the commit has been flushed; >> >>http://www.postgresql.org/docs/devel/static/runtime-config-replication.ht >>ml#RUNTIME-CONFIG-REPLICATION-MASTER >> >>> 2. Is there anyway to configure my cluster so I have a synch node but >>>not >>> have my master node ³HANG² if the synch node is down? >> >> You can do that at transaction level for example by disabling >> synchronous_commit. > >Or you could configure both of your standbys as synchronous standbys. >Only one of them will actually be a synchronous standby at a time, and >the other one will take over that role if the first one is down, so >your system won't hang but you'll still have the sync standby >guarantee. > >-- >Thomas Munro >http://www.enterprisedb.com
On Tue, Feb 23, 2016 at 12:02 PM, John Wiencek <jwiencek3@comcast.net> wrote: > How do I configure both standbys as ³synch² nodes? > > Do I list both nodenames or ip addresesses in the postgresql.conf > synchronous_standyby_names entry? > > My current entery looks like this: > > synchronous_standby_names = 'keypg2,*' This is fine. Thanks to '*', all the other standby nodes connected to this primary server will be thought as potential synchronous candidates. And one of them will be. With this configuration keypg2 is chosen first though if it is connected. -- Michael