与 Aurora PostgreSQL 兼容的与远程 PostgreSQL 数据库的集成

本节讨论了与 Amazon Aurora PostgreSQL 兼容的版本使用postgres_fdw（外部数据包装器）扩展或该功能与远程 PostgreSQL 数据库的集成。dblink该postgres_fdw模块提供联合查询功能，用于与基于 PostgreSQL 的远程数据库进行交互。远程数据库可以在 Amazon 上或本地进行托管 EC2或自行管理。该postgres_fdw扩展适用于所有当前支持的适用于 PostgreSQL 的亚马逊关系数据库服务 (Amazon RDS) 版本，并且兼容 Aurora PostgreSQL。

使用该postgres_fdw扩展，您可以像本地表一样访问和查询远程 PostgreSQL 数据库中的数据。该postgres_fdw扩展还支持以下内容：

跨版本兼容性，用于访问运行不同版本的外部 PostgreSQL 服务器的数据。
事务管理，当您在本地和外部 PostgreSQL 服务器上执行操作时，这有助于确保数据的一致性和完整性。
分布式事务，当您跨多个外部 PostgreSQL 服务器执行操作时，它提供了原子性（ACID 事务的属性）和隔离保证。这有助于确保要么提交事务中的所有操作，要么不提交任何操作，从而保持数据的一致性和完整性。

尽管该dblink模块提供了一种与远程 PostgreSQL 数据库交互的方法，但它不支持分布式事务或其他高级功能。如果您需要更高级的功能，可以考虑改用该postgres_fdw扩展程序。该postgres_fdw扩展提供了更多的集成和优化功能。

postgres_fdw 用例和高级步骤

兼容 Aurora PostgreSQL 的postgres_fdw扩展程序使用支持以下用例和场景：

联合查询和数据集成 − 在一个兼容 Aurora PostgreSQL 的实例中查询和合并来自多个 PostgreSQL 数据库的数据
卸载读取工作负载 − 连接到外部 PostgreSQL 服务器的只读副本，卸载读取密集型工作负载并提高查询性能
跨数据库操作 − 跨多个 PostgreSQL 数据库执行INSERTUPDATEDELETE、、和COPY操作，实现跨数据库的数据操作和维护任务

要进行配置postgres_fdw，请使用以下高级步骤：

使用 PostgreSQL 客户端连接到与 Aurora PostgreSQL 兼容的集群，然后创建扩展模块：postgres_fdw
```
CREATE EXTENSION postgres_fdw;
```
此扩展提供了连接到远程 PostgreSQL 数据库的功能。
使用CREATE SERVER命令创建名为my_fdw_target的外部服务器。此服务器代表您要连接的远程 PostgreSQL 数据库。指定数据库名称、主机名和 SSL 模式作为此服务器的选项。
确保必要的安全组和网络配置到位，以允许兼容 Aurora PostgreSQL 的服务器连接到远程 PostgreSQL 数据库。

如果远程数据库托管在本地，则可能需要配置虚拟专用网络 (VPN) 或 AWS Direct Connect 连接。

运行以下命令：
```
CREATE SERVER my_fdw_target Foreign Data Wrapper postgres_fdw OPTIONS (DBNAME 'postgres', HOST 'SOURCE_HOSTNAME', SSLMODE 'require');
```
在my_fdw_target服务器上为dbuser用户创建用户映射。此映射将本地 Aurora PostgreSQL 兼容实例上的dbuser用户和密码与远程数据库上的相应用户相关联。
```
CREATE USER MAPPING FOR dbuser SERVER my_fdw_target OPTIONS (user 'DBUSER', password 'PASSWORD');
```
此步骤是进行身份验证和提供对远程数据库的访问权限所必需的。
使用您之前设置customer_fdw的my_fdw_target服务器和用户映射创建一个名为的外部表：
```
CREATE FOREIGN TABLE customer_fdw( id int, name varchar, emailid varchar, projectname varchar, contactnumber bigint) server my_fdw_target OPTIONS( TABLE_NAME 'customers');
```
该customer_fdw表映射到my_fdw_target服务器指定的远程数据库中的customers表。外部表与远程表具有相同的结构，因此您可以像本地表一样与远程数据进行交互。

您可以对customer_fdw外部表执行各种数据操作操作，例如INSERTUPDATE、和SELECT查询。该脚本演示了插入新行和更新现有行、删除记录以及通过customer_fdw外部表截断远程customers表中的表：


INSERT INTO customer_fdw values ( 1, 'Test1', 'Test1@email.com', 'LMS1', '888888888');
INSERT INTO customer_fdw values ( 2, 'Test2', 'Test2@email.com', 'LMS2', '999999999');
INSERT INTO customer_fdw values ( 3, 'Test3', 'Test3@email.com', 'LMS3', '111111111');
UPDATE customer_fdw set contactnumber = '123456789' where id = 2;
DELETE FROM customer_fdw where id = 1;
TRUNCATE TABLE customer_fdw;

您可以使用语EXPLAIN句分析customer_fdw表中某个查询的查询计划，从而验证 SQL SELECT 查询计划：
```
EXPLAIN select * from customer_fdw where id =1;
```
这可以帮助您了解查询的运行方式以及如何对其进行优化。有关使用该EXPLAIN语句的更多信息，请参阅规范指南中的优化 PostgreSQL 查询性能 AWS 。
要将多个表从远程数据库导入本地架构，请使用以下IMPORT FOREIGN SCHEMA命令：
```
CREATE SCHEMA public_fdw;
IMPORT FOREIGN SCHEMA public LIMIT TO (employees, departments)
    FROM SERVER my_fdw_target INTO public_fdw;
```
这将为public_fdw架构中的指定表创建本地外部表。在此示例中，具体的表是员工和部门。
要向特定数据库用户授予必要的权限，以便他们能够访问和使用 FDW 和关联的外部服务器，请运行以下命令：
```
GRANT USAGE ON FOREIGN SERVER my_fdw_target TO targetdbuser;
GRANT USAGE ON FOREIGN DATA WRAPPER postgres_fdw TO targetdbuser;
```
当多个用户需要访问由外部数据包装器提供的外部表时，此步骤可能很有用。

使用外部表时，请注意以下限制：

从远程来源访问数据可能会导致网络延迟导致的数据传输成本和性能开销。对于需要在兼容 Aurora PostgreSQL 的实例和远程数据源之间进行大量数据传输的大型数据集或查询，可能会出现明显的性能问题。
在涉及窗口函数等功能的复杂查询中，递归查询可能无法按预期工作或可能不受支持。
当前，不支持密码加密。实施控制措施，确保只有经过授权的用户才能访问远程数据库 FDWs 并从中检索数据。

无法在外部表上定义主键约束，如以下表创建脚本尝试所示：


CREATE FOREIGN TABLE customer_fdw2( id int primary key, name varchar, emailid varchar, projectname varchar, contactnumber bigint) server my_fdw_target OPTIONS( TABLE_NAME 'customers');
Primary keys cannot be defined on Foreign table

外部INSERT表不支持语ON CONFLICT句子句，如以下示例所示：


INSERT INTO customer_fdw (id, name, emailid, projectname, contactnumber) VALUES
(1, 'test1', 'test@email.com', 'LMS', 11111111 ),
(3, 'test3', 'test3@email.com', 'LMS', 22222222 )
ON CONFLICT (id) DO UPDATE 
SET name = EXCLUDED.name;
On Conflict option doesnot work.

清理

要清理创建的对象，包括删除postgres_fdw扩展、my_fdw_target服务器、用户映射和外部表，请运行以下命令：


DROP FOREIGN TABLE customer_fdw;
DROP USER MAPPING for postgres;
DROP SERVER  my_fdw_target;
DROP EXTENSION postgres_fdw cascade;

使用 dblink 创建连接

dblink模块函数提供了一种在远程 PostgreSQL 数据库上创建连接和运行 SQL 语句的替代方法。该dblink解决方案是一种更简单、更灵活的方式，可以在远程数据库上运行一次性查询或操作。对于涉及大规模数据集成、性能优化和数据完整性要求的更复杂的场景，我们建议使用postgres_fdw。

使用dblink涉及以下高级步骤：

创建扩dblink展：
```
CREATE EXTENSION dblink;
```
此扩展提供了连接到远程 PostgreSQL 数据库的功能。

要建立与远程 PostgreSQL 数据库的连接，请使用以下函数：dblink_connect


SELECT dblink_connect('myconn', 'dbname=postgres port=5432 host=SOURCE_HOSTNAME user=postgres password=postgres');

连接到远程 PostgreSQL 数据库后，使用以下函数在远程数据库上运行 SQL 语句：dblink
```
SELECT  FROM dblink('myconn', 'SELECT col1, col2 FROM remote_table') AS remote_data(col1 int, col2 text);
```
此查询使用myconn连接在远程数据库上运行该SELECT * FROM remote_table语句。该查询会将结果检索到包含列col1和col2的本地临时表中。
您还可以使用以下dblink_exec函数在远程数据库上运行非查询语句DELETE，例如、或：INSERTUPDATE
```
SELECT dblink_exec('myconn', 'INSERT INTO remote_table VALUES (1, ''value'')');
```

Javascript 在您的浏览器中被禁用或不可用。

要使用 Amazon Web Services 文档，必须启用 Javascript。请参阅浏览器的帮助页面以了解相关说明。

文档惯例

兼容 Aurora PostgreSQL 的集成

异构数据库