postgresql - 带有 pglogical : ERROR: connection to other side has died 的 Postgres 复制

Question

在 2 个 Postgres 实例之间复制时出现此错误(在副本上):

错误:与另一端的连接已终止

这是副本/订阅者上的日志:

    2017-09-15 20:03:55 UTC [14335-3] LOG:  apply worker [14335] at slot 7 generation 109 crashed
    2017-09-15 20:03:55 UTC [2961-1732] LOG:  worker process: pglogical apply 16384:3661733826 (PID 14335) exited with exit code 1
    2017-09-15 20:03:59 UTC [14331-2] ERROR:  connection to other side has died
    2017-09-15 20:03:59 UTC [14331-3] LOG:  apply worker [14331] at slot 2 generation 132 crashed
    2017-09-15 20:03:59 UTC [2961-1733] LOG:  worker process: pglogical apply 16384:3423246629 (PID 14331) exited with exit code 1
    2017-09-15 20:04:02 UTC [14332-2] ERROR:  connection to other side has died
    2017-09-15 20:04:02 UTC [14332-3] LOG:  apply worker [14332] at slot 4 generation 125 crashed
    2017-09-15 20:04:02 UTC [2961-1734] LOG:  worker process: pglogical apply 16384:2660030132 (PID 14332) exited with exit code 1
    2017-09-15 20:04:02 UTC [14350-1] LOG:  starting apply for subscription parking_sub
    2017-09-15 20:04:05 UTC [14334-2] ERROR:  connection to other side has died
    2017-09-15 20:04:05 UTC [14334-3] LOG:  apply worker [14334] at slot 6 generation 119 crashed
    2017-09-15 20:04:05 UTC [2961-1735] LOG:  worker process: pglogical apply 16384:394989729 (PID 14334) exited with exit code 1
    2017-09-15 20:04:06 UTC [14333-2] ERROR:  connection to other side has died

主/提供者的日志:

    2017-09-15 23:22:43 UTC [22068-5] repuser@ga-master ERROR:  got sequence entry 1 for toast chunk 1703536315 instead of seq 0
    2017-09-15 23:22:43 UTC [22068-6] repuser@ga-master LOG:  could not receive data from client: Connection reset by peer
    2017-09-15 23:22:44 UTC [22067-5] repuser@ga-master ERROR:  got sequence entry 1 for toast chunk 1703536315 instead of seq 0
    2017-09-15 23:22:44 UTC [22067-6] repuser@ga-master LOG:  could not receive data from client: Connection reset by peer
    2017-09-15 23:22:48 UTC [22070-5] repuser@ga-master ERROR:  got sequence entry 1 for toast chunk 1703536315 instead of seq 0
    2017-09-15 23:22:48 UTC [22070-6] repuser@ga-master LOG:  could not receive data from client: Connection reset by peer
    2017-09-15 23:22:49 UTC [22069-5] repuser@ga-master ERROR:  got sequence entry 1 for toast chunk 1703536315 instead of seq 0
    2017-09-15 23:22:49 UTC [22069-6] repuser@ga-master LOG:  could not receive data from client: Connection reset by peer

在主/提供者上配置:

    archive_mode = on
    archive_command = 'cp %p /data/pgdata/wal_archives/%f'
    max_wal_senders = 20
    wal_level = logical
    max_worker_processes = 100
    max_replication_slots = 100
    shared_preload_libraries = pglogical
    max_wal_size = 20GB

在副本/订阅者上配置:

    max_replication_slots = 100
    shared_preload_libraries = pglogical
    max_worker_processes = 100
    max_wal_size = 20GB

我总共有 18 个模式的 18 个订阅。一开始它似乎工作正常，但它很快就变坏了，一些订阅开始在 down 和 replicating 状态之间反弹，并出现上面的错误。

问题

可能的原因是什么？我需要更改我的 Pg 配置吗？

另外，我注意到当复制正在进行时，主服务器/提供者的 CPU 使用率非常高。

    /# ps aux | sort -nrk 3,3 | head -n 5
    postgres 18180 86.4  1.0 415168 162460 ?       Rs   22:32  19:03 postgres: getaround getaround 10.240.0.7(64106) CREATE INDEX
    postgres 20349 37.0  0.2 339428 38452 ?        Rs   22:53   0:07 postgres: wal sender process repuser 10.240.0.7(49742) idle
    postgres 20351 33.8  0.2 339296 36628 ?        Rs   22:53   0:06 postgres: wal sender process repuser 10.240.0.7(49746) idle
    postgres 20350 28.8  0.2 339016 44024 ?        Rs   22:53   0:05 postgres: wal sender process repuser 10.240.0.7(49744) idle
    postgres 20352 27.6  0.2 339420 36632 ?        Rs   22:53   0:04 postgres: wal sender process repuser 10.240.0.7(49750) idle

提前致谢!

Answer 1

我有一个类似的问题，通过将 master/provider 上的 wal_sender_timeout 配置设置为 5 分钟(默认为 1 分钟)来解决。如果超时，它将断开连接 - 这似乎已经解决了我的问题。