PostgreSQL主服务器挂起复制流

Question

首先，我不是一个数据工程师，所以我会尽我最大的努力给你解决我的问题所需要的东西。

上下文：--我正在尝试创建两个PostgreSQL服务器、一个主服务器和一个从服务器。

psql (PostgreSQL) 10.9 (Ubuntu10.9-0ubuntu0.18.04.1)

据我所知，当我们只有2台服务器时，进行同步复制并不是个好主意。但我得明白这是怎么回事..。

问题:当我尝试执行CREATE SCHEMA test;时，主服务器挂起。但是，图式存在于主人身上，也存在于奴隶身上。主人挂起是因为它等待奴隶提交状态..。

主服务器的配置：/etc/postgresql/10/main/conf.d/conf.d

# Connection
listen_addresses = '127.0.0.1,slave-ip'
ssl = on
ssl_cert_file = '/etc/ssl/postgresql/certs/server.pem'
ssl_key_file = '/etc/ssl/postgresql/private/server.key'
ssl_ca_file = '/etc/ssl/postgresql/certs/server.pem'
password_encryption = scram-sha-256
# WAL
wal_level = replica
synchronous_commit = remote_apply #local works, remote_apply hangs
# Archive
archive_mode = on
archive_command = 'rsync -av %p postgres@lab-3:/var/lib/postgresql/wal_archive_lab_2/%f'
# Replication master
max_wal_senders = 2
wal_keep_segments = 100
synchronous_standby_names = 'ANY 1 ("lab-3")'

/etc/postgresql/10/main/pg_hba.conf

hostssl replication     replicate       slave-ip/32         scram-sha-256

从服务器的配置： /etc/postgresql/10/main/conf.d/standby.conf

# Connection
listen_addresses = '127.0.0.1,master-ip'
ssl = on
ssl_cert_file = '/etc/ssl/postgresql/certs/server.pem'
ssl_key_file = '/etc/ssl/postgresql/private/server.key'
ssl_ca_file = '/etc/ssl/postgresql/certs/server.pem'
password_encryption = scram-sha-256
# WAL
wal_level = replica
# Archive
archive_mode = on
archive_command = 'rsync -av %p postgres@lab-3:/var/lib/postgresql/wal_archive_lab_3/%f'
# Replication slave
max_wal_senders = 2
wal_keep_segments = 100
hot_standby = on

/var/lib/postgresql/10/main/recovery.conf

standby_mode = on
primary_conninfo = 'host=master-ip port=5432 user=replicate password=replicate_password sslmode=require application_name="lab-3"'
trigger_file = '/var/lib/postgresql/10/postgresql.trigger'

当日志文件挂起时，我绝对没有得到任何信息，只有当我在主实例上中止Ctrl+C时出现的错误：

警告:由于用户请求，取消等待同步复制 详细信息:事务已在本地提交，但可能尚未复制到备用服务器。

有没有一种方法来检查什么附加，以及为什么它会像这样被困住？

编辑1

pg_stat_replication的内容：

查询前

  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
-------+----------+-----------+------------------+--------------+-----------------+-------------+-------------------------------+--------------+-----------+------------+------------+------------+------------+-----------+-----------+------------+---------------+------------
 54431 |    16384 | replicate | "lab-3"          | slave-ip     |                 |       47742 | 2019-08-06 07:56:48.105056+02 |              | streaming | 0/110000D0 | 0/110000D0 | 0/110000D0 | 0/110000D0 |           |           |            |             0 | async

(1行)

当它挂起/之后

  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
-------+----------+-----------+------------------+--------------+-----------------+-------------+-------------------------------+--------------+-----------+------------+------------+------------+------------+-----------------+-----------------+---------------+---------------+------------
 54431 |    16384 | replicate | "lab-3"          | slave-ip     |                 |       47742 | 2019-08-06 07:56:48.105056+02 |              | streaming | 0/11000C10 | 0/11000C10 | 0/11000C10 | 0/11000C10 | 00:00:00.000521 | 00:00:00.004421 | 00:00:00.0045 |             0 | async

(1行)

谢谢！

Answer 1

正如Laurenz所说，问题是引用同步备用名称。

文档解释说，如果主服务器上的synchronous_standby_names配置条目包含破折号，则应该引用它，但是不能在从服务器上的primary_conninfo值中引用它。