投稿
  1. 云图网首页
  2. 技术专区
  3. 大数据

PostgreSQL访问外部服务器postgres_fdw

• 大数据

一、PostgreSQL的postgres_fdw扩展

  • 1、PostgreSQL的postgres_fdw（Foreign Data Wrapper）是一个模块，它允许PostgreSQL数据库访问存储在外部PostgreSQL服务器中的数据。此模块提供的功能与旧版dblink模块的功能有很大重叠。但postgres_fdw它提供了更透明、更符合标准的语法来访问远程表，并且在许多情况下可以提供更好的性能。
  • 2、使用postgres_fdw，用户可以创建外部服务器对象、用户映射和外部表，以便像操作本地表一样操作远程数据库中的数据。这个扩展支持SELECT、INSERT、UPDATE和DELETE操作，并且可以在许多情况下提供优于dblink的性能.
  • 3、要使用postgres_fdw，首先需要在PostgreSQL数据库中安装该扩展，然后创建一个指向远程PostgreSQL服务器的外部服务器对象，并为每个需要访问远程数据库的本地用户创建用户映射。之后，可以创建外部表来映射远程数据库中的表，这样就可以通过这些外部表查询、插入、更新或删除远程数据库中的数据.
  • 4、postgres_fdw的使用涉及到一系列的SQL命令，包括CREATE EXTENSION、CREATE SERVER、CREATE USER MAPPING和CREATE FOREIGN TABLE。这些命令共同构成了在PostgreSQL中设置和使用外部数据访问的基础.
  • 5、postgres_fdw的实现原理包括外部数据封装器、外部服务器对象、用户映射和外部表的概念。这些组件协同工作，使得PostgreSQL能够将外部数据封装起来，并通过标准的SQL查询语法进行操作.
  • 6、需要访问更多外部数据库（如：Oracle、MySql、SqlServer等），可以在此链接下载： https://wiki.postgresql.org/wiki/Foreign_data_wrappers

二、部署流程

2.1、环境规划

  • 说明：目的是通过本地库访问远程库。

环境规划目录如下：

项目 目标库(本地库) 远程库
IP地址 192.168.1.8 137.78.97.88
用户名 sy postgres
密 码 Sy@123 postgres
数据库 postgres postgres
版本 17beta1 16.2

2.2、使用postgres用户，并将目录切换到扩展目录

代码如下：

[root@Server ~]# su postgres
[postgres@Server root]$
[postgres@Server root]$ cd /install/postgresql-17beta1/contrib/postgres_fdw/
[postgres@Server postgres_fdw]$ ls
connection.c  option.c                    postgres_fdw.h
deparse.c     postgres_fdw--1.0--1.1.sql  shippable.c
expected      postgres_fdw--1.0.sql       sql
Makefile      postgres_fdw.c
meson.build   postgres_fdw.control
[postgres@Server postgres_fdw]$

2.3、编译安装

  • 说明： 接上，目录切换到/install/postgresql-17beta1/contrib/postgres_fdw，使用编译安装。

代码如下：

[postgres@Server postgres_fdw]$ make install
make -C ../../src/backend generated-headers
make[1]: 进入目录“/install/postgresql-17beta1/src/backend”
make -C ../include/catalog generated-headers
make[2]: 进入目录“/install/postgresql-17beta1/src/include/catalog”
make[2]: 对“generated-headers”无需做任何事。
make[2]: 离开目录“/install/postgresql-17beta1/src/include/catalog”
make -C nodes generated-header-symlinks
make[2]: 进入目录“/install/postgresql-17beta1/src/backend/nodes”
make[2]: 对“generated-header-symlinks”无需做任何事。
make[2]: 离开目录“/install/postgresql-17beta1/src/backend/nodes”
make -C utils generated-header-symlinks
make[2]: 进入目录“/install/postgresql-17beta1/src/backend/utils”
make -C adt jsonpath_gram.h
make[3]: 进入目录“/install/postgresql-17beta1/src/backend/utils/adt”
make[3]: “jsonpath_gram.h”已是最新。
make[3]: 离开目录“/install/postgresql-17beta1/src/backend/utils/adt”
make[2]: 离开目录“/install/postgresql-17beta1/src/backend/utils”
make[1]: 离开目录“/install/postgresql-17beta1/src/backend”
make -C ../../src/interfaces/libpq all
make[1]: 进入目录“/install/postgresql-17beta1/src/interfaces/libpq”
make -C ../../../src/port all
make[2]: 进入目录“/install/postgresql-17beta1/src/port”
make[2]: 对“all”无需做任何事。
make[2]: 离开目录“/install/postgresql-17beta1/src/port”
make -C ../../../src/common all
make[2]: 进入目录“/install/postgresql-17beta1/src/common”
make[2]: 对“all”无需做任何事。
make[2]: 离开目录“/install/postgresql-17beta1/src/common”
make[1]: 离开目录“/install/postgresql-17beta1/src/interfaces/libpq”
gcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wimplicit-fallthrough=3 -Wcast-function-type -Wshadow=compatible-local -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -Wno-format-truncation -Wno-stringop-truncation -O2 -fPIC -fvisibility=hidden -I../../src/interfaces/libpq -I. -I. -I../../src/include  -D_GNU_SOURCE   -c -o connection.o connection.c
gcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wimplicit-fallthrough=3 -Wcast-function-type -Wshadow=compatible-local -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -Wno-format-truncation -Wno-stringop-truncation -O2 -fPIC -fvisibility=hidden -I../../src/interfaces/libpq -I. -I. -I../../src/include  -D_GNU_SOURCE   -c -o deparse.o deparse.c
gcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wimplicit-fallthrough=3 -Wcast-function-type -Wshadow=compatible-local -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -Wno-format-truncation -Wno-stringop-truncation -O2 -fPIC -fvisibility=hidden -I../../src/interfaces/libpq -I. -I. -I../../src/include  -D_GNU_SOURCE   -c -o option.o option.c
gcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wimplicit-fallthrough=3 -Wcast-function-type -Wshadow=compatible-local -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -Wno-format-truncation -Wno-stringop-truncation -O2 -fPIC -fvisibility=hidden -I../../src/interfaces/libpq -I. -I. -I../../src/include  -D_GNU_SOURCE   -c -o postgres_fdw.o postgres_fdw.c
gcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wimplicit-fallthrough=3 -Wcast-function-type -Wshadow=compatible-local -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -Wno-format-truncation -Wno-stringop-truncation -O2 -fPIC -fvisibility=hidden -I../../src/interfaces/libpq -I. -I. -I../../src/include  -D_GNU_SOURCE   -c -o shippable.o shippable.c
gcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wimplicit-fallthrough=3 -Wcast-function-type -Wshadow=compatible-local -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -Wno-format-truncation -Wno-stringop-truncation -O2 -fPIC -fvisibility=hidden -shared -o postgres_fdw.so  connection.o deparse.o option.o postgres_fdw.o shippable.o -L../../src/port -L../../src/common -L../../src/interfaces/libpq -lpq   -Wl,--as-needed -Wl,-rpath,'/postgres/server/lib',--enable-new-dtags  -fvisibility=hidden
/usr/bin/mkdir -p '/postgres/server/lib'
/usr/bin/mkdir -p '/postgres/server/share/extension'
/usr/bin/mkdir -p '/postgres/server/share/extension'
/usr/bin/install -c -m 755  postgres_fdw.so '/postgres/server/lib/postgres_fdw.so'
/usr/bin/install -c -m 644 ./postgres_fdw.control '/postgres/server/share/extension/'
/usr/bin/install -c -m 644 ./postgres_fdw--1.0.sql ./postgres_fdw--1.0--1.1.sql  '/postgres/server/share/extension/'
[postgres@Server postgres_fdw]$

2.4、编译安装

  • 说明： 接上，检查/postgres/server/share/extension/目录是否安装成功，如下内容，说明postgres_fdw已安装成功。

代码如下：

[postgres@Server ~]$ ls /postgres/server/share/extension/
plpgsql--1.0.sql            tds_fdw--2.0.1--2.0.2.sql
plpgsql.control             tds_fdw--2.0.2--2.0.3.sql
postgres_fdw--1.0--1.1.sql  tds_fdw--2.0.3.sql
postgres_fdw--1.0.sql       tds_fdw.control
postgres_fdw.control

2.5、检查当前扩展情况

  • 说明： 如下内容，目前只有 plpgsql扩展。

代码如下：

postgres=#
postgres=# dx
                 List of installed extensions
  Name   | Version |   Schema   |         Description
---------+---------+------------+------------------------------
 plpgsql | 1.0     | pg_catalog | PL/pgSQL procedural language
(1 row)

postgres=#

2.6、安装postgres_fdw扩展

  • 说明： 安装扩展，扩展名称：postgres_fdw。

代码如下：

postgres=# CREATE EXTENSION postgres_fdw;
CREATE EXTENSION
postgres=#
postgres=#

2.7、创建登录用户，并授权访问扩展。

  • 说明： 创建登录用户sy，并授权访问扩展postgres_fdw

代码如下：

postgres=#
postgres=# create user sy with password 'Sy@123';
CREATE ROLE
postgres=#
postgres=#
postgres=# grant usage on foreign data wrapper postgres_fdw to sy;
GRANT
postgres=#

2.8、创建远程服务器

  • 说明： 创建远程服务器，远程服务器IP地址：137.78.97.88，端口：5432，数据库名称：postgres。
  • 1.创建远程服务器

代码如下：

postgres=# create server pg_fdw foreign data wrapper postgres_fdw options
(host '137.78.97.88',port '5432',dbname 'postgres');
CREATE SERVER
postgres=#

备注：

  • pg_fdw：远程数据库的名称。
  • server_type、server_version：可选，可能对一些外部数据封装有用。
  • postgres_fdw：外部数据分装名字，这里我们是postgres_fdw。
  • options：其他可选参数，包括数据库地址，数据库名称和端口等。
  • 2.查看创建的远程服务器

代码如下：

postgres=#
postgres=# SELECT srvname
  FROM pg_foreign_table t
  JOIN pg_foreign_server s ON s.oid = t.ftserver;
  srvname
-----------
 sqlserver
 pg_fdw
(2 rows)

2.9、创建用户映射

  • 说明： 指定远程访问数据库的用户，并将创建的远程访问数据库的用户，何创建的外部服务pg_fdw服务器一起被封装起来，拥有foreign server权限的用户可以创建映射用户。

代码如下：

postgres=# create user mapping for sy server pg_fdw options (user 'postgres',password '123');
CREATE USER MAPPING

备注：

  • user_name：现有的用户映射到远程server。
  • password：远程服务器密码。
  • pg_fdw：当前需要用户映射到的远程数据库服务器名。
  • options：其他可选，包括用户名、密码等。

2.10、创建远程访问表

  • 说明：远程表名称：foreign_actor，远程表模式：schema，远程表名称：actor。

代码如下：

postgres=# create foreign table foreign_actor(actor_id integer,first_name character varying(45),last_name character varying(45),last_update timestamp)server pg_fdw options(schema_name 'public',table_name 'actor');
CREATE FOREIGN TABLE
postgres=#

2.11、查询远程表

代码如下：

postgres=#
postgres=# select*from foreign_actor;
 actor_id | first_name  |  last_name   |     last_update
----------+-------------+--------------+---------------------
        1 | PENELOPE    | GUINESS      | 2006-02-15 04:34:33
        2 | NICK        | WAHLBERG     | 2006-02-15 04:34:33
        3 | ED          | CHASE        | 2006-02-15 04:34:33
        4 | JENNIFER    | DAVIS        | 2006-02-15 04:34:33
        5 | JOHNNY      | LOLLOBRIGIDA | 2006-02-15 04:34:33
        6 | BETTE       | NICHOLSON    | 2006-02-15 04:34:33
        7 | GRACE       | MOSTEL       | 2006-02-15 04:34:33
        8 | MATTHEW     | JOHANSSON    | 2006-02-15 04:34:33
        9 | JOE         | SWANK        | 2006-02-15 04:34:33

2.12、给外部表添加数据

代码如下：

postgres=#
postgres=# insert into foreign_actor values (301,'SYTC','CHSK',CURRENT_DATE);
INSERT 0 1
postgres=#

2.13、修改外部表数据

代码如下：

postgres=# select*from foreign_actor where actor_id=301;
 actor_id | first_name | last_name |     last_update
----------+------------+-----------+---------------------
      301 | SYTC       | CHSK      | 2024-06-29 00:00:00
(1 row)

postgres=#
postgres=# update foreign_actor set first_name='HKT' where actor_id=301;
UPDATE 1
postgres=#

三、常见问题

3.1、安装依赖：确保你的系统上安装了libpq库，并且它可以被编译器找到。

3.2、检查版本兼容性：确认你的PostgreSQL版本支持postgres_fdw。

3.3、编译安装：按照正确的步骤编译和安装postgres_fdw。

四、总结

4.1、postgresql_fdw功能图谱如下

PostgreSQL访问外部服务器postgres_fdw

4.2、安装流程

使用CREATE EXTENSION postgres_fdw;命令在数据库中安装postgres_fdw扩展。

  • 创建外部服务器： 使用CREATE SERVER命令创建一个外部服务器对象，指定远程数据库的连接信息，如主机地址、端口和数据库名称。
  • 创建用户映射： 使用CREATE USER MAPPING命令为本地用户创建一个远程用户映射，指定远程数据库的用户名和密码。
  • 创建外部表： 使用CREATE FOREIGN TABLE命令创建一个外部表，指定远程服务器、架构名称和表名称。
  • 查询和操作远程数据： 通过外部表可以像操作本地表一样查询、插入、更新和删除远程数据库中的数据。
  • 在使用postgres_fdw时，需要注意的是，远程数据库的用户必须具有相应的权限，并且网络通信必须配置正确以允许数据库之间的连接。此外，postgres_fdw支持异步执行，这可以提高处理远程数据时的性能.

4.3、PostgreSQL FDW支持哪些类型的数据源连接

PostgreSQL的Foreign Data Wrapper (FDW) 是一个功能强大的特性，它允许用户透明地访问存储在PostgreSQL数据库之外的数据。FDW可以连接到多种类型的数据源，包括但不限于：

  • 其他关系型数据库：例如Oracle、MySQL、SQLite等，通过相应的FDW扩展如oracle_fdw, mysql_fdw等实现连接。
  • NoSQL数据库：例如MongoDB、Redis等，通过专门为此设计的FDW扩展进行连接。
  • 文件系统：可以通过file_fdw访问文本文件、CSV文件等。
  • 大数据平台：例如Hive/Hadoop/HDFS等，通过相应的FDW扩展实现集成。
  • 云存储服务：例如Amazon S3等，通过支持特定云服务的FDW扩展进行访问。
  • FDW的灵活性使得PostgreSQL成为一个多模型数据库管理系统，能够作为一个单一的接口来整合和查询多种不同类型的数据源。

原创文章，作者：kirin，如若转载，请注明出处：https://blog.ytso.com/tech/bigdata/317958.html

赞 (0)
0 0
上一篇 1天前
下一篇 1天前

相关推荐

发表回复

登录后才能评论