使用 postgres_fdw 和 dblink 扩展在 OCI Database 中使用 PostgreSQL 启用数据库间访问

简介

Oracle Cloud Infrastructure Database with PostgreSQL(OCI Database with PostgreSQL) 支持使用扩展（例如 postgres_fdw 和 dblink ）实现数据库间连接。这些扩展支持跨 PostgreSQL 数据库无缝访问和数据共享，可用于分析、联合查询或旧集成方案。

什么是 dblink？

DBlink 是 PostgreSQL 扩展，允许在远程 PostgreSQL 数据库上直接执行 SQL 语句。它将返回结果集作为行，或者在远程服务器上执行命令。

什么是 postgres_fdw？

postgres_fdw（Foreign Data Wrapper，外部数据包装）是一个 PostgreSQL 扩展，它使数据库能够连接到远程 PostgreSQL 服务器中的表并查询这些表，就像它们是本地表一样。与 dblink 不同，它将远程表映射为本地对象，并提供更多的过程控制。

在本教程中，我们将探讨在具有 PostgreSQL 实例的 OCI 数据库中设置 postgres_fdw 和 dblink 扩展，以及每个实例的示例。

目标

通过位于不同虚拟云网络 (Virtual Cloud Network，VCN) 和不同区域的 PostgreSQL 数据库系统，在两个 OCI 数据库之间实现安全可靠的网络通信。此设置可确保数据库连接在 VCN 边界内无缝工作。它包括配置本地对等连接网关 (Local Peering Gateways，LPG) 以使用专用视图进行路由和设置 DNS 解析。目标是为数据库扩展（如 postgres_fdw 或 dblink）支持跨 VCN/跨区域连接。

Prerequisites

要使用 PostgreSQL 数据库在 OCI 数据库中创建和管理 postgres_fdw 或 dblink 扩展，用户必须具有：
- 数据库访问权限。
- 授予用户的 OCI_ADMIN_ROLE 。
具有 PostgreSQL 数据库系统（源和目标）的 OCI 数据库必须具有网络连接。

任务 1：使用 PostgreSQL 在 OCI 数据库中启用 postgres_fdw 和 dblink 扩展

从 OCI 控制台启用扩展。
1. 创建一个配置文件，启用 dblink 或 postgres_fdw 扩展名。
2. 将配置应用于数据库系统。
在数据库级别启用扩展。

在 OCI 控制台中启用该扩展后，连接到数据库并运行以下 SQL 命令。
```
 CREATE EXTENSION postgres_fdw;

 CREATE EXTENSION dblink;
```

任务 2：配置不同 VCN 中的数据库系统之间的网络连接

要启用两个 OCI 数据库与位于不同 VCN 中的 PostgreSQL 数据库系统之间的通信，请执行以下步骤：

创建本地对等连接网关 (LPG) 。

在每个 VCN 中创建一个 LPG，以便在它们之间建立对等连接。
配置路由表。
1. 对于每个 VCN，更新与 LPG 关联的路由表以包括路由规则。
2. 将目标 CIDR 块设置为其他目标 VCN 的地址范围，并将下一个跃点指定为对应的 LPG。
使用专用视图设置 DNS 解析。
1. 转到 OCI 控制台，搜索专用视图以访问两个 VCN 的 DNS 配置。
2. 打开每个 VCN 的专用视图并创建一个区域条目。
为每个数据库系统创建区域记录。

在每个 VCN 的专用视图中，添加一个区域，该区域表示另一个 VCN 中数据库系统的全限定域名 (Fully Qualified Domain Name，FQDN)。
添加 DNS 记录。
1. 单击新区域条目旁边的三个点图标，或单击 FQDN 链接。
2. 选择 Manage Records（管理记录）并单击 Add Record（添加记录）。
3. 选择类型 A（IPv4 地址），将 TTL 设置为 500 ，然后在相反的 VCN 中输入目标数据库系统的主要端点 IP 地址。
4. 单击添加记录，然后单击发布更改。

任务 3：配置不同 VCN 和不同区域中的数据库系统之间的网络连接

在两个 OCI 数据库与位于不同 VCN 和不同区域的 PostgreSQL 数据库系统之间实现通信。

创建动态路由网关 (DRG) 。

在每个区域中创建一个 DRG，并将其连接到相应的 VCN。
配置路由表。
1. 对于每个 VCN，更新与 DRG 关联的路由表。
2. 添加具有其他 VCN 的目标 CIDR 块的路由规则，并将下一个跃点指定为附加的 DRG。
使用专用视图设置 DNS 解析。
1. 转到 OCI 控制台，搜索专用视图以访问每个 VCN 的 DNS 设置。
2. 打开每个 VCN 的专用视图并创建一个区域条目。
为每个数据库系统创建区域记录。

在每个 VCN 的专用视图中，在另一个 VCN 中添加一个表示数据库系统 FQDN 的区域。
添加 DNS 记录。
1. 单击新区域条目旁边的三个点图标，或单击 FQDN 链接。
2. 选择 Manage Records（管理记录）并单击 Add Record（添加记录）。
3. 选择 Type A (IPv4 Address) ，将 TTL 设置为 500 ，然后输入相反数据库系统的主要端点 IP 地址。
4. 单击添加记录，然后单击发布更改。

任务 4：使用示例配置 postgres_fdw 扩展

Create Foreign Data Wrapper (FDW) Server （创建外部数据包装服务器）。

CREATE SERVER 命令使用 postgres_fdw 包装器定义名为 fdw_serv 的外部服务器。它指定连接详细信息，例如源 PostgreSQL 数据库的主机、数据库名称和端口。这用作访问目标 PostgreSQL 实例中的源数据的逻辑引用。
```
 CREATE SERVER <fdw_servername> FOREIGN DATA WRAPPER <fdw_servername> OPTIONS (host 'primary.xxxxxx.FQDN_Remote_source_DBSystem.... oci.oraclecloud.com', dbname 'remote_source_dbname', port '5432');
```
映射用户。

CREATE USER MAPPING 命令将目标 PostgreSQL 用户 (arvindya) 与源数据库身份证明关联。这允许在目标服务器连接到定义的外部服务器时进行目标验证。它可确保在源服务器上正确授权执行查询的目标。
```
 CREATE USER MAPPING FOR <user_name> SERVER <fdw_servername> OPTIONS (user <user_name>, password <'user_password'>);
```
导入方案源。

IMPORT FOREIGN SCHEMA 命令将源服务器的公共方案中的所有表作为外部表导入目标数据库。这些表显示为本地表，但实时从源数据库提取数据。
```
 IMPORT FOREIGN SCHEMA <schema_name> FROM SERVER <fdw_server_name> INTO public;
```

例如：

-- Create extension
CREATE EXTENSION postgres_fdw;

-- Create FDW Server
CREATE SERVER fdw_serv FOREIGN DATA WRAPPER postgres_fdw OPTIONS (
    host 'primary.xxxxxx.Remote Source DBSystem FQDN.....xxxxx.oci.oraclecloud.com',
    dbname 'source',
    port '5432'
);

-- User Mapping
CREATE USER MAPPING FOR arvindya SERVER fdw_serv OPTIONS (
    user '<user_name>',
    password '<user_password>'
);

-- Import Schema from source
IMPORT FOREIGN SCHEMA public FROM SERVER fdw_serv INTO public;

-- Validate Data
SELECT * FROM emp;

任务 5：使用示例配置 dblink 扩展名

dblink_connect 函数使用提供的连接字符串建立从目标 PostgreSQL 数据库到源 PostgreSQL 数据库的连接。它包括数据库名称、主机、端口、用户名和密码等参数。连接由唯一名称 (myconn) 标识，该名称用于后续的 dblink 操作。成功的连接返回 OK ，指示远程查询的就绪状态。

SELECT dblink_connect ('Conn_name', 'dbname=<Remote_source_DBName> port=5432 host=primary.xxxxxx..Remote_source_DbSystem FQDN..xxxx.oci.oraclecloud.com user=<user_name> password=<'user_password'>);