# 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 [, ...] ) ]
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'
);
# 編譯安裝
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
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
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;
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()
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
硬件層面優化
加載后操作 “`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版本，部分語法在不同版本中可能略有差異。
免責聲明：本站發布的內容（圖片、視頻和文字）以原創、轉載和分享為主，文章觀點不代表本網站立場，如果涉及侵權請聯系站長郵箱：is@yisu.com進行舉報，并提供相關證據，一經查實，將立刻刪除涉嫌侵權內容。
