本文共 4064 字，大约阅读时间需要 13 分钟。
PostgreSQL , ms sql , SQL Server
测试表结构
create table test (id int, info text);
MS SQL bcp与BULK INSERT都不支持stdin，所以我这里使用落地到文件的方法，从PostgreSQL导入MS SQL。
1、psql写出1亿行记录到本地文件
time psql -h /tmp -p 1925 -U postgres postgres -c "copy (select id,md5(random()::text) from generate_series(1,100000000) t(id)) to stdout WITH (NULL '')" > /data01/test.out real 2m1.441s user 0m10.535s sys 0m12.536s
2、使用bcp，从本地文件批量加载到ms sql
bcp test in /data01/test.out -c -k -S localhost -U SA -P Digoal_mssql -b 65535 -a 65535 -r \\n Starting copy... 100000000 rows copied. Network packet size (bytes): 4096 Clock Time (ms.) Total : 779490 Average : (128289.0 rows per sec.)
1、创建fifo管道
mkpipe /tmp/namepipe
2、PostgreSQL，使用copy command的服务端PROGRAM调用接口，从管道读取内容，写入test表
time psql -h /tmp -p 1925 -U postgres postgres -c "copy test from PROGRAM 'cat /tmp/namepipe' WITH (NULL '')" COPY 100000103 real 4m57.212s user 0m0.002s sys 0m0.004s
3、MS SQL，使用bcp，批量导出数据，写到管道
bcp test out /tmp/namepipe -c -k -S localhost -U SA -P Digoal_mssql -b 65535 -a 65535 -r \\n Starting copy... 100000103 rows copied. Network packet size (bytes): 32576 Clock Time (ms.) Total : 301248 Average : (331952.8 rows per sec.)
# bcp test out /tmp/namepipe -c -k -S localhost -U SA -P Digoal_mssql -b 65535 -a 65535 -r \\n | psql -h /tmp -p 1925 -U postgres postgres -c "copy test from PROGRAM 'cat /tmp/namepipe' WITH (NULL '')"
1、如果mssql的导入导出都可以支持管道，使用起来会更加的方便。目前看只有导出可以支持管道，导入时使用管道会报错(使用bcp, bulk insert试过是这样的情况)。
1.1、文件内容与pipe如下
# cat /tmp/test 1 test # mkfifo /tmp/namepipe # ll /tmp/test -rw-r--r-- 1 root root 7 Aug 20 00:15 /tmp/test # ll /tmp/namepipe prwxrwxrwx 1 root root 0 Aug 20 00:07 /tmp/namepipe # cat /tmp/test|cat /dev/stdin 1 test
1.2、bcp, BULK INSERT无法正确读取来自stdin与namepipe的内容
cat test|bcp test in /dev/stdin -c -S 127.0.0.1 -U SA -P Digoal_mssql -a 65535 -r \\n Starting copy... 0 rows copied. # 正常的话这里应该是COPY 1条 Network packet size (bytes): 32576 Clock Time (ms.) Total : 1
cat /tmp/test > /tmp/namepipe|bcp test in /tmp/namepipe -c -S localhost -U SA -P Digoal_mssql -a 65535 -r \\n Starting copy... 0 rows copied. # 正常的话这里应该是COPY 1条 Network packet size (bytes): 32576 Clock Time (ms.) Total : 1
cat test|sqlcmd -S localhost -U SA -P 'Digoal_mssql' -Q "bulk insert test from '/dev/stdin'" (0 rows affected) # 正常的话这里应该是COPY 1条
# cat /tmp/test > /tmp/namepipe another session: # sqlcmd -S localhost -U SA -P 'Digoal_mssql' -Q "bulk insert test from '/tmp/namepipe'" Msg 4860, Level 16, State 1, Server iZbp13nu0s9j3x3, Line 1 Cannot bulk load. The file "/tmp/namepipe" does not exist or you don't have file access rights. # 正常的话这里应该是COPY 1条 psql -h 127.0.0.1 -p 1925 -U postgres postgres -c "copy (select generate_series(1,10000),md5(random()::text)) to stdout;" |bcp test in /dev/stdin -c -S localhost -U SA -P Digoal_mssql -a 65535 -r \\n Starting copy...BCP copy in failed# 正常的话应该导入10000条。
1.3、bcp, BULK INSERT直接从文件读取内容正常
# bcp test in /tmp/test -c -S 127.0.0.1 -U SA -P Digoal_mssql -a 65535 -r \\n Starting copy... 1 rows copied. Network packet size (bytes): 32576 Clock Time (ms.) Total : 2 Average : (500.0 rows per sec.) # sqlcmd -S localhost -U SA -P 'Digoal_mssql' -Q "bulk insert test from '/tmp/test'" (1 rows affected)
2、PostgreSQL在数据库服务对端、客户端、协议层都支持COPY协议，数据的进出都非常方便。
3、批量写入加载速度对比
MS SQL： 12.8万行/s
PostgreSQL： 33.2万行/s
4、格式问题，如果在数据内容中出现了分隔符的值，bcp的output模式并不会对其进行处理。 但是可以输出为bcp自己识别的fmt。而输出到文本后直接导入到PG并不适合。
例如这里的内容中用到了制表符、逗号等。导入到pg就存在问题。 create table test1 (id int, info text, c1 int, c2 text);insert into test1 values (null,null,null,'abc');insert into test1 values (null,null,1,'abc');insert into test1 values (1,'test , test',1,'a,b,c, d d');insert into test1 values (1,'test test test test',1,'a a'); 需要加入转义的过程，解决这个跨产品迁移的问题。
关于转义，可能会将其数据迁移产品，与rds_dbsync一样开源出来。
1、man mkfifo
2、bcp
3、BULK INSERT
4、关于PostgreSQL的服务端copy与协议层copy（客户端copy)
5、
6、
转载地址：http://czonl.baihongyu.com/