0

I have Postgresql 14 and I made streaming replication (remote_apply) for 3 nodes.

When the two standby nodes are down, if I tried to do an insert command this will show up:

WARNING:  canceling wait for synchronous replication due to user request
DETAIL:  The transaction has already committed locally, but might not have been replicated to the standby.
INSERT 0 1

I don't want to insert it locally. I want to reject the transaction and show an error instead.

Is it possible to do that?

Mohammed Alali
  • 7
  • 2

3 Answers3

1

No, there is no way to do that with synchronous replication.

Laurenz Albe
  • 209,280
  • 17
  • 206
  • 263
0

I don't think you have thought through the implications of what you want. If it doesn't commit locally first, then what should happen if the master crashes after sending the transaction to the replica, but before getting back word that it was applied there? If it was committed on the replica but rejected on the master, how would they ever get back into sync?

jjanes
  • 37,812
  • 5
  • 27
  • 34
0

I made a script that checks the number of standby nodes and then make the primary node read-only if the standby nodes are down.

Mohammed Alali
  • 7
  • 2
  • As it’s currently written, your answer is unclear. Please [edit] to add additional details that will help others understand how this addresses the question asked. You can find more information on how to write good answers [in the help center](/help/how-to-answer). – Community Jul 15 '22 at 15:29