PostgreSQL技术大讲堂 - 第27讲：Oracle-FDW部署

admin

PostgreSQL从小白到专家，是从入门逐渐能力提升的一个系列教程，内容包括对PG基础的认知、包括安装使用、包括角色权限、包括维护管理、、等内容，希望对热爱PG、学习PG的同学们有帮助，欢迎持续关注CUUG PG技术大讲堂。

第27讲：Oracle-FDW部署

内容1 : Foreign Data Wrappers(FDW)简介与特性

内容2 : Oracle_FDW部署

内容3 : FDW执行原理

Foreign Data Wrappers

Foreign Data Wrappers (FDW)

FDW部署

部署FDW(在客户端部署)

1、安装 oracle 19c client(postgres用户)

        mkdir /usr/local/oracle

        unzip -d /usr/local/oracle instantclient-basic-linux.x64-19.6.0.0.0dbru.zip

        unzip -d /usr/local/oracle instantclient-sdk-linux.x64-19.6.0.0.0dbru.zip

2、配置环境变量(临时设置)

        export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:/usr/local/pg12.2/lib:/usr/local/oracle/instantclient_19_6/

        export PATH=$PATH:/usr/local/pg12.2/bin

        export ORACLE_HOME=/usr/local/oracle/instantclient_19_6

3、编译oracle_fdw(需要单独下载)

        unzip oracle_fdw-2.2.0.zip

        cd oracle_fdw-2.2.0

        make

        make install

4、安装oracle_fdw插件(哪个database上使用，就在哪个database上安装)

        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

        1、通过以下步骤解决(root)，编辑 /etc/ld.so.conf.d/oracle-x86_64.conf

        /usr/local/oracle/instantclient_11_2

        -------------------------------------

        /usr/local/oracle/instantclient_19_6

        2、执行

        ldconfig

5、创建fdw服务器

        CREATE SERVER oradb FOREIGN DATA WRAPPER oracle_fdw

        OPTIONS (dbserver '//pg1:1521/PROD1');

        pg1：远程oracle主机名

        1521：远程oracle数据库监听端口

        PROD1：远程oracle service名字

6、授权

        GRANT USAGE ON FOREIGN SERVER pgdb TO oracle_fdw1;

7、创建用户映射(本地用户与远程用户映射)

        CREATE USER MAPPING FOR oracle_fdw1 SERVER oradb

        OPTIONS (user SCOTT', password ‘TIGER');

        user：远程数据库用户

        password：用户密码

8、创建FDW表(以oracle_fdw1用户创建)

        CREATE FOREIGN TABLE emp_fdw (

        EMPNO int ,

        ENAME VARCHAR(10),

        JOB VARCHAR(9),

        MGR int,

        HIREDATE date,

        SAL float4,

        COMM float4,

        DEPTNO int

        ) SERVER oradb OPTIONS (schema 'SCOTT', table 'EMP');

        Schema_name：必须大写，因为Oracle数据字典中默认是大写

        Table_name：需要访问的表表名

9、创建FDW表(以oracle_fdw1用户创建)

        CREATE FOREIGN TABLE dept_fdw (

        deptno integer,

        dname character varying(14),

        loc character varying(13)

        )SERVER oradb OPTIONS (schema 'SCOTT', table 'DEPT');

        Schema_name：public，特定schema用户创建的表，则写该schema名字

        Table_name：需要访问的表表名

10、访问FDW表(以oracle_fdw1用户访问)

        SELECT * FROM emp_fdw;

        SELECT * FROM dept_fdw;

        *访问FDW表的语法与访问本地表一样。

FDW原理

FDW执行流程

FDW执行流程

                1、Creating a Query Tree(访问pg_catalog.pg_class和pg_catalog.pg_foreign_table)

                2、Connecting to the Remote Server(使用libpq库)

                3、Creating a Plan Tree Using EXPLAIN Commands (Optional)(访问pg_catalog.pg_user_mapping和pg_catalog.pg_foreign_server)

                4、Deparesing(postgres_fdw从通过解析和分析创建的查询树中重新创建一个纯文本文件，在PostgreSQL中称为deparsing。)

                5、Sending SQL Statements and Receiving Result

各个版本功能演进

执行DML操作(PG-Oracle不支持DML操作)

       

多表查询

Version 9.6以前版本

                localdb=# EXPLAIN SELECT * FROM tbl_a AS a, tbl_b AS b WHERE a.id = b.id AND a.id < 200;

                QUERY PLAN

                ------------------------------------------------------------------------------

                Merge Join (cost=532.31..700.34 rows=10918 width=16)

                Merge Cond: (a.id = b.id)

                -> Sort (cost=200.59..202.72 rows=853 width=8)

                Sort Key: a.id

                -> Foreign Scan on tbl_a a (cost=100.00..159.06 rows=853 width=8)

                -> Sort (cost=331.72..338.12 rows=2560 width=8)

                Sort Key: b.id

                -> Foreign Scan on tbl_b b (cost=100.00..186.80 rows=2560 width=8)

                (8 rows)

多表查询

                (PG-PG)如果使用ALTER SERVER命令将use_remote_estimate选项设置为on，则计划器将通过执行EXPLAIN命令向远程服务器查询计划的成本，此时连接操作在远程进行，提高性能。

                ALTER SERVER pgdb OPTIONS (use_remote_estimate 'on');

                \des+

                localdb=# EXPLAIN SELECT * FROM tbl_a AS a, tbl_b AS b WHERE a.id = b.id AND a.id < 200;

                QUERY PLAN

                -----------------------------------------------------------

                Foreign Scan (cost=134.35..244.45 rows=80 width=16)

                Relations: (public.tbl_a a) INNER JOIN (public.tbl_b b)

                (2 rows)

排序操作

在9.5或更早版本中：

                localdb=# EXPLAIN SELECT * FROM tbl_a AS a WHERE a.id < 200 ORDER BY a.id;

                QUERY PLAN

                -----------------------------------------------------------------------

                Sort (cost=200.59..202.72 rows=853 width=8)

                Sort Key: id

                -> Foreign Scan on tbl_a a (cost=100.00..159.06 rows=853 width=8)

                (3 rows)

排序操作

                在9.6或以后版本中：

                localdb=# EXPLAIN SELECT * FROM tbl_a AS a WHERE a.id < 200 ORDER BY a.id;

                QUERY PLAN

                -----------------------------------------------------------------

                Foreign Scan on tbl_a a (cost=100.00..167.46 rows=853 width=8)

                (1 row)

聚组函数操作

在9.6或更早版本中：

                localdb=# EXPLAIN SELECT AVG(data) FROM tbl_a AS a WHERE a.id < 200;

                QUERY PLAN

                -----------------------------------------------------------------------

                Aggregate (cost=168.50..168.51 rows=1 width=4)

                -> Foreign Scan on tbl_a a (cost=100.00..166.06 rows=975 width=4)

                (2 rows)

聚组函数操作

                在10或以后版本中：

                localdb=# EXPLAIN SELECT AVG(data) FROM tbl_a AS a WHERE a.id < 200;

                QUERY PLAN

                -----------------------------------------------------

                Foreign Scan (cost=102.44..149.03 rows=1 width=32)

                Relations: Aggregate on (public.tbl_a a)

                (2 rows)

以上就是【PostgreSQL从小白到专家】第27讲 - Oracle-FDW部署  的内容，欢迎一起探讨交流钉钉交流群：35，82，24，60，往期视频及文档内容联系CUUG