pon., 24 paź 2022, 06:08 użytkownik Laurenz Albe <laurenz.a...@cybertec.at> napisał:
> On Sat, 2022-10-22 at 17:28 +0200, Joseph Kennedy wrote: > > Hi,I have a question. Its stays at the intersection of software > engineering and PostgreSQL. > > > > I have configured streaming synchronous replication and whit setting > "synchronous_commit=remote_apply" > > to make sure that the slave will always respond the same as the MASTER > (this is a developers' > > requirement that the MASTER always responds the same as SLAVE). I set > "hot_standby_feedback=on" > > and "max_standby_streaming_delay=-1", > > max_standby_streaming_delay set to -1 to make MASTER wait indefinitely > before SELECT conflicts on the SLAVE will end. > > > > Here's where the problem arises, because not long after the replication > has been started some serious > > delays occur in the form of "replay_lag" - which rather indicates the > appearance of conflicts; > > the replication stops working properly. > > > > From the server logs it appears that UPDATE (select for update) has > occurred on the MASTER, and SELECT > > queries are in progress on SLAVE causing replication conflicts, with > setting "max_standby_streaming_delay=-1" > > they never ends and there are huge lags. > > > > From the findings with the developers it emerged that they do not want > me to set max_standby_streaming_delay > > to a value after which the queries conflicted with replication will be > canceled. > > * So I'm wondering if, in this configuration, it can work properly at > all without setting, for example, > > "max_standby_streaming_delay=30" ? > > * On the other hand I wonder if the application should not be developed > in such a way to support replication > > of PostgreSQL configured as a streaming synchronous replication > cluster with "synchronous_commit=remote_apply" ? > > * Or perhaps "synchronous streaming replication" is a bad choice, maybe > logical replication would be better ? > > * What are the best practices? > > * Perhaps you just need to force/teach applications to work with > synchronous replication in such a way that > > when the SELECT causes conflicts with replication such queries are > canceled and the application should resend/repeat query ? > > * I also think that after setting, for example, > "max_standby_streaming_delay=30" queries (addressed) to the > > database should be very well optimized, so that too long queries are > not canceled too frequently? > > * Do you know any books focused on applications adapted to work in > postgresql synchronous streaming > > replication environment i.e. High Availability? > > This can never work properly. If you have synchronous replication with > "synchronous_commit = remote_apply", > COMMIT on the primary will wait until the information has been replayed on > the standby. If you set > "max_standby_streaming_delay = -1", replication can be delayed > indefinitely long in the event of a replication > conflict, so COMMIT can take arbitrarily long. > > You can reduce replication conflicts (by setting "hot_standby_feedback = > on" and by altering all tables to > set "vacuum_truncate = off"), but you will never get rid of them > completely. > > You will either have to accept stale ready on the standby (by setting > "synchronous_commit" to something lower) > or you have to accept canceled queries on the standby (by lowering > "max_standby_streaming_delay"). > > Yours, > Laurenz Albe Bloated tables are a serious matter then, after setting vacuum_truncate=off auto vacuum will be turned off , then how to reduce size of tables ? Use pg_repack to reduce locks ?
