synch streaming replication question

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general