Thread: Operating of synchronous master when no standby is available
Hello!
I use Postgresql+Pacemaker+Corosync cluster with 2 Postgresql instances in synchronous replication mode. When one of the nodes is down, clients hang on INSERT operations because the primary server waits until standby confirms that it got the data, but the standby node is down.
I need clients to feel no difference when they operate with a fully operational cluster or with a degraded cluster containing primary node alone.
Is it possible to configure synchronous replication to operate normally when primary and replica are online, but primary no waits confirmation from replica if replica is not connected?
Best regards,
Sergey Cherukhin
I need clients to feel no difference when they operate with a fully operational cluster or with a degraded cluster containing primary node alone.
Is it possible to configure synchronous replication to operate normally when primary and replica are online, but primary no waits confirmation from replica if replica is not connected?
Best regards,
Sergey Cherukhin
Am 02.10.23 um 10:54 schrieb Sergey Cherukhin: > Hello! > > I use Postgresql+Pacemaker+Corosync cluster with 2 Postgresql > instances in synchronous replication mode. When one of the nodes is > down, clients hang on INSERT operations because the primary server > waits until standby confirms that it got the data, but the standby > node is down. > > I need clients to feel no difference when they operate with a fully > operational cluster or with a degraded cluster containing primary > node alone. > > Is it possible to configure synchronous replication to operate > normally when primary and replica are online, but primary no waits > confirmation from replica if replica is not connected? yes, with 3 or more sync. standbys. Andreas -- Andreas Kretschmer Technical Account Manager (TAM) www.enterprisedb.com
On 10/2/23 03:54, Sergey Cherukhin wrote:
This is why we use async physical replication even with local HA clusters.
Locally, async replication over Gbit (or faster) NICs will probably be Good Enough. (I ran pgbench at loads that saturated all the CPUs and inserted 100GB of data against a pgpool cluster -- pgpool VIP on one node, primary database on the second node and replica on the third node -- and saw no lag.)
Hello!I use Postgresql+Pacemaker+Corosync cluster with 2 Postgresql instances in synchronous replication mode. When one of the nodes is down, clients hang on INSERT operations because the primary server waits until standby confirms that it got the data, but the standby node is down.
I need clients to feel no difference when they operate with a fully operational cluster or with a degraded cluster containing primary node alone.
This is why we use async physical replication even with local HA clusters.
Is it possible to configure synchronous replication to operate normally when primary and replica are online, but primary no waits confirmation from replica if replica is not connected?
Locally, async replication over Gbit (or faster) NICs will probably be Good Enough. (I ran pgbench at loads that saturated all the CPUs and inserted 100GB of data against a pgpool cluster -- pgpool VIP on one node, primary database on the second node and replica on the third node -- and saw no lag.)
--
Born in Arizona, moved to Babylonia.
Born in Arizona, moved to Babylonia.
Pgsql RA has rep_mode=sync parameter.
2023年10月2日(月) 23:48 Sergey Cherukhin <sergey.cherukhin@gmail.com>:
Hello!I use Postgresql+Pacemaker+Corosync cluster with 2 Postgresql instances in synchronous replication mode. When one of the nodes is down, clients hang on INSERT operations because the primary server waits until standby confirms that it got the data, but the standby node is down.
I need clients to feel no difference when they operate with a fully operational cluster or with a degraded cluster containing primary node alone.
Is it possible to configure synchronous replication to operate normally when primary and replica are online, but primary no waits confirmation from replica if replica is not connected?
Best regards,
Sergey Cherukhin
I already have rep_mode set to "sync", but the primary node switches to async mode only if the replica is shutted down correctly. When I shut down the replica by powering it off to emulate power unit failure, primary remains in sync mode until "pcs resource cleanup" is performed. I think it is possible to run resource cleanup in script on cluster alert.
вт, 3 окт. 2023 г. в 05:13, MATSUO Takatoshi <matsuo.tak@gmail.com>:
Pgsql RA has rep_mode=sync parameter.2023年10月2日(月) 23:48 Sergey Cherukhin <sergey.cherukhin@gmail.com>:Hello!I use Postgresql+Pacemaker+Corosync cluster with 2 Postgresql instances in synchronous replication mode. When one of the nodes is down, clients hang on INSERT operations because the primary server waits until standby confirms that it got the data, but the standby node is down.
I need clients to feel no difference when they operate with a fully operational cluster or with a degraded cluster containing primary node alone.
Is it possible to configure synchronous replication to operate normally when primary and replica are online, but primary no waits confirmation from replica if replica is not connected?
Best regards,
Sergey Cherukhin
It's not normal behavior.
Pgsql RA is developed here.
You may get better advice if you ask on this mailing list.
2023年10月3日(火) 18:03 Sergey Cherukhin <sergey.cherukhin@gmail.com>:
I already have rep_mode set to "sync", but the primary node switches to async mode only if the replica is shutted down correctly. When I shut down the replica by powering it off to emulate power unit failure, primary remains in sync mode until "pcs resource cleanup" is performed. I think it is possible to run resource cleanup in script on cluster alert.вт, 3 окт. 2023 г. в 05:13, MATSUO Takatoshi <matsuo.tak@gmail.com>:Pgsql RA has rep_mode=sync parameter.2023年10月2日(月) 23:48 Sergey Cherukhin <sergey.cherukhin@gmail.com>:Hello!I use Postgresql+Pacemaker+Corosync cluster with 2 Postgresql instances in synchronous replication mode. When one of the nodes is down, clients hang on INSERT operations because the primary server waits until standby confirms that it got the data, but the standby node is down.
I need clients to feel no difference when they operate with a fully operational cluster or with a degraded cluster containing primary node alone.
Is it possible to configure synchronous replication to operate normally when primary and replica are online, but primary no waits confirmation from replica if replica is not connected?
Best regards,
Sergey Cherukhin
Thank you for your answer.
вт, 3 окт. 2023 г., 17:22 MATSUO Takatoshi <matsuo.tak@gmail.com>:
It's not normal behavior.Pgsql RA is developed here.You may get better advice if you ask on this mailing list.2023年10月3日(火) 18:03 Sergey Cherukhin <sergey.cherukhin@gmail.com>:I already have rep_mode set to "sync", but the primary node switches to async mode only if the replica is shutted down correctly. When I shut down the replica by powering it off to emulate power unit failure, primary remains in sync mode until "pcs resource cleanup" is performed. I think it is possible to run resource cleanup in script on cluster alert.вт, 3 окт. 2023 г. в 05:13, MATSUO Takatoshi <matsuo.tak@gmail.com>:Pgsql RA has rep_mode=sync parameter.2023年10月2日(月) 23:48 Sergey Cherukhin <sergey.cherukhin@gmail.com>:Hello!I use Postgresql+Pacemaker+Corosync cluster with 2 Postgresql instances in synchronous replication mode. When one of the nodes is down, clients hang on INSERT operations because the primary server waits until standby confirms that it got the data, but the standby node is down.
I need clients to feel no difference when they operate with a fully operational cluster or with a degraded cluster containing primary node alone.
Is it possible to configure synchronous replication to operate normally when primary and replica are online, but primary no waits confirmation from replica if replica is not connected?
Best regards,
Sergey Cherukhin