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

postgresql

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?

Best Answer

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)'.

Related Solutions

PostgreSQL 9.1 Hot Backup Error: the database system is starting up

The message "The database system is starting up." does not indicate an error. The reason it is at the FATAL level is so that it will always make it to the log, regardless of the setting of log_min_messages:

http://www.postgresql.org/docs/9.1/interactive/runtime-config-logging.html#RUNTIME-CONFIG-LOGGING-WHEN

After the rsync, did you really run what you show?:

pgsql -c "select pg_stop_backup();";

Since there is, so far as I know, no pgsql executable, that would leave the backup uncompleted, and the slave would never come out of recovery mode. On the other hand, maybe you really did run psql, because otherwise I don't see how the slave would have logged such success messages as:

Log: consistent recovery state reached at 0/BF0000B0

and:

Log: streaming replication successfully connected to primary

Did you try connecting to the slave at this point? What happened?

The "Success. You can now start..." message you mention is generated by initdb, which shouldn't be run as part of setting up a slave; so I think you may be confused about something there. I'm also concerned about these apparently conflicting statements:

The only ways I have restarted Postgres is through the service postgresql-9.1 restart or /etc/init.d/postgresql-9.1 restart commands. After I receive this error, I kill all processes and again try to restart the database...

Did you try to stop the service through the service script? What happened? It might help in understanding the logs if you prefixed lines with more information. We use:

log_line_prefix = '[%m] %p %q<%u %d %r> '

The recovery.conf script looks odd. Are you copying from the master's pg_xlog directory, the slave's active pg_xlog directory, or an archive directory?

Postgresql replication – pg_stat_replication is showing empty columns

From the prompt, it looks like you are not connecting as a superuser. To view the address information about the connection, you must be connected as superuser.

Best Answer

Related Solutions

PostgreSQL 9.1 Hot Backup Error: the database system is starting up

Postgresql replication – pg_stat_replication is showing empty columns

Related Question