Re: Avoiding data loss with synchronous replication

> 25 июля 2021 г., в 05:29, Andres Freund <andres(at)anarazel(dot)de> написал(а):
>
> Hi,
>
> On 2021-07-24 15:53:15 +0500, Andrey Borodin wrote:
>> Are there any other problems with blocking cancels?
>
> Unless you have commandline access to the server, it's not hard to get
> into a situation where you can't change the configuration setting
> because all connections are hanging, and you can't even log in to do an
> ALTER SERVER etc. You can't kill applications to kill the connection,
> because they will just continue to hang.

Hmm, yes, it's not hard to get to this situation. Intentionally. But what would be setup to get into such troubles? Setting sync rep, but not configuring HA tool?

In normal circumstances HA cluster is not configured to allow this. Normally hanging commits are part of the failover. Somewhere new primary server is operating. You have to find commandline access to the server to execute pg_rewind, and join this node to cluster again as a standby.

Anyway it's a good idea to set up superuser_reserved_connections for administrative intervention [0].

I like the idea of transferring transaction locks somewhere until synchronous_commit requirements are satisfied. It makes us closer to making this locks durable to survive restart. But, IMO, the complexity and potentially dangerous conditions outweigh the benefits of this approach easily.

Thanks!

Best regards, Andrey Borodin.

[0]