使用 PostgreSQL dblink 扩展

Timescale Cloud：性能、规模、企业级

自托管产品

MST

`dblink` PostgreSQL 扩展允许您连接到其他 PostgreSQL 数据库并运行任意查询。

您可以使用外部数据封装器（FDW）来定义远程`foreign server`（外部服务器）以访问其数据。数据库连接详细信息（如主机名）保存在一个位置，您只需创建一个 `user mapping`（用户映射）即可存储远程连接凭据。

先决条件

在开始之前，请登录您的 Managed Service for TimescaleDB 门户，导航到 `Overview`（概览）选项卡，并记下 PostgreSQL 远程服务器的以下参数。或者，您也可以在 Aiven 客户端中使用 `avn service get` 命令。

HOSTNAME：远程数据库主机名
PORT：远程数据库端口
USER：要连接的远程数据库用户。默认用户为 `tsdbadmin`。
PASSWORD：`USER` 的远程数据库密码
DATABASE_NAME：远程数据库名称。默认数据库名称为 `defaultdb`。

启用 dblink 扩展

在 MST PostgreSQL 服务上启用 `dblink` 扩展

以 `tsdbadmin` 用户身份连接到数据库


psql -x "postgres://tsdbadmin:<PASSWORD>@<HOSTNAME>:<PORT>/defaultdb?sslmode=require"

创建 `dblink` 扩展
```
CREATE EXTENSION dblink;
```
创建名为 `inventory` 的表
```
CREATE TABLE inventory (id int);
```

将数据插入 `inventory` 表


INSERT INTO inventory (id) VALUES (100), (200), (300);

使用 dblink_fdw 创建外部数据封装器

创建用户 `user1`，使其可以访问 `dblink`
```
CREATE USER user1 PASSWORD 'secret1'
```

创建名为 `mst_remote` 的远程服务器定义，使用 `dblink_fdw` 和 MST 服务的连接详细信息。


CREATE SERVER mst_remote
    FOREIGN DATA WRAPPER dblink_fdw
    OPTIONS (
             host 'HOST',
             dbname 'DATABASE_NAME',
             port 'PORT'
             );

为 `user1` 创建用户映射，以便在使用 `dblink` 时自动以 `tsdbadmin` 身份进行身份验证。


CREATE USER MAPPING FOR user1
       SERVER mst_remote
       OPTIONS (
        user 'tsdbadmin',
        password 'PASSWORD'
        );

允许 `user1` 使用远程 PostgreSQL 连接 `mst_remote`
```
GRANT USAGE ON FOREIGN SERVER mst_remote TO user1;
```

使用外部数据封装器查询数据

在此示例中，`user1` 用户从 `mst_remote` 服务器定义中查询目标 PostgreSQL 数据库中定义的远程表 `inventory`。

使用外部数据封装器查询数据

要查询外部数据封装器，您必须是具有远程服务器必要权限的数据库用户。

以 `user1` 身份连接到 MST 服务，并授予远程服务器必要的权限。
建立与远程目标服务器的 `dblink` 连接
```
SELECT dblink_connect('my_new_conn', 'mst_remote');
```

使用外部服务器定义作为参数进行查询


SELECT * FROM dblink('my_new_conn','SELECT * FROM inventory') AS t(a int);

输出类似以下内容


a
  -----
   100
   200
   300
 (3 rows)

使用 PostgreSQL dblink 扩展

先决条件

启用 dblink 扩展

使用 dblink_fdw 创建外部数据封装器

使用外部数据封装器查询数据

使用外部数据封装器查询数据

相关内容