PostgreSQL Streaming Replication Reject Insert

Question

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?

Answer 1

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

Answer 2

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?

Answer 3

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.