Node.js使用PostgreSQL COPY批量插入数据时连接终止问题排查

问题根源 #

你遇到的Connection terminated错误，核心原因有三个：

• 直接拼接CSV行时未处理特殊字符（逗号、双引号、换行等），导致PostgreSQL解析CSV格式出错，主动断开连接
• 流未正确结束，数据库连接长时间挂起被强制终止
• COPY命令未明确指定目标列和数据格式，数据库无法正确解析输入内容

无需本地文件的解决方案 #

用流处理工具将二维数组直接转成符合规范的CSV流，再通过pg-copy-streams写入PostgreSQL，全程内存内处理，无需生成本地文件。

步骤1：安装依赖

需要pg、pg-copy-streams，再加轻量的CSV流处理库csv-stringify（自动处理转义，比手动拼接靠谱）：

npm install pg pg-copy-streams csv-stringify

步骤2：完整代码示例

const { Client } = require('pg');
const copyFrom = require('pg-copy-streams').from;
const { stringify } = require('csv-stringify');

// 你的50万行二维数组，格式：[[user_id1, email1, city1], [user_id2, email2, city2], ...]
const largeDataArray = [];

// 数据库连接配置
const client = new Client({
  host: 'your-host',
  port: 5432,
  user: 'your-user',
  password: 'your-password',
  database: 'your-db'
});

async function batchInsert() {
  try {
    await client.connect();

    // 构造COPY命令：指定目标列，明确CSV格式，无头部
    const copyCommand = `COPY my_test_table (user_id, email, city) FROM STDIN WITH (FORMAT csv, DELIMITER ',', NULL '', ENCODING 'UTF8')`;
    const stream = client.query(copyFrom(copyCommand));

    // 配置CSV字符串化流：不生成头部，自动处理特殊字符转义
    const csvStream = stringify({
      header: false,
      quoted: true, // 强制给字段加引号，避免含特殊字符的字段出错
      escape: '"' // 双引号转义规则匹配PostgreSQL要求
    });

    // 管道流：数组转CSV流 → COPY输入流
    csvStream.pipe(stream);

    // 分块写入数据（降低内存占用，避免一次性加载50万行）
    const batchSize = 1000;
    for (let i = 0; i < largeDataArray.length; i += batchSize) {
      const batch = largeDataArray.slice(i, i + batchSize);
      batch.forEach(row => csvStream.write(row));
      // 可选：每批写入后短暂等待，避免压垮数据库
      await new Promise(resolve => setTimeout(resolve, 10));
    }

    // 结束CSV流，触发COPY操作完成
    csvStream.end();

    // 等待COPY流执行完成
    await new Promise((resolve, reject) => {
      stream.on('finish', resolve);
      stream.on('error', reject);
    });

    console.log('批量插入完成');
  } catch (err) {
    console.error('插入失败:', err);
  } finally {
    await client.end();
  }
}

batchInsert();

关键细节说明

• COPY命令规范：必须指定目标列(user_id, email, city)，明确FORMAT csv，同时定义分隔符、空值标识，避免数据库猜测格式出错
• CSV转义处理：csv-stringify会自动处理字段中的特殊字符，比如把含逗号的字段"New York, NY"转成"""New York, NY"""，完全符合PostgreSQL的CSV解析规则
• 流的生命周期：必须调用csvStream.end()结束输入，监听stream的finish事件确保COPY操作完成，否则数据库连接会因输入未终止而断开
• 分块写入优化：50万行一次性写入会占用大量内存，分块写入（比如每1000行一批）能有效降低内存压力，避免Node.js内存溢出

原代码问题修正点 #

1. 移除手动拼接CSV行的逻辑，改用专业工具处理转义
2. 补充COPY命令的列定义和格式参数
3. 确保流正确结束，监听完成和错误事件
4. 增加分块写入逻辑优化内存使用

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