postgresql - AWS 链接数据库并在多个数据库上运行查询

Question

我们在 AWS RDS 中托管了多个 postgreSQL 数据库，我想知道是否可以将它们连接在一起并在多个数据库上运行查询，例如：

SELECT * FROM databaseA.TableX ax
LEFT JOIN databaseB.TableX bx ON bx.id = ax.id

我记得 SQL Server 有这个功能，它被称为链接服务器。

标签： postgresqlamazon-s3linked-servermultiple-databases

Answer 1

这是完整的命令序列：

-- Install extension
CREATE EXTENSION IF NOT EXISTS postgres_fdw WITH SCHEMA "public";

-- Link the foreign server
CREATE SERVER foreign_server
    FOREIGN DATA WRAPPER postgres_fdw
    OPTIONS (host '<url>', port '5432', dbname '<database>');

-- Map the local user with the foreign one
CREATE USER MAPPING FOR <local user>
    SERVER foreign_server
    OPTIONS (user '<foreign user>', password '<foreign user password>');

-- Create locally a schema
CREATE SCHEMA IF NOT EXISTS "northwind";

-- Import the foreign schema into the new local one
IMPORT FOREIGN SCHEMA "public" FROM SERVER foreign_server INTO "northwind";

-- Select from foreign table
SELECT * FROM "northwind"."categories";