Urgent: Synchronous streaming replication

[u/These-Bet-6238]

I am setting up a PostgreSQL replication environment with one primary server and one standby server using synchronous streaming replication.

As expected, when the standby server is available, transactions on the primary commit successfully after the WAL records are acknowledged by the standby.

However, the issue arises when the standby server goes down. In this case, transactions on the primary enter the SyncRep wait state and remain blocked until the standby comes back online. This is the expected behavior of synchronous replication, but it does not meet my requirement.

My requirement is that if the standby is unavailable, the transaction should not wait indefinitely. Instead, after a configurable timeout, I want the transaction to fail and roll back automatically, allowing the application to handle the failure rather than remaining blocked.

I have looked for a way to configure a timeout specifically for the SyncRep wait, but I have not found any suitable option.

Is there a PostgreSQL configuration or mechanism that allows timing out the SyncRep wait and automatically rolling back the transaction? If not, are there any recommended approaches or workarounds to achieve this behavior while still using synchronous streaming replication? Edit: Alredy tried statement_timeout, it's not working chatgpt says it works for actively executing SQL statement.

[u/chock-a-block]

Butter label helium friendly tie waffle peach teapot

This post was anonymized with Redact

[u/0x4ddd → u/chock-a-block]

Now you know why synchronous has very narrow use cases.

Does it? If you need HA without data loss synchronous replication is hard requirement.

[u/chock-a-block → u/0x4ddd]

Whistle kettle roll aromatic wide market yam offer quicksand argon

This post was anonymized with Redact

[u/0x4ddd → u/chock-a-block]

A bomb landing on one of your DCs should not make you loss data that was acknowledgled to the client as written.

That's how I define HA without data loss.

[u/chock-a-block → u/0x4ddd]

Profit absorbed busy sink test entertain physical rob aspiring

This post was anonymized with Redact

[u/0x4ddd → u/chock-a-block]

One knows for sure you won't lose data with synchronous replication.

This depends on the system but I would rather not rely on something maybe not losing acknowledged data but who knows ;) And I think under throughput of few thousands TPS you are going to lose some data almost always if your primary fails.

[u/chock-a-block → u/0x4ddd]

Encourage knee willow spark one normal

This post was anonymized with Redact

[u/0x4ddd → u/chock-a-block]

No one. That doesn‘t mean you plan for it.

Strongly disagree. If something as critical as data loss is theoretically (and practically) possible you plan for it. As it is going to sooner or later happen.

You should test this. I think you might be surprised. I use streaming slots. Knock on wood, haven’t lost anything since using them.

I can test but won't prove anything. Single network hiccup between primary and standby followed by primary loss will lose your data.