pg.ddx.io  pgsql-general@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Operating 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