PostgreSQL 15逻辑复制特定表报SSL SYSCALL error: EOF detected求助

问题概述 #

使用PostgreSQL 15进行逻辑复制时，仅特定表触发SSL SYSCALL error: EOF detected错误，订阅端日志显示逻辑复制worker进程反复启动后异常退出，调整日志级别至info仍无法获取有效细节。

关键日志与配置 #

订阅端日志 #

2023-12-07 18:56:49.386 KST [1227687] LOG:  background worker "logical replication worker" (PID 1228473) exited with exit code 1
2023-12-07 18:56:49.423 KST [1228553] LOG:  logical replication apply worker for subscription "sub" has started
2023-12-07 19:00:15.063 KST [1228553] ERROR:  could not receive data from WAL stream: SSL SYSCALL error: EOF detected
2023-12-07 19:00:15.072 KST [1227687] LOG:  background worker "logical replication worker" (PID 1228553) exited with exit code 1
2023-12-07 19:00:15.091 KST [1228609] LOG:  logical replication apply worker for subscription "sub" has started

发布端postgresql.conf关键配置 #

# - Memory -
shared_buffers = 64MB
work_mem = 4MB
maintenance_work_mem = 256MB

max_wal_size = 40GB
min_wal_size = 30GB
max_wal_senders = 13
max_replication_slots = 13
max_slot_wal_keep_size = -1
#wal_sender_timeout = 900s

订阅端postgresql.conf关键配置 #

# - Memory -
shared_buffers = 128MB
logical_decoding_work_mem = 600MB

max_wal_size = 10GB
min_wal_size = 5GB

#wal_receiver_timeout = 600s

# - Subscribers -
max_logical_replication_workers = 20
max_sync_workers_per_subscription = 15

排查与解决步骤 #

• 检查特定表的特性
  查看该表是否包含大字段（如text、bytea、jsonb）或存在高频批量操作，这类场景会生成大体积WAL数据，易触发SSL连接中断。同时验证表的复制标识：

  SELECT relreplident FROM pg_class WHERE relname = '你的表名';
  

  若结果为d（默认）且表无主键，建议添加主键或设置复制标识为f（全表），避免逻辑解码生成过大变更记录。

• 调整SSL超时配置
  发布端启用并缩短wal_sender_timeout，订阅端调整wal_receiver_timeout，快速检测连接异常：

  # 发布端postgresql.conf
  wal_sender_timeout = 30s
  
  # 订阅端postgresql.conf
  wal_receiver_timeout = 30s
  

  修改后需重启PostgreSQL服务。

• 提升日志级别获取细节
  临时将两端日志级别调至debug1，排查隐性错误：

  log_min_messages = debug1
  log_min_error_statement = debug1
  

  重启后重点观察该表变更时的WAL发送、逻辑解码过程，是否存在内存不足或解码失败情况。

• 检查复制槽与WAL保留
  查看发布端复制槽状态：

  SELECT slot_name, active, wal_status FROM pg_replication_slots WHERE slot_name = '你的订阅槽名';
  

  若wal_status为lost，说明WAL已被清理，需重新初始化该表复制。可临时设置max_slot_wal_keep_size = 10GB避免WAL过早清理。

• 验证网络与SSL稳定性
  使用openssl s_client -connect 发布端IP:5432测试SSL连接是否稳定，排查防火墙或VPN是否拦截大流量数据包。

• 重置特定表的复制
  若以上步骤无效，单独重置该表复制：

  -- 订阅端删除表复制
  ALTER SUBSCRIPTION sub DROP TABLE 你的表名;
  
  -- 订阅端重新添加并同步表
  ALTER SUBSCRIPTION sub ADD TABLE 你的表名 WITH (copy_data = true);
  

内容的提问来源于stack exchange，提问作者m tam