【干货分享】Foreign Data Wrappers

云贝教育 2021-12-03

1884

点击上方蓝字关注我们吧

上期回顾-高频面试题系列-HOT

行数据更新时，索引也需要进行维护，如果是高并发的情况下，索引维护的代价很大，可能造成索引分裂。

Pg为了避免这个问题，采用了HOT（堆内元组技术）解决这个问题。

没有HOT更新后的索引,引用情况。

堆元祖(5,1) id=1000, 索引key=1000 tid=(5,1),更新堆元祖后索引key=1000 tid=(5,2)

当使用hot特性更新行时，如果更新后的行存储在同旧行同一个页面中，PostgreSQL 不会插入相应的索引元组,而是分别设置老元组的 HEAP_HOT_UPDATED 标记位和新元组的 HEAP_ONLY_TUPLE 标记位,存储在 t_informask2 字段中。

a.修剪前

1.索引找到行指针1

2.行指针1找到堆元祖1

3.堆元祖1的ctid找到堆元祖2,完成

b.修剪后

1.索引找到行指针1

2.行指针1找到行指针2

3.行指针2找到堆元祖2,完成

多源连通

概述

2003 年，SQL 标准中添加了访问远程数据的规范，称为SQL 外部数据管理(SQL/MED)。此功能由 PostgreSQL 从 9.1 版本开始开发以实现 SQL/MED 的一部分。

在 SQL/MED 中，远程服务器上的表称为外部表。PostgreSQL 的外部数据包装器 (FDW)是使用 SQL/MED 来管理类似于本地表的外部表。

postgres fdw是一种外部访问接口，它可以被用来访问存储在外部的数据，这些数据可以是外部的pg数据库，也可以oracle、mysql等数据库，甚至可以是文件。

开放的外部数据源接口，使得PG支持丰富的外部数据源，例如可以通过FDW读写MySQL, redis, mongo, oracle, sqlserver, hive, www, hbase, ldap, 等等只要你能想到的数据源都可以通过FDW接口读写。

目前支持的fdw外部数据源：

https://wiki.postgresql.org/wiki/Foreign_data_wrappers

FDW对比dblink

*dblink只能访问postgresql，fdw可以定制多种数据源。

*dblink在使用上没有fdw方便。

*dblink在运行数据量大的SQL时会丢失连接，fdw更加稳定。

FDW是如何运行的

(1) analyzer/analyser创建输入 SQL的查询树.

(2) 规划器 (或执行器) 连接到远程 server.

(3) 如果use_remote_estimate选项处于打开状态 (默认值为关闭), 规划器执行EXPLAIN命令, 以估算每个计划路径的成本.

(4) 规划器从计划树中创建纯文本 SQL 语句, 该语句在内部称为deparesing.

(5) 执行器将纯文本 SQL 语句发送到远程服务器并接收结果。

然后, 执行器在必要时处理接收到的数据。例如, 如果执行多表查询, 则执行器将执行处理完的数据和其他表的连接。

创建查询树

analyzer/analyser使用外部表的定义创建输入SQL的查询树,在使用CREATE FOREIGN TABLE或IMPORT FOREIGN 。SCHEMA命令时，这些表的相关信息存储在了pg_catalog.pg_class和pg_catalog.pg_foreign_table中。

连接远程服务器

要连接到远程服务器, 规划器 (或执行器) 使用特定库连接到远程数据库服务器。

例如, 要连接到远程PostgreSQL服务器, postgres_fdw使用 libpq。要连接到 mysql服务器, 则使用由interpriseDB开发的mysql_fdw,mysql_fdw使用libmysqlclient库。

连接参数 (如用户名、服务器IP地址和端口号) ，在执行CREATE USER MAPPING和CREATE SERVER命令后，存储在pg_catalog.pg_user_mapping和pg_catalog.pg_foreign_server使用EXPLAIN命令创建一棵计划树 (可选)PostgreSQL的FDW支持获取外部表的统计信息来估算查询的计划树, 这些值被一些FDW扩展使用, 如 postgres_fdw、mysql_fdw、tds_fdw和 jdbc2_fdw。

如果在使用ALTER_SERVER时，use_remote_estimate的值设为on，则规划器通过执行EXPLAIN向远程服务器获取查询计划的成本;否则, 默认情况使用嵌入的常量值。

ALTER SERVER remote_server_name OPTIONS (use_remote_estimate 'on');

虽然某些扩展使用 EXPLAIN 命令的值, 但只有postgres fdw 才能反映 EXPLAIN 命令的结果, 因为PostgreSQL的EXPLAIN命令同时返回启动和总成本。

postgres_fdw 概述

postgres_fdw模块提供了外部数据包装器postgres_fdw，它可以被用来访问存储在外部PostgreSQL服务器中的数据。

这个模块提供的功能大体上覆盖了较老的dblink模块的功能。但是postgres_fdw提供了更透明且更兼容标准的语法来访问远程表，并且可以在很多情况下给出更好的性能。

postgres_fdw实验 1

create extension postgres_fdw; --创建扩展




create server db0 foreign data wrapper postgres_fdw OPTIONS (host '172.16.9.181', port




'5432', dbname 'vcssmp', use_remote_estimate 'true');--创建远端映射
复制

host远端数据库IP地址

port远端数据库端口

dbname 远端数据库名称

如果use_remote_estimate选项处于打开true状态 (默认值为关闭 false), 规划器执行EXPLAIN命令, 以估算每个计划路径的成本.

postgres_fdw实验 2

create user mapping FOR mpa SERVER db0 options (user 'vcssmp', password 'vcssmp');

本地用户到远端的映射

import FOREIGN SCHEMA public from server db0 into public ;

将远端的用户下的 public模式下的表映射到本地public模式下

postgres_fdw实验 3

跳过test123表

import FOREIGN SCHEMA public except (test123) from server sdms3 into public

只导入test123表

import FOREIGN SCHEMA public limit to (test123) from server sdms3 into public

注意, 导入时会同时将视图, 物化视图, 外部表都一并导入, 除非使用except来排除.

select * from pg_foreign_data_wrapper;
select * from pg_foreign_server ;
select * from pg_user_mappings;
DROP FOREIGN TABLE bb;
复制

如果远端的表有drop或者create,在本地不会同步更新。所以如果远端删除了表，再通过外部表查询，会提示不存在。还可以使用limit to或者except来控制只导某些表, 或排除某些表.

MySQL FDW

使用mysql_fdw插件能够将PostgreSQL和MySQL连接，同步MySQL数据进行数据分析。https://pgxn.org/dist/mysql_fdw/

mysql_fdw插件。

postgres=> create extension mysql_fdw; 
CREATE EXTENSION
复制

创建MySQL服务器定义。

postgres=> CREATE SERVER 
postgres->      FOREIGN DATA WRAPPER mysql_fdw
postgres->      OPTIONS (host '<连接地址>', port '<连接端口>'); 
CREATE SERVER
复制

创建用户映射，将MySQL服务器定义映射到PostgreSQL的某个用户上，将来使用这个用户访问MySQL的数据。

postgres=> CREATE USER MAPPING FOR  
SERVER 
OPTIONS (username '', password ''); 
CREATE USER MAPPING




postgres=> CREATE FOREIGN TABLE ft_test (id1 int, name1 text) server mysql_server options (dbname 'test123', table_name 'test');








CREATE FOREIGN TABLE
复制

Oracle_FDW

Oracle_fdw是PG的一个外部数据接口，可以使PostgreSQL轻松跨库操作Oracle。Oracle_fdw的作用有以下两点：

PG可以跨库增删改查Oracle中的表，可以查询Oracle的视图，可以使PG中的表和Oracle中表/视图作Join查询，

类似dblink的功能。

快速将Oralce表迁移进入PostgreSQL。





http://pgxn.org/dist/oracle_fdw/ 安装Oracle Instant Client
cd /opt/oracle
unzip instantclient-basic-linux.x64-11.4.0.1.0.zip
unzip instantclient-sdk-linux.x64-11.4.0.1.0.zip
mv instantclient_11_1 instantclient
cd instantclient
#建立一下软连接
ln -s libclntsh.so.11.1 libclntsh.so
#设置环境变量
vi /etc/profile
#边界内容如下：
#oracle_home一定要写，否则编译会报错
export ORACLE_HOME=/opt/oracle1/instantclient
export OCI_LIB_DIR=$ORACLE_HOME
export OCI_INC_DIR=$ORACLE_HOME/sdk/include
#保存退出
#重启用profile文件
source /etc/profile
复制

编译oracle_fdw

[root@bogon opt]# source /home/postgres/.bashrc
[root@bogon opt]# unzip oracle_fdw-1.4.0.zip
[root@bogon opt]# cd oracle_fdw-1.4.0
[root@bogon oracle_fdw-1.4.0]# make
[root@bogon oracle_fdw-1.4.0]# make install




postgres=# create extension oracle_fdw;
CREATE EXTENSION




postgres=# create server orclfdw foreign data wrapper oracle_fdw options(dbserver ‘IP地址:1521/SID');
postgres=# grant usage on foreign server orclfdw to postgres;
postgres=# create user mapping for postgres server orclfdw  options(user ‘用户名’,password ‘密码');




postgres=# create foreign table test
(
  id  int,
  name    VARCHAR(50
) server orclfdw  options(schema ‘test',table ‘test');
复制

test用户下的 test表

file_fdw

file_fdw模块提供了外部数据封装器file_fdw，可以用来在服务器的文件系统中访问数据文件。

数据文件必须是COPY FROM 可读的格式；访问这样的数据文件当前只是可读的。　　

简单的说，就是创建一个表，表的数据来源于数据库之外的文件，而这个文件是通过copy命令导出去的。

db1=# create extension file_fdw ;
CREATE EXTENSION
复制

db1=# \dx
                                List of installed extensions
    Name     | Version |   Schema   |                      Description                     
-------------+---------+------------+-------------------------------------------------------
 file_fdw    | 1.0     | public     | foreign-data wrapper for flat file access
 pageinspect | 1.6     | public     | inspect the contents of database pages at a low level
 plpgsql     | 1.0     | pg_catalog | PL/pgSQL procedural language
(3 rows)
复制

db1=# create  table test(id int,name varchar(20));
CREATE TABLE
db1=#
db1=# insert into test(id,name) values(1,'c1');
INSERT 0 1
db1=# insert into test(id,name) values(2,'c2');
INSERT 0 1
db1=# insert into test(id,name) values(3,'c3');
INSERT 0 1
db1=# copy test to '/home/postgres/test.csv';
COPY 3
db1=# create server server_file_fdw foreign data wrapper file_fdw;
CREATE SERVER
db1=# create foreign table foreign_test (id int,name varchar(20))server server_file_fdw  options (filename '/home/postgres/test.csv');
CREATE FOREIGN TABLE




db1=# explain select * from foreign_test;
QUERY PLAN                           
-----------------------------------------------------------------
Foreign Scan on foreign_test  (cost=0.00..1.10 rows=1 width=62)
Foreign File: /home/postgres/test.csv
Foreign File Size: 15
(3 rows)
复制