Postgres synchronous replication?

Good afternoon.
In PostgreSQL is synchronous replication via the setting of synchronous_commit = on (https://postgrespro.ru/docs/postgrespro/12/runtime... on the master.
Raised 1 master node and 2 slave. Set up replication postgres correctly says that both slave can work via synchronous replication.
To check wrote a script which does the insert into master and then extracts the data from the two slaves.
For the test insert 1000 records in a row. When you search for them in the slave sometimes not. For example finds 998 out of 1000 is inserted. If you add a small delay, you can find all of the records consistently.
When synchronous_commit = on, the master must wait for confirmation that the data saved to disk from each synchronous slave and only then to respond to the client on successful implementation.
Can anyone faced similar problem?

The task is to put the master node to write, but all read requests must handle synchronous replicas, but the problem is not found by the records casts doubt on a bundle.

For the dough raised in containers and ask questions on github. Perhaps a more detailed description of the problem will be there https://github.com/bitnami/bitnami-docker-postgres...
April 7th 20 at 11:56
1 answer
April 7th 20 at 11:58
Solution
Point the first: what synchronous_standby_names?
Point the second:
replication via the setting of synchronous_commit = on

https://www.postgresql.org/docs/current/runtime-co...
When set to on, commits will wait until replies from the current synchronous standby(s) indicate they have received the commit record of the transaction and flushed it to disk.

When set to remote_apply, commits will wait until replies from the current synchronous standby(s) indicate they have received the commit record of the transaction and applied it, so that it has become visible to queries on the standby(s).

Feel the difference?

master node for write, but all read requests must handle synchronous replica

A very unusual workload. Never met.
I'm curious, what are you doing, you bad latency recording, you have a heavy writing load (not to be confused with the amount of writing load - replicas that all will have to write the same, but still reading), a lot extremely critical to lag asynchronous replication read.
Thank you very much for the reply.
remote_apply helped in solving the problem. I by inexperience thought that after saving to the hard disk all transactions that are already applied (they are in RAM)

I want to try to raise 1 master and two synchronous replication. To balance the load on the master + 2 replicas to read, but since the program can go on the record and then immediately read the data replica to the moment of reading must be synchronized with the master, to avoid possible errors in queries.

The question is a little off topic. Can you recommend a good balancer, which can be further customized scripts, routing rules query? In our project, currently a lot of stored procedures to abandon them, we can't right now. Therefore, the rules must specify that a stored procedure call will always send to master, because there are often procedures change data. - Eric_Denesik commented on April 7th 20 at 12:01
I by inexperience thought that after saving to the hard disk all transactions that are already applied (they are in RAM)

Here's the thing - preserving what.
Receiving a stream of changes is a single process, the walreceiver. WAL, it receives primary, writes to disk (synchronous_commit = remote_write), pulls fsync to ensure that in the event of failure of this transaction is already stored locally (on)
applying changes to the database - is done completely separately and asynchronously from data retrieval - the process startup (remote_apply)
And they both have only an indirect relationship to record the applied changes directly in the database to the disk - this involved separate processes bgwriter and checkpointer (well, OK, if you have time - if no time then the startup will have to write datafile)

Can you recommend a good balancer, which can be further customized scripts, routing rules query?

select * from something;
This request reading or writing? Yes unknown! That gives you a reason to decide that something is a table and not, for example, view over the writing hranilki?
Who knows for sure? The application developer. Therefore the most correct place to divide the query server is the application itself.
Sharing requests must be in the following groups:
- read and record critical lag
- read oltp queries fast, little lag replicas
reading olap is not critical to the lag. For long queries, the replica can lag noticeably

Balancing the sets of equivalent servers, for example, haproxy. But the choice of which server group to send task application.

Read with a synchronous replica of the... Well, try. There is great concern that well this will not work. Synchronous replicas they are primarily about the "not lose a transaction" than about the replication lag.

Fanatically to remove all the reading from the master just because it's reading and it's a master - useless worries, will heroically solve a nonexistent problem. (yeah, I know, it is in the tradition of IT looking for problems where they never had, and heroically resolved) When the team is just starting to stretch in one server - it is clear that the application is designed to work on a single DBMS server. But very good results can be achieved by removing the replica just a few queries point. Turn pg_stat_satements, the next day you see what you have in the top of the load and will see what can easily carry on a normal asynchronous replica. Can be customized for a small gap, and can - separately for hard queries is not critical to the lag at all. - luciano_Kunze commented on April 7th 20 at 12:04
@alva_Adams, thanks for the reply. Almost all the stages of description You have are in the plan. Metrics currently being assembled for analysis.
But infrastructure does the client, so I want to try the easiest option of scaling the system.
At the moment it works on your hardware 64 cores on the sql server. The base is about 1.7 TB. Planning care in the "cloud" (the choice fell on Russia because PD is stored is important). And Rostelecom according to client's maximum will give machines 16 cores. 80 percent of queries that read the number. Traffic 95%+ reading.
Server 64 core downloaded 20-30 percent most of the time.

About views, etc. we have no problems. The main problem is the legacy stored procedures (about 1700 at the moment) do not plan to refactor them, would be a serious alteration of the application server associated with the removal of almost all granimar. Therefore, the increase in recording time is likely to be affected significantly, and the smearing of reading can help pass the limitations of Rostelecom. - Eric_Denesik commented on April 7th 20 at 12:07
@Rossie,
I want to run the easiest option to scale the system.

But look just at the most difficult.

Rostelecom, hmm. And selectel any what? Good iron under the base they have. - luciano_Kunze commented on April 7th 20 at 12:10
@alva_Adams, the customer requirements from the FSB and in addition they refuse Rostelecom someone to consider.

For development costs the easiest. Rewriting code is not required.

The administration, in principle, is not a problem to lift a replica. load balancer 1 time set it and forget it (if you get any effect from such schemes work). - Eric_Denesik commented on April 7th 20 at 12:13
Simple, I don't believe so. If it was easy. - luciano_Kunze commented on April 7th 20 at 12:16

Find more questions by tags PostgreSQL