使用 Amazon Aurora PostgreSQL 支持的外部数据包装器
外部数据包装器 (FDW) 是一种特定类型的扩展,提供对外部数据的访问。例如,oracle_fdw
扩展允许您的 Aurora PostgreSQL 数据库实例 使用 Oracle 数据库。
在下文中,您可以了解有关几种支持的 PostgreSQL 外部数据包装器的信息。
主题
使用 log_fdw 扩展通过 SQL 访问数据库日志
Aurora PostgreSQL 数据库集群支持 log_fdw
扩展,您可以使用该扩展通过 SQL 界面访问数据库引擎日志。此 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
角色的成员可以将这些函数的访问权限授予其他数据库用户。
默认情况下,日志文件由 Amazon Aurora 以 stderr
(标准错误)格式生成,如 log_destination
参数中指定。此参数只有两个选项,即,stderr
和 csvlog
(逗号分隔值,CSV)。如果您为参数添加 csvlog
选项,Amazon Aurora 会同时生成 stderr
和 csvlog
日志。这可能会影响数据库集群的存储容量,因此您需要了解影响日志处理的其它参数。有关更多信息,请参阅设置日志目标(stderr、csvlog)。
生成 csvlog
日志的一个优势是 log_fdw
扩展允许您构建将数据整齐地拆分为多个列的外部表。为此,您的实例需要与自定义数据库参数组关联,以便您可以更改 log_destination
的设置。有关如何执行此操作的更多信息,请参阅使用参数组。
以下示例假设 log_destination
参数包含 cvslog
。
使用 log_fdw 扩展
-
安装
log_fdw
扩展。postgres=>
CREATE EXTENSION log_fdw;
CREATE EXTENSION
-
创建日志服务器,作为外部数据包装器。
postgres=>
CREATE SERVER log_server FOREIGN DATA WRAPPER log_fdw;
CREATE SERVER
-
选择日志文件列表中的所有文件。
postgres=>
SELECT * FROM list_postgres_log_files() ORDER BY 1;
示例响应如下所示。
file_name | file_size_bytes ------------------------------+----------------- postgresql.log.2023-08-09-22.csv | 1111 postgresql.log.2023-08-09-23.csv | 1172 postgresql.log.2023-08-10-00.csv | 1744 postgresql.log.2023-08-10-01.csv | 1102 (4 rows)
-
为所选文件创建包含单个“log_entry”列的表。
postgres=>
SELECT create_foreign_table_for_log_file('my_postgres_error_log', 'log_server', 'postgresql.log.2023-08-09-22.csv');
除了告知现在存在表格外,响应不提供详细信息。
----------------------------------- (1 row)
-
选择日志文件的示例。以下代码检索日志时间和错误消息描述。
postgres=>
SELECT log_time, message FROM my_postgres_error_log ORDER BY 1;
示例响应如下所示。
log_time | message ----------------------------------+--------------------------------------------------------------------------- Tue Aug 09 15:45:18.172 2023 PDT | ending log output to stderr Tue Aug 09 15:45:18.175 2023 PDT | database system was interrupted; last known up at 2023-08-09 22:43:34 UTC Tue Aug 09 15:45:18.223 2023 PDT | checkpoint record is at 0/90002E0 Tue Aug 09 15:45:18.223 2023 PDT | redo record is at 0/90002A8; shutdown FALSE Tue Aug 09 15:45:18.223 2023 PDT | next transaction ID: 0/1879; next OID: 24578 Tue Aug 09 15:45:18.223 2023 PDT | next MultiXactId: 1; next MultiXactOffset: 0 Tue Aug 09 15:45:18.223 2023 PDT | oldest unfrozen transaction ID: 1822, in database 1 (7 rows)
使用 postgres_fdw 扩展访问外部数据
您可以使用 postgres_fdw
使用 postgres_fdw 访问远程数据库服务器
安装 postgres_fdw 扩展。
CREATE EXTENSION postgres_fdw;
使用 CREATE SERVER 创建外部数据服务器。
CREATE SERVER foreign_server FOREIGN DATA WRAPPER postgres_fdw OPTIONS (host 'xxx.xx.xxx.xx', port '5432', dbname 'foreign_db');
创建用户映射,用于标识将在远程服务器上使用的角色。
CREATE USER MAPPING FOR local_user SERVER foreign_server OPTIONS (user 'foreign_user', password 'password');
创建一个表,该表映射到远程服务器上的表。
CREATE FOREIGN TABLE foreign_table ( id integer NOT NULL, data text) SERVER foreign_server OPTIONS (schema_name 'some_schema', table_name 'some_table');
使用 mysql_fdw 扩展处理 MySQL 数据库
要从 Aurora PostgreSQL 数据库集群访问 MySQL 兼容数据库,您可以安装并使用 mysql_fdw
扩展。这款外部数据包装器允许您使用 RDS for MySQL、Aurora MySQL、MariaDB 和其他 MySQL 兼容数据库。从 Aurora PostgreSQL 数据库集群到 MySQL 数据库的连接会尽可能加密,具体取决于客户端和服务器配置。但是,如果您愿意,可以强制加密。有关更多信息,请参阅将传输中加密与扩展配合使用。
mysql_fdw
扩展在 Amazon Aurora PostgreSQL 版本 15.4、14.9、13.12、12.16、 以及更高版本中受支持。它支持从 RDS for PostgreSQL 数据库到 MySQL 兼容数据库实例上的表的选择、插入、更新和删除。
将 Aurora PostgreSQL 数据库设置为使用 mysql_fdw 扩展
在您的 Aurora PostgreSQL 数据库集群上设置 mysql_fdw
扩展涉及在您的数据库集群中加载扩展,然后创建到 MySQL 数据库实例的连接点。对于该任务,您需要了解有关 MySQL 数据库实例的以下详细信息:
主机名或终端节点。对于 Aurora MySQL 数据库集群,您可以使用控制台查找终端节点。选择 Connectivity & security(连接和安全)选项卡,然后查看 Endpoint and port(终端节点和端口)部分。
端口号。MySQL 的默认端口是 3306。
数据库的名称。数据库标识符。
您还需要为 MySQL 端口 3306 提供对安全组或访问控制列表 (ACL) 的访问权限。Aurora PostgreSQL 数据库集群和 Aurora MySQL 数据库集群均需要访问端口 3306。如果访问权限配置不正确,当尝试连接到 MySQL 兼容表时,您会看到一条与以下内容类似的错误消息:
ERROR: failed to connect to MySQL: Can't connect to MySQL server on 'hostname
.aws-region
.rds.amazonaws.com:3306' (110)
在以下过程中,您(作为 rds_superuser
账户)创建外部服务器。然后,您将访问外部服务器的权限授予特定用户。然后,这些用户创建其自身到相应 MySQL 用户账户的映射以使用 MySQL 数据库实例。
使用 mysql_fdw 访问 MySQL 数据库服务器
使用具有
rds_superuser
角色的账户连接到您的 PostgreSQL 数据库实例。如果在创建 Aurora PostgreSQL 数据库集群时接受默认值,则用户名为postgres
,您可以使用psql
命令行工具进行连接,如下所示:psql --host=
your-DB-instance
.aws-region
.rds.amazonaws.com --port=5432 --username=postgres –-password按如下方式安装
mysql_fdw
扩展:postgres=>
CREATE EXTENSION mysql_fdw;
CREATE EXTENSION
在 Aurora PostgreSQL 数据库集群上安装扩展后,您可以设置提供与 MySQL 数据库连接的外部服务器。
创建外部服务器
在 Aurora PostgreSQL 数据库集群上执行这些任务。这些步骤假定您以具有 rds_superuser
特权的用户身份连接,例如 postgres
。
在 Aurora PostgreSQL 数据库集群中创建外部服务器:
postgres=>
CREATE SERVER
mysql-db
FOREIGN DATA WRAPPER mysql_fdw OPTIONS (host 'db-name.111122223333
.aws-region
.rds.amazonaws.com', port '3306');CREATE SERVER
向适当的用户授予访问外部服务器的权限。这些用户应该是非管理员用户,即,没有
rds_superuser
角色的用户。postgres=>
GRANT USAGE ON FOREIGN SERVER
mysql-db
touser1
;GRANT
PostgreSQL 用户通过外部服务器创建和管理其自身与 MySQL 数据库的连接。
示例:从 Aurora PostgreSQL 使用 Aurora MySQL 数据库
假设您在 Aurora PostgreSQL 数据库实例上有一个简单的表。您的 Aurora PostgreSQL 用户想要查询该表中的(SELECT
)、INSERT
、UPDATE
和 DELETE
项目。假设 mysql_fdw
扩展是在您的 RDS for PostgreSQL 数据库实例上创建的,如前面的过程中所述。以具有 rds_superuser
权限的用户身份连接到 RDS for PostgreSQL 数据库实例后,您可以继续执行以下步骤。
在 Aurora PostgreSQL 数据库实例上,创建一个外部服务器:
test=>
CREATE SERVER
mysqldb
FOREIGN DATA WRAPPER mysql_fdw OPTIONS (host 'your-DB
.aws-region
.rds.amazonaws.com', port '3306');CREATE SERVER
将使用权授予没有
rds_superuser
权限的用户,例如user1
:test=>
GRANT USAGE ON FOREIGN SERVER mysqldb TO user1;
GRANT
作为
user1
连接,然后创建一个到 MySQL 用户的映射:test=>
CREATE USER MAPPING FOR
user1
SERVER mysqldb OPTIONS (username 'myuser
', password 'mypassword
');CREATE USER MAPPING
创建链接到 MySQL 表的外部表:
test=>
CREATE FOREIGN TABLE
mytab
(a int, b text) SERVER mysqldb OPTIONS (dbname 'test', table_name '');CREATE FOREIGN TABLE
针对外表运行简单查询:
test=>
SELECT * FROM mytab;
a | b ---+------- 1 | apple (1 row)
您可以从 MySQL 表中添加、更改和删除数据。例如:
test=>
INSERT INTO mytab values (2, 'mango');
INSERT 0 1
再次运行
SELECT
查询以查看结果:test=>
SELECT * FROM mytab ORDER BY 1;
a | b ---+------- 1 | apple 2 | mango (2 rows)
将传输中加密与扩展配合使用
默认情况下,从 Aurora PostgreSQL 到 MySQL 的连接使用传输中加密(TLS/SSL)。但是,当客户端和服务器配置不同时,连接会回退为非加密状态。您可以通过在 RDS for MySQL 用户账户上指定 REQUIRE SSL
选项来对所有传出连接强制加密。这种方法也适用于 MariaDB 和 Aurora MySQL 用户账户。
对于配置为 REQUIRE SSL
的 MySQL 用户帐户,如果无法建立安全连接,则连接尝试将失败。
要对现有 MySQL 数据库用户帐户强制加密,可以使用 ALTER USER
命令。根据 MySQL 版本的不同,语法有所不同,如下表所示。有关更多信息,请参阅《MySQL 参考手册》中的 ALTER USER
MySQL 5.7、MySQL 8.0 | MySQL 5.6 |
---|---|
|
|
有关 mysql_fdw
扩展的更多信息,请参阅 mysql_fdw
通过使用 oracle_fdw 扩展来使用 Oracle 数据库
要从 Aurora PostgreSQL 数据库集群 访问 Oracle 数据库,您可以安装并使用 oracle_fdw
扩展。此扩展是 Oracle 数据库的外部数据包装器。要了解有关此扩展的更多信息,请参阅 oracle_fdw
Aurora PostgreSQL 12.7(Amazon Aurora PostgreSQL 版本 4.2)及更高版本支持 oracle_fdw
扩展。
启用 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 数据库的外部服务器
-
请注意 RDS for Oracle 数据库实例上的以下内容:
-
Endpoint
-
端口
-
数据库名称
-
-
创建外部服务器。
test=>
CREATE SERVER oradb FOREIGN DATA WRAPPER oracle_fdw OPTIONS (dbserver '//
endpoint
:port
/DB_name
');CREATE SERVER
-
将使用权授予没有
rds_superuser
权限的用户,例如user1
。test=>
GRANT USAGE ON FOREIGN SERVER oradb TO user1;
GRANT
-
作为
user1
连接并创建到 Oracle 用户的映射。test=>
CREATE USER MAPPING FOR user1 SERVER oradb OPTIONS (user '
oracleuser'
, password 'mypassword'
);CREATE USER MAPPING
-
创建链接到 Oracle 表的外部表。
test=>
CREATE FOREIGN TABLE
mytab
(a int) SERVER oradb OPTIONS (table 'MYTABLE'
);CREATE FOREIGN TABLE
-
查询外部表。
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 文档中的关于协商加密和完整性的值ACCEPTED
,这意味着加密取决于 Oracle 数据库服务器配置。
如果您的数据库位于 RDS for Oracle 上,请参阅 Oracle 本机网络加密来配置加密。
了解 pg_user_mappings 视图和权限
PostgreSQL 目录 pg_user_mapping
存储 Aurora PostgreSQL 用户到外部数据(远程)服务器上用户的映射。虽然对目录的访问受到限制,但是您可以使用 pg_user_mappings
视图来查看映射。在下面,您可以找到一个示例,该示例演示如何将权限应用于示例 Oracle 数据库,但此信息更普遍地适用于任何外部数据包装器。
在以下输出中,您可以找到映射到三个不同示例用户的角色和权限。用户 rdssu1
和 rdssu2
是 rds_superuser
角色的成员,而 user1
不是。此示例使用 psql
元命令 \du
列出现有角色。
test=>
\du
List of roles Role name | Attributes | Member of -----------------+------------------------------------------------------------+------------------------------------------------------------- rdssu1 | | {rds_superuser} rdssu2 | | {rds_superuser} user1 | | {}
所有用户,包括具有 rds_superuser
权限的用户,都可以查看 pg_user_mappings
表中他们自己的用户映射 (umoptions
)。如以下示例所示,当 rdssu1
尝试获取所有用户映射时,即使存在 rdssu1
rds_superuser
权限,也会出现错误:
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=>
SELECT * 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_mappings
和 pg_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
。
通过使用 tds_fdw 扩展来使用 SQL Server 数据库
您可以使用 PostgreSQL tds_fdw
扩展来访问支持表格数据流 (TDS) 协议的数据库,例如 Sybase 和 Microsoft SQL Server 数据库。此外部数据包装器可让您从 Aurora PostgreSQL 数据库集群连接到使用 TDS 协议的数据库,包括 Amazon RDS for Microsoft SQL Server。有关更多信息,请参阅 GitHub 上的 tds-fdw/tds_fdw
Amazon Aurora PostgreSQL 版本 13.6 及更高版本支持该 tds_fdw
扩展。
将 Aurora PostgreSQL 数据库设置为使用 tds_fdw 扩展
在以下过程中,您可以找到设置 tds_fdw
并将其与 Aurora PostgreSQL 数据库集群结合使用的示例。在可以使用 tds_fdw
连接到 SQL Server 数据库之前,您需要获取实例的以下详细信息:
主机名或终端节点。对于 RDS for SQL Server 数据库实例,您可以使用控制台查找终端节点。选择 Connectivity & security(连接和安全)选项卡,然后查看 Endpoint and port(终端节点和端口)部分。
端口号。Microsoft SQL Server 的默认端口是 1433。
数据库的名称。数据库标识符。
您还需要为 SQL Server 端口 1433 提供对安全组或访问控制列表 (ACL) 的访问权限。Aurora PostgreSQL 数据库集群和 RDS for SQL Server 数据库实例都需要访问端口 1433。如果访问权限配置不正确,当您尝试查询 Microsoft SQL Server 时,会看到以下错误消息:
ERROR: DB-Library error: DB #: 20009, DB Msg: Unable to connect:
Adaptive Server is unavailable or does not exist (mssql2019
.aws-region
.rds.amazonaws.com), OS #: 0, OS Msg: Success, Level: 9
使用 tds_fdw 连接到 SQL Server 数据库
使用具有
rds_superuser
角色的账户连接到您的 Aurora PostgreSQL 数据库集群的主要实例:psql --host=
your-cluster-name-instance-1
.aws-region
.rds.amazonaws.com --port=5432 --username=test –-password安装
tds_fdw
扩展:test=>
CREATE EXTENSION tds_fdw;
CREATE EXTENSION
在 Aurora PostgreSQL 数据库集群上安装扩展后,应设置外部服务器。
创建外部服务器
使用具有 rds_superuser
权限的账户在 Aurora PostgreSQL 数据库集群上执行这些任务。
在 Aurora PostgreSQL 数据库集群中创建外部服务器:
test=>
CREATE SERVER
sqlserverdb
FOREIGN DATA WRAPPER tds_fdw OPTIONS (servername 'mssql2019
.aws-region
.rds.amazonaws.com', port '1433', database 'tds_fdw_testing
');CREATE SERVER
要访问 SQLServer 端的非 ASCII 数据,请在 Aurora PostgreSQL 数据库集群中使用 character_set 选项创建服务器链接:
test=>
CREATE SERVER
sqlserverdb
FOREIGN DATA WRAPPER tds_fdw OPTIONS (servername 'mssql2019
.aws-region
.rds.amazonaws.com', port '1433', database 'tds_fdw_testing
', character_set'UTF-8'
);CREATE SERVER
向没有
rds_superuser
角色权限的用户授予权限,例如user1
:test=>
GRANT USAGE ON FOREIGN SERVER
sqlserverdb
TOuser1
;以 user1 身份进行连接,然后创建到 SQL Server 用户的映射:
test=>
CREATE USER MAPPING FOR user1 SERVER
sqlserverdb
OPTIONS (username 'sqlserveruser
', password 'password
');CREATE USER MAPPING
创建链接到 SQL Server 表的外部表:
test=>
CREATE FOREIGN TABLE mytab (a int) SERVER
sqlserverdb
OPTIONS (table 'MYTABLE
');CREATE FOREIGN TABLE
查询外部表:
test=>
SELECT * FROM mytab;
a --- 1 (1 row)
使用传输中的加密进行连接
Aurora PostgreSQL 到 SQL Server 的连接使用传输中加密 (TLS/SSL),具体取决于 SQL Server 数据库配置。如果 SQL Server 未配置为加密,则向 SQL Server 数据库发出请求的 RDS for PostgreSQL 客户端将回退到未加密状态。
您可以通过设置 rds.force_ssl
参数对与 RDS for SQL Server 数据库实例的连接进行加密。要了解操作方法,请参阅强制与数据库实例的连接使用 SSL。有关 RDS for SQL Server 的 SSL/TLS 配置的更多信息,请参阅将 SSL 与 Microsoft SQL Server 数据库实例结合使用。