0

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?
0

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

  • Apart from this solution is there any other alternate way ,i have seen some resources stating to set some wal_sender_timeout or use pg_receivewal.Am not sure how it works .Have u tried ? or whatever u have mentioned above is only solution ? – vyshu Ysh Sep 19 '18 at 7:16
  • wal_sender_timeout and pg_receivewal are about aynshronous replication. Maybe you don't really need synchronous replication. The question you need to answer is "What lag between primary and secondary can you afford" ? 1µs ? 1ms ? 1s ? – Arkhena Sep 20 '18 at 9:53

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.