PostgreSQL集群和复制fdw

举报
xcc-2022 发表于 2022/07/04 21:17:41 2022/07/04
【摘要】 oracle_fdw概述oracle_fdw 是一种postgresql外部表插件,可以读取到Oracle上面的数据。是一种非常方便且常见的pg与Oracle的同步数据的方法配置Oracle环境Oracle_fdw 的编译依赖系统中需要有pg_config和Oracle的环境1.下载instant oracle client从oralce官网下载 ‘Basic’ and ‘SDK’下载网址:...

oracle_fdw概述

oracle_fdw 是一种postgresql外部表插件,可以读取到Oracle上面的数据。是一种非常方便且常见的pg与Oracle的同步数据的方法

配置Oracle环境

Oracle_fdw 的编译依赖系统中需要有pg_config和Oracle的环境

1.下载instant oracle client

从oralce官网下载 ‘Basic’ and ‘SDK’
下载网址:https://www.oracle.com/database/technologies/instant-client/linux-x86-64-downloads.html

在这里插入图片描述

下载如下两个压缩包:

instantclient-basic-linux.x64-11.2.0.4.0.zip

instantclient-sdk-linux.x64-11.2.0.4.0.zip

oracle_fdw依赖上述两个包,包里面的内容可以在oracle下载页查看,如下

Instant Client Package - Basic: All files required to run OCI, OCCI, and JDBC-OCI applications

Instant Client Package - SDK: Additional header files and an example makefile for developing Oracle applications with Instant Client

oracle_fdw下载地址 [https://pgxn.org/dist/oracle_fdw/](# https://pgxn.org/dist/oracle_fdw/)

oracle_fdw官方(https://github.com/laurenz/oracle_fdw#5-installation-requirements)介绍里面有以下对依赖包的描述:

You need to install Oracle’s C header files as well (SDK package for Instant Client). If you use the Instant Client ZIP files provided by Oracle, you will have to create a symbolic link from libclntsh.so to the actual shared library file yourself.

从官网下载 instantclient-basic-linux.x64-12.2.0.1.0.zip、 instantclient-sdk-linux.x64-12.2.0.1.0.zip、 instantclient-sqlplus-linux.x64-12.2.0.1.0.zip三个文件包,并放到/opt/oracle/目录下

mv instantclient-basic-linux.x64-12.2.0.1.0.zip instantclient-sdk-linux.x64-12.2.0.1.0.zip instantclient-sqlplus-linux.x64-12.2.0.1.0.zip  /opt/oracle/

2.安装instant oracle client

# mkdir /usr/local/oracle
# unzip -d /usr/local/oracle instantclient-basic-linux.x64-11.2.0.4.0.zip
# unzip -d /usr/local/oracle instantclient-sdk-linux.x64-11.2.0.4.0.zip
解压三个文件包
unzip instantclient-basic-linux.x64-12.2.0.1.0.zip
unzip instantclient-sdk-linux.x64-12.2.0.1.0.zip
unzip instantclient-sqlplus-linux.x64-12.2.0.1.0.zip

解压后会生成instantclient_12_2 目录,将其更名为instantclient
mv instantclient_12_2 instantclient

3.下载并上传oracle_fdw

下载网址:https://github.com/laurenz/oracle_fdw/releases

4.配置环境变量(主要是动态链接库)

在root和postgres下最好都配置好这个环境
export ORACLE_HOME=/opt/oracle/instantclient
export OCI_LIB_DIR=$ORACLE_HOME
export OCI_INC_DIR=$ORACLE_HOME/sdk/include
export LD_LIBRARY_PATH=$ORACLE_HOME:$LD_LIBRARY_PATH
make
[root@pg01 oracle_fdw-2.0.0]# make
gcc -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 -fPIC -I/sdk/include -I/oci/include -I/rdbms/public -I/usr/include/oracle/12.2/client -I/usr/include/oracle/12.2/client64 -I/usr/include/oracle/12.1/client -I/usr/include/oracle/12.1/client64 -I/usr/include/oracle/11.2/client -I/usr/include/oracle/11.2/client64 -I/usr/include/oracle/11.1/client -I/usr/include/oracle/11.1/client64 -I/usr/include/oracle/10.2.0.5/client -I/usr/include/oracle/10.2.0.5/client64 -I/usr/include/oracle/10.2.0.4/client -I/usr/include/oracle/10.2.0.4/client64 -I/usr/include/oracle/10.2.0.3/client -I/usr/include/oracle/10.2.0.3/client64 -I. -I./ -I/usr/pgsql-9.6/include/server -I/usr/pgsql-9.6/include/internal -D_GNU_SOURCE -I/usr/include/libxml2  -I/usr/include  -c -o oracle_fdw.o oracle_fdw.c
gcc -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 -fPIC -I/sdk/include -I/oci/include -I/rdbms/public -I/usr/include/oracle/12.2/client -I/usr/include/oracle/12.2/client64 -I/usr/include/oracle/12.1/client -I/usr/include/oracle/12.1/client64 -I/usr/include/oracle/11.2/client -I/usr/include/oracle/11.2/client64 -I/usr/include/oracle/11.1/client -I/usr/include/oracle/11.1/client64 -I/usr/include/oracle/10.2.0.5/client -I/usr/include/oracle/10.2.0.5/client64 -I/usr/include/oracle/10.2.0.4/client -I/usr/include/oracle/10.2.0.4/client64 -I/usr/include/oracle/10.2.0.3/client -I/usr/include/oracle/10.2.0.3/client64 -I. -I./ -I/usr/pgsql-9.6/include/server -I/usr/pgsql-9.6/include/internal -D_GNU_SOURCE -I/usr/include/libxml2  -I/usr/include  -c -o oracle_utils.o oracle_utils.c
gcc -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 -fPIC -I/sdk/include -I/oci/include -I/rdbms/public -I/usr/include/oracle/12.2/client -I/usr/include/oracle/12.2/client64 -I/usr/include/oracle/12.1/client -I/usr/include/oracle/12.1/client64 -I/usr/include/oracle/11.2/client -I/usr/include/oracle/11.2/client64 -I/usr/include/oracle/11.1/client -I/usr/include/oracle/11.1/client64 -I/usr/include/oracle/10.2.0.5/client -I/usr/include/oracle/10.2.0.5/client64 -I/usr/include/oracle/10.2.0.4/client -I/usr/include/oracle/10.2.0.4/client64 -I/usr/include/oracle/10.2.0.3/client -I/usr/include/oracle/10.2.0.3/client64 -I. -I./ -I/usr/pgsql-9.6/include/server -I/usr/pgsql-9.6/include/internal -D_GNU_SOURCE -I/usr/include/libxml2  -I/usr/include  -c -o oracle_gis.o oracle_gis.c
gcc -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 -fPIC -shared -o oracle_fdw.so oracle_fdw.o oracle_utils.o oracle_gis.o -L/usr/pgsql-9.6/lib -Wl,--as-needed  -L/usr/lib64 -Wl,--as-needed -Wl,-rpath,'/usr/pgsql-9.6/lib',--enable-new-dtags  -L -L/bin -L/lib -lclntsh -L/usr/lib/oracle/12.2/client/lib -L/usr/lib/oracle/12.2/client64/lib -L/usr/lib/oracle/12.1/client/lib -L/usr/lib/oracle/12.1/client64/lib -L/usr/lib/oracle/11.2/client/lib -L/usr/lib/oracle/11.2/client64/lib -L/usr/lib/oracle/11.1/client/lib -L/usr/lib/oracle/11.1/client64/lib -L/usr/lib/oracle/10.2.0.5/client/lib -L/usr/lib/oracle/10.2.0.5/client64/lib -L/usr/lib/oracle/10.2.0.4/client/lib -L/usr/lib/oracle/10.2.0.4/client64/lib -L/usr/lib/oracle/10.2.0.3/client/lib -L/usr/lib/oracle/10.2.0.3/client64/lib 
make install
[root@pg01 oracle_fdw-2.0.0]# make install
/usr/bin/mkdir -p '/usr/pgsql-9.6/lib'
/usr/bin/mkdir -p '/usr/pgsql-9.6/share/extension'
/usr/bin/mkdir -p '/usr/pgsql-9.6/share/extension'
/usr/bin/mkdir -p '/usr/pgsql-9.6/doc/extension'
/usr/bin/install -c -m 755  oracle_fdw.so '/usr/pgsql-9.6/lib/oracle_fdw.so'
/usr/bin/install -c -m 644 .//oracle_fdw.control '/usr/pgsql-9.6/share/extension/'
/usr/bin/install -c -m 644 .//oracle_fdw--1.1.sql .//oracle_fdw--1.0--1.1.sql  '/usr/pgsql-9.6/share/extension/'
/usr/bin/install -c -m 644 .//README.oracle_fdw '/usr/pgsql-9.6/doc/extension/'
 export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:/usr/local/pgsql/lib:/usr/local/oracle/instantclient_11_2
 export PATH=$PATH:/usr/local/pgsql/bin
 export ORACLE_HOME=/usr/local/oracle/instantclient_11_2
 cd /usr/local/oracle/instantclient_11_2
 oracle_fdw找的是libclntsh.so,因此要加一个软连接去掉版本号
 ln -s libclntsh.so.11.1 libclntsh.so
 unzip oracle_fdw-ORACLE_FDW_2_1_0.zip
 cd oracle_fdw-ORACLE_FDW_2_1_0
 make
 make install

注意:这些环境变量是临时的,安装oracle_fdw使用

在后面pg中创建插件的时候,还是会报找不到动态连接库(虽然我配置了postgres用户的LD_LIBRARY_PATH环境变量),后面通过修改ld.conf解决(详见第五步),因此这里的LD_LIBRARY_PATH环境变量配置可以直接使用后面的ld.conf解决,但为显示整个过程,这里暂时使用临时变量

在这里插入图片描述

5.进入数据库安装插件

# su - postgres
# psql
postgres=#  create extension oracle_fdw;

执行会报错:ERROR: could not load library “/usr/local/pgsql-10.6/lib/oracle_fdw.so”: libclntsh.so.11.1: cannot open shared object file: No such file or directory

通过以下步骤解决:

# cd /etc/ld.so.conf.d/
# vi oracle-x86_64.conf
-------------------------------------
/usr/local/oracle/instantclient_11_2
-------------------------------------
# ldconfig

将oracle的库加入动态链接库,再次添加扩展即可

6.创建tnsnames.ora

 cd /usr/local/oracle/instantclient_11_2/
 mkdir -p network/admin/

创建一个或者复制一个tnsnames.ora过来,配置TNS

7.外部表使用

创建外部服务器

create server oracle_test foreign data wrapper oracle_fdw options(dbserver 'db');

oracle_test为外部服务器的名称,db为上一步tnsnames.ora中配置的tns

授权

grant usage on foreign server oracle_test to postgres;

本步不是必须的,如果要给非外部服务器的创建者使用,可以赋权

创建mapping

create user mapping for postgres server oracle_test options (user 'oracle', password 'oracle');

进入oracle数据库的凭证

创建外部表

create foreign table
    test_fdw( id int,
    name varchar(10) ) server oracle_test options (schema 'USER01',table 'TEST01');

schema和table必须为大写,否则无法使用

查看外部服务的信息

select oracle_diag();
select * from test_fdw;
 

其他

创建外部数据源服务

postgres=# CREATE SERVER oradb FOREIGN DATA WRAPPER oracle_fdw OPTIONS (dbserver '//192.168.56.8:1521/orcl');

建用户映射,指定连接远程数据库的用户名和密码,命令如下:

postgres=# CREATE USER MAPPING FOR postgres SERVER osdba_fdw OPTIONS (user 'test', password 'cstech');
CREATE USER MAPPING

没有报错的情况下说明已经建好了,可以创建外部表进行测试了,注意:外部表的字段必须包含在数据源数据的字段内,可以一一对应,也可以只使用其中某些字段,但是不能指定数据源没有的字段,并且每个字段的数据类型要一一对应,否则创建外部表会失败。

postgres=# CREATE  FOREIGN TABLE "test_tab" (id int,name text SERVER osdba_fdw OPTIONS (table 'TEST_TAB');
CREATE FOREIGN TABLE
查看表的结构

postgres=# \d test_tab 
                 Foreign table "public.test_tab"
 Column |  Type   | Collation | Nullable | Default | FDW options 
--------+---------+-----------+----------+---------+-------------
 id     | integer |           |          |         | 
 name   | text    |           |          |         | 
Server: osdba_fdw
FDW options: ("table" 'TEST_TAB')
查看数据

postgres=# select * from test_tab ;
【版权声明】本文为华为云社区用户原创内容,转载时必须标注文章的来源(华为云社区)、文章链接、文章作者等基本信息, 否则作者和本社区有权追究责任。如果您发现本社区中有涉嫌抄袭的内容,欢迎发送邮件进行举报,并提供相关证据,一经查实,本社区将立刻删除涉嫌侵权内容,举报邮箱: cloudbbs@huaweicloud.com
  • 点赞
  • 收藏
  • 关注作者

评论(0

0/1000
抱歉,系统识别当前为高风险访问,暂不支持该操作

全部回复

上滑加载中

设置昵称

在此一键设置昵称,即可参与社区互动!

*长度不超过10个汉字或20个英文字符,设置后3个月内不可修改。

*长度不超过10个汉字或20个英文字符,设置后3个月内不可修改。