Postgres 数据库转移到 MSSQL 2012 手记

Postgres数据库数据转移到 MSSQL 2012


刚从澳门一家酒店做了个酒店系统数据转移，从Postgres数据库里转移数据到我们的威望酒店管理系统（Microwin Property Management System）的MS SQL 2012里，在这里记录下步骤，以便以后用，分析对方的数据结构是件痛苦的事情，在转移数据花时间最多的步骤。




环境参数： Postgres数据库安装在Linux下，Postgres版本是７．４， 数据库的大小有5G左右，最大一个数据表的行数超过400万行。MSSQL 2012 安装在Windows 2008 server 下，繁体系统，我做转移的系统是Windows XP SP3 简体系统，为什么要说我做转移时是简体系统呢，因为在做转移时如果没有选择好ODBC的Driver会出现乱码，


因为只有3个小时的时间做转换数据，所以前期准备工作要做好，保证转移过来的数据要正确，而不能影响正常的工作。


前提准备连接PostgreSQL 数据库：


Windows怎么连接到Linux下的Postgres数据库上，直接从官网上下载PSQLODBC安装到Windows上用ODBC方式连接到Linux下的Postgres数据库上，
这样的连接数据库就解决了，安装PSQLODBC后会在ODBC数据源管理器里有两个驱动程式，PostgreSQL ANSI 和 PostgreSQL Unicode ，刚开始我是用PostgreSQL Unicode这个来连接到PostgreSQL 的，但发现读出来的数据中文会乱码，在繁体XP里也是乱码，但用PostgreSQL ANSI这个繁体中文就没有问题，但简体中文又出现乱码，最后只能繁体中文和简体中文分别转移了。




第一种备份恢复方法：
在我的XP上安装从Postgres 官网上下载下来的Windows 版本的 Postgres 8.4 ,恢复从Linux下备份的Postgres数据库来做转移数据，但恢复过来的数据不但中文是乱码，重要的是大表的数据(超过100万级的数据行）没有恢复进来，到现在也没有搞清楚这是什么问题，所以就放弃这个方法。

第二种直连方法：
直接用MSSQL的sp_addlinkedserver 连接到PostgreSQL ，真接读取数据到暂时数据库，再做数据转换到我们的威望酒店系统，当时想是服务器对服务器直接读写应该很快，但试后发现很慢，远远超过5小时间，所以这种方法也放弃了，如果数据少的话这种方法是可以用的。


sp_addLinkedserver方法使用如下：

先建立一个能读取PostgreSQL 数据库的用户和密码一样的用户在MSSQL 数据库上。

sp_dropserver 'sourceDB'

EXEC master.dbo.sp_addlinkedserver @server = N'sourceDB', @srvproduct=N'PostgreSQL ANSI', 
   @provider=N'MSDASQL', @provstr=N'Driver=PostgreSQL ANSI;uid=username;pwd=password;Server=ipaddress;database=databasename;'


exec sp_droplinkedsrvlogin @rmtsrvname = 'sourceDB' ,    @locallogin =null


/* For security reasons the linked server remote logins password is changed with ######## */
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'sourcdDB', @useself=N'True',@locallogin=NULL,@rmtuser='username',@rmtpassword='password'


--在MSSQL上读取PostgreSQL那边的数据表
SELECT * FROM OpenQuery(sourceDB, 'SELECT * From information_schema.tables')

--在MSSQL上读取PostgreSQL那边的指定数据表的数据
select * from "sourceDB"."databasename"."public"."tablename"




第三种DTS方法（分两大步）：
第一步： 在MSSQL建立一下新的数据库，用DTS（也就是MSSQL提供的导出/导入数据服务）把Postgres数据库全部数据转移到新的MSSQL数据库里，在选择源数据库时最好用PostgreSQL ANSI ODBC驱动程式，否则会出现中文乱码，但奇怪是的，MSSQL 2012 提供的DTS不能读取Postgres的数据结构，但MSSQL 2000提供的DTS是可以的，所以说有时不是版本越高就越好，能用才是好工具啊，所以我就用2000提供的DTS工具想把PostgreSQL数据库的数据先转移到mssql 2012 ,但在转移大数据表的时候会出现 Out of memory 信息，但我的XP已经是 4G 内存了，32位的系统最大了，但还是不能转移大数据表的数据，我发现数据行大行50万以上就会出现out of memory 信息，所以最后只能编程方法来把数据分成30万行左右一次转移，用DTS保存下来的DTS包，用VB6编程把数据提取30万行左右一次一次转移大数据表，其它少于30万的数据直接用DTS来转移，做好程式安装到繁体和简体系统电脑里分别进行，如果可以的话安装多台机器来运行会更快点，这个方法我用1个半小时就转移完我要的数据到MSSQL的暂时数据库了。

第二步： 用了数据在同一个服务器后，读取就方便和快捷了，在新的MSSQL数据库里再根据数据结构用T-SQL提取数据需要的数据到我们的酒店系统的数据里，由于两个数据库的数据表结构不一样所以这步在同一个SQL服务器里的不同数据库做比较方便，根据数据结构不同写不同的SQL语句进行读取及写入。这个步骤大概也花了一小时间左右，当然根据服务的性能不同时间也会不同的。



结论： 我经常要转移数据到我们的威望酒店系统（Microwin Property Management System)，所以也经常做不同数据库间的转移数据， 以前大部分是同类型的数据库转移，这次是不同数据库和不同系统所以做起来比较麻烦，所以记下来以后参考，大家用更好方法来信分享一下。