Postgresql – slave server is down,master server query halts (postgresql)

postgresql

I have my master and slave server in sync.

I have stopped the slave server and tried to perform insert query in master server. The execution is in progress and doesn't give any error message. There is no proper information in logs.

I understand that in synchronous replication master waits for slave to reply but:

  1. Is there anyway to avoid master server being halted?
  2. I have stopped my master server and checked the logs that data is locally committed, is this the behaviour of synchronous replication? commit should not work until all data is committed to master and standby?

Best Answer

In synchronous mode, your primary needs to wait for the result to be acknowledged by the secondary to answer.

From the documentation (links below):

When requesting synchronous replication, each commit of a write transaction will wait until confirmation is received that the commit has been written to the write-ahead log on disk of both the primary and standby server. [...] Waiting for confirmation increases the user's confidence that the changes will not be lost in the event of server crashes but it also necessarily increases the response time for the requesting transaction. The minimum wait time is the round-trip time between primary to standby.

That's why, except for someone who can afford downtime (it happens and it's not so rare), we add other asynchronous secondaries that can become synchronous secondaries if needed.

The best solution for high availability is to ensure you keep as many synchronous standbys as requested. This can be achieved by naming multiple potential synchronous standbys using synchronous_standby_names.

In a priority-based synchronous replication, the standbys whose names appear earlier in the list will be used as synchronous standbys. Standbys listed after these will take over the role of synchronous standby if one of current ones should fail.

In a quorum-based synchronous replication, all the standbys appearing in the list will be used as candidates for synchronous standbys. Even if one of them should fail, the other standbys will keep performing the role of candidates of synchronous standby.

You'll may find useful to read that documentation page : https://www.postgresql.org/docs/current/static/warm-standby.html#SYNCHRONOUS-REPLICATION

Related Question