使用R通过SSL连接PostgreSQL执行大查询时遭遇Connection reset by peer及SSL解密错误的技术求助

先直接说结论：你的推测有一定道理，但这个问题不只是服务端的锅，客户端侧也有不少可以尝试修复的方向，咱们一步步拆解来看：

一、关于"Connection reset by peer"的原因 #

你说这个错误来自服务端，没错——"peer"指的就是远端（这里要么是SSH服务端，要么是PostgreSQL服务器）主动断开了连接。但背后的触发原因可能有好几种：

• 服务器端的超时机制：比如PostgreSQL的statement_timeout设置得太短，大查询还没跑完就被掐断；或者SSH服务的ClientAliveInterval/ClientAliveCountMax参数，长时间传输数据时被判定为闲置而断开
• 服务器资源不够：大查询占了太多内存，被系统的OOM Killer干掉了进程；或者数据库连接数已经耗尽
• 中间网络设备搞的鬼：防火墙、路由器这类设备如果检测到长时间没有"交互"（其实是一直在传数据，但设备识别不出来），也会主动重置连接

而后面跟着的SSL error: decryption failed or bad record mac，其实是连接断开后的连锁反应——SSH隧道本身是加密的，当底层连接突然断了，客户端的SSL层拿到不完整的数据包，解密自然就失败了。

二、客户端（R环境）能尝试的修复方法 #

针对你现在的代码，给你几个具体的调整方案：

1. 让SSH隧道更稳定 #

你用后台进程跑隧道，可能存在进程意外退出或者缓冲区不够的问题，试试这两个调整：

• 给SSH连接加保活参数，避免被服务端踢掉：

  # 连接时设置每60秒发一次保活包，最多重试10次
  session <- ssh::ssh_connect('user@host:port', 
                              opts = list(ServerAliveInterval = 60, 
                                          ServerAliveCountMax = 10))
  

• 如果工作流允许，改用阻塞模式的隧道（不用后台进程），稳定性会好很多：

  session <- ssh::ssh_connect('user@host:port', opts = list(ServerAliveInterval = 60))
  # 这个会卡住当前进程，直到隧道关闭，所以可以开个新终端跑这个，或者调整你的脚本结构
  ssh::ssh_tunnel(session, port = 5432, target = '127.0.0.1:5432')
  

2. 不要一次性拉取所有大表数据 #

大查询一次性读全量数据很容易触发连接问题，改成分批读取：

# 用dbSendQuery+dbFetch分块读取
res <- DBI::dbSendQuery(conn = dbcon, statement = "SELECT * FROM large_table;")
# 每次读10000行，你可以根据实际情况调整这个数字
chunk <- DBI::dbFetch(res, n = 10000)
all_data <- list()
while(nrow(chunk) > 0) {
  all_data <- c(all_data, list(chunk))
  chunk <- DBI::dbFetch(res, n = 10000)
}
DBI::dbClearResult(res)
# 把所有块合并成一个数据框
all_data <- do.call(rbind, all_data)

3. 调整PostgreSQL的SSL连接参数 #

试试禁用SSL压缩，有些环境下压缩会导致传输异常：

dbcon <- DBI::dbConnect(
  drv = RPostgres::Postgres(), 
  dbname = "db_name", 
  host = "127.0.0.1", 
  port = 5432, 
  user = "db_user", 
  password = "db_password", 
  sslmode = "require",
  sslcompression = 0, # 关闭SSL压缩
  service = NULL
)

4. 监控后台隧道进程的状态 #

你用sys::r_background启动的隧道进程，可能因为输出缓冲区满或者其他原因挂掉，试试把日志存下来方便排查：

# 把隧道的输出和错误日志写到文件里
pid <- sys::r_background(
  std_out = "/tmp/ssh_tunnel_out.log", 
  std_err = "/tmp/ssh_tunnel_err.log", 
  args = c("-e", cmd)
)
# 定期检查进程是否还活着
if(!sys::is_process_alive(pid)) {
  stop("SSH隧道进程意外退出了！")
}

三、如果客户端调整后还是不行，得查服务端 #

要是上面的方法都没用，建议找服务器管理员帮忙查这些点：

• 看PostgreSQL的日志，有没有查询超时、内存不足或者其他错误记录
• 检查SSH服务的sshd_config配置，看看超时参数是不是设得太严
• 看看服务器的资源使用情况（内存、CPU、磁盘IO），是不是大查询跑的时候资源不够用

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