分布式postgres部署不同步

Question

我建立了一个分布式postgres数据库系统，并使用hot_standby wal_level配置了复制。

有一个具有多个副本的中央主数据库(目前全世界有15个副本)被用作只读实例--因此不需要故障转移--我们只是希望将数据同步到可以读取它们的远程位置。

师父：

wal_level = hot_standby
max_wal_senders = 20
checkpoint_segments = 8    
wal_keep_segments = 8

客户：

wal_level = hot_standby
max_wal_senders = 3
checkpoint_segments = 8    
wal_keep_segments = 8 
hot_standby = on

客户端上的/var/lib/postgresql/9.4/Recepy.conf：

standby_mode = 'on'
primary_conninfo = 'host=<IP of master> port=5432 user=replicator password=xxxx sslmode=require'
trigger_file = '/tmp/postgresql.trigger'

复制开始了--几天内一切似乎都很好。过了几天，似乎没有更多的连接在主服务器上可供复制.

客户：

2017-05-04 01:16:51 UTC [9608-1] FATAL:  could not connect to the primary server: FATAL:  sorry, too many clients already
2017-05-04 01:16:57 UTC [10807-1] FATAL:  could not connect to the primary server: FATAL:  sorry, too many clients already
2017-05-04 01:17:02 UTC [12022-1] FATAL:  could not connect to the primary server: FATAL:  sorry, too many clients already
2017-05-04 01:17:06 UTC [13217-1] FATAL:  could not connect to the primary server: FATAL:  remaining connection slots are reserved for non-replication superuser connections
...

师父：

然后日志里充满了像下面这样的信息-而且它永远不会恢复.

2017-05-04 08:44:14 UTC [24850-1] replicator@[unknown] ERROR:  requested WAL segment 000000010000003500000014 has already been removed
2017-05-04 08:44:19 UTC [25958-1] replicator@[unknown] ERROR:  requested WAL segment 000000010000003500000014 has already been removed
2017-05-04 08:44:24 UTC [27063-1] replicator@[unknown] ERROR:  requested WAL segment 000000010000003500000014 has already been removed
2017-05-04 08:44:29 UTC [28144-1] replicator@[unknown] ERROR:  requested WAL segment 000000010000003500000014 has already been removed
2017-05-04 08:44:34 UTC [29227-1] replicator@[unknown] ERROR:  requested WAL segment 000000010000003500000014 has already been removed
2017-05-04 08:44:39 UTC [30316-1] replicator@[unknown] ERROR:  requested WAL segment 000000010000003500000014 has already been removed
...

客户：

2017-04-30 11:26:22 UTC [28474-1] LOG:  started streaming WAL from primary at 35/14000000 on timeline 1
2017-04-30 11:26:22 UTC [28474-2] FATAL:  could not receive data from WAL stream: ERROR:  requested WAL segment 000000010000003500000014 has already been removed
2017-04-30 11:26:26 UTC [29328-1] LOG:  started streaming WAL from primary at 35/14000000 on timeline 1
2017-04-30 11:26:26 UTC [29328-2] FATAL:  could not receive data from WAL stream: ERROR:  requested WAL segment 000000010000003500000014 has already been removed
2017-04-30 11:26:31 UTC [30394-1] LOG:  started streaming WAL from primary at 35/14000000 on timeline 1
2017-04-30 11:26:31 UTC [30394-2] FATAL:  could not receive data from WAL stream: ERROR:  requested WAL segment 000000010000003500000014 has already been removed
...

所以我的问题是：

• 是否有更好的方法来同步对远程只读实例的更改，还是只是在信任中出现了错误，使复制在一段时间后中断？

Answer 1

https://www.postgresql.org/docs/current/static/runtime-config-replication.html

max_wal_senders (整数) 指定来自备用服务器或流式基本备份客户端的最大并发连接数(即同时运行WAL发送进程的最大数量)。默认值为零，意味着复制被禁用。WAL发送进程计算连接的总数，因此参数不能设置高于max_connections。突然流客户端断开连接可能会导致孤立连接槽，直到达到超时为止，因此，此参数应略高于预期客户端的最大数量，以便断开连接的客户端可以立即重新连接。

(强调我的)。无论是应用程序连接还是孤立连接，都会导致

FATAL: sorry, too many clients already

您可能需要为应用程序使用一些连接池，比如pgbouncer，在它们实际发生之前限制过多的连接。

在回答您的问题时，如果您的有 archive_command设置为在某个地方实际复制WAL，请修改“从服务器上的recovery.conf中的restore_command”以获取它们。它将允许奴隶从失去溪流的那一刻起迎头赶上。否则你就得重建它。