SQLServer数据实时同步至PostgreSQL

前言:

为迎合工作需求有时候传送的数据保存在SQLServer中但由于工作需要需要保存到PostgreSQL中进行处理，本文主要通过在SQLServer中设置触发器和存储过程的方式完成数据的同步

系统环境说明

软件	版本	说明
SQLServer	2008R2_X64	X64位
PostgreSQL	9.5_X64
odbc	9.5_X64

postgre_odbc下载安装

odbc主要是让PostgreSQL与SQLServer之间建立桥梁利于数据传输下载地址

PostgreSQL_odbc选择要下载的版本

SQLServer数据实时同步PostgreSQL

演示环境下载的9.5版本随本机安装Postgre数据库

下载完成解压得到Psqlodbc_x64.msi,双击运行安装默认即可

SQLServer数据实时同步PostgreSQL

安装完成后通过系统自带的ODBC数据源配置系统DNS

SQLServer数据实时同步PostgreSQL

点击添加打开需要添加的数据源，这里选择PostgreSQL ANSIx64

SQLServer数据实时同步PostgreSQL

添加PostgreSQL连接

DataSource:连接名称，后续在SQLServer中会用到

Database: 要连接的数据库

Server: Posgresql服务地址,也可以是IP

Port: 服务端口号

User Name:用户登录名

Password:登录密码

SQLServer数据实时同步PostgreSQL

输入连接参数后点击Test 测试是否成功成功后点击Save 保存即可

添加链接服务器方法1——用操作界面添加

SQLServer中添加服务器对象

打开SQLServer数据库连接，找到服务器对象->链接服务器->鼠标右键选择新建链接服务器

SQLServer数据实时同步PostgreSQL

常规

1.设置连接对象名称

2.选择访问接口，这里先连接本地所以选择如图，当添加ODBC时会有所不同

3.输入产品名称，这里随意填写（不能为null），测试单词中有空格添加失败

4.数据源名称，这里为SQL Server服务器连接IP,本地连接故以“.”代替

SQLServer数据实时同步PostgreSQL

安全性

当切换到安全性选项卡时，默认

[x] 不使用安全上下文连接（N）

这里切换到 使用此安全上下文建立连接 使用SQLServer登录用户名登陆即可

SQLServer数据实时同步PostgreSQL

服务器选项

将RPC 设置为 True 默认为False

将RPC Out 设置为 True 默认为False

将为RPC 启动针对分布式事务升级设置为 false 默认为 true

SQLServer数据实时同步PostgreSQL

设置完成后单击确定即可完成设置

添加PostgreSQL 连接服务器

添加PostgreSQL 链接服务器与 SQLServer 步骤类似

区别在于：

访问接口-> Microsoft OLE DB　Provider for ODBC Drivers

数据源 -> 为ODBC链接对象DataSource 名称

SQLServer数据实时同步PostgreSQL

安全性输入填写 PostgreSQL 的登陆账号、密码即可

服务器选项相同

添加链接服务器方法2————用T-SQL命令添加

use master

go

/****** Object:  LinkedServer [LOCALHOSTSQL]

   判断是否存在 LOCALHOSTSQL 名称的LinkedServer 如果有则删除

******/

IF  EXISTS (SELECT srv.name FROM sys.servers srv WHERE srv.server_id != 0 AND srv.name = N'LOCALHOSTSQL')EXEC master.dbo.sp_dropserver @server=N'LOCALHOSTSQL', @droplogins='droplogins'

GO

/****** Object:  LinkedServer [LOCALHOSTSQL]

添加本地链接 调用存储过程 master.dbo.sp_addlinkedserver

******/

EXEC master.dbo.sp_addlinkedserver

@server = N'LOCALHOSTSQL',	--链接服务器

@srvproduct=N'SQlServer',	--产品名称

@provider=N'SQLNCLI',		--访问接口

@datasrc=N'.\SQL08R2'		--数据源

 /*

 安全性添加  调用存储过程 master.dbo.sp_addlinkedsrvlogin

  */

EXEC master.dbo.sp_addlinkedsrvlogin

@rmtsrvname=N'LOCALHOSTSQL',	--链接服务器

@useself=N'False',				--

@locallogin=NULL,				--本地登陆

@rmtuser=N'sa',					--远程登陆用户

@rmtpassword='########'			--远程登陆密码 改成实际用户名密码

GO

 /*

 服务器选项  调用存储过程 master.dbo.sp_serveroption 这里服务器选项操作很多，这里只选择需要的配置，其他为默认选项

  */

EXEC master.dbo.sp_serveroption

@server=N'LOCALHOSTSQL',	--链接服务器

@optname=N'rpc out',		--操作 rpc out选项

@optvalue=N'true'			--选项值

GO

EXEC master.dbo.sp_serveroption

@server=N'LOCALHOSTSQL',	--链接服务器

@optname=N'rpc',		    --操作rpc选项

@optvalue=N'true'			--选项值

GO

EXEC master.dbo.sp_serveroption

@server=N'LOCALHOSTSQL',

@optname=N'remote proc transaction promotion',  --rpc 事务选项

@optvalue=N'false'

GO

/*

判断是否有 名称为 POSTGRESQL 的链接服务器 如果有则删除

*/

IF  EXISTS (SELECT srv.name FROM sys.servers srv WHERE srv.server_id != 0 AND srv.name = N'POSTGRESQL')EXEC master.dbo.sp_dropserver @server=N'POSTGRESQL', @droplogins='droplogins'

GO

/****** 常规 ******/

EXEC master.dbo.sp_addlinkedserver

@server = N'POSTGRESQL',	--链接服务器

@srvproduct=N'PostgreSQL',	--产品名称

@provider=N'MSDASQL',		--驱动

@datasrc=N'PostgreSQL95'	--数据源

 /* POSTGRESQL 安全性配置 */

EXEC master.dbo.sp_addlinkedsrvlogin

@rmtsrvname=N'POSTGRESQL',	--链接服务器

@useself=N'False',			--

@locallogin=NULL,			--本地登陆

@rmtuser=N'postgres',		--登陆账号

@rmtpassword='########'		--登陆密码 改为实际密码

GO

/*服务器选项*/

EXEC master.dbo.sp_serveroption

@server=N'POSTGRESQL',		--链接服务器

@optname=N'rpc out',		--操作选项

@optvalue=N'true'			--选项值

GO

EXEC master.dbo.sp_serveroption

@server=N'POSTGRESQL',		--链接服务器

@optname=N'rpc',		--操作选项

@optvalue=N'true'			--选项值

GO

EXEC master.dbo.sp_serveroption

@server=N'POSTGRESQL',

@optname=N'remote proc transaction promotion',

@optvalue=N'false'

GO

利用T-SQL添加数据源链接服务器名称可小写大小写混合，利用窗口添加链接服务器名称默认大写。未找到更好兼容解决方案，看个人习惯选择即可

检查链接服务器是否正常显示数据源，展开刚添加的数据源对象，查看目录下是否有链接数据库名称

SQLServer数据实时同步PostgreSQL

准备测试数据结构

在SQLServer Books数据库中新建书单信息表、并添加测试数据

use Books

go

--判断是否有存在表

if OBJECT_ID('dbo.books','U') is not null drop table dbo.books

go

--创建表存储

create table books(

id int identity(1,1) primary key,

name varchar(150) not null,

price float not null,

stock int not null

)

go

--添加数据

insert into books(name,price,stock)values

('Access入门实战',49.5,999),

('T-SQL性能调优秘笈',49.0,999),

('.NET MVC5 高级变成',79.8,999),

('Python 入门实战',89.00,999);

--检查添加数据

select * from books;

postgreSQL中添加同结构数据表

编写存储过程

use Books

GO

if OBJECT_ID('Insert_Books','P') is not null drop procedure dbo.Insert_Books

go

--添加插入存储过程

CREATE PROCEDURE Insert_Books

	@name varchar(100),@price float,@stock int

AS

BEGIN

	SET NOCOUNT ON;

		insert openquery(POSTGRESQL,'select name,price,stock from books where 1=0')(name,price,stock) values

		(@name,@price,@stock);

	SET NOCOUNT ON;

END

GO

if OBJECT_ID('update_Books','P') is not null drop procedure dbo.update_Books

go

添加触发器

--创建添加触发器

CREATE TRIGGER insert_trigger

   ON  Books.dbo.books

   AFTER INSERT

AS

BEGIN

	declare @name varchar(150),@price float,@stock int

	select @name=name,@price=price,@stock=stock from inserted

	SET NOCOUNT ON;

	exec LOCALHOSTSQL.[books].[dbo].[Insert_Books] @name,@price,@stock

    -- Insert statements for trigger here

END

测试效果

同步SQLServer 数据库中的数据至PostgreSQL

insert openquery(POSTGRESQL,'select name,price,stock from books where 1=0')

select name,price,stock from books

SQLServer数据实时同步PostgreSQL

postgresql数据中

SQLServer数据实时同步PostgreSQL

测试添加数据

insert into books(name,price,stock) values('代码整洁之道',56.3,623)

select * from books

SQLServer数据实时同步PostgreSQL

可以看到当在SQLServer中数据后，PostgreSQL数据库中的数据也随之增加了，证明此方法测试运行成功

遇到的问题:

1.无法执行该操作，因为链接服务器 "XXX" 的 OLE DB 访问接口 "SQLNCLI10" 无法启动分布式事务。

在组件服务中->本地DTC->属性->安全配置

SQLServer数据实时同步PostgreSQL

重启msdtc 服务 net start msdtc net stop msdtc

SQLServer数据实时同步PostgreSQL

2.Microsoft 分布式事务处理协调器(MS DTC)已停止此事务。

检查连接服务器配置，rpc、rpc out 、rpc 分布式事务连接

秒客网

SQLServer数据实时同步PostgreSQL

SQLServer数据实时同步至PostgreSQL

postgre_odbc下载安装

添加链接服务器方法1——用操作界面添加

常规

安全性

服务器选项

添加PostgreSQL 连接服务器

添加链接服务器方法2————用T-SQL命令添加

准备测试数据结构

编写存储过程

添加触发器

测试效果

遇到的问题:

相关文章