PostgreSQL外部表应用

来源：互联网发布：去男朋友的城市知乎编辑：程序博客网时间：2024/06/06 17:54

Postgresql连接查询 MS SQL Server

简介

PostgreSQL从9.x开始支持所谓的外表的功能,就是在PostgreSQL中通过安装一些扩展再进行一些配置可以在本地建立一个外表映射到其他不同类型的数据库。
今天我们介绍一下在PostgreSQL中通过tds_fdw扩展来映射Sqlserver数据库的方法

环境

Sqlserver：

操作系统：Windows 8数据库系统： MS SQL Server 2012IP： 192.168.3.215port: 1433

PostgreSQL:

操作系统：CentOS Linux release 7.3.1611 (Core)数据库系统： PostgreSQL 9.5.8IP： 192.168.230.134port: 5432

PostgreSQL端插件安装

安装EPEL repository

sudo yum install epel-release

输出：

[root@localhost ~]# sudo yum install epel-releaseLoaded plugins: fastestmirror, langpacksbase                                                             | 3.6 kB  00:00:00     extras                                                           | 3.4 kB  00:00:00     pgdg95                                                           | 4.1 kB  00:00:00     updates                                                          | 3.4 kB  00:00:00     pgdg95/7/x86_64/primary_db                                       | 163 kB  00:00:02     Loading mirror speeds from cached hostfile * base: mirrors.sohu.com * extras: mirrors.163.com * updates: mirrors.163.comResolving Dependencies--> Running transaction check---> Package epel-release.noarch 0:7-9 will be installed--> Finished Dependency ResolutionDependencies Resolved======================================================================================== Package                  Arch               Version           Repository          Size========================================================================================Installing: epel-release             noarch             7-9               extras              14 kTransaction Summary========================================================================================Install  1 PackageTotal download size: 14 kInstalled size: 24 kIs this ok [y/d/N]: yDownloading packages:epel-release-7-9.noarch.rpm                                      |  14 kB  00:00:00     Running transaction checkRunning transaction testTransaction test succeededRunning transaction  Installing : epel-release-7-9.noarch                                              1/1   Verifying  : epel-release-7-9.noarch                                              1/1 Installed:  epel-release.noarch 0:7-9                                                             Complete!

安装FreeTDS

sudo yum install freetds freetds-devel

输出：

[root@localhost ~]# sudo yum install freetds freetds-develLoaded plugins: fastestmirror, langpacksepel/x86_64/metalink                                             | 6.5 kB  00:00:00     epel                                                             | 4.3 kB  00:00:00     (1/3): epel/x86_64/group_gz                                      | 170 kB  00:00:00     (2/3): epel/x86_64/updateinfo                                    | 817 kB  00:00:01     (3/3): epel/x86_64/primary_db                                    | 4.8 MB  00:00:01     Loading mirror speeds from cached hostfile * base: mirrors.sohu.com * epel: mirrors.ustc.edu.cn * extras: mirrors.163.com * updates: mirrors.163.comResolving Dependencies--> Running transaction check---> Package freetds.x86_64 0:0.95.81-1.el7 will be installed--> Processing Dependency: libodbcinst.so.2()(64bit) for package: freetds-0.95.81-1.el7.x86_64--> Processing Dependency: libodbc.so.2()(64bit) for package: freetds-0.95.81-1.el7.x86_64---> Package freetds-devel.x86_64 0:0.95.81-1.el7 will be installed--> Running transaction check---> Package unixODBC.x86_64 0:2.3.1-11.el7 will be installed--> Finished Dependency ResolutionDependencies Resolved======================================================================================== Package                 Arch             Version                  Repository      Size========================================================================================Installing: freetds                 x86_64           0.95.81-1.el7            epel           635 k freetds-devel           x86_64           0.95.81-1.el7            epel            41 kInstalling for dependencies: unixODBC                x86_64           2.3.1-11.el7             base           413 kTransaction Summary========================================================================================Install  2 Packages (+1 Dependent package)Total download size: 1.1 MInstalled size: 4.0 MIs this ok [y/d/N]: yDownloading packages:warning: /var/cache/yum/x86_64/7/epel/packages/freetds-0.95.81-1.el7.x86_64.rpm: Header V3 RSA/SHA256 Signature, key ID 352c64e5: NOKEYPublic key for freetds-0.95.81-1.el7.x86_64.rpm is not installed(1/3): freetds-0.95.81-1.el7.x86_64.rpm                          | 635 kB  00:00:00     (2/3): freetds-devel-0.95.81-1.el7.x86_64.rpm                    |  41 kB  00:00:00     (3/3): unixODBC-2.3.1-11.el7.x86_64.rpm                          | 413 kB  00:00:13     ----------------------------------------------------------------------------------------Total                                                       75 kB/s | 1.1 MB  00:14     Retrieving key from file:///etc/pki/rpm-gpg/RPM-GPG-KEY-EPEL-7Importing GPG key 0x352C64E5: Userid     : "Fedora EPEL (7) <epel@fedoraproject.org>" Fingerprint: 91e9 7d7c 4a5e 96f1 7f3e 888f 6a2f aea2 352c 64e5 Package    : epel-release-7-9.noarch (@extras) From       : /etc/pki/rpm-gpg/RPM-GPG-KEY-EPEL-7Is this ok [y/N]: yRunning transaction checkRunning transaction testTransaction test succeededRunning transaction  Installing : unixODBC-2.3.1-11.el7.x86_64                                         1/3   Installing : freetds-0.95.81-1.el7.x86_64                                         2/3   Installing : freetds-devel-0.95.81-1.el7.x86_64                                   3/3   Verifying  : freetds-devel-0.95.81-1.el7.x86_64                                   1/3   Verifying  : unixODBC-2.3.1-11.el7.x86_64                                         2/3   Verifying  : freetds-0.95.81-1.el7.x86_64                                         3/3 Installed:  freetds.x86_64 0:0.95.81-1.el7          freetds-devel.x86_64 0:0.95.81-1.el7         Dependency Installed:  unixODBC.x86_64 0:2.3.1-11.el7                                                        Complete!

安装PostgreSQL开发包（如已安装可不执行）

sudo yum install postgresql95 postgresql95-server postgresql95-libs postgresql95-devel

安装git(如已安装可不执行)

sudo yum install git

PostgreSQL安装 tds_fdw

通过git工具下载安装包并开始安装

git clone https://github.com/tds-fdw/tds_fdw.gitcd tds_fdwPATH=/usr/pgsql-9.5/bin:$PATH make USE_PGXS=1sudo PATH=/usr/pgsql-9.5/bin:$PATH make USE_PGXS=1 install

输出：

[root@localhost ~]# git clone https://github.com/tds-fdw/tds_fdw.gitCloning into 'tds_fdw'...remote: Counting objects: 827, done.remote: Total 827 (delta 0), reused 0 (delta 0), pack-reused 827Receiving objects: 100% (827/827), 347.64 KiB | 38.00 KiB/s, done.Resolving deltas: 100% (491/491), done.[root@localhost ~]# cd tds_fdw[root@localhost tds_fdw]# PATH=/usr/pgsql-9.5/bin:$PATH make USE_PGXS=1gcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -O2 -g -pipe -Wall -Wp,-D_FORTIFY_SOURCE=2 -fexceptions -fstack-protector-strong --param=ssp-buffer-size=4 -grecord-gcc-switches -m64 -mtune=generic -DLINUX_OOM_ADJ=0 -fPIC -I./include/ -fvisibility=hidden -I. -I./ -I/usr/pgsql-9.5/include/server -I/usr/pgsql-9.5/include/internal -D_GNU_SOURCE -I/usr/include/libxml2  -I/usr/include  -c -o src/tds_fdw.o src/tds_fdw.cgcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -O2 -g -pipe -Wall -Wp,-D_FORTIFY_SOURCE=2 -fexceptions -fstack-protector-strong --param=ssp-buffer-size=4 -grecord-gcc-switches -m64 -mtune=generic -DLINUX_OOM_ADJ=0 -fPIC -I./include/ -fvisibility=hidden -I. -I./ -I/usr/pgsql-9.5/include/server -I/usr/pgsql-9.5/include/internal -D_GNU_SOURCE -I/usr/include/libxml2  -I/usr/include  -c -o src/options.o src/options.cgcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -O2 -g -pipe -Wall -Wp,-D_FORTIFY_SOURCE=2 -fexceptions -fstack-protector-strong --param=ssp-buffer-size=4 -grecord-gcc-switches -m64 -mtune=generic -DLINUX_OOM_ADJ=0 -fPIC -I./include/ -fvisibility=hidden -I. -I./ -I/usr/pgsql-9.5/include/server -I/usr/pgsql-9.5/include/internal -D_GNU_SOURCE -I/usr/include/libxml2  -I/usr/include  -c -o src/deparse.o src/deparse.cgcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -O2 -g -pipe -Wall -Wp,-D_FORTIFY_SOURCE=2 -fexceptions -fstack-protector-strong --param=ssp-buffer-size=4 -grecord-gcc-switches -m64 -mtune=generic -DLINUX_OOM_ADJ=0 -fPIC -shared -o tds_fdw.so src/tds_fdw.o src/options.o src/deparse.o -L/usr/pgsql-9.5/lib -Wl,--as-needed  -L/usr/lib64 -Wl,--as-needed -Wl,-rpath,'/usr/pgsql-9.5/lib',--enable-new-dtags  -lsybdb cp sql/tds_fdw.sql sql/tds_fdw--2.0.0-alpha.1.sqlcp README.md README.tds_fdw.md[root@localhost tds_fdw]# sudo PATH=/usr/pgsql-9.5/bin:$PATH make USE_PGXS=1 install/usr/bin/mkdir -p '/usr/pgsql-9.5/lib'/usr/bin/mkdir -p '/usr/pgsql-9.5/share/extension'/usr/bin/mkdir -p '/usr/pgsql-9.5/share/extension'/usr/bin/mkdir -p '/usr/pgsql-9.5/doc/extension'/usr/bin/install -c -m 755  tds_fdw.so '/usr/pgsql-9.5/lib/tds_fdw.so'/usr/bin/install -c -m 644 .//tds_fdw.control '/usr/pgsql-9.5/share/extension/'/usr/bin/install -c -m 644 .//sql/tds_fdw--2.0.0-alpha.1.sql  '/usr/pgsql-9.5/share/extension/'/usr/bin/install -c -m 644 .//README.tds_fdw.md '/usr/pgsql-9.5/doc/extension/'

安装拓展包

/usr/pgsql-9.5/bin/psql -U postgrespostgres=# CREATE EXTENSION tds_fdw;

输出：

[root@localhost tds_fdw]# /usr/pgsql-9.5/bin/psql -U postgrespsql (9.5.9)Type "help" for help.postgres=# CREATE EXTENSION tds_fdw;CREATE EXTENSION

创建包装类

在PostgreSQL中执行

CREATE SERVER mssql_svr    FOREIGN DATA WRAPPER tds_fdw    OPTIONS (servername '192.168.3.215', port '1433', database 'CRSDB', tds_version '7.1');

输入：

postgres=# CREATE SERVER mssql_svrpostgres-# FOREIGN DATA WRAPPER tds_fdwpostgres-# OPTIONS (servername '192.168.3.215', port '1433', database 'CRSDB', tds_version '7.1');CREATE SERVER

创建映射

在PostgreSQL中执行

CREATE USER MAPPING FOR postgres    SERVER mssql_svr     OPTIONS (username 'sa', password 'oracle');

输入：

postgres=# CREATE USER MAPPING FOR postgrespostgres-# SERVER mssql_svr postgres-# OPTIONS (username 'sa', password 'oracle');CREATE USER MAPPING

按schema建立外部表

将SQL Server的dbo映射到PostgreSQL的public中

IMPORT FOREIGN SCHEMA dbo    FROM SERVER mssql_svr    INTO public    OPTIONS (import_default 'true');

输入：

postgres=# IMPORT FOREIGN SCHEMA dbopostgres-# FROM SERVER mssql_svrpostgres-# INTO publicpostgres-# OPTIONS (import_default 'true');IMPORT FOREIGN SCHEMA

外部表的使用

本实验事先在SQL Server数据库的dbo模式中建立了一个student表用来做测试
经过上面的操作后，此student表被映射到了PostgreSQL数据库的public模式中，且
表中数据与SQL Server中的对应表实时同步

尝试查看student表数据：

postgres=# SELECT count(*) FROM public.student;

输入：

postgres=# SELECT count(*) FROM public.student;NOTICE:  tds_fdw: Query executed correctlyNOTICE:  tds_fdw: Getting resultsWARNING:  Table definition mismatch: Foreign source has column named , but target table does not. Column will be ignored.WARNING:  Table definition mismatch: Could not match local column sno with column from foreign tableWARNING:  Table definition mismatch: Could not match local column sname with column from foreign table count  -------- 999901(1 row)

库迁移

由于旧库表结构及新库设计未提供，暂时只演示以student表为示例的执行方案：

根据外部表student创建新表new_student

create table public.new_student(id int,name varchar); insert into public.new_student select sno,sname from public.student;

输入：

postgres=# create table public.new_student(id int,name varchar);CREATE TABLEpostgres=# insert into public.new_student select sno,sname from public.student;NOTICE:  tds_fdw: Query executed correctlyNOTICE:  tds_fdw: Getting resultsINSERT 0 999901

==100w条数据，耗时5s，传输速度非常快==

附录

student表建表语句：

create table student(sno int ,sname VARCHAR(200));DECLARE @LN VARCHAR(300),@MN VARCHAR(200),@FN VARCHAR(200)DECLARE @LN_N INT,@MN_N INT,@FN_N INTSET @LN='李王张刘陈杨黄赵周吴徐孙朱马胡郭林何高梁郑罗宋谢唐韩曹许邓萧冯曾程蔡彭潘袁于董余苏叶吕魏蒋田杜丁沈姜范江傅钟卢汪戴崔任陆廖姚方金邱夏谭韦贾邹石熊孟秦阎薛侯雷白龙段郝孔邵史毛常万顾赖武康贺严尹钱施牛洪龚'SET @MN='德绍宗邦裕傅家積善昌世贻维孝友继绪定呈祥大正启仕执必定仲元魁家生先泽远永盛在人为任伐风树秀文光谨潭棰'SET @FN='丽云峰磊亮宏红洪量良梁良粮靓七旗奇琪谋牟弭米密祢磊类蕾肋庆情清青兴幸星刑'SET @LN_N=LEN(@LN)SET @MN_N=LEN(@MN)SET @FN_N=LEN(@FN)DECLARE @TMP VARCHAR(1000),@I INTSET @I=100WHILE @I<1000000BEGIN    SET @TMP=CAST(SUBSTRING(@LN,CAST(RAND()*@LN_N AS INT),1) AS VARCHAR)    SET @TMP=@TMP+CAST(SUBSTRING(@MN,CAST(RAND()*@MN_N AS INT),1) AS VARCHAR)    SET @TMP=@TMP+CAST(SUBSTRING(@FN,CAST(RAND()*@FN_N AS INT),1) AS VARCHAR)    INSERT INTO student(sno,sname)VALUES('2005'+@I,@TMP)    SET @I=@I+1end

SQL Server数据在PostgreSQL中对应的数据类型：

MS SQL Server PostgreSQL int int4 bigint int8 binary bytea bit int2 char char date date datetime timestamp datetime2 timestamp datetimeoffset timestamptz decimal numeric float float8 geography text geometry text hierarchyid text image bytea money money nchar char ntext text numeric numeric nvarchar varchar real float4 smalldatetime timestamp smallint int2 smallmoney money sql_variant text sysname varchar text text time time timestamp bytea tinyint int2 uniqueidentifier text varbinary bytea varchar varchar xml xml nvarchar(MAX) text varbinary(MAX) bytea varchar(MAX) text

阅读全文

0 0