使用Oracle_FDW实现Postgres连接Oracle
Oracle_fdw的编译依赖系统中需要有pg_config和Oracle的环境,需要安装oracle客户端。
1、安装客户端需要如下3个文件包
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
2、配置环境变量
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
3、下载oracle_fdw插件并安装
unzip oracle_fdw-2.0.0.zip
cd oracle_fdw-2.0.0
Make
Make install
--检查确认没有依赖未解决
ldd oracle_fdw.so
4、创建拓展
postgres=# create extension oracle_fdw ;
postgres=# \des
List of foreign servers
Name | Owner | Foreign-data wrapper
-------+----------+----------------------
oradb | postgres | oracle_fdw
5、创建外部数据源服务
postgres=# CREATE SERVER oradb FOREIGN DATA WRAPPER oracle_fdw OPTIONS (dbserver '// xx:xx1521/posdb');
6、建用户映射
postgres=# CREATE USER MAPPING FOR postgres SERVER oradb OPTIONS (user ' ', password ' ');
7、创建外部表
postgres=# CREATE FOREIGN TABLE "xx" (
id int,
uuid character(32),
name character(32),
Minfo text) SERVER oradb OPTIONS (table 'xx');
8、测试访问速度
测试结果：
1、使用postgres连接oracle查询2G的表，4520万行数据，全表扫描耗时9分钟23秒。
select * from xx;
Time: 563775.627 ms (09:23.776)
2、建表时加入prefetch'10240' 参数后耗时5分22秒。
select * from xx;
Time: 322470.280 ms (05:22.470)
[
PostgreSQL连接PostgreSQL
]
使用Postgres_FDW实现Postgresql连接Postgresql
Postgres_FDW为postgresql自带拓展可以直接创建。支持远程select和DML，和本地表操作一样。
1、创建拓展
postgres=# create extension postgres_fdw;
2、创建服务
postgres=# create server f_postgres foreign data wrapper postgres_fdw options (host 'xx',port 'xx',dbname 'xx');
3、创建用户映射
postgres=# create user mapping if not exists for postgres server f_postgres options (user 'xx',password 'xx');
4、创建外部表
postgres=# CREATE FOREIGN TABLE "xx" (
id int,
uuid character(32),
name character(32),
Minfo text) SERVER f_postgres OPTIONS (table_name 'xx');
5、测试访问速度
测试结果：
1、使用postgres连接postgres查询2G的表，4520万行数据，全表扫描耗时12分钟59秒。
select * from vol_audit_nbr_sum_qs;
Time: 779932.685 ms (12:59.933)
2、建表时加入fetch_size'10240' 参数后耗时3分58秒。
select * from xx;
Time: 238503.016 ms (03:58.503)
[
Oracle连接PostgreSQL
]
1、安装postgresql的odbc驱动包
--需要安装unixODBC 和 postgresql_odbc
yum install -y unixODBC.x86_64
yum install -y postgresql-odbc.x86_64
2、配置/etc/odbc.ini
[postgresql]
Description = PostgresSQLODBC
Driver = PostgreSQL
Database = testdb
Servername = xx
UserName = test
Password = test123
Port = 5432
ReadOnly = 0
ConnSettings = set client_encoding to UTF8
连接成功：
[root@localoracle ~]# isql postgresql
+---------------------------------------+
| Connected! |
| |
| sql-statement |
| help [tablename] |
| quit |
| |
+---------------------------------------+
3、创建.odbc.ini文件
在/home/oracle下创建隐藏文件.odbc.ini
[PG_LINK]
Description = PostgreSQL connection to SallyDB
Driver = usr/lib64/psqlodbc.so
Setup = usr/lib64/libodbcpsqlS.so
Database = testdb
Servername = xx
UserName = test
Password = test123
Port = 5432
Protocol = 12.2
ReadOnly = No
RowVersioning = No
ShowSystemTables = No
ConnSettings = set client_encoding to UTF8
4、配置透明网关
在$ORACLE_HOME/network/admin/下面创建initPG_LINK.ora文件，这个文件名字中的PG_LINK是上面自定义的名字
HS_FDS_CONNECT_INFO = PG_LINK
HS_FDS_TRACE_LEVEL = 255
HS_FDS_SHAREABLE_NAME=/usr/lib64/psqlodbc.so
HS_LANGUAGE=AMERICAN_AMERICA.ZHS16GBK
set ODBCINI=/home/oracle/.odbc.ini
5、配置tnsnames.ora文件
PG_LINK =
(DESCRIPTION=
(ADDRESS=(PROTOCOL=tcp)(HOST=xx)(PORT=1521))
(CONNECT_DATA=(SID=PG_LINK))
(HS=OK)
)
6、配置监听文件
SID_LIST_LISTENER=
(SID_LIST=
(SID_DESC=
(SID_NAME=PG_LINK)
(ORACLE_HOME=/u01/app/oracle/product/12.2/db_1) (ENV="LD_LIBRARY_PATH=/usr/lib64/:/u01/app/oracle/product/12.2/db_1/bin/") (PROGRAM=dg4odbc)
)
)
7、创建DBLINK
create database link to_pglink connect to "test" identified by "test123" using 'PG_LINK';
8、访问PG数据库
访问postgre的数据库表是需要表名字小写并加上双引号
select count(*) from "xx"@to_pglink;
9、测试访问速度
测试结果：
使用oracle连接postgres查询2G的表，4520万行数据，全表扫描耗时10分钟37秒。
SQL> set timing on
SQL> set autot trace
SQL> select "xx" from "xx"@to_pglink;
45201535 rows selected.
Elapsed: 00:10:37.14
