oracle_fdw：实现远程oracle与PG数据同步

oracle_fdw官方安装包下载、安装指导、使用说明、常见问题地址：http://pgxn.org/dist/oracle_fdw/.在所有操作开始之前，请首先阅读该文档。

要求Postgresql是 9.1 以及更高版本。从9.2开始支持ANALYZE；从9.3开始支持INSERT、UODATE 和 DELETE。

要求Oracle客户端是10.1以及更高版本。基于Oracle Instant Client ，或者带有Universal Installer的Oracle Client和Server的安装，Oracle_fdw可以进行创建和使用。通过Oracle Client 10 编译的二进制文件，可以被更高版本的客户端使用，而不需要recompilation 或者 relink。

环境：

一个windows xp(32bit)虚拟机，装有oracle11g。本文假设该oracle服务器安装完毕，可远程访问，ip为192.168.100.234。

一个centos（32bit）linux虚拟机，用来安装postgresql，oracle客户端，oracle_fdw。(本文所有操作在此进行)

一、源码编译安装Postgresql

下载Postgresql源码安装包，本文使用的：

postgresql-9.4.4.tar.gz

编译安装：

注意：编译时使用--without-ldap

/configure--prefix=/opt/pgsql--with-pgport=5432--with-segsize=8--with-wal-segsize=64--with-wal-blocksize=64--with-perl--without-openssl--without-pam--without-ldap--enable-thread-safety
gmakeworld
gmakeinstall-world

cd/opt/pgsql
mkdirdata

useradd-mpostgres
passwdpostgres
chownpostgresdata

su-postgres
cd..
cdbin
./initdb-D../data--locale=C-Upostgres
./pg_ctlstart-D../data
./psql

添加环境变量：

vi/etc/profile

在文件末尾添加

exportPG_HOME=/opt/pgsql
Esc
:w
:q

source/etc/profile

二、安装oracle database instant client客户端

官方介绍和安装指导：http://www.oracle.com/technetwork/database/features/instant-client/index-100365.html

官方下载地址：http://www.oracle.com/technetwork/database/features/instant-client/index-097480.html

安装过程：

1. 下载：basic/sdk/sqlplus三个安装包，一定要与操作系统和位数(32bit或64bit)符合,本文使用的是：

instantclient-basic-linux-12.1.0.2.0 .zip

instantclient-sdk-linux-12.1.0.2.0.zip

instantclient-sqlplus-linux-12.1.0.2.0.zip

2. 创建一个oracle客户端的目录/opt/oracle，

3.将三个压缩包解压后，将所有文件直接拷贝到/opt/oracle/下面.

4.在/opt/oracle/下面创建配置文件tnsname.ora

cd/opt/oracle
vitnsname.ora

添加内容：

MYDB=
(DESCRIPTION=
(ADDRESS_LIST=
(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.100.234)(PORT=1521))
)
(CONNECT_DATA=(SID=orcl)(SERVER=DEDICATED))
）

5.配置关于oracle客户端的环境变量

vi/etc/profile

添加以下内容

exportORACLE_HOME=/opt/oracle;
exportsqlPATH=/opt/oracle;
exportTNS_ADMIN=/opt/oracle;
exportLD_LIBRARY_PATH=$ORACLE_HOME:$LD_LIBRARY_PATH;
exportPATH=$PATH:$ORACLE_HOME;

source/opt/profile

6. 创建oracle用户，并对客户端目录授权

groupaddoinstall
useradd－goinstalloracle
passwdoracle

chown-Roracle:oinstall/opt/oracle
chmod-R775/usr/oracle

7. 使用客户端进行连接测试

su-oracle
cd/opt/oracle
[oracle@localhostoracle]$sqlpluslyy/lyy@//192.168.100.234:1521/orcl
sql*Plus:Release12.1.0.2.0ProductiononSunSep619:32:122015
Copyright(c)1982,2014,Oracle.Allrightsreserved.
Connectedto:
OracleDatabase11gEnterpriseEditionRelease11.2.0.1.0-Production
WiththePartitioning,OLAP,DataMiningandRealApplicationTestingoptions
sql>

此时oracle客户端配置完毕并连接成功。

常见问题：参考http://my.oschina.net/liuyuanyuangogo/blog/502090 来解决。

三、源码编译安装oracle_fdw

官方源码地址：http://pgfoundry.org/frs/?group_id=1000600

官方安装包下载、安装指导、使用说明、常见问题地址：http://pgxn.org/dist/oracle_fdw/

1.下载源码并阅读安装说明.

官方下载和使用说明地址：http://pgxn.org/dist/oracle_fdw/

本文下载的是：oracle_fdw-1.2.0.zip

要确保与postgresql安装的操作系统及其位数(32bit或64bit)匹配。

2. 解压压缩包并配置Makefile

查找pg_config位置

[root@oracle_fdw-1.2.0]#find/-namepg_config/opt/PostgresPlus/9.3AS/bin/pg_config

更改oracle_fdw-0.9.9文件夹里的Makefile文件，指定pg_config位置

[root@oracle_fdw-1.2.0]#catMakefile|grepPG_CONFIG

改为PG_CONFIG=$PG_HOME/bin/pg_config（更改文件的路径）

3.编译并安装(必须是root用户下)

直接在oracle_fdw-1.2.0目录下执行：

[root@localhost oracle_fdw-1.2.0]# make

gcc-Wall-Wmissing-prototypes-Wpointer-arith-Wdeclaration-after-statement-Wendif-labels-Wmissing-format-attribute-Wformat-security-fno-strict-aliasing-fwrapv-O2-fpic-shared-ooracle_fdw.sooracle_fdw.ooracle_utils.ooracle_gis.o-L/opt/pgsql/lib-Wl,-rpath,'/opt/pgsql/lib',--enable-new-dtags-L/opt/oracle-L/opt/oracle/bin-L/opt/oracle/lib-lclntsh-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

[root@localhost oracle_fdw-1.2.0]# ldd oracle_fdw.so

linux-gate.so.1=>(0x007e6000)
libclntsh.so.12.1=>/opt/oracle/libclntsh.so.12.1(0x007e7000)
libc.so.6=>/lib/libc.so.6(0x00248000)
libnnz12.so=>/opt/oracle/libnnz12.so(0x04873000)
libons.so=>/opt/oracle/libons.so(0x00110000)
libdl.so.2=>/lib/libdl.so.2(0x00144000)
libm.so.6=>/lib/libm.so.6(0x005e4000)
libpthread.so.0=>/lib/libpthread.so.0(0x00671000)
libnsl.so.1=>/lib/libnsl.so.1(0x00149000)
librt.so.1=>/lib/librt.so.1(0x00162000)
/lib/ld-linux.so.2(0x0022b000)
libaio.so.1=>/usr/lib/libaio.so.1(0x0016b000)
libclntshcore.so.12.1=>/opt/oracle/libclntshcore.so.12.1(0x02b7c000)

[root@localhost oracle_fdw-1.2.0]# make install

/bin/mkdir-p'/opt/pgsql/lib'
/bin/mkdir-p'/opt/pgsql/share/extension'
/bin/mkdir-p'/opt/pgsql/share/extension'
/bin/mkdir-p'/opt/pgsql/share/doc/extension'
/usr/bin/install-c-m755oracle_fdw.so'/opt/pgsql/lib/oracle_fdw.so'
/usr/bin/install-c-m644oracle_fdw.control'/opt/pgsql/share/extension/'
/usr/bin/install-c-m644oracle_fdw--1.1.sqloracle_fdw--1.0--1.1.sql'/opt/pgsql/share/extension/'
/usr/bin/install-c-m644README.oracle_fdw'/opt/pgsql/share/doc/extension/'

出现以上信息说明orale_fdw编译安装完成。

常见错误：

在make时遇到错误cannot find -lclntsh：

/usr/bin/ld:cannotfind-lclntsh
collect2:ldreturned1exitstatus
make:***[oracle_fdw.so]Error1

解决办法：到oracle客户端的目录下，为libclntsh.so创建指向libclntsh.so.12.1的软链接。

[postgres@localhostbin]$su-root
Password:
[root@localhost~]#cd/opt/oracle/
--创建软连接
[root@localhostoracle]#ln-slibclntsh.so.12.1libclntsh.so
--查看指向情况
[root@localhostoracle]#ll
total175420
-rwxrwxr-x1oracleoinstall24706Jul82014adrci
-rwxrwxr-x1oracleoinstall438Jul82014BASIC_README
-rwxrwxr-x1oracleoinstall33309Jul82014genezi
-rwxrwxr-x1oracleoinstall342Jul82014glogin.sql
-rwxrwxr-x1oracleoinstall5520733Jul82014libclntshcore.so.12.1
lrwxrwxrwx1rootroot17Sep623:02libclntsh.so->libclntsh.so.12.1
-rwxrwxr-x1oracleoinstall45817130Jul82014libclntsh.so.12.1
-rwxrwxr-x1oracleoinstall5323903Jul82014libnnz12.so
-rwxrwxr-x1oracleoinstall1958194Jul82014libocci.so.12.1
-rwxrwxr-x1oracleoinstall109543276Jul82014libociei.so
-rwxrwxr-x1oracleoinstall183705Jul82014libocijdbc12.so
-rwxrwxr-x1oracleoinstall268133Jul82014libons.so
-rwxrwxr-x1oracleoinstall81153Jul82014liboraMysqL12.so
-rwxrwxr-x1oracleoinstall1561437Jul82014libsqlplusic.so
-rwxrwxr-x1oracleoinstall1299573Jul82014libsqlplus.so
drwxrwxr-x3oracleoinstall4096Sep620:16oci
-rwxrwxr-x1oracleoinstall3692096Jul82014ojdbc6.jar
-rwxrwxr-x1oracleoinstall3698857Jul82014ojdbc7.jar
drwxrwxr-x5oracleoinstall4096Jul82014sdk
-rwxrwxr-x1oracleoinstall7353Jul82014sqlplus
-rwxrwxr-x1oracleoinstall442Jul82014sqlPLUS_README
-rwxrwxr-x1oracleoinstall172720Jul82014uidrvci
-rwxrwxr-x1oracleoinstall71202Jul82014xstreams.jar
--查看libclntsh.so.12.1所调用的库
[root@localhostoracle_fdw-1.2.0]#ldd/opt/oracle/libclntsh.so.12.1
linux-gate.so.1=>(0x00a30000)
libnnz12.so=>/opt/oracle/libnnz12.so(0x00248000)
libons.so=>/opt/oracle/libons.so(0x00110000)
libdl.so.2=>/lib/libdl.so.2(0x00144000)
libm.so.6=>/lib/libm.so.6(0x00149000)
libpthread.so.0=>/lib/libpthread.so.0(0x00172000)
libnsl.so.1=>/lib/libnsl.so.1(0x0018c000)
librt.so.1=>/lib/librt.so.1(0x00937000)
libc.so.6=>/lib/libc.so.6(0x0069f000)
/lib/ld-linux.so.2(0x0022b000)
libaio.so.1=>/usr/lib/libaio.so.1(0x001a5000)
libclntshcore.so.12.1=>/opt/oracle/libclntshcore.so.12.1(0x083b1000)
--操作完毕即可重新执行make和makeinstall

四、Postgresql配置oracle_fdw

[root@localhostoracle_fdw-1.2.0]#cd/opt/oracle/
[root@localhostoracle]#cplibclntsh.so.12.1/opt/pgsql/lib/
[root@localhostoracle]#cplibnnz12.so/opt/pgsql/lib/
[root@localhostoracle]#chown.daemon/opt/pgsql/lib/libclntsh.so.12.1
[root@localhostoracle]#chown.daemon/opt/pgsql/lib/libnnz12.so

五、在Postgresql中使用oracle_fdw

[postgres@localhostbin]$./psql
psql(9.4.4)
Type"help"forhelp.
postgres=#createextensionoracle_fdw;
CREATEEXTENSION
postgres=#createserveroracledbforeigndatawrapperoracle_fdwoptions(dbserver'//192.168.100.234:1521/orcl');
CREATESERVER
postgres=#createuseroracle_fdwsuperuserpassword'oracle';
CREATEROLE
postgres=#createusermappingfororacle_fdwserveroracledboptions(user'lyy',password'lyy');
CREATEUSERMAPPING
postgres=#createforeigntableoracle_lyy(idint,namevarchar)serveroracledboptions(schema'lyy',table'LYY');--注意oracle中表名一般为大写
CREATEFOREIGNTABLE
postgres=#\q

[postgres@localhostbin]$./psql-Uoracle_fdw-dpostgres
psql(9.4.4)
Type"help"forhelp.
postgres=#select*fromoracle_lyy;
id|name
----+----------------------
1|ddd
(1row)

此后，在oracle中对lyy表的操作提交后，Postgresql中oracle_lyy也会更新。在Postgresql中对oracle_lyy表的操作提交后，Oracle中lyy也会更新。

六、oracle_fdw在Postgresql中使用的常见错误

详细请参阅官方问题列表: problemhttp://pgxn.org/dist/oracle_fdw/

错误举例：

Encoding
--------

CharacteRSStoredinanOracledatabasethatcannotbeconvertedtothePostgresqldatabaseencodingwillsilentlybereplacedby"replacementcharacters",typicallyanormalorinvertedquestionmark,byOracle.Youwillgetnowarningorerrormessages.

IfyouuseaPostgresqldatabaseencodingthatOracledoesnotknow
(currently,theseareEUC_CN,EUC_KR,LATIN10,MULE_INTERNAL,WIN874
andsql_ASCII),non-ASCIIcharacterscannotbetranslatedcorrectly.
Youwillgetawarninginthiscase,andthecharacterswillbereplaced
byreplacementcharactersasdescribedabove.

Youcansetthe"nls_lang"optionoftheforeigndatawrappertoforcea
certainOracleencoding,buttheresultingcharacterswillmostlikelybe
incorrectandleadtoPostgresqlerrormessages.Thisisprobablyonly
usefulforsql_ASCIIencodingifyouknowwhatyouaredoing.
See"Options"above.

Potgresql初始化时指定--locale-C（该编码oracle不识别），之后使用oracle_fdw会出现，如下错误：

postgres=#select*fromoracle_lyy;
WARNING:noOraclecharactersetfordatabaseencoding"sql_ASCII"
DETAIL:AllbutASCIIcharacterswillbelost.
HINT:Youcansettheoption"nls_lang"ontheforeigndatawrappertoforceanOraclecharacterset.
WARNING:noOraclecharactersetfordatabaseencoding"sql_ASCII"

只能重新初始化一个可以支持的编码的库，比如--locale=en_US.utf8.

参考文章：

http://wenku.baidu.com/link?url=48wDO8XBo6njaqFzgH8gWJI8gLagjZxSa7kKabuQcs9SjfgLNfHUH-JeS5JNNZZ1cQAygBJscSoyN0w2xlka124RfWVWnm9_FG3ad_Ug7a7

http://francs3.blog.163.com/blog/static/4057672720122354546425/

postgres_fdw：实现PG对外部PG服务器的数据访问

官方文档：http://www.postgresql.org/docs/9.4/static/postgres-fdw.html

postgres_fdw是Postgresql 9.3新增的功能模块。

postgres_fdw用来访问外部的Postgresql服务器。

举例列示步骤：

一、配置外部数据库服务器允许远程访问。

通过配置pg_hba.conf和postgreql.conf两个配置文件,配置完毕后需重启服务。

二、在本地数据库服务器中，创建外部数据表

postgres=#createextensionpostgres_fdw;
CREATEEXTENSION
postgres=#createserverforeign_serverforeigndatawrapperpostgres_fdwoptions(host'192.168.100.232',port'5432',dbname'postgres');
CREATESERVER
postgres=#createuserpostgres_fdwsuperuserpassword'postgres';
CREATEROLE
postgres=#createusermappingforpostgres_fdwserverforeign_serveroptions(user'postgres',password'postgres');
CREATEUSERMAPPING
postgres=#createforeigntablelyy_fdw(idint,namevarchar)serverforeign_serveroptions(schema_name'public',table_name'lyy');--确保首先在oracle中创建表LYY。
CREATEFOREIGNTABLE
--此时外部数据表创建完毕，进行查询（已可获得外部服务器中的public.lyy表的数据）：
postgres=#select*frompostgres.lyy_fdw;
id|name
----+------
1|lily
2|lucy
11|hhhh
(3rows)

此后每当外部数据库服务器中相应的表数据变化，本地服务器中的相应外部数据表数据也变化。

file_fdw：实现对Postgresql服务器系统中的数据文件的访问

官方文档：http://www.postgresql.org/docs/9.4/static/file-fdw.html

file_fdw是Postgresql 9.1 新增的功能模块。

file_fdw可以用来访问Postgresql服务器系统中的数据文件。数据文件的格式必须是COPY FROM可以读取的；请参考COPY。对这些数据文件的访问，目前仅是只读的。