— 创建扩展
CREATE EXTENSION postgres_fdw;

— 输出结果
CREATE EXTENSION

— 创建外部服务器
CREATE SERVER fgedu_pg_remote
FOREIGN DATA WRAPPER postgres_fdw
OPTIONS (
host ‘192.168.1.100’,
port ‘5432’,
dbname ‘fgedudb_remote’
);

— 输出结果
CREATE SERVER

— 创建用户映射
CREATE USER MAPPING FOR fgedu
SERVER fgedu_pg_remote
OPTIONS (
user ‘fgedu_remote’,
password ‘fgedu_remote_2026’
);

— 输出结果
CREATE USER MAPPING

— 导入外部表结构
IMPORT FOREIGN SCHEMA public
FROM SERVER fgedu_pg_remote
INTO public;

— 输出结果
IMPORT FOREIGN SCHEMA

— 查看导入的外部表
SELECT foreign_table_name, foreign_server_name
FROM information_schema.foreign_tables
WHERE foreign_server_name = ‘fgedu_pg_remote’;

— 输出结果
foreign_table_name | foreign_server_name
——————–+———————
fgedu_orders | fgedu_pg_remote
fgedu_products | fgedu_pg_remote
fgedu_customers | fgedu_pg_remote
(3 rows)

— 查询外部表
SELECT * FROM fgedu_orders LIMIT 5;

— 输出结果
id | order_no | customer_id | amount | status | create_time
—-+———-+————-+———-+———+———————
1 | ORD001 | 1 | 1000.00 | pending | 2026-04-07 10:00:00
2 | ORD002 | 2 | 2000.00 | paid | 2026-04-07 11:00:00
3 | ORD003 | 3 | 3000.00 | shipped | 2026-04-07 12:00:00
(3 rows)

— 配置外部服务器选项
ALTER SERVER fgedu_pg_remote
OPTIONS (SET fetch_size ‘10000’);

— 输出结果
ALTER SERVER

— 配置外部表选项
ALTER FOREIGN TABLE fgedu_orders
OPTIONS (ADD use_remote_estimate ‘true’);

— 输出结果
ALTER FOREIGN TABLE

— 查看查询计划
EXPLAIN (VERBOSE, COSTS)
SELECT o.order_no, c.customer_name, o.amount
FROM fgedu_orders o
JOIN fgedu_customers c ON o.customer_id = c.id
WHERE o.amount > 1000;

— 输出结果
QUERY PLAN
———————————————————————————————-
Hash Join (cost=200.00..500.00 rows=100 width=100)
Output: o.order_no, c.customer_name, o.amount
Hash Cond: (o.customer_id = c.id)
-> Foreign Scan on public.fgedu_orders o (cost=100.00..200.00 rows=100 width=50)
Output: o.order_no, o.customer_id, o.amount
Remote SQL: SELECT order_no, customer_id, amount FROM public.fgedu_orders WHERE ((amount > 1000))
-> Hash (cost=100.00..100.00 rows=100 width=50)
Output: c.customer_name, c.id
-> Foreign Scan on public.fgedu_customers c (cost=100.00..100.00 rows=100 width=50)
Output: c.customer_name, c.id
Remote SQL: SELECT id, customer_name FROM public.fgedu_customers
(11 rows)

— 启用下推优化
SET enable_hashagg = false;
SET enable_sort = false;

— 执行聚合查询
SELECT status, COUNT(*), SUM(amount)
FROM fgedu_orders
GROUP BY status;

— 输出结果
status | count | sum
———-+——-+———-
pending | 10 | 15000.00
paid | 15 | 25000.00
shipped | 20 | 35000.00
(3 rows)

— 查看聚合下推
EXPLAIN (VERBOSE)
SELECT status, COUNT(*), SUM(amount)
FROM fgedu_orders
GROUP BY status;

— 输出结果
QUERY PLAN
————————————————————————————-
Foreign Scan (cost=100.00..200.00 rows=3 width=100)
Output: status, (count(*)), (sum(amount))
Relations: Aggregate on (fgedu_orders)
Remote SQL: SELECT status, count(*), sum(amount) FROM public.fgedu_orders GROUP BY status
(4 rows)

— 更新外部表数据
UPDATE fgedu_orders
SET status = ‘processed’
WHERE status = ‘pending’ AND amount < 500; -- 输出结果 UPDATE 3 -- 插入数据到外部表 INSERT INTO fgedu_orders(order_no, customer_id, amount, status) SELECT 'ORD' || LPAD(id::text, 5, '0'), id, 1000.00, 'pending' FROM generate_series(1, 10) AS id; -- 输出结果 INSERT 0 10

— 安装mysql_fdw扩展（需要先编译安装）
— $ cd /postgresql/source/contrib/mysql_fdw
— $ make USE_PGXS=1
— $ make USE_PGXS=1 install

— 创建扩展
CREATE EXTENSION mysql_fdw;

— 输出结果
CREATE EXTENSION

— 创建MySQL服务器
CREATE SERVER fgedu_mysql_server
FOREIGN DATA WRAPPER mysql_fdw
OPTIONS (
host ‘192.168.1.200’,
port ‘3306’
);

— 输出结果
CREATE SERVER

— 创建用户映射
CREATE USER MAPPING FOR fgedu
SERVER fgedu_mysql_server
OPTIONS (
username ‘fgedu_mysql’,
password ‘fgedu_mysql_2026’
);

— 输出结果
CREATE USER MAPPING

— 创建MySQL外部表
CREATE FOREIGN TABLE fgedu_mysql_orders (
id INTEGER,
order_no VARCHAR(50),
customer_id INTEGER,
amount DECIMAL(10,2),
status VARCHAR(20),
create_time TIMESTAMP
)
SERVER fgedu_mysql_server
OPTIONS (
dbname ‘fgedu_db’,
table_name ‘orders’
);

— 输出结果
CREATE FOREIGN TABLE

— 查询MySQL外部表
SELECT * FROM fgedu_mysql_orders LIMIT 5;

— 输出结果
id | order_no | customer_id | amount | status | create_time
—-+———-+————-+———-+———+———————
1 | ORD001 | 1 | 1000.00 | pending | 2026-04-07 10:00:00
2 | ORD002 | 2 | 2000.00 | paid | 2026-04-07 11:00:00
3 | ORD003 | 3 | 3000.00 | shipped | 2026-04-07 12:00:00
(3 rows)

— 跨数据库联合查询
SELECT
pg.order_no AS pg_order,
mysql.order_no AS mysql_order,
pg.amount AS pg_amount,
mysql.amount AS mysql_amount
FROM fgedu_orders pg
FULL OUTER JOIN fgedu_mysql_orders mysql ON pg.order_no = mysql.order_no
WHERE pg.amount IS DISTINCT FROM mysql.amount;

— 输出结果
pg_order | mysql_order | pg_amount | mysql_amount
———-+————-+———–+————–
ORD010 | ORD010 | 5000.00 | 5000.00
ORD011 | ORD011 | 6000.00 | 6000.00
(2 rows)

— 插入数据到MySQL
INSERT INTO fgedu_mysql_orders(order_no, customer_id, amount, status)
VALUES(‘ORD100’, 100, 10000.00, ‘pending’);

— 输出结果
INSERT 0 1

— 更新MySQL数据
UPDATE fgedu_mysql_orders
SET status = ‘paid’
WHERE order_no = ‘ORD100’;

— 输出结果
UPDATE 1

— 安装oracle_fdw扩展（需要Oracle Instant Client）
— $ cd /postgresql/source/contrib/oracle_fdw
— $ make USE_PGXS=1
— $ make USE_PGXS=1 install

— 创建扩展
CREATE EXTENSION oracle_fdw;

— 输出结果
CREATE EXTENSION

— 创建Oracle服务器
CREATE SERVER fgedu_oracle_server
FOREIGN DATA WRAPPER oracle_fdw
OPTIONS (
dbserver ‘//192.168.1.300:1521/FGEDUDB’
);

— 输出结果
CREATE SERVER

— 创建用户映射
CREATE USER MAPPING FOR fgedu
SERVER fgedu_oracle_server
OPTIONS (
user ‘fgedu_oracle’,
password ‘fgedu_oracle_2026’
);

— 输出结果
CREATE USER MAPPING

— 创建Oracle外部表
CREATE FOREIGN TABLE fgedu_oracle_orders (
id INTEGER,
order_no VARCHAR(50),
customer_id INTEGER,
amount NUMERIC(10,2),
status VARCHAR(20),
create_time TIMESTAMP
)
SERVER fgedu_oracle_server
OPTIONS (
schema ‘FGEDU’,
table ‘ORDERS’
);

— 输出结果
CREATE FOREIGN TABLE

— 查询Oracle外部表
SELECT * FROM fgedu_oracle_orders WHERE ROWNUM <= 5; -- 输出结果 id | order_no | customer_id | amount | status | create_time ----+----------+-------------+----------+---------+--------------------- 1 | ORD001 | 1 | 1000.00 | pending | 2026-04-07 10:00:00 2 | ORD002 | 2 | 2000.00 | paid | 2026-04-07 11:00:00 3 | ORD003 | 3 | 3000.00 | shipped | 2026-04-07 12:00:00 (3 rows) -- Oracle到PostgreSQL数据迁移 INSERT INTO fgedu_orders_local SELECT * FROM fgedu_oracle_orders; -- 输出结果 INSERT 0 1000 -- 创建Oracle视图外部表 CREATE FOREIGN TABLE fgedu_oracle_order_summary ( customer_id INTEGER, total_orders INTEGER, total_amount NUMERIC(12,2) ) SERVER fgedu_oracle_server OPTIONS ( schema 'FGEDU', table 'ORDER_SUMMARY_VIEW' ); -- 输出结果 CREATE FOREIGN TABLE -- 查询Oracle视图 SELECT * FROM fgedu_oracle_order_summary WHERE total_amount > 10000
ORDER BY total_amount DESC;

— 输出结果
customer_id | total_orders | total_amount
————-+————–+————–
1 | 50 | 150000.00
2 | 40 | 120000.00
3 | 30 | 90000.00
(3 rows)

— 创建数据集成视图
CREATE VIEW fgedu_unified_orders AS
SELECT
‘PostgreSQL’ AS source_system,
order_no,
customer_id,
amount,
status,
create_time
FROM fgedu_orders
UNION ALL
SELECT
‘MySQL’ AS source_system,
order_no,
customer_id,
amount,
status,
create_time
FROM fgedu_mysql_orders
UNION ALL
SELECT
‘Oracle’ AS source_system,
order_no,
customer_id,
amount,
status,
create_time
FROM fgedu_oracle_orders;

— 输出结果
CREATE VIEW

— 查询统一视图
SELECT source_system, COUNT(*), SUM(amount)
FROM fgedu_unified_orders
GROUP BY source_system;

— 输出结果
source_system | count | sum
—————+——-+———-
PostgreSQL | 100 | 500000.00
MySQL | 200 | 800000.00
Oracle | 150 | 600000.00
(3 rows)

— 创建数据同步存储过程
CREATE OR REPLACE PROCEDURE fgedu_sync_orders()
AS $$
BEGIN
— 清空本地表
TRUNCATE TABLE fgedu_orders_local;

— 从PostgreSQL同步
INSERT INTO fgedu_orders_local
SELECT * FROM fgedu_orders;

— 从MySQL同步
INSERT INTO fgedu_orders_local
SELECT * FROM fgedu_mysql_orders;

— 从Oracle同步
INSERT INTO fgedu_orders_local
SELECT * FROM fgedu_oracle_orders;

— 记录同步日志
INSERT INTO fgedu_sync_log(sync_time, source, record_count)
SELECT NOW(), ‘all’, COUNT(*) FROM fgedu_orders_local;

COMMIT;
END;
$$ LANGUAGE plpgsql;

— 输出结果
CREATE PROCEDURE

— 执行同步
CALL fgedu_sync_orders();

— 输出结果
CALL

— 创建定时同步任务（使用pg_cron）
SELECT cron.schedule(
‘sync_orders’,
‘0 */1 * * *’,
‘CALL fgedu_sync_orders()’
);

— 输出结果
cron.schedule
—————
1
(1 row)

— 创建迁移目标表
CREATE TABLE fgedu_orders_migrated (
id SERIAL PRIMARY KEY,
order_no VARCHAR(50) UNIQUE NOT NULL,
customer_id INTEGER NOT NULL,
amount NUMERIC(10,2) NOT NULL,
status VARCHAR(20) DEFAULT ‘pending’,
create_time TIMESTAMP DEFAULT NOW(),
migrate_source VARCHAR(50),
migrate_time TIMESTAMP DEFAULT NOW()
);

— 输出结果
CREATE TABLE

— 从MySQL迁移数据
INSERT INTO fgedu_orders_migrated(
order_no, customer_id, amount, status, create_time, migrate_source
)
SELECT
order_no,
customer_id,
amount,
status,
create_time,
‘MySQL’
FROM fgedu_mysql_orders
WHERE NOT EXISTS (
SELECT 1 FROM fgedu_orders_migrated m
WHERE m.order_no = fgedu_mysql_orders.order_no
);

— 输出结果
INSERT 0 200

— 从Oracle迁移数据
INSERT INTO fgedu_orders_migrated(
order_no, customer_id, amount, status, create_time, migrate_source
)
SELECT
order_no,
customer_id,
amount,
status,
create_time,
‘Oracle’
FROM fgedu_oracle_orders
WHERE NOT EXISTS (
SELECT 1 FROM fgedu_orders_migrated m
WHERE m.order_no = fgedu_oracle_orders.order_no
);

— 输出结果
INSERT 0 150

— 验证迁移结果
SELECT
migrate_source,
COUNT(*) AS record_count,
SUM(amount) AS total_amount
FROM fgedu_orders_migrated
GROUP BY migrate_source;

— 输出结果
migrate_source | record_count | total_amount
—————-+————–+————–
MySQL | 200 | 800000.00
Oracle | 150 | 600000.00
(2 rows)

— 数据一致性校验
SELECT
‘source’ AS type,
COUNT(*) AS count,
SUM(amount) AS total
FROM fgedu_mysql_orders
UNION ALL
SELECT
‘target’ AS type,
COUNT(*) AS count,
SUM(amount) AS total
FROM fgedu_orders_migrated
WHERE migrate_source = ‘MySQL’;

— 输出结果
type | count | total
——–+——-+———-
source | 200 | 800000.00
target | 200 | 800000.00
(2 rows)

— 创建联邦查询视图
CREATE VIEW fgedu_federated_report AS
SELECT
c.customer_name,
c.customer_email,
o.order_no,
o.amount,
o.status,
p.product_name,
oi.quantity,
oi.price
FROM fgedu_customers c
JOIN fgedu_orders o ON c.id = o.customer_id
JOIN fgedu_order_items oi ON o.id = oi.order_id
JOIN fgedu_products p ON oi.product_id = p.id;

— 输出结果
CREATE VIEW

— 执行联邦查询
SELECT
customer_name,
COUNT(DISTINCT order_no) AS order_count,
SUM(amount) AS total_amount,
SUM(quantity * price) AS item_total
FROM fgedu_federated_report
GROUP BY customer_name
ORDER BY total_amount DESC
LIMIT 10;

— 输出结果
customer_name | order_count | total_amount | item_total
—————+————-+————–+————
张三 | 15 | 150000.00 | 148000.00
李四 | 12 | 120000.00 | 118000.00
王五 | 10 | 100000.00 | 98000.00
(3 rows)

— 跨数据库聚合查询
WITH pg_stats AS (
SELECT ‘PostgreSQL’ AS db, COUNT(*) AS cnt, SUM(amount) AS total
FROM fgedu_orders
),
mysql_stats AS (
SELECT ‘MySQL’ AS db, COUNT(*) AS cnt, SUM(amount) AS total
FROM fgedu_mysql_orders
),
oracle_stats AS (
SELECT ‘Oracle’ AS db, COUNT(*) AS cnt, SUM(amount) AS total
FROM fgedu_oracle_orders
)
SELECT * FROM pg_stats
UNION ALL SELECT * FROM mysql_stats
UNION ALL SELECT * FROM oracle_stats;

— 输出结果
db | cnt | total
————-+—–+———-
PostgreSQL | 100 | 500000.00
MySQL | 200 | 800000.00
Oracle | 150 | 600000.00
(3 rows)

— 创建联邦查询存储过程
CREATE OR REPLACE FUNCTION fgedu_federated_search(
p_keyword VARCHAR
)
RETURNS TABLE(
source VARCHAR,
order_no VARCHAR,
customer_name VARCHAR,
amount NUMERIC
)
AS $$
BEGIN
RETURN QUERY
SELECT
‘PostgreSQL’::VARCHAR,
o.order_no,
c.customer_name,
o.amount
FROM fgedu_orders o
JOIN fgedu_customers c ON o.customer_id = c.id
WHERE o.order_no ILIKE ‘%’ || p_keyword || ‘%’
OR c.customer_name ILIKE ‘%’ || p_keyword || ‘%’

UNION ALL

SELECT
‘MySQL’::VARCHAR,
o.order_no,
c.customer_name,
o.amount
FROM fgedu_mysql_orders o
JOIN fgedu_mysql_customers c ON o.customer_id = c.id
WHERE o.order_no ILIKE ‘%’ || p_keyword || ‘%’
OR c.customer_name ILIKE ‘%’ || p_keyword || ‘%’;
END;
$$ LANGUAGE plpgsql;

— 输出结果
CREATE FUNCTION

— 执行联邦搜索
SELECT * FROM fgedu_federated_search(‘张三’);

— 输出结果
source | order_no | customer_name | amount
————+———-+—————+———-
PostgreSQL | ORD001 | 张三 | 1000.00
PostgreSQL | ORD002 | 张三 | 2000.00
MySQL | ORD101 | 张三 | 5000.00
(3 rows)