使用 Amazon RDS for PostgreSQL 支持的外部数据包装器 - Amazon Relational Database Service
Amazon Web Services 文档中描述的 Amazon Web Services 服务或功能可能因区域而异。要查看适用于中国区域的差异,请参阅中国的 Amazon Web Services 服务入门

使用 Amazon RDS for PostgreSQL 支持的外部数据包装器

外部数据包装器 (FDW) 是一种特定类型的扩展,提供对外部数据的访问。例如,通过使用 PostgreSQL 本机 postgres_fdw 扩展,您可以访问存储在 Amazon RDS for PostgreSQL 外部的 PostgreSQL 数据库实例中的数据。作为另一个示例,oracle_fdw 扩展允许 RDS for PostgreSQL 数据库实例与 Oracle 数据库实例结合使用。有关从 RDS for PostgreSQL 数据库实例访问 Oracle 数据的更多信息,请参阅 通过使用 oracle_fdw 扩展来使用 Oracle 数据库

在下文中,您可以了解有关几种支持的 PostgreSQL 外部数据包装器的信息。

使用 log_fdw 扩展通过 SQL 访问数据库日志

RDS for PostgreSQL 支持 log_fdw 扩展,您可以该扩展通过 SQL 界面访问数据库引擎日志。您可以查看 Amazon RDS 生成的默认 stderr 日志文件。您也可以查看逗号分隔值 (CSV) 日志,以及构建将数据整齐地拆分为多个列的外部表。为此,首先在 RDS for PostgreSQL 数据库实例上将 log_destination 参数设置为 csvlog。此设置意味着您需要为该实例使用自定义数据库参数组。要了解如何操作,请参阅在 RDS for PostgreSQL 数据库实例上使用参数

log_fdw 扩展提供了两个新函数,便于创建数据库日志的外部表:

  • list_postgres_log_files – 列出数据库日志目录中的文件,以及文件大小 (以字节为单位)。

  • create_foreign_table_for_log_file(table_name text, server_name text, log_file_name text) – 针对当前数据库中的指定文件构建外部表。

log_fdw 创建的所有函数均归 rds_superuser 所有。rds_superuser 角色的成员可以将这些函数的访问权限授予其他数据库用户。

以下示例展示了将 log_fdw 扩展与 log_destination 参数设置为 csvlog 的 RDS for PostgreSQL 数据库实例搭配使用。

使用 log_fdw 扩展

  1. 获取 log_fdw 扩展。

    postgres=> CREATE EXTENSION log_fdw; CREATE EXTENSION
  2. 创建日志服务器,作为外部数据包装程序。

    postgres=> CREATE SERVER log_server FOREIGN DATA WRAPPER log_fdw; CREATE SERVER
  3. 选择日志文件列表中的所有文件。

    postgres=> SELECT * from list_postgres_log_files() ORDER BY 1;

    示例响应如下所示。

    file_name | file_size_bytes ------------------------------+----------------- postgresql.log.2016-08-09-22.csv | 1111 postgresql.log.2016-08-09-23.csv | 1172 postgresql.log.2016-08-10-00.csv | 1744 postgresql.log.2016-08-10-01.csv | 1102 (4 rows)
  4. 为所选文件创建包含单个“log_entry”列的表。

    postgres=> SELECT create_foreign_table_for_log_file('my_postgres_error_log', 'log_server', 'postgresql.log.2016-08-09-22.csv');

    除了告知现在存在表格外,响应不提供详细信息。

    ----------------------------------- (1 row)
  5. 选择日志文件的示例。以下代码检索日志时间和错误消息描述。

    postgres=> SELECT log_time, message from my_postgres_error_log ORDER BY 1;

    示例响应如下所示。

    log_time | message ----------------------------------+--------------------------------------------------------------------------- Tue Aug 09 15:45:18.172 2016 PDT | ending log output to stderr Tue Aug 09 15:45:18.175 2016 PDT | database system was interrupted; last known up at 2016-08-09 22:43:34 UTC Tue Aug 09 15:45:18.223 2016 PDT | checkpoint record is at 0/90002E0 Tue Aug 09 15:45:18.223 2016 PDT | redo record is at 0/90002A8; shutdown FALSE Tue Aug 09 15:45:18.223 2016 PDT | next transaction ID: 0/1879; next OID: 24578 Tue Aug 09 15:45:18.223 2016 PDT | next MultiXactId: 1; next MultiXactOffset: 0 Tue Aug 09 15:45:18.223 2016 PDT | oldest unfrozen transaction ID: 1822, in database 1 (7 rows)

通过使用 oracle_fdw 扩展来使用 Oracle 数据库

要从 RDS for PostgreSQL 数据库实例访问 Oracle 数据库,您可以安装并使用 oracle_fdw 扩展。此扩展是 Oracle 数据库的外部数据包装器。要了解有关此扩展的更多信息,请参阅 oracle_fdw 文档。

Amazon RDS for PostgreSQL 版本 12.7、13.3 及更高版本支持 oracle_fdw 扩展。

启用 oracle_fdw 扩展

启用 oracle_fdw 扩展

  • 使用具有 rds_superuser 权限的账户运行以下命令。

    CREATE EXTENSION oracle_fdw;

使用链接到 Amazon RDS for Oracle Database 的外部服务器示例

以下示例展示如何使用链接到 Amazon RDS for Oracle Database 的外部服务器。

创建链接到 RDS for Oracle 数据库的外部服务器

  1. 请注意 RDS for Oracle 数据库实例上的以下内容:

    • Endpoint

    • 端口

    • 数据库名称

  2. 创建外部服务器。

    test=> CREATE SERVER oradb FOREIGN DATA WRAPPER oracle_fdw OPTIONS (dbserver '//endpoint:port/DB_name'); CREATE SERVER
  3. 将使用权授予没有 rds_superuser 权限的用户,例如 user1

    test=> GRANT USAGE ON FOREIGN SERVER oradb TO user1; GRANT
  4. 作为 user1 连接并创建到 Oracle 用户的映射。

    test=> CREATE USER MAPPING FOR user1 SERVER oradb OPTIONS (user 'oracleuser', password 'mypassword'); CREATE USER MAPPING
  5. 创建链接到 Oracle 表的外部表。

    test=> CREATE FOREIGN TABLE mytab (a int) SERVER oradb OPTIONS (table 'MYTABLE'); CREATE FOREIGN TABLE
  6. 查询外部表。

    test=> SELECT * FROM mytab; a --- 1 (1 row)

如果查询报告以下错误,请检查您的安全组和访问控制列表(ACL)以确保两个实例可以通信。

ERROR: connection for foreign table "mytab" cannot be established DETAIL: ORA-12170: TNS:Connect timeout occurred

传输中加密

PostgreSQL-to-Oracle 传输中加密基于客户端和服务器配置参数的组合。有关使用 Oracle 21c 的示例,请参阅 Oracle 文档中的关于协商加密和完整性的值。用于 Amazon RDS 上 oracle_fdw 的客户端配置有 ACCEPTED,这意味着加密取决于 Oracle 数据库服务器配置。

如果您的数据库位于 RDS for Oracle 上,请参阅 Oracle 本机网络加密 配置加密。

pg_user_mapping 和 pg_user_mappings 权限

在以下输出中,您可以找到映射到三个不同示例用户的角色和权限。用户 rdssu1rdssu2rds_superuser 角色的成员,而 user1 不是。psql 元命令 \du 可列出现有的角色。

test=> \du List of roles Role name | Attributes | Member of -----------------+------------------------------------------------------------+------------------------------------------------------------- rdssu1 | | {rds_superuser} rdssu2 | | {rds_superuser} user1 | | {}

RDS for PostgreSQL 用户只能在 pg_user_mappings 表中查看自己的用户映射 (umoptions)。具有 rds_superuser 角色的用户也不例外。例如,尽管 rdssu1rds_superuser 权限,rdssu1 也无法从该表获取所有映射。

test=> SELECT * FROM pg_user_mapping; ERROR: permission denied for table pg_user_mapping

下面是一些示例。

test=> SET SESSION AUTHORIZATION rdssu1; SET test=> SELECT * FROM pg_user_mappings; umid | srvid | srvname | umuser | usename | umoptions -------+-------+---------+--------+------------+---------------------------------- 16414 | 16411 | oradb | 16412 | user1 | 16423 | 16411 | oradb | 16421 | rdssu1 | {user=oracleuser,password=mypwd} 16424 | 16411 | oradb | 16422 | rdssu2 | (3 rows) test=> SET SESSION AUTHORIZATION rdssu2; SET test=> SEELCT * FROM pg_user_mappings; umid | srvid | srvname | umuser | usename | umoptions -------+-------+---------+--------+------------+---------------------------------- 16414 | 16411 | oradb | 16412 | user1 | 16423 | 16411 | oradb | 16421 | rdssu1 | 16424 | 16411 | oradb | 16422 | rdssu2 | {user=oracleuser,password=mypwd} (3 rows) test=> SET SESSION AUTHORIZATION user1; SET test=> SELECT * FROM pg_user_mappings; umid | srvid | srvname | umuser | usename | umoptions -------+-------+---------+--------+------------+-------------------------------- 16414 | 16411 | oradb | 16412 | user1 | {user=oracleuser,password=mypwd} 16423 | 16411 | oradb | 16421 | rdssu1 | 16424 | 16411 | oradb | 16422 | rdssu2 | (3 rows)

由于 information_schema._pg_user_mappingspg_catalog.pg_user_mappings 的实施差异,手动创建的 rds_superuser 需要额外的权限才能在 pg_catalog.pg_user_mappings 中查看密码。

rds_superuser 无需额外权限即可在 information_schema._pg_user_mappings 中查看密码。

没有 rds_superuser 角色的用户只能在以下条件下在 pg_user_mappings 中查看密码:

  • 当前用户是被映射的用户,拥有服务器或对其具有 USAGE 权限。

  • 当前用户是服务器所有者,此映射用于 PUBLIC

使用 postgres_fdw 扩展访问外部数据

您可以使用 postgres_fdw 扩展访问远程数据库服务器上表中的数据。如果您从 PostgreSQL 数据库实例设置远程连接,则访问还可用于您的只读副本。

使用 postgres_fdw 访问远程数据库服务器

  1. 安装 postgres_fdw 扩展。

    CREATE EXTENSION postgres_fdw;
  2. 使用 CREATE SERVER 创建外部数据服务器。

    CREATE SERVER foreign_server FOREIGN DATA WRAPPER postgres_fdw OPTIONS (host 'xxx.xx.xxx.xx', port '5432', dbname 'foreign_db');
  3. 创建用户映射,用于标识将在远程服务器上使用的角色。

    CREATE USER MAPPING FOR local_user SERVER foreign_server OPTIONS (user 'foreign_user', password 'password');
  4. 创建一个表,该表映射到远程服务器上的表。

    CREATE FOREIGN TABLE foreign_table ( id integer NOT NULL, data text) SERVER foreign_server OPTIONS (schema_name 'some_schema', table_name 'some_table');