SQLserver 同步至PG

首先安裝配置好tds_fdw-2.0.0-alpha.3(步驟如下)
一、安装EPEL源
[root@ty-dxbtest02 src]# rpm -ivh http://dl.fedoraproject.org/pub/epel/5/x86_64/epel-release-5-4.noarch.rpm
二、安装freetds软件
[root@ty-dxbtest02 yum.repos.d]# yum install freetds*
三、安装tds-fdw扩展模块
[root@ty-dxbtest02 tds_fdw-2.0.0-alpha.3]# cd tds_fdw/
[root@ty-dxbtest02 tds_fdw-2.0.0-alpha.3]# PATH=/usr/local/pgsql10.5/bin:$PATH make USE_PGXS=1 install
postgres=# CREATE EXTENSION tds_fdw;、

四、新建TDS_FDW服务

postgres=# create server TDS_ghanDB foreign data wrapper tds_fdw options (servername ‘108.88.3.247’, port ‘1433’, database ‘ghan’, tds_version ‘4.2’, character_set ‘UTF-8’);
CREATE SERVER
postgres=# grant usage on foreign server TDS_ghanDB to postgres;
GRANT
postgres=# create user mapping for postgres server TDS_ghanDB options (username ‘ghan123’, password ‘ghan123’);
CREATE USER MAPPING

postgres=# create foreign table test_ms (id integer, name varchar(20)) server TDS_ghanDB options (table ‘TEST’);
CREATE FOREIGN TABLE

//*注意由于MS SQL Server默认编码为GBK,而且Postgresql编码变ＵＴＦ８，需要改变编码显示为ＧＢＫ才能正常显示中文

postgres=# select * from test_ms;
NOTICE: tds_fdw: Query executed correctly
NOTICE: tds_fdw: Getting results
id | name
----±-----
1 | 系统
1 | 系统
1 | 系统
1 | 系统
1 | 系统
1 | 系统
1 | 系统
1 | 系统
1 | 系统
3 | uuu
(10 rows)

CREATE SERVER mssql_svr
FOREIGN DATA WRAPPER tds_fdw
OPTIONS (servername ‘10.76.122.140’, port ‘1433’, database ‘tds_fdw_test’);

CREATE USER MAPPING FOR postgres
SERVER mssql_svr
OPTIONS (username ‘sa’, password ‘tydbaoracle123.’);

CREATE FOREIGN TABLE mssql_table (
id integer,
data varchar)
SERVER mssql_svr
OPTIONS (table_name ‘dbo.mytable’);

本文来源https://www.modb.pro/db/42834