PostgreSQL 与 MSSQL(SQL Server) 之间 数据相互迁移、导入、导出测试

2 minute read

背景

测试表结构

create table test (id int, info text);  

从 PostgreSQL 导入 MSSQL

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.)  

从 MSSQL 不落地 导入 PostgreSQL

使用named pipe

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.)  

或者直接使用stdout, stdin:

# 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');
  
需要加入转义的过程,解决这个跨产品迁移的问题。

[<乘数科技在sqlserver迁移PostgreSQL项目中总结的文档-sqlserver转PG经验总结及PG的一些特性>](20180819_02_pdf_001.pdf)

关于转义,可能会将其数据迁移产品,与rds_dbsync一样开源出来。

参考

1、man mkfifo

2、bcp

https://docs.microsoft.com/en-us/sql/tools/bcp-utility?view=sql-server-2017

3、BULK INSERT

https://docs.microsoft.com/en-us/sql/t-sql/statements/bulk-insert-transact-sql?view=sql-server-2017

4、关于PostgreSQL的服务端copy与协议层copy(客户端copy)

《PostgreSQL 服务端COPY和客户端COPY - 暨PG有哪些服务端操作接口》

《PostgreSQL copy (quote,DELIMITER,…) single byte char 的输入》

5、《MSSQL(SQL Server) on Linux 简明部署与使用》

6、

https://stackoverflow.com/questions/2197017/can-sql-server-bulk-insert-read-from-a-named-pipe-fifo

Flag Counter

digoal’s 大量PostgreSQL文章入口