0

For some reason synchronous replication does not work (everything works asynchronously)

Master settings:
wal_level = replica
fsync = on
synchronous_commit = on
max_wal_senders = 10
wal_keep_segments = 512
synchronous_standby_names = 'ANY 1 (post122, post124)'

Slave 1 settings:
wal_level = replica
fsync = on
synchronous_commit = on
max_wal_senders = 10
wal_keep_segments = 512
primary_conninfo = 'host=10.128.241.123 port=5432 user=replica application_name=app124'
primary_slot_name = 'slot124'
hot_standby = on

Slave 2 settings:
wal_level = replica
fsync = on
synchronous_commit = on
max_wal_senders = 10
wal_keep_segments = 512
primary_conninfo = 'host=10.128.241.123 port=5432 user=replica application_name=app122'
primary_slot_name = 'slot122'
hot_standby = on

If execute sql statement

SELECT pg_wal_lsn_diff(pg_current_wal_lsn(), s.sent_lsn) AS send_byte_lag, pg_wal_lsn_diff(s.sent_lsn,s.replay_lsn) AS replay_byte_lag, * FROM pg_stat_replication s;

you can see that sync_state = async for all nodes.

+-----------------+-------------------+-------+------------+--------------------+----------------------+------------------+-------------------+---------------+--------------------------------+----------------+-------------+----------------+----------------+----------------+----------------+-------------------+-------------------+--------------------+-----------------+--------------+--------------------------------+
| "SEND_BYTE_LAG" | "REPLAY_BYTE_LAG" | "PID" | "USESYSID" |     "USENAME"      |  "APPLICATION_NAME"  |  "CLIENT_ADDR"   | "CLIENT_HOSTNAME" | "CLIENT_PORT" |        "BACKEND_START"         | "BACKEND_XMIN" |   "STATE"   |   "SENT_LSN"   |  "WRITE_LSN"   |  "FLUSH_LSN"   |  "REPLAY_LSN"  |    "WRITE_LAG"    |    "FLUSH_LAG"    |    "REPLAY_LAG"    | "SYNC_PRIORITY" | "SYNC_STATE" |          "REPLY_TIME"          |
+-----------------+-------------------+-------+------------+--------------------+----------------------+------------------+-------------------+---------------+--------------------------------+----------------+-------------+----------------+----------------+----------------+----------------+-------------------+-------------------+--------------------+-----------------+--------------+--------------------------------+
|                 |                   |       |            |                    |                      |                  |                   |               |                                |                |             |                |                |                |                |                   |                   |                    |                 |              |                                |
| "0"             | "0"               | 14914 | 69296      | "replica"          | "walreceiver"        | "10.128.241.122" | NULL              | 60010         | "2020-07-27 11:49:35.202751+03 | NULL           | "streaming" | "BA5/D20002D0" | "BA5/D20002D0" | "BA5/D20002D0" | "BA5/D20002D0" | NULL              | NULL              | NULL               | 0               | "async"      | "2020-08-11 11:05:22.398199+03 |
|                 |                   |       |            |                    |                      |                  |                   |               |                                |                |             |                |                |                |                |                   |                   |                    |                 |              |                                |
| "0"             | "0"               | 14913 | 69296      | "replica"          | "walreceiver"        | "10.128.241.124" | NULL              | 47916         | "2020-07-27 11:49:35.202262+03 | NULL           | "streaming" | "BA5/D20002D0" | "BA5/D20002D0" | "BA5/D20002D0" | "BA5/D20002D0" | NULL              | NULL              | NULL               | 0               | "async"      | "2020-08-11 11:05:21.838096+03 |
|                 |                   |       |            |                    |                      |                  |                   |               |                                |                |             |                |                |                |                |                   |                   |                    |                 |              |                                |
| "0"             | NULL              | 14952 | 85885      | "streaming_barman" | "barman_receive_wal" | "10.128.241.223" | NULL              | 53304         | "2020-07-27 11:50:01.795733+03 | NULL           | "streaming" | "BA5/D20002D0" | "BA5/D20002D0" | "BA5/D2000000" | NULL           | "00:00:07.658259" | "65:31:39.378476" | "359:15:13.971633" | 0               | "async"      | "2020-08-11 11:05:15.781336+03 |
+-----------------+-------------------+-------+------------+--------------------+----------------------+------------------+-------------------+---------------+--------------------------------+----------------+-------------+----------------+----------------+----------------+----------------+-------------------+-------------------+--------------------+-----------------+--------------+--------------------------------+

And such a moment is also suspicious, in the slave settings application_name = app124 is specified and in select application_name = walreceiver as if application_name is not recognized.

What can be done to make synchronous replication work?

Improve this question
7
  • You want synchronous_commit = remote_apply in the configuration of the primary postgresql.org/docs/current/…
    – user1822
    Aug 11, 2020 at 9:40
  • I don't understand. It's question? I want synchronous replication. The documentation indicates several levels of synchrony and according to my logic, synchronous replication should work with any value of synchronous_commit. Just synchronous_commit = remote_apply should be faster than ON. Isn't it?
    – MinistrBob
    Aug 11, 2020 at 9:54
  • "I want synchronous replication." - then you have to use remote_apply or remote_write as described in the link from my comment and description in the chapter Synchronous Replication
    – user1822
    Aug 11, 2020 at 9:59
  • I'll try it now on the server, but I still don't understand why it doesn't work with ON. Because the documentation, just below "26.2.8.1. Basic Configuration" says that "synchronous_commit must also be set to on, but since this is the default value, typically no change is required."
    – MinistrBob
    Aug 11, 2020 at 10:15
  • @a_horse_with_no_name synchronous_commit = on is enough for synchronous replication.
    – Laurenz Albe
    Aug 11, 2020 at 10:49

2 Answers 2

Reset to default
0

That seems to be a typo.

You used application_name=app124 and application_name=app122 in primary_conninfo on the standbys, but synchronous_standby_names = 'ANY 1 (post122, post124)'.

Improve this answer
0

I done it :)

  1. There was a typo. Thank @LaurenzAlbe. Synchronous_standby_names on master used application_name (post122, post124) and slave used primary_conninfo (app122, app124). Made on master synchronous_standby_names = 'FIRST 1 (app124, app122, barman_receive_wal)'

But it didn't help because

  1. Postgresql.auto.conf had another primary_conninfo on both slaves. Removed through ALTER SYSTEM RESET ALL

Synchronous replication is working.

Now such settings (synchronous_commit decided to set to remote_write)

Master:
wal_level = replica
fsync = on
synchronous_commit = remote_write
max_wal_senders = 10
wal_keep_segments = 512
synchronous_standby_names = 'FIRST 1 (app124, app122, barman_receive_wal)'

Slave 1:
wal_level = replica
fsync = on
synchronous_commit = remote_write
max_wal_senders = 10
wal_keep_segments = 512
primary_conninfo = 'host=10.128.241.123 port=5432 user=replica application_name=app124'
primary_slot_name = 'slot124'
hot_standby = on

Slave 2:
wal_level = replica
fsync = on
synchronous_commit = remote_write
max_wal_senders = 10
wal_keep_segments = 512
primary_conninfo = 'host=10.128.241.123 port=5432 user=replica application_name=app122'
primary_slot_name = 'slot122'
hot_standby = on

Thank to all!

+-----------------+-------------------+-------+------------+--------------------+----------------------+------------------+-------------------+---------------+--------------------------------+----------------+-------------+----------------+----------------+----------------+----------------+------------------+------------------+-------------------+-----------------+--------------+--------------------------------+
| "SEND_BYTE_LAG" | "REPLAY_BYTE_LAG" | "PID" | "USESYSID" |     "USENAME"      |  "APPLICATION_NAME"  |  "CLIENT_ADDR"   | "CLIENT_HOSTNAME" | "CLIENT_PORT" |        "BACKEND_START"         | "BACKEND_XMIN" |   "STATE"   |   "SENT_LSN"   |  "WRITE_LSN"   |  "FLUSH_LSN"   |  "REPLAY_LSN"  |   "WRITE_LAG"    |   "FLUSH_LAG"    |   "REPLAY_LAG"    | "SYNC_PRIORITY" | "SYNC_STATE" |          "REPLY_TIME"          |
+-----------------+-------------------+-------+------------+--------------------+----------------------+------------------+-------------------+---------------+--------------------------------+----------------+-------------+----------------+----------------+----------------+----------------+------------------+------------------+-------------------+-----------------+--------------+--------------------------------+
|                 |                   |       |            |                    |                      |                  |                   |               |                                |                |             |                |                |                |                |                  |                  |                   |                 |              |                                |
| "0"             | "0"               | 38269 | 69296      | "replica"          | "app124"             | "10.128.241.124" | NULL              | 46536         | "2020-08-11 15:39:26.610345+03 | NULL           | "streaming" | "BA5/D7010708" | "BA5/D7010708" | "BA5/D7010708" | "BA5/D7010708" | NULL             | NULL             | NULL              | 1               | "sync"       | "2020-08-11 15:57:01.972806+03 |
|                 |                   |       |            |                    |                      |                  |                   |               |                                |                |             |                |                |                |                |                  |                  |                   |                 |              |                                |
| "0"             | "0"               | 38270 | 69296      | "replica"          | "app122"             | "10.128.241.122" | NULL              | 58578         | "2020-08-11 15:39:26.626625+03 | NULL           | "streaming" | "BA5/D7010708" | "BA5/D7010708" | "BA5/D7010708" | "BA5/D7010708" | NULL             | NULL             | NULL              | 2               | "potential"  | "2020-08-11 15:57:01.97172+03" |
|                 |                   |       |            |                    |                      |                  |                   |               |                                |                |             |                |                |                |                |                  |                  |                   |                 |              |                                |
| "0"             | NULL              | 38311 | 85885      | "streaming_barman" | "barman_receive_wal" | "10.128.241.223" | NULL              | 50770         | "2020-08-11 15:40:02.10472+03" | NULL           | "streaming" | "BA5/D7010708" | "BA5/D7010708" | "BA5/D7010708" | NULL           | "00:00:00.00168" | "00:00:00.00168" | "00:16:59.671909" | 3               | "potential"  | "2020-08-11 15:57:01.789078+03 |
+-----------------+-------------------+-------+------------+--------------------+----------------------+------------------+-------------------+---------------+--------------------------------+----------------+-------------+----------------+----------------+----------------+----------------+------------------+------------------+-------------------+-----------------+--------------+--------------------------------+
Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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