PostgreSQL中怎么批量导入数据

发布时间：2021-07-26 15:54:38 作者：Leah
来源：亿速云阅读：1857

# PostgreSQL中怎么批量导入数据

## 目录
1. [引言](#引言)
2. [常见批量导入方法概览](#常见批量导入方法概览)
3. [COPY命令详解](#copy命令详解)
4. [pg_bulkload扩展使用](#pg_bulkload扩展使用)
5. [使用psql执行SQL文件](#使用psql执行sql文件)
6. [外部表(Foreign Data Wrapper)方式](#外部表foreign-data-wrapper方式)
7. [编程语言接口批量插入](#编程语言接口批量插入)
8. [性能优化建议](#性能优化建议)
9. [总结](#总结)

## 引言

PostgreSQL作为功能强大的开源关系型数据库，在日常业务中经常需要处理大量数据的导入操作。相比单条INSERT语句，批量导入能显著提高数据加载效率，降低系统开销。本文将详细介绍PostgreSQL中五种主流批量数据导入方法，包含具体操作示例和性能对比。

## 常见批量导入方法概览

| 方法名称               | 适用场景                          | 速度排名 | 是否需要预处理 |
|------------------------|-----------------------------------|----------|----------------|
| COPY命令               | 本地/远程结构化文件导入           | 1        | 可选           |
| pg_bulkload            | 超大数据量(千万级以上)初始化加载  | 2        | 需要           |
| psql执行SQL文件        | 中小规模数据或已有SQL脚本         | 3        | 需要           |
| 外部表(FDW)            | 实时访问外部数据源                | 4        | 需要           |
| 编程语言批量插入       | 应用层数据导入                    | 5        | 需要           |

## COPY命令详解

### 基本语法

```sql
COPY table_name [ ( column_list ) ]
    FROM { 'filename' | PROGRAM 'command' | STDIN }
    [ [ WITH ] ( option [, ...] ) ]

实用示例

从CSV文件导入

COPY users(id, name, email) 
FROM '/path/to/users.csv' 
WITH CSV HEADER DELIMITER ',';

使用管道导入

cat data.csv | psql -h localhost -U user -d dbname -c "COPY table FROM STDIN CSV"

导出数据到文件

COPY (SELECT * FROM large_table) TO '/path/to/export.csv' WITH CSV;

性能参数

COPY large_table FROM '/path/to/file' WITH (
    FORMAT 'csv',
    FREEZE true,
    DELIMITER '|',
    NULL '',
    ENCODING 'utf8',
    HEADER true,
    BUFFER_SIZE '256MB'
);

pg_bulkload扩展使用

安装步骤

# 编译安装
git clone https://github.com/ossc-db/pg_bulkload
cd pg_bulkload
make && make install

# 创建扩展
psql -c "CREATE EXTENSION pg_bulkload;"

配置文件示例

load.ctl文件内容：

TYPE = CSV
INPUT = /data/users.csv
DATABASE = mydb
TABLE = users
DELIMITER = ","
SKIP = 1
PARSE_BADFILE = /tmp/users.bad
LOGFILE = /tmp/users.log

执行加载

pg_bulkload load.ctl

优势对比

跳过WAL日志写入
支持并行加载
错误隔离机制
加载速度可达普通COPY的3-5倍

使用psql执行SQL文件

基础用法

psql -h host -U user -d dbname -f insert_data.sql

高效插入脚本示例

bulk_insert.sql:

BEGIN;
INSERT INTO large_table VALUES 
(1, 'data1'), (2, 'data2'), ... (1000, 'data1000');
COMMIT;

参数优化执行

psql -c "SET synchronous_commit TO off;" -f big_insert.sql

外部表(Foreign Data Wrapper)方式

配置流程

安装file_fdw扩展

CREATE EXTENSION file_fdw;

创建服务器和外部表

CREATE SERVER csv_server FOREIGN DATA WRAPPER file_fdw;

CREATE FOREIGN TABLE ext_users (
    id int,
    name text,
    reg_date timestamp
) SERVER csv_server
OPTIONS (
    filename '/path/to/users.csv',
    format 'csv',
    header 'true'
);

数据导入本地表

INSERT INTO local_users 
SELECT * FROM ext_users WHERE id > 1000;

编程语言接口批量插入

Python示例(psycopg2)

import psycopg2
from psycopg2.extras import execute_batch

data = [(1, 'Alice'), (2, 'Bob'), ... (1000, 'Zoe')]

conn = psycopg2.connect("dbname=test user=postgres")
cur = conn.cursor()

execute_batch(cur,
    "INSERT INTO users (id, name) VALUES (%s, %s)",
    data,
    page_size=1000)

conn.commit()

Java示例(JDBC)

String sql = "INSERT INTO users (id, name) VALUES (?, ?)";
try (Connection conn = DriverManager.getConnection(url);
     PreparedStatement pstmt = conn.prepareStatement(sql)) {
    
    conn.setAutoCommit(false);
    
    for(int i=0; i<10000; i++){
        pstmt.setInt(1, i);
        pstmt.setString(2, "user_"+i);
        pstmt.addBatch();
        
        if(i % 1000 == 0){
            pstmt.executeBatch();
        }
    }
    
    pstmt.executeBatch();
    conn.commit();
}

性能优化建议

预处理建议
- 导入前禁用索引：DROP INDEX idx_name;
- 关闭外键检查：SET session_replication_role = replica;
- 增大维护工作内存：SET maintenance_work_mem = '1GB';

服务器参数调整

# postgresql.conf
checkpoint_timeout = 1h
max_wal_size = 10GB
wal_level = minimal
synchronous_commit = off

硬件层面优化
- 使用SSD存储
- 增加服务器内存
- 采用RD 0磁盘阵列
加载后操作 “`sql – 重建索引 REINDEX TABLE large_table;

– 更新统计信息 ANALYZE large_table;

– 恢复设置 RESET session_replication_role;


## 总结

不同批量导入方法对比总结：

| 方法           | 百万条记录耗时 | 适用数据量级 | 是否需要停机 |
|----------------|----------------|--------------|--------------|
| COPY命令       | 12-15秒        | 百万到亿级   | 否           |
| pg_bulkload    | 8-10秒         | 千万到十亿级 | 建议维护窗口 |
| SQL文件        | 45-60秒        | 万到百万级   | 否           |
| 外部表         | 30-40秒        | 百万级以下   | 否           |
| 编程语言批量   | 60-90秒        | 万级以下     | 否           |

**最佳实践建议**：
1. 超大规模初始化加载首选pg_bulkload
2. 日常批量导入推荐COPY命令
3. 应用层数据同步建议使用编程语言批量插入
4. 定期维护时可考虑禁用索引和约束来提升性能

通过合理选择导入方法并配合优化参数，PostgreSQL可以高效处理TB级的数据导入任务。

注：实际执行时可根据具体环境调整参数值，建议在生产环境操作前先在测试环境验证。本文示例基于PostgreSQL 14版本，部分语法在不同版本中可能略有差异。