PostgreSQL 的 COPY 命令

PostgreSQL 的 COPY 命令是高效數據導入導出的核心工具，性能遠超常規 INSERT 語句。以下是 COPY 命令的深度解析：

一 COPY 命令基礎

1.1 基本語法對比

命令類型	語法示例	執行位置	文件訪問權限
服務器端COPY	COPY table FROM '/path/file.csv';	數據庫服務器	需要postgres系統用戶權限
客戶端COPY	\copy table FROM 'file.csv';	客戶端機器	使用客戶端用戶權限

1.2 核心功能矩陣

功能	COPY FROM	COPY TO
數據加載速度	每秒萬行級	每秒萬行級
事務處理	單事務操作	單事務操作
二進制支持	是	是
錯誤處理	可跳過錯誤行	-

二 高級使用技巧

2.1 復雜數據轉換

-- 導入時轉換數據類型
COPY users(id, name, reg_date) 
FROM '/data/users.csv' 
WITH (FORMAT csv, HEADER,DELIMITER '|',NULL 'NULL',FORCE_NOT_NULL (id, name),ENCODING 'UTF8');

2.2 條件導出

-- 導出查詢結果
COPY (SELECT * FROM orders WHERE order_date > '2025-01-01') 
TO '/data/recent_orders.csv' 
WITH (FORMAT csv, HEADER);

三 性能優化方案

3.1 批量加載最佳實踐

# 使用并行加載（拆分文件后）
for i in {1..4}; dopsql -c "COPY large_table FROM '/data/part$i.csv' WITH (FORMAT csv)" &
done
wait

3.2 關鍵性能參數

參數	推薦值	影響
maintenance_work_mem	1GB+	提高導入排序效率
max_wal_size	4GB+	減少WAL檢查點
synchronous_commit	off	禁用同步提交加速導入

四 異常處理機制

4.1 錯誤日志記錄

-- 創建錯誤日志表
CREATE TABLE import_errors (line_num integer,error_msg text,raw_data text
);-- 帶錯誤記錄的導入
BEGIN;
CREATE TEMP TABLE temp_import (LIKE target_table);
COPY temp_import FROM '/data/source.csv' WITH (FORMAT csv, HEADER);
INSERT INTO target_tableSELECT * FROM temp_importON CONFLICT DO NOTHING;
INSERT INTO import_errorsSELECT pg_copy_log();
COMMIT;

4.2 二進制格式處理

# 導出二進制數據
pg_dump -t table_name -Fc -f output.dump dbname# 二進制文件轉換
pg_restore -l output.dump > output.list

五 監控與維護

5.1 性能監控查詢

-- 查看COPY操作歷史
SELECT query, duration 
FROM pg_stat_statements 
WHERE query LIKE 'COPY%' 
ORDER BY duration DESC;-- 檢查導入進度（PostgreSQL 14+）
SELECT pid, query, pg_stat_get_progress_info('COPY') 
FROM pg_stat_activity 
WHERE backend_type = 'client backend';

5.2 維護建議

1. 定期清理臨時文件：COPY操作可能產生大量WAL日志
2. 版本升級驗證：不同PostgreSQL版本COPY行為可能有差異
3. 網絡優化：跨數據中心傳輸時考慮壓縮選項

COPY命令是PostgreSQL數據遷移的核心工具，掌握其高級用法可以顯著提升ETL效率。對于TB級數據遷移，建議：

• 使用二進制格式減少I/O
• 結合表分區并行加載
• 在維護窗口禁用WAL歸檔
• 考慮使用pg_bulkload擴展處理超大規模數據

更詳細內容請查看官方文檔：

https://www.postgresql.org/docs/17/sql-copy.html

謹記：心存敬畏，行有所止。