PostgreSQL 的 COPY 命令深度解析_MsSqlserver

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}; do
  psql -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_table
  select * from temp_import
  on conflict do nothing;
insert into import_errors
  select 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';