云贝教育 |【技术文章】POSTGRESQL FDW应用

2024-03-20 05:44:03
开发
39

注: 本文为云贝教育刘峰原创，请尊重知识产权，转发请注明出处，不接受任何抄袭、演绎和未经注明出处的转载。

前言

Wrapper（FDW）是一项关键特性，它赋予数据库用户直接通过SQL语句访问存储于外部数据源的能力。遵循SQL/MED标准设计，FDW使PostgreSQL能够无缝对接多种异构数据库系统以及非数据库类数据源。

FDW机制由四个核心组件构成：

Foreign Data Wrapper：特定于各数据源的库，定义了如何建立与外部数据源的连接、执行查询及处理其他操作。例如，“postgres_fdw”用于连接其他PostgreSQL服务器，“mysql_fdw”则专门连接MySQL数据库。
Foreign Server：在本地PostgreSQL中定义一个外部服务器对象，对应实际的远程或非本地数据存储实例。
User Mapping：为每个外部服务器设置用户映射，明确哪些本地用户有权访问，并提供相应的认证信息，如用户名和密码。
Foreign Table：在本地数据库创建表结构，作为外部数据源中表的映射。对这些“外表”发起的SQL查询将被转换并传递给相应的FDW，在外部数据源上执行。

随着FDW生态的日益繁荣，各类针对不同数据源的FDW插件层出不穷，如postgres_fdw实现PostgreSQL间的互连互通，mysql_fdw、oracle_fdw分别用于连接MySQL和Oracle数据库，还有file_fdw可用于读取文本文件或CSV等非结构化数据。

接下来，我们重点介绍FDW功能使用。

一、外部表

使用PostgreSQL的file_fdw扩展，可以创建一个外部表来读取和查询文件系统中的文本或CSV文件。以下是如何通过file_fdw实现外部表的详细步骤：

使用场景：当有个1G表数据，CSV格式，需要和本地表运算出报表结果

1.1 准备csv数据

create table testpc (oid int,relname text);
insert into testpc select oid,relname from pg_class limit 100;
COPY (SELECT * FROM testpc) TO '/tmp/testpc.csv' WITH CSV DELIMITER ',';

1.2 创建外部表

1、--添加扩展
CREATE EXTENSION file_fdw;

2、--创建 SERVER FOR file
create server pg_file_server foreign data wrapper file_fdw;

3、--创建外部表，与外部文件结构一致
create foreign table testpc_file_fdw
(oid int,relname text)
server pg_file_server
options(filename '/tmp/testpc.csv',format 'csv',header 'true',delimiter ',');

1.3 与本地表进行运算

postgres=# explain select count(1) from pg_class a,testpc_file_fdw b where a.oid=b.oid;
                                       QUERY PLAN                                       
----------------------------------------------------------------------------------------
 Aggregate  (cost=28.82..28.83 rows=1 width=8)
   ->  Hash Join  (cost=9.43. .28.63 rows=75 width=0)
         Hash Cond: (a.oid = (b.oid)::oid)
         ->  Seq Scan on pg_class a  (cost=0.00..16.96 rows=396 width=4)
         ->  Hash  (cost=8.50..8.50 rows=75 width=4)
               ->  Foreign Scan on testpc_file_fdw b  (cost=0.00..8.50 rows=75 width=4)
                     Foreign File: /tmp/testpc.csv
                     Foreign File Size: 2409 b
(8 rows)

二、访问其它集群中的pg库

由于pg内部不同的数据库不能访问，pg_fdw就能解决此问题。

注：如无特殊说明，以下操作均在当前库操作

2.1 创建扩展

create extension postgres_fdw;

2.2 创建外部数据服务

 CREATE SERVER pgserver FOREIGN DATA WRAPPER postgres_fdw OPTIONS (host '192.168.2.104', dbname 'postgres', port '5666');

查看外部服务

postgres=# \des+
                                                                List of foreign servers
   Name   |  Owner   | Foreign-data wrapper | Access privileges | Type | Version |                      FDW options | Description 
----------+----------+----------------------+-------------------+------+---------+--------------------------------------------------------+-------------
 pgserver | postgres | postgres_fdw         |                   |      |         | (host '192.168.2.104', dbname 'postgres', port '5666') | 
(1 row)

删除外部服务

rop SERVER pgserver cascade;

2.3 将外部服务授权给指定用户

grant usage on FOREIGN SERVER pgserver to postgres;

2.4 创建外部服务的mapping

用于连接指向用户及指定数据库

CREATE USER MAPPING FOR postgres SERVER pgserver OPTIONS (user'postgres', password'postgres');

查看mapping关系

select * from information_schema.user_mappings;
 authorization_identifier | foreign_server_catalog | foreign_server_name 
--------------------------+------------------------+---------------------
 postgres                 | postgres               | pgserver
(1 row)


postgres=# \deu
List of user mappings
  Server  | User name 
----------+-----------
 pgserver | postgres
(1 row)

如果密码配置错误，用以下语句修改

 ALTER USER MAPPING FOR  postgres SERVER pgserver OPTIONS (SET password'1qaz@WSX' );

补充：

删除外部mapping

DROP USER MAPPING [ IF EXISTS ] FOR { user_name | USER | CURRENT_ROLE | CURRENT_USER | PUBLIC } SERVER server_name

2.5 在远端创建表

postgres=# create table test1(id int);
CREATE TABLE
postgres=# insert into test1 values(1);
INSERT 0 1
postgres=# select * from test1;
 id 
----
  1
(1 row)
postgres=# \d test1
               Table "public.test1"
 Column |  Type   | Collation | Nullable | Default 
--------+---------+-----------+----------+---------
 id     | integer |           |          |

2.6 创建外部表

CREATE FOREIGN TABLE localt11 (id int) SERVER pgserver OPTIONS (table_name 'test1');

补充：

查看外部表

postgres=# \det
    List of foreign tables
 Schema |  Table   |  Server  
--------+----------+----------
 public | localt1  | pgserver
 public | localt11 | pgserver
(2 rows)

删除外部表

drop FOREIGN table localt11 ;

2.7 访问自身集群中的pg库

实现过程和第二章节一样

实现目标：通过postgres数据库中的postgres用户访问appdb中appuser在schema:appuser中的表t1

\c postgres postgres 
postgres=#  CREATE SERVER  appdbserver FOREIGN DATA WRAPPER postgres_fdw OPTIONS (host '192.168.2.103', dbname 'appdb', port '5666');
CREATE SERVER

postgres=# CREATE USER MAPPING FOR postgres SERVER appdbserver OPTIONS (user'appuser', password'1qaz@WSX');
CREATE USER MAPPING

\c appdb appuser
appdb=> select * from t1;
 id 
----
  1
  2
  3
  4
(4 rows)

\c postgres postgres 
CREATE FOREIGN TABLE lt2 (id int) SERVER appdbserver OPTIONS (schema_name'appuser',table_name 't1');

postgres=# select * from lt2;
 id 
----
  1
  2
  3
  4
(4 rows)

注意：

添加外部表时，需要指定schme_name参数，否则会到默认public模式下找数据

三、访问oracle中的表

3.1 安装oracle客户端

根据不同数据库版本，自行到oracle官网下载，如LINUX.X64_193000_client_home包

上传解压即可

3.2 配置环境变量

/etc/profile

export ORACLE_HOME=/root/oracle
export PATH=$ORACLE_HOME/bin:$PATH:/pg14/soft/bin
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:$LD_LIBRARY_PATH
export TNS_ADMIN=$ORACLE_HOME/network/admin

3.3 配置oracle全局动态库

echo "/root/oracle/lib" >> /etc/ld.so.conf
ldconfig

3.4 测试连接oracle

sqlplus sys/oracle@192.168.2.115:1521/orclcdb as sysdba

3.5 安装oracle_fdw

下载地址：GitHub - laurenz/oracle_fdw: PostgreSQL Foreign Data Wrapper for Oracle

上传解压安装
[root@pg01 oracle_fdw-master]# 
cd oracle_fdw-master/
make
make install

3.6 创建oracle_fdw扩展

[pg14@pg01 ~]$ psql postgres postgres
psql (13. 8)
Type "help" for help.

postgres=# CREATE EXTENSION oracle_fdw;
CREATE EXTENSION

如果碰到如下问题

postgres=# CREATE EXTENSION oracle_fdw;
ERROR:  could not load library "/pg14/soft/lib/postgresql/oracle_fdw.so": libclntshcore.so.19.1: cannot open shared object file: No such file or directory

先通过 ldd命令跟踪库文件的调用过程，比如

[pg14@pg01 ~]$ ldd /pg14/soft/lib/postgresql/oracle_fdw.so
        linux-vdso.so.1 =>  (0x00007ffefaf42000)
        libclntsh.so.19.1 => /pg14/soft/lib/libclntsh.so.19.1 (0x00007fdbd3063000)
        libc.so.6 => /lib64/libc.so.6 (0x00007fdbd2c95000)
        libnnz19.so => /pg14/soft/lib/libnnz19.so (0x00007fdbd254d000)
        libdl.so.2 => /lib64/libdl.so.2 (0x00007fdbd2349000)
        libm.so.6 => /lib64/libm.so.6 (0x00007fdbd2047000)
        libpthread.so.0 => /lib64/libpthread.so.0 (0x00007fdbd1e2b000)
        libnsl.so.1 => /lib64/libnsl.so.1 (0x00007fdbd1c11000)
        librt.so.1 => /lib64/librt.so.1 (0x00007fdbd1a09000)
        libaio.so.1 => /lib64/libaio.so.1 (0x00007fdbd1807000)
        libresolv.so.2 => /lib64/libresolv.so.2 (0x00007fdbd15ed000)
        /lib64/ld-linux-x86-63. so.2 (0x00007fdbd7295000)
        libclntshcore.so.19.1 => not found
        libclntshcore.so.19.1 => not found

而libclntshcore.so.19.1这个包在oracle客户端的lib目录下，可以通过以下方式尝试解决：

1、通过软链接

示例
ln -s /root/oracle/lib/libclntsh.so.12.1  /pg14/soft/lib/libclntsh.so.12.1

2、直接cp

示例
root
cp libclntshcore.so.19.1 /pg14/soft/lib/
chown pg14:pg14 /pg14/soft/lib/libclntshcore.so.19.1

3.7 创建对oracle的外部表

postgres=# create server oradb_test foreign data wrapper oracle_fdw options(dbserver '192.168.0.215:1521/ora_db');
postgres=# grant usage on foreign server oradb_test to postgres;
postgres=# create user mapping for postgres server oradb_test  options(user 'MG_APP',password 'QWERasdf');


#创建外部表
postgres=# create foreign table orat1
(
  id int 
) server oradb_test  options(schema 'appuser',table 't1');
postgres=# select * from orat1 limit 10;

四、FDW的演进与未来展望

随着时间推移，FDW的功能不断丰富和优化，包括增强性能、增加更多类型的FDW以及改善用户体验。如今，FDW已经成为PostgreSQL生态不可或缺的一部分，大大提高了其数据集成能力。

展望未来，FDW将继续成为实现跨多种数据库和非传统数据源交互的关键技术，尤其是在大数据时代背景下，对于构建复杂的企业级数据湖和数据仓库解决方案具有深远意义。同时，随着云原生和分布式计算范式的兴起，FDW也将顺应潮流，持续演进以适应更加多样化且充满挑战性的应用场景。

原文地址:https://blog.csdn.net/yunbee666/article/details/136849298 本文来自互联网用户投稿，该文观点仅代表作者本人，不代表本站立场。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如若转载，请注明出处：https://www.suanlizi.com/kf/1770204613052600320.html 如若内容造成侵权/违法违规/事实不符，请联系《酸梨子》网邮箱：1419361763@qq.com进行投诉反馈，一经查实，立即删除！

阅读全部