使用Oracle_FDW实现Postgres连接OracleOracle_fdw的编译依赖系统中需要有pg_config和Oracle的环境,需要安装oracle客户端。1、安装客户端需要如下3个文件包unzip instantclient-basic-linux.x64-12.2.0.1.0.zipunzip instantclient-sdk-linux.x64-12.2.0.1.0.zipunzip instantclient-sqlplus-linux.x64-12.2.0.1.0.zip2、配置环境变量export ORACLE_HOME/opt/oracle/instantclientexport OCI_LIB_DIR$ORACLE_HOMEexport OCI_INC_DIR$ORACLE_HOME/sdk/includeexport LD_LIBRARY_PATH$ORACLE_HOME:$LD_LIBRARY_PATH3、下载oracle_fdw插件并安装unzip oracle_fdw-2.0.0.zipcd oracle_fdw-2.0.0MakeMake install--检查确认没有依赖未解决ldd oracle_fdw.so4、创建拓展postgres# create extension oracle_fdw ;postgres# \desList of foreign serversName | Owner | Foreign-data wrapper---------------------------------------oradb | postgres | oracle_fdw5、创建外部数据源服务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、建表时加入prefetch10240 参数后耗时5分22秒。select * from xx;Time: 322470.280 ms (05:22.470)[PostgreSQL连接PostgreSQL]使用Postgres_FDW实现Postgresql连接PostgresqlPostgres_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_size10240 参数后耗时3分58秒。select * from xx;Time: 238503.016 ms (03:58.503)[Oracle连接PostgreSQL]1、安装postgresql的odbc驱动包--需要安装unixODBC 和 postgresql_odbcyum install -y unixODBC.x86_64yum install -y postgresql-odbc.x86_642、配置/etc/odbc.ini[postgresql]Description PostgresSQLODBCDriver PostgreSQLDatabase testdbServername xxUserName testPassword test123Port 5432ReadOnly 0ConnSettings set client_encoding to UTF8连接成功[rootlocaloracle ~]# isql postgresql---------------------------------------| Connected! || || sql-statement || help [tablename] || quit || |---------------------------------------3、创建.odbc.ini文件在/home/oracle下创建隐藏文件.odbc.ini[PG_LINK]Description PostgreSQL connection to SallyDBDriver usr/lib64/psqlodbc.soSetup usr/lib64/libodbcpsqlS.soDatabase testdbServername xxUserName testPassword test123Port 5432Protocol 12.2ReadOnly NoRowVersioning NoShowSystemTables NoConnSettings set client_encoding to UTF84、配置透明网关在$ORACLE_HOME/network/admin/下面创建initPG_LINK.ora文件这个文件名字中的PG_LINK是上面自定义的名字HS_FDS_CONNECT_INFO PG_LINKHS_FDS_TRACE_LEVEL 255HS_FDS_SHAREABLE_NAME/usr/lib64/psqlodbc.soHS_LANGUAGEAMERICAN_AMERICA.ZHS16GBKset ODBCINI/home/oracle/.odbc.ini5、配置tnsnames.ora文件PG_LINK (DESCRIPTION(ADDRESS(PROTOCOLtcp)(HOSTxx)(PORT1521))(CONNECT_DATA(SIDPG_LINK))(HSOK))6、配置监听文件SID_LIST_LISTENER(SID_LIST(SID_DESC(SID_NAMEPG_LINK)(ORACLE_HOME/u01/app/oracle/product/12.2/db_1) (ENVLD_LIBRARY_PATH/usr/lib64/:/u01/app/oracle/product/12.2/db_1/bin/) (PROGRAMdg4odbc)))7、创建DBLINKcreate database link to_pglink connect to test identified by test123 using PG_LINK;8、访问PG数据库访问postgre的数据库表是需要表名字小写并加上双引号select count(*) from xxto_pglink;9、测试访问速度测试结果使用oracle连接postgres查询2G的表4520万行数据全表扫描耗时10分钟37秒。SQL set timing onSQL set autot traceSQL select xx from xxto_pglink;45201535 rows selected.Elapsed: 00:10:37.14
\des Postgres oracle dblink 互联
使用Oracle_FDW实现Postgres连接OracleOracle_fdw的编译依赖系统中需要有pg_config和Oracle的环境,需要安装oracle客户端。1、安装客户端需要如下3个文件包unzip instantclient-basic-linux.x64-12.2.0.1.0.zipunzip instantclient-sdk-linux.x64-12.2.0.1.0.zipunzip instantclient-sqlplus-linux.x64-12.2.0.1.0.zip2、配置环境变量export ORACLE_HOME/opt/oracle/instantclientexport OCI_LIB_DIR$ORACLE_HOMEexport OCI_INC_DIR$ORACLE_HOME/sdk/includeexport LD_LIBRARY_PATH$ORACLE_HOME:$LD_LIBRARY_PATH3、下载oracle_fdw插件并安装unzip oracle_fdw-2.0.0.zipcd oracle_fdw-2.0.0MakeMake install--检查确认没有依赖未解决ldd oracle_fdw.so4、创建拓展postgres# create extension oracle_fdw ;postgres# \desList of foreign serversName | Owner | Foreign-data wrapper---------------------------------------oradb | postgres | oracle_fdw5、创建外部数据源服务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、建表时加入prefetch10240 参数后耗时5分22秒。select * from xx;Time: 322470.280 ms (05:22.470)[PostgreSQL连接PostgreSQL]使用Postgres_FDW实现Postgresql连接PostgresqlPostgres_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_size10240 参数后耗时3分58秒。select * from xx;Time: 238503.016 ms (03:58.503)[Oracle连接PostgreSQL]1、安装postgresql的odbc驱动包--需要安装unixODBC 和 postgresql_odbcyum install -y unixODBC.x86_64yum install -y postgresql-odbc.x86_642、配置/etc/odbc.ini[postgresql]Description PostgresSQLODBCDriver PostgreSQLDatabase testdbServername xxUserName testPassword test123Port 5432ReadOnly 0ConnSettings set client_encoding to UTF8连接成功[rootlocaloracle ~]# isql postgresql---------------------------------------| Connected! || || sql-statement || help [tablename] || quit || |---------------------------------------3、创建.odbc.ini文件在/home/oracle下创建隐藏文件.odbc.ini[PG_LINK]Description PostgreSQL connection to SallyDBDriver usr/lib64/psqlodbc.soSetup usr/lib64/libodbcpsqlS.soDatabase testdbServername xxUserName testPassword test123Port 5432Protocol 12.2ReadOnly NoRowVersioning NoShowSystemTables NoConnSettings set client_encoding to UTF84、配置透明网关在$ORACLE_HOME/network/admin/下面创建initPG_LINK.ora文件这个文件名字中的PG_LINK是上面自定义的名字HS_FDS_CONNECT_INFO PG_LINKHS_FDS_TRACE_LEVEL 255HS_FDS_SHAREABLE_NAME/usr/lib64/psqlodbc.soHS_LANGUAGEAMERICAN_AMERICA.ZHS16GBKset ODBCINI/home/oracle/.odbc.ini5、配置tnsnames.ora文件PG_LINK (DESCRIPTION(ADDRESS(PROTOCOLtcp)(HOSTxx)(PORT1521))(CONNECT_DATA(SIDPG_LINK))(HSOK))6、配置监听文件SID_LIST_LISTENER(SID_LIST(SID_DESC(SID_NAMEPG_LINK)(ORACLE_HOME/u01/app/oracle/product/12.2/db_1) (ENVLD_LIBRARY_PATH/usr/lib64/:/u01/app/oracle/product/12.2/db_1/bin/) (PROGRAMdg4odbc)))7、创建DBLINKcreate database link to_pglink connect to test identified by test123 using PG_LINK;8、访问PG数据库访问postgre的数据库表是需要表名字小写并加上双引号select count(*) from xxto_pglink;9、测试访问速度测试结果使用oracle连接postgres查询2G的表4520万行数据全表扫描耗时10分钟37秒。SQL set timing onSQL set autot traceSQL select xx from xxto_pglink;45201535 rows selected.Elapsed: 00:10:37.14
