Standby behavior with setting synchronous_commit = remote_apply
Hi there, >From documentation https://www.postgresql.org/docs/10/runtime-config-wal.html standby behavior with setting synchronous_commit = remote_apply is not obvious. I think we should explain this case. Am I correct that master always sync COMMIT, except 'off' and regardless of whether standby is connected or not? But I am not sure, when standby will sync replayed COMMIT? Does it depend on synchronous_commit value in standby config? For example, if synchronous_commit in standbys config is “on”, master synchronous_standby_names is non-empty, are the following details correct? synchronous_commit = on master sync standby sync synchronous_commit = remote_apply master sync standby ??? synchronous_commit = remote_write master sync standby no sync synchronous_commit = local master sync standby no recv synchronous_commit = off master sync standby no recv -- Konstantin Evteev
Using standby for read-only queries in production and DML operations on primary.
Hello! There is a problem connected with using standby for read-only queries in production and DML operations on primary. On primary we use alter table command with statement_timeout and deadlock_timeout values about 10 - 50 ms with rertry until it would successfully be executed. But the same locks would be replayed on standby - without timeouts. So there we see locks and our standby pools are overflowed - it is an incident/lsr/problem for users. There is a workaround to have 2 standbies: Before altering table on primary pause 1st standby and switch read only queries to it. Then alter table and wait until it would be replicated to 2-nd standby. Switch read only queries to 2-nd standby and remove pause from 1-st. -- Konstantin Evteev
