pg.ddx.io pgsql-general@postgresql.org mailing list archive
help / color / mirror / Atom feedOperating of synchronous master when no standby is available
7+ messages / 4 participants
[nested] [flat]
* Operating of synchronous master when no standby is available
@ 2023-10-02 08:54 Sergey Cherukhin <sergey.cherukhin@gmail.com>
2023-10-02 11:15 ` Re: Operating of synchronous master when no standby is available Andreas Kretschmer <andreas@a-kretschmer.de>
2023-10-02 14:59 ` Re: Operating of synchronous master when no standby is available Ron <ronljohnsonjr@gmail.com>
2023-10-02 22:13 ` Re: Operating of synchronous master when no standby is available MATSUO Takatoshi <matsuo.tak@gmail.com>
0 siblings, 3 replies; 7+ messages in thread
From: Sergey Cherukhin @ 2023-10-02 08:54 UTC (permalink / raw)
To: pgsql-general@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Operating of synchronous master when no standby is available
2023-10-02 08:54 Operating of synchronous master when no standby is available Sergey Cherukhin <sergey.cherukhin@gmail.com>
@ 2023-10-02 11:15 ` Andreas Kretschmer <andreas@a-kretschmer.de>
2 siblings, 0 replies; 7+ messages in thread
From: Andreas Kretschmer @ 2023-10-02 11:15 UTC (permalink / raw)
To: pgsql-general@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Operating of synchronous master when no standby is available
2023-10-02 08:54 Operating of synchronous master when no standby is available Sergey Cherukhin <sergey.cherukhin@gmail.com>
@ 2023-10-02 14:59 ` Ron <ronljohnsonjr@gmail.com>
2 siblings, 0 replies; 7+ messages in thread
From: Ron @ 2023-10-02 14:59 UTC (permalink / raw)
To: pgsql-general@lists.postgresql.org
On 10/2/23 03:54, Sergey Cherukhin wrote:
> 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.
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Operating of synchronous master when no standby is available
2023-10-02 08:54 Operating of synchronous master when no standby is available Sergey Cherukhin <sergey.cherukhin@gmail.com>
@ 2023-10-02 22:13 ` MATSUO Takatoshi <matsuo.tak@gmail.com>
2023-10-03 08:51 ` Re: Operating of synchronous master when no standby is available Sergey Cherukhin <sergey.cherukhin@gmail.com>
2 siblings, 1 reply; 7+ messages in thread
From: MATSUO Takatoshi @ 2023-10-02 22:13 UTC (permalink / raw)
To: Sergey Cherukhin <sergey.cherukhin@gmail.com>; +Cc: pgsql-general@lists.postgresql.org
Pgsql RA has rep_mode=sync parameter.
https://wiki.clusterlabs.org/wiki/PgSQL_Replicated_Cluster
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
>
>
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Operating of synchronous master when no standby is available
2023-10-02 08:54 Operating of synchronous master when no standby is available Sergey Cherukhin <sergey.cherukhin@gmail.com>
2023-10-02 22:13 ` Re: Operating of synchronous master when no standby is available MATSUO Takatoshi <matsuo.tak@gmail.com>
@ 2023-10-03 08:51 ` Sergey Cherukhin <sergey.cherukhin@gmail.com>
2023-10-03 10:22 ` Re: Operating of synchronous master when no standby is available MATSUO Takatoshi <matsuo.tak@gmail.com>
0 siblings, 1 reply; 7+ messages in thread
From: Sergey Cherukhin @ 2023-10-03 08:51 UTC (permalink / raw)
To: pgsql-general@lists.postgresql.org
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.
> https://wiki.clusterlabs.org/wiki/PgSQL_Replicated_Cluster
>
> 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
>>
>>
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Operating of synchronous master when no standby is available
2023-10-02 08:54 Operating of synchronous master when no standby is available Sergey Cherukhin <sergey.cherukhin@gmail.com>
2023-10-02 22:13 ` Re: Operating of synchronous master when no standby is available MATSUO Takatoshi <matsuo.tak@gmail.com>
2023-10-03 08:51 ` Re: Operating of synchronous master when no standby is available Sergey Cherukhin <sergey.cherukhin@gmail.com>
@ 2023-10-03 10:22 ` MATSUO Takatoshi <matsuo.tak@gmail.com>
2023-10-03 12:40 ` Re: Operating of synchronous master when no standby is available Sergey Cherukhin <sergey.cherukhin@gmail.com>
0 siblings, 1 reply; 7+ messages in thread
From: MATSUO Takatoshi @ 2023-10-03 10:22 UTC (permalink / raw)
To: Sergey Cherukhin <sergey.cherukhin@gmail.com>; +Cc: pgsql-general@lists.postgresql.org
It's not normal behavior.
Pgsql RA is developed here.
https://github.com/ClusterLabs/resource-agents/blob/main/heartbeat/pgsql
You may get better advice if you ask on this mailing list.
https://oss.clusterlabs.org/mailman/listinfo/users
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.
>> https://wiki.clusterlabs.org/wiki/PgSQL_Replicated_Cluster
>>
>> 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
>>>
>>>
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Operating of synchronous master when no standby is available
2023-10-02 08:54 Operating of synchronous master when no standby is available Sergey Cherukhin <sergey.cherukhin@gmail.com>
2023-10-02 22:13 ` Re: Operating of synchronous master when no standby is available MATSUO Takatoshi <matsuo.tak@gmail.com>
2023-10-03 08:51 ` Re: Operating of synchronous master when no standby is available Sergey Cherukhin <sergey.cherukhin@gmail.com>
2023-10-03 10:22 ` Re: Operating of synchronous master when no standby is available MATSUO Takatoshi <matsuo.tak@gmail.com>
@ 2023-10-03 12:40 ` Sergey Cherukhin <sergey.cherukhin@gmail.com>
0 siblings, 0 replies; 7+ messages in thread
From: Sergey Cherukhin @ 2023-10-03 12:40 UTC (permalink / raw)
To: MATSUO Takatoshi <matsuo.tak@gmail.com>; +Cc: pgsql-general@lists.postgresql.org
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.
> https://github.com/ClusterLabs/resource-agents/blob/main/heartbeat/pgsql
> You may get better advice if you ask on this mailing list.
> https://oss.clusterlabs.org/mailman/listinfo/users
>
>
> 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.
>>> https://wiki.clusterlabs.org/wiki/PgSQL_Replicated_Cluster
>>>
>>> 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
>>>>
>>>>
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2023-10-03 12:40 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2023-10-02 08:54 Operating of synchronous master when no standby is available Sergey Cherukhin <sergey.cherukhin@gmail.com>
2023-10-02 11:15 ` Andreas Kretschmer <andreas@a-kretschmer.de>
2023-10-02 14:59 ` Ron <ronljohnsonjr@gmail.com>
2023-10-02 22:13 ` MATSUO Takatoshi <matsuo.tak@gmail.com>
2023-10-03 08:51 ` Sergey Cherukhin <sergey.cherukhin@gmail.com>
2023-10-03 10:22 ` MATSUO Takatoshi <matsuo.tak@gmail.com>
2023-10-03 12:40 ` Sergey Cherukhin <sergey.cherukhin@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox