这里简要介绍一下PG中的Copy以及\Copy快速加载数据的基本用法及注意事项。同时也比较了Batch Insert与Copy之间的性能差异。它们在PG日常维护与使用的场景中经常要用到。

1.

Copy语法

PG 9.0以后的新语法如下：

COPY table_name [ ( column_name [, ...] ) ] FROM { 'filename' | PROGRAM 'command' | STDIN } [ [ WITH ] ( option [, ...] ) ] [ WHERE condition ] COPY { table_name [ ( column_name [, ...] ) ] | ( query ) } TO { 'filename' | PROGRAM 'command' | STDOUT } [ [ WITH ] ( option [, ...] ) ] where option can be one of: FORMAT format_name FREEZE [ boolean ] DELIMITER 'delimiter_character' NULL 'null_string' HEADER [ boolean ] QUOTE 'quote_character' ESCAPE 'escape_character' FORCE_QUOTE { ( column_name [, ...] ) | * } FORCE_NOT_NULL ( column_name [, ...] ) FORCE_NULL ( column_name [, ...] ) ENCODING 'encoding_name'

在PG9.0以前，语法是下边这样的：

COPY table_name [ ( column_name [, ...] ) ] FROM { 'filename' | STDIN } [ [ WITH ] [ BINARY ] [ DELIMITER [ AS ] 'delimiter_character' ] [ NULL [ AS ] 'null_string' ] [ CSV [ HEADER ] [ QUOTE [ AS ] 'quote_character' ] [ ESCAPE [ AS ] 'escape_character' ] [ FORCE NOT NULL column_name [, ...] ] ] ] COPY { table_name [ ( column_name [, ...] ) ] | ( query ) } TO { 'filename' | STDOUT } [ [ WITH ] [ BINARY ] [ DELIMITER [ AS ] 'delimiter_character' ] [ NULL [ AS ] 'null_string' ] [ CSV [ HEADER ] [ QUOTE [ AS ] 'quote_character' ] [ ESCAPE [ AS ] 'escape_character' ] [ FORCE QUOTE { column_name [, ...] | * } ] ] ]

2.使用提示

1. COPY 命令用于PostgreSQL表和标准文件系统文件之间交换数据

2. COPY TO 把一个表的所有内容都拷贝到一个文件

3. COPY FROM 从一个文件里拷贝数据到一个表里（把数据附加到表中已经存在的内容里）。

4. COPY 命令从PostgreSQL 服务器直接读

这里简要介绍一下PG中的Copy以及\Copy快速加载数据的基本用法及注意事项。同时也比较了Batch Insert与Copy之间的性能差异。它们在PG日常维护与使用的场景中经常要用到。

3. Copy命令的使用

mydb=> set search_path="$user",public,mydb; SET mydb=> \d mydb | t | 数据表 | scott public | department_id_seq | 序列数 | mydb public | dept | 数据表 | mydb public | weather | 数据表 | postgres // - insert 10条记录准备 mydb=> insert into t select * from generate_series(1, 10, 1); INSERT 0 10 mydb=> copy t(id) to 'e:/t_id.dat'; ERROR: must be superuser or a member of the pg_write_server_files role to COPY to a file 提示: Anyone can COPY to stdout or from stdin. psql's \copy command also works for an yone. mydb=> \c mydb postgres 用户 postgres 的口令：您现在已经连接到数据库 "mydb",用户 "postgres". mydb=# \d public | department_id_seq | 序列数 | mydb public | dept | 数据表 | mydb public | weather | 数据表 | postgres mydb=# copy t(id) to 'e:/t_id.dat'; ERROR: relation "t" does not exist mydb=# set search_path="$user",public,mydb; SET mydb=# copy t(id) to 'e:/t_id.dat'; ERROR: relative path not allowed for COPY to file mydb=# copy t(id) to '/var/lib/pgsql/t_id.dat'; COPY 10 用的是服务器端的路径。:-(, search_path是连接级的设置

再来一个完整的示例:

mydb=# set search_path= "$user",mydb,postgres,public; SET mydb=# \d mydb | bonus | 数据表 | mydb mydb | dept | 数据表 | mydb mydb | emp | 数据表 | mydb mydb | salgrade | 数据表 | mydb mydb | t | 数据表 | scott public | weather | 数据表 | postgres mydb=# copy emp to '/export/dmp/emp.dat' CSV HEADER; COPY 14 mydb=# \! cat /export/dmp/emp.dat empno,ename,job,mgr,hiredate,sal,comm,deptno 7369,SMITH,CLERK,7902,1980-12-17,800.00,,20 7499,ALLEN,SALESMAN,7698,1981-02-20,1600.00,300.00,30 7521,WARD,SALESMAN,7698,1981-02-22,1250.00,500.00,30 7566,JONES,MANAGER,7839,1981-04-02,2975.00,,20 7654,MARTIN,SALESMAN,7698,1981-09-28,1250.00,1400.00,30 mydb=# create table emp_2 (like emp); CREATE TABLE mydb=# copy emp_2 from '/export/dmp/emp.dat' CSV HEADER; COPY 14

从上边也可以明显看到，它是直接在服务器端操作。

注意: \\! 执行的命令是psql命令行所在的host上的OS命令。这个确实有点绕。而copy to/copy from是直接执行的服务器上的路径。

grant pg_write_server_files to zdb; 这样用户zdb才有权限使用copy命令。

copy mywords(word) to '/pg/pgdata/mywords.csv'; 后边的目标路径是DB服务器上的路径。

\copy 不需要superuser的权限。与copy语句中的不同。所以普通用户建议直接使用\copy。

4.实际例子

下边再来举两个copy与\copy使用的简单例子：

mydb=> \c mydb postgres Password for user postgres: You are now connected to database "mydb" as user "postgres". mydb=# \d List of relations Schema | Name | Type | Owner --------+-------------------+----------+---------- public | a | table | postgres public | department | table | postgres public | department_id_seq | sequence | postgres public | myseq | sequence | postgres (4 rows) 新语法：'true'的引号也可以不要 mydb=# copy department to '/pgccc/tmp/department.dat' with (format 'csv', HEADER 'true'); COPY 2 旧就法： mydb=# copy department to '/pgccc/tmp/department.2.dat' csv HEADER; COPY 2 回到linux主机检查： mydb=# \! cat /pgccc/tmp/department.dat id,name 1,Marketing 2,Marketing mydb=# \! cat /pgccc/tmp/department2.dat cat: /pgccc/tmp/department2.dat: No such file or directory mydb=# \! cat /pgccc/tmp/department.2.dat id,name 1,Marketing 2,Marketing mydb=# 传到client主机上的示例： mydb=# \copy department to 'c:/logs/department.dat' csv HEADER COPY 2 mydb=# \! cat 'c:/logs/department.dat' id,name 1,Marketing 2,Marketing -- 使用copy from mydb=# create table dept(id int primary key, name varchar(100)); CREATE TABLE mydb=# \copy dept from 'c:/logs/department.dat' with (format 'csv', HEADER true); COPY 2 mydb=# select * from dept; id | name ----+----------- 1 | Marketing 2 | Marketing (2 rows)

\copy 导出与装入的例子：

mydb=# create table t(id int, col2 varchar(32), col3 varchar(32)); CREATE TABLE mydb=# insert into t values(1, 'abc,def', 'abc,aaa'); INSERT 0 1 mydb=# \copy t to '/pgccc/tmp/t.csv' csv; COPY 1 mydb-# \! cat /pgccc/tmp/t.csv 1,"abc,def","abc,aaa" mydb=# truncate table t; TRUNCATE TABLE mydb=# \copy t from '/pgccc/tmp/t.csv' csv quote '"'; COPY 1 mydb=# select * from t; id | col2 | col3 ----+---------+--------- 1 | abc,def | abc,aaa (1 row)

5. 实验环节

如可通过实验来比较insert和copy的区别？

insert 和 copy 的区别：

insert单条插入，循环次数多，与DB server端交互次数太多。(网络I/O, 解析、执行并提交) 效率上不占优。

insert每次都batch insert, 比如每次插入100条. 这样能很大的提高效率。

\copy from 操作入库，是支持事务处理的。要么全部成功，要么失败。

为何copy会如此快？

mydb=# create table t(id int, col2 varchar(32)); CREATE TABLE mydb=# \timing on Timing is on. mydb=# insert into t select n, 'test' || n from generate_series(1, 1000000) as n; INSERT 0 1000000 Time: 1812.542 ms (00:01.813) mydb=# \copy t to '/pgccc/tmp/t.dat'; COPY 1000000 Time: 267.931 ms mydb=# truncate table t; TRUNCATE TABLE Time: 5.234 ms mydb=# \copy t from '/pgccc/tmp/t.dat'; COPY 1000000 Time: 871.730 ms

上边的例子，可能看到使用\copy插入100万条数据，只需要871毫秒，而insert (bulk)需要1.8秒。中间这100万数据copy导出到客户端本地所花时间是268毫秒。

6.关于性能方面的重要参考

https://stackoverflow.com/questions/46715354/how-does-copy-work-and-why-is-it-so-much-faster-than-insert

> - Network latency and round-trip delays

> - Per-statement overheads in PostgreSQL

> - Context switches and scheduler delays

> - `COMMIT` costs, if for people doing one commit per insert (you aren't)

> - `COPY`-specific optimisations for bulk loading

开发方面的小结：

https://www.jianshu.com/p/0f4b7bc4d22c (rewriteBatchedStatements=true, 用于MySQL)

https://vladmihalcea.com/postgresql-multi-row-insert-rewritebatchedinserts-property/ (reWriteBatchedInserts=true, 用于PG)

refer to: https://jdbc.postgresql.org/documentation/use/

> - **`reWriteBatchedInserts (`\*boolean\*`)`**

> This will change batch inserts from insert into foo (col1, col2, col3) values (1, 2, 3) into insert into foo (col1, col2, col3) values (1, 2, 3), (4, 5, 6) this provides 2-3x performance improvement

在PG的开发中，使用reWriteBatchedInserts=true，可以快速提高insert性能。是因为PG能将batch的SQL语句多条变为一条来进行集中处理，节省了大量的网络I/O。

ITPub博客

PostgreSQL中的Copy(快速加载）

最新文章