PostgreSQL实战指南：多领域数据库应用与实践 - 第21章：外部数据封装器（FDW）与异构数据集成

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28

-- 步骤 1: 在本地数据库创建扩展
CREATE EXTENSION postgres_fdw;

-- 步骤 2: 创建外部服务器
-- 'remote_db_host' 是远程 PostgreSQL 服务器的地址
CREATE SERVER remote_pg_server
    FOREIGN DATA WRAPPER postgres_fdw
    OPTIONS (host 'remote_db_host', port '5432', dbname 'remote_db');

-- 步骤 3: 为当前用户创建用户映射
-- 'remote_user' 和 'remote_password' 是登录远程数据库的凭据
CREATE USER MAPPING FOR CURRENT_USER
    SERVER remote_pg_server
    OPTIONS (user 'remote_user', password 'remote_password');

-- 步骤 4: 创建外部表
-- 'remote_schema.users' 是远程数据库中的原始表
CREATE FOREIGN TABLE local_users (
    id INTEGER,
    name TEXT,
    email TEXT
)
SERVER remote_pg_server
OPTIONS (schema_name 'remote_schema', table_name 'users');

-- 或者，更方便地导入整个 schema
-- IMPORT FOREIGN SCHEMA remote_schema
-- FROM SERVER remote_pg_server INTO public;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

SELECT name, email FROM local_users WHERE id = 123;

-- 甚至可以与本地表进行 JOIN
CREATE TABLE local_profiles (
    user_id INTEGER PRIMARY KEY,
    bio TEXT
);

SELECT u.name, p.bio
FROM local_users u
JOIN local_profiles p ON u.id = p.user_id;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27

-- 假设我们已经有一个本地的 users 表
-- CREATE TABLE users (...);

-- 1. 安装并创建 mysql_fdw 扩展
-- (需要先在操作系统层面安装 mysql_fdw 的二进制文件)
CREATE EXTENSION mysql_fdw;

-- 2. 创建连接到 MySQL 的外部服务器
CREATE SERVER mysql_order_server
    FOREIGN DATA WRAPPER mysql_fdw
    OPTIONS (host 'mysql_host', port '3306');

-- 3. 创建用户映射
CREATE USER MAPPING FOR CURRENT_USER
    SERVER mysql_order_server
    OPTIONS (username 'mysql_user', password 'mysql_password');

-- 4. 创建外部表，映射到 MySQL 中的 orders 表
-- 注意 MySQL 和 PostgreSQL 的数据类型差异
CREATE FOREIGN TABLE remote_orders (
    order_id INTEGER,
    user_id INTEGER,
    total_amount DECIMAL(10, 2),
    order_date DATETIME -- 假设 MySQL 中是 DATETIME 类型
)
SERVER mysql_order_server
OPTIONS (dbname 'order_db', table_name 'orders');

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15

SELECT
    u.name,
    u.email,
    SUM(ro.total_amount) AS total_spent,
    COUNT(ro.order_id) AS total_orders
FROM
    users u
JOIN
    remote_orders ro ON u.id = ro.user_id
WHERE
    u.registration_date > '2024-01-01' -- 本地过滤
GROUP BY
    u.id, u.name, u.email
ORDER BY
    total_spent DESC;