当前位置：首页 > article >正文

PostgreSQL外部表FDW【file_fdw】

article 2024/11/26 6:44:21

随着数据量的不断增长和数据源的多样化，如何高效地整合和管理这些数据成为了数据库领域的一个重要挑战。PostgreSQL，作为一款功能强大的开源关系型数据库，通过其外部数据包装器（Foreign Data Wrapper，简称FDW）提供了一种优雅的解决方案。在本文中，我们将深入探讨PostgreSQL外部表FDW的概念、功能、使用场景以及如何配置和使用它。

一、引言

PostgreSQL和FDW的背景

PostgreSQL是一个功能强大、开源的对象-关系型数据库系统。它以其稳定性、可靠性和可扩展性而闻名，支持大量的SQL标准，并提供了许多现代数据库特性。随着数据驱动的决策成为企业运营的核心，对高效、灵活地管理和访问多种数据源的需求也日益增长。在这种背景下，PostgreSQL的外部数据包装器（Foreign Data Wrapper，FDW）功能应运而生。FDW是PostgreSQL的一个扩展功能，它允许用户从其他外部数据源（如其他数据库、Web服务或文件）中访问和操作数据，就好像这些数据存储在本地PostgreSQL数据库中一样。

为什么需要外部表FDW

随着企业数据生态系统的复杂化，数据往往分散在不同的系统和平台中。将这些数据整合到一个统一的视图中进行分析和报告变得至关重要。然而，传统的数据整合方法，如ETL（提取、转换、加载）过程，可能既耗时又容易出错。此外，实时或近实时的数据访问需求也超出了传统批处理能力的范围。

FDW通过提供一个透明的数据访问层来解决这些问题。它使得用户能够：

无缝集成：无需移动或复制数据，即可直接查询和操作外部数据源。
实时分析：获取最新的外部数据，以支持实时决策和报告。
简化架构：减少数据冗余和复杂性，通过统一的SQL接口管理多种数据源。

二、FDW基础概念

什么是FDW

在PostgreSQL中，外部数据包装器（Foreign Data Wrapper，FDW）是一个可扩展的框架，用于访问和操作存储在外部数据源中的数据。FDW实现了一组标准的SQL接口，这些接口允许PostgreSQL将外部数据源视为本地表进行查询和操作。

FDW的主要功能和优势

主要功能：

数据访问透明性：允许用户通过标准的SQL语句查询外部数据源，无需了解底层数据的具体存储和访问细节。
数据集成：能够将来自不同外部数据源的数据与本地PostgreSQL数据无缝集成，提供一个统一的视图。
数据同步与一致性：支持在外部数据源和本地数据库之间实现数据同步，确保数据的一致性。
性能和可扩展性：FDW框架可以针对特定的外部数据源进行优化，以提供高效的查询性能，并且易于扩展以支持新的数据源。

优势：

灵活性：FDW支持多种数据源类型，包括关系型数据库、NoSQL数据库、Web服务等，为用户提供了极大的灵活性。
成本效益：通过减少数据迁移和复制的需求，FDW可以降低数据整合和管理的成本。
简化开发：开发人员可以使用熟悉的SQL语法和工具来操作外部数据，无需学习新的查询语言或API。
可维护性：通过集中管理外部数据连接和访问逻辑，FDW可以简化数据访问层的维护和更新工作。

FDW的工作原理

FDW的工作原理可以概括为以下几个步骤：

安装与配置：首先，需要在PostgreSQL数据库中安装相应的FDW扩展，并配置与外部数据源的连接信息（如服务器地址、认证凭据等）。

定义外部表：使用CREATE FOREIGN TABLE语句在PostgreSQL中定义一个或多个外部表。这些外部表映射到外部数据源中的实际数据表或视图。在定义外部表时，需要指定所使用的FDW以及相关的连接选项。

查询与操作：一旦外部表被定义，用户就可以像查询本地表一样使用标准的SQL语句来查询外部表中的数据。当执行查询时，FDW负责将SQL语句转换为外部数据源可以理解的查询语言（如另一个数据库的SQL方言或API调用），并将结果返回给PostgreSQL。同样地，用户也可以通过INSERT、UPDATE或DELETE语句来操作外部表中的数据（如果外部数据源支持这些操作）。

数据同步与一致性维护（可选）：在某些情况下，可能需要保持外部数据源与本地PostgreSQL数据库之间的数据同步。这可以通过配置FDW来实现，例如使用触发器在外部数据源上捕获数据更改并自动更新本地数据库中的相应记录。

三、安装与配置（以file_fdw为例）

1. 安装file_fdw扩展

在PostgreSQL中，要使用file_fdw访问服务器文件系统中的数据文件，首先需要安装该扩展。可以通过以下SQL命令查看是否安装了file_fdw。

postgres=# create extension file_fdw;
CREATE EXTENSION

执行上述命令后，如果安装成功，系统将返回确认消息。
如果未安装，会提示ERROR: extension "file_fdw" is not available，如下所示：

postgres=# create extension file_fdw;
2024-11-20 22:45:43.668 CST [512783] ERROR:  extension "file_fdw" is not available
2024-11-20 22:45:43.668 CST [512783] DETAIL:  Could not open extension control file "/usr/pgsql-15/share/extension/file_fdw.control": 没有那个文件或目录.
2024-11-20 22:45:43.668 CST [512783] HINT:  The extension must first be installed on the system where PostgreSQL is running.
2024-11-20 22:45:43.668 CST [512783] STATEMENT:  create extension file_fdw;
ERROR:  extension "file_fdw" is not available
DETAIL:  Could not open extension control file "/usr/pgsql-15/share/extension/file_fdw.control": 没有那个文件或目录.
HINT:  The extension must first be installed on the system where PostgreSQL is running.

找到postgres的安装目录，我的在/app/postgresql-15.8/，有个contrib文件夹，里面有个目录名字是file_fdw，通过cd /app/postgresql-15.8/contrib/file_fdw指令进入这个目录。然后按顺序执行：
1，make
2，make install
执行过程如下：

[root@pcp file_fdw]# make
make -C ../../src/backend generated-headers
make[1]: 进入目录“/app/postgresql-15.8/src/backend”
make -C catalog distprep generated-header-symlinks
make[2]: 进入目录“/app/postgresql-15.8/src/backend/catalog”
make[2]: 对“distprep”无需做任何事。
make[2]: 对“generated-header-symlinks”无需做任何事。
make[2]: 离开目录“/app/postgresql-15.8/src/backend/catalog”
make -C utils distprep generated-header-symlinks
make[2]: 进入目录“/app/postgresql-15.8/src/backend/utils”
make[2]: 对“distprep”无需做任何事。
make[2]: 对“generated-header-symlinks”无需做任何事。
make[2]: 离开目录“/app/postgresql-15.8/src/backend/utils”
make[1]: 离开目录“/app/postgresql-15.8/src/backend”
gcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wimplicit-fallthrough=3 -Wcast-function-type -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -Wno-format-truncation -Wno-stringop-truncation -g -O2 -fPIC -I. -I. -I../../src/include  -D_GNU_SOURCE   -c -o file_fdw.o file_fdw.c
gcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wimplicit-fallthrough=3 -Wcast-function-type -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -Wno-format-truncation -Wno-stringop-truncation -g -O2 -fPIC file_fdw.o -L../../src/port -L../../src/common   -Wl,--as-needed -Wl,-rpath,'/usr/pgsql-15/lib',--enable-new-dtags  -shared -o file_fdw.so
[root@pcp file_fdw]# make install
make -C ../../src/backend generated-headers
make[1]: 进入目录“/app/postgresql-15.8/src/backend”
make -C catalog distprep generated-header-symlinks
make[2]: 进入目录“/app/postgresql-15.8/src/backend/catalog”
make[2]: 对“distprep”无需做任何事。
make[2]: 对“generated-header-symlinks”无需做任何事。
make[2]: 离开目录“/app/postgresql-15.8/src/backend/catalog”
make -C utils distprep generated-header-symlinks
make[2]: 进入目录“/app/postgresql-15.8/src/backend/utils”
make[2]: 对“distprep”无需做任何事。
make[2]: 对“generated-header-symlinks”无需做任何事。
make[2]: 离开目录“/app/postgresql-15.8/src/backend/utils”
make[1]: 离开目录“/app/postgresql-15.8/src/backend”
/usr/bin/mkdir -p '/usr/pgsql-15/share/extension'
/usr/bin/mkdir -p '/usr/pgsql-15/share/extension'
/usr/bin/mkdir -p '/usr/pgsql-15/lib'
/usr/bin/install -c -m 644 ./file_fdw.control '/usr/pgsql-15/share/extension/'
/usr/bin/install -c -m 644 ./file_fdw--1.0.sql  '/usr/pgsql-15/share/extension/'
/usr/bin/install -c -m 755  file_fdw.so '/usr/pgsql-15/lib/'

执行完成后就安装好了，如果需要安装postgres_fdw也可以参考此步骤。

2. 配置外部服务器（foreign server）

安装完file_fdw扩展后，需要配置一个外部服务器，以便指定要访问的文件系统位置。使用以下SQL命令创建外部服务器：

postgres=# CREATE SERVER file_server FOREIGN DATA WRAPPER file_fdw;
CREATE SERVER --这是运行结果

这里file_server是外部服务器的名称，可以根据需要自定义。

3. 用户映射（user mapping）

对于file_fdw，通常不需要创建用户映射，因为它不涉及远程数据库的身份验证。file_fdw直接访问本地文件系统中的文件。不过我们可以先创建一下数据文件

--创建员工表
postgres=# CREATE TABLE employees (
    id SERIAL PRIMARY KEY,
    name TEXT,
    age INTEGER,
    department TEXT
);
CREATE TABLE

--插入三条数据
postgres=# INSERT INTO employees (name, age, department) VALUES
('John Doe', 30, 'HR'),
('Jane Smith', 28, 'Engineering'),
('Bob Johnson', 35, 'Marketing');
INSERT 0 3

--导出表中的数据到/home/postgres/employees.csv中
postgres=# \COPY employees TO '/home/postgres/employees.csv' with DELIMITER ',' CSV HEADER;
COPY 3

查看刚才导出的文件内容

[root@pcp postgres]# cat employees.csv
id,name,age,department
1,John Doe,30,HR
2,Jane Smith,28,Engineering
3,Bob Johnson,35,Marketing

四、创建与使用外部表（以file_fdw为例）

1. 创建外部表

使用file_fdw创建外部表时，需要指定要访问的文件路径、文件格式等选项。以下是一个创建外部表的示例：

CREATE FOREIGN TABLE csv_employees (
    id integer,
    name text,
    age integer,
    department text
) SERVER file_server OPTIONS (filename '/home/postgres/employees.csv', format 'csv', header 'true');

在这个示例中，csv_employees 是外部表的名称，/home/postgres/employees.csv是要访问的CSV文件路径，也就是刚才保存的数据，format 'csv'指定文件格式为CSV，header 'true'表示文件包含标题行。

2. 查询外部表数据

创建外部表后，可以使用标准的SQL查询语句来查询外部表中的数据：

postgres=# SELECT * FROM csv_employees;
 id |    name     | age | department
----+-------------+-----+-------------
  1 | John Doe    |  30 | HR
  2 | Jane Smith  |  28 | Engineering
  3 | Bob Johnson |  35 | Marketing
(3 rows)

返回外部表中的所有数据。

3. 外部表与本地表的差异和限制

数据位置：外部表的数据存储在数据库外部的文件系统中，而不是在数据库内部。

只读性：目前基于file_fdw的外部表通常仅支持只读操作，不支持INSERT、UPDATE或DELETE操作。

性能考虑：查询外部表可能比查询本地表慢，特别是当处理大量数据或需要复杂数据处理时。

格式限制：file_fdw支持的文件格式有限，如CSV、TEXT等。需要确保外部数据的格式与创建外部表时指定的格式相匹配。

安全性：访问外部文件可能涉及文件系统级别的权限和安全性问题，需要确保适当的访问控制和安全措施。

通过合理使用PostgreSQL的外部表FDW，我们可以方便地整合和查询位于服务器文件系统中的数据，从而简化数据整合和分析的过程。然而，在利用FDW时，我们也需要注意其限制，例如只读性、性能可能下降、数据一致性和安全性等问题。因此，在实际应用中，我们需要综合考虑这些因素，确保数据访问的高效性、安全性和准确性。

查看全文

http://www.kler.cn/a/402595.html