PostgreSQL+Oracle跨库连接实操

IT那活儿发布于2023-01-11 13:20 / 2154人阅读

在我们异构数据库数据迁移的过程中，涉及大量的跨库查询操作的需求。常见的跨库连接有A连B，B连B，B连A的现实需求。本文带给大家的是PostgreSQL和oracle之间上述三种跨库连接的实战分享。

[

PostgreSQL连接Oracle

]

使用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 //192.168.217.120:1521/posdb);

6、建用户映射

postgres=# CREATE USER MAPPING FOR postgres SERVER oradb OPTIONS (user wen, password wen123);

7、创建外部表

postgres=# CREATE FOREIGN TABLE "vol_audit_nbr_sum_qs" (

id int,

uuid character(32),

name character(32),

Minfo text) SERVER oradb OPTIONS (table vol_audit_nbr_sum_qs);

8、测试访问速度

测试结果：

1、使用postgres连接oracle查询2G的表，4520万行数据，全表扫描耗时9分钟23秒。

select * from vol_audit_nbr_sum_qs;

Time: 563775.627 ms (09:23.776)

2、建表时加入prefetch10240 参数后耗时5分22秒。

select * from vol_audit_nbr_sum_qs;

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 192.168.86.95,port 5433,dbname db_demo);

3、创建用户映射

postgres=# create user mapping if not exists for postgres server f_postgres options (user wen,password wen123);

4、创建外部表

postgres=# CREATE FOREIGN TABLE "vol_audit_nbr_sum_qs" (

id int,

uuid character(32),

name character(32),

Minfo text) SERVER f_postgres OPTIONS (table_name vol_audit_nbr_sum_qs);

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 vol_audit_nbr_sum_qs;

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 = 192.168.12.123

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 = 192.168.12.123

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=192.168.217.120)(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 "vol_audit_nbr_sum_qs"@to_pglink;

9、测试访问速度

测试结果：

使用oracle连接postgres查询2G的表，4520万行数据，全表扫描耗时10分钟37秒。

SQL> set timing on

SQL> set autot trace

SQL> select "billing_cycle_id" from "vol_audit_nbr_sum_qs"@to_pglink;

45201535 rows selected.

Elapsed: 00:10:37.14

文章版权归作者所有，未经允许请勿转载,若此文章存在违规行为，您可以联系管理员删除。

转载请注明本文地址：https://www.ucloud.cn/yun/130182.html

springboot+mycat 分表分库

摘要：而在分布式系统中，分表分库也是常用的一种解决此类瓶颈的手段。支持数据的多片自动路由与聚合，支持等常用的聚合函数支持跨库分页。支持通过全局表，关系的分片策略，实现了高效的多表查询。支持多租户方案。前言对于业务量越来越大的时候，单表数据超过几千万，甚至上亿时，一张表里面查询真的会很费时。而在分布式系统中，分表分库也是常用的一种解决此类瓶颈的手段。今天就选用springboot+myca...

lakeside 2019-08-19 10:14 评论0 收藏0
题库分库分表架构方案

摘要：个人博客地址方案项目背景在现在题库架构下，针对新购买的多道数据进行整合，不影响现有功能。数据切分尽量通过数据冗余或表分组来降低跨库的可能。个人博客地址 https://www.texixi.com/2019/0... 方案项目背景在现在题库架构下，针对新购买的1300W多道数据进行整合，不影响现有功能。由于数据量偏多，需要进行数据的切分目标场景兼容旧的功能对1300多W...

kohoh_ 2019-07-01 11:17 评论0 收藏0
阿里云如何打破Oracle迁移上云的壁垒

摘要：摘要第九届中国数据库技术大会，阿里云数据库产品专家萧少聪带来以阿里云如何打破迁移上云的壁垒为题的演讲。于是，阿里云给出了上面的解决方案。摘要： 2018第九届中国数据库技术大会，阿里云数据库产品专家萧少聪带来以阿里云如何打破Oracle迁移上云的壁垒为题的演讲。Oracle是指数据库管理系统，面对Oracle迁移上云的壁垒，阿里云如何能够打破它呢？本文提出了Oracle 到云数据库P...

chavesgu 2019-06-25 18:39 评论0 收藏0
移动易开源APP组合套件更新——支持多种外部数据库，支持全文搜索

摘要：移动易后台实现外部数据库连接要实现外置数据库，即上层开发人员不关心下层数据库的实现，在项目中需要针对不同数据库修改文件以及在项目中添加依赖包。本文主要介绍移动易后台如何实现同不同数据源的连接，数据源包括，。 1、移动易后台实现外部数据库连接要实现外置数据库，即上层开发人员不关心下层数据库的实现，在Spring boot项目中需要针对不同数据库修改application.proper...

anyway 2019-08-15 10:57 评论0 收藏0