为何DBeaver仅插入1000条记录？PostgreSQL跨库批量插入问题

可能的原因 #

• DBeaver默认结果集行数限制：DBeaver自带结果显示限制，默认可能设为1000行，即便dblink拉取了5000条数据，客户端也只会取前1000条执行插入，全程无报错。
• dblink默认fetch批次限制：PostgreSQL的dblink获取远程数据时，默认分批次拉取，如果客户端未处理完所有批次，就会只插入部分数据。
• DBeaver事务提交设置：若自动提交未开启，或事务仅提交了部分数据，也会出现该情况。

解决办法 #

1. 调整DBeaver的结果集行数限制 #

打开DBeaver，依次点击编辑 > 首选项 > 数据库 > 结果集，找到「最大行数」选项，将数值改大（比如设为10000，或填0表示无限制），重启DBeaver后重新测试插入。

2. 分批次插入（适合大量数据） #

一次性拉取1600万条数据易触发内存或传输瓶颈，建议分批次处理，示例代码如下：

DO $$
DECLARE
    batch_size INT := 5000;  -- 每批次插入行数
    total_rows INT;
    current_offset INT := 0;
BEGIN
    -- 获取远程表总条数（可选，用于跟踪进度）
    SELECT COUNT(*) INTO total_rows 
    FROM dblink('myconn', 'SELECT id FROM public.table_server2') AS t(id varchar);
    
    WHILE current_offset < total_rows LOOP
        INSERT INTO table_server1 ([some 115 columns])
        SELECT *
        FROM dblink('myconn',$MARK$
            SELECT [some 115 columns]
            FROM public.table_server2 
            LIMIT $1 OFFSET $2
            $MARK$, ARRAY[batch_size, current_offset]) AS t1 (
                id varchar, col1 varchar, col2 varchar, col3 integer, ... , col115 varchar);
        
        current_offset := current_offset + batch_size;
        COMMIT; -- 每批次提交，避免事务过大导致性能问题
    END LOOP;
END $$;

3. 用高效的COPY方式（推荐用于超大量数据） #

如果两台服务器网络连通，COPY结合dblink的效率远高于普通INSERT，示例：

-- 在目标库执行，直接拉取远程表数据导入
SELECT dblink_exec('myconn', $MARK$
    COPY (SELECT [some 115 columns] FROM public.table_server2) TO STDOUT
    $MARK$) AS result \gset
COPY table_server1 ([some 115 columns]) FROM STDIN;

或者直接在服务器命令行用管道传输（效率最高）：

pg_dump -h 远程服务器IP -U 用户名 -d 远程数据库名 -t public.table_server2 | psql -h 目标服务器IP -U 用户名 -d 目标数据库名 -c "COPY table_server1 FROM STDIN"

4. 绕过DBeaver，直接用psql执行 #

若怀疑是DBeaver的限制导致，直接登录目标PostgreSQL服务器的psql命令行执行插入语句，规避客户端的各种限制。

5. 调整dblink的fetch size #

在dblink查询时显式指定fetch size，确保一次性拉取足够的数据：

INSERT INTO table_server1 ([some 115 columns])
SELECT *
FROM dblink('myconn', 'SELECT [some 115 columns] FROM public.table_server2 LIMIT 5000', 'fetchsize=5000') AS t1 (
    id varchar, col1 varchar, col2 varchar, col3 integer, ... , col115 varchar);

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