@sgotti or who can help.
Our configuration consists of multiple servers, each server being its own single server “cluster”. One server is primary (server1) in master mode and the other servers (server 2 and server 3) are in standby mode.
The postgres client jdbc url is written as multi-server style:
Jdbc url is postgresql://server1:5432,server2:5432,server3:5432
(server1 is writable, server2 and server3 are read only databases)
We have a process that monitors the database on server1. When server1 becomes unavailable, server2 and server3 promote themselves to master. When server1 comes back in service, server2 and server3 revert to standby and ship any database updates back to server1.
The process that monitors server1 runs at set intervals (10 seconds). We generally don’t do it every second because a fluttering server1 database (going up and down in a very short time period) stacks up the shipment of updates back to server1 and can cause resource problems as those efforts stack.
The issue is that during the time period between when the primary is offline and when the monitoring process changes the standby servers to primary, the standby servers are in read-only mode. Any JDBC calls to the primary determines it is offline so it goes to the next one in the list. If it tries to write to a standby server (insert, update, or delete), it fails, causing issues for the applications using the database.
We are looking for a way to handle this scenario in the best way to avoid those read-only moments or at least minimize them as much as possible.