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

标签

PostgreSQL , ms sql , SQL Server


背景

测试表结构

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的一些特性>

关于转义,可能会将其数据迁移产品,与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

  • 0
    点赞
  • 0
    评论
  • 1
    收藏
  • 一键三连
    一键三连
  • 扫一扫,分享海报

oracle学习文档 笔记 全面 深刻 详细 通俗易懂 doc word格式 清晰 第一章 Oracle入门 一、 数据库概述 数据库(Database)是按照数据结构来组织、存储和管理数据的仓库,它产生于距今五十年前。简单来说是本身可视为电子化的文件柜——存储电子文件的处所,用户可以对文件中的数据运行新增、截取、更新、删除等操作。 常见的数据模型 1. 层次结构模型: 层次结构模型实质上是一种有根结点的定向有序树,IMS(Information Manage-mentSystem)是其典型代表。 2. 网状结构模型:按照网状数据结构建立的数据库系统称为网状数据库系统,其典型代表是DBTG(Data Base Task Group)。 3. 关系结构模型:关系式数据结构把一些复杂的数据结构归结为简单的二元关系(即二维表格形式)。常见的有Oracle、mssql、mysql等 二、 主流数据库 数据库名 公司 特点 工作环境 mssql 微软 只能能运行在windows平台,体积比较庞大,占用许多系统资源, 但使用很方便,支持命令和图形化管理,收费。 中型企业 Mysql 甲骨文 是个开源的数据库server,可运行在多种平台, 特点是响应速度特别快,主要面向中小企业 中小型企业 PostgreSQL 号称“世界上最先进的开源数据库“,可以运行在多种平台下,是tb级数据库,而且性能也很好 中大型企业 oracle 甲骨文 获得最高认证级别的ISO标准安全认证,性能最高, 保持开放平台下的TPC-D和TPC-C的世界记录。但价格不菲 大型企业 db2 IBM DB2在企业级的应用最为广泛, 在全球的500家最大的企业中,几乎85%以上用DB2数据库服务器。收费 大型企业 Access 微软 Access是一种桌面数据库,只适合数据量少的应用,在处理少量 数据和单机访问的数据库时是很好的,效率也很高 小型企业 三、 Oracle数据库概述 ORACLE数据库系统是美国ORACLE公司(甲骨文)提供的以分布式数据库为核心的一组软件产品,是目前最流行的客户/服务器(CLIENT/SERVER)或B/S体系结构的数据库之一。  拉里•埃里森  就业前景 从就业与择业的角度来讲,计算机相关专业的大学生从事oracle方面的技术是职业发展中的最佳选择。 其一、就业面广:全球前100强企业99家都在使用ORACLE相关技术,中国政府机构,大中型企事业单位都能有ORACLE技术的工程师岗位。 其二、技术层次深:如果期望进入IT服务或者产品公司(类似毕博、DELL、IBM等),Oracle技术能够帮助提高就业的深度。 其三、职业方向多:Oracle数据库管理方向、Oracle开发及系统架构方向、Oracle数据建模数据仓库等方向。 四、 如何学习 认真听课、多思考问题、多动手操作、有问题一定要问、多参与讨论、多帮组同学 五、 体系结构 oracle的体系很庞大,要学习它,首先要了解oracle的框架。oracle的框架主要由物理结构、逻辑结构、内存分配、后台进程、oracle例程、系统改变号 (System Change Number)组成  物理结构 物理结构包含三种数据文件: 1) 控制文件 2) 数据文件 3) 在线重做日志文件  逻辑结构 功能:数据库如何使用物理空间 组成:表空间、段、区、块的组成层次 六、 oracle安装、卸载和启动  硬件要求 物理内存:1GB 可用物理内存:50M 交换空间大小:3.25GB 硬盘空间:10GB  安装 1. 安装程序成功下载,将会得到如下2个文件: 解压文件将得到database文件夹,文件组织如下: 点击setup.exe执行安装程序,开始安装。 2. 点击安装程序将会出现如下安装界面,步骤 1/9:配置安全更新 填写电子邮件地址(可以不填),去掉复选框,点击下一步 3. 步骤2/9:选择安装选项 勾选第一个,安装和配置数据库,点击下一步 4. 步骤3/8:选择系统类 勾选第一个:桌面类,点击下一步 5. 步骤4/8:配置数据库安装 选择安装路径,选择数据库版本(企业版),选择字符集(默认值) 填写全局数据库名,管理口令 6. 步骤5/8:先决条件检查 如果你的电脑满足要求但仍然显示检查失败,这时候直接忽略,勾选全部忽略 7. 步骤6/8:概要信息 核对将要安装数据的详细信息,并保存响应文件,以备以后查看。然后点击完成数据库安装 8. 步骤7/8:安装产品 产品安装过程中将会出现以上2个界面 9. 步骤8/8:完成安装  卸载Oracle 1. 在运行services.msc打开服务,停止Oracle的所
©️2021 CSDN 皮肤主题: 大白 设计师:CSDN官方博客 返回首页
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、C币套餐、付费专栏及课程。

余额充值