1
-- 在本地数据库执行
2
CREATE EXTENSION IF NOT EXISTS postgres_fdw;

1
CREATE SERVER remote_server
2
FOREIGN DATA WRAPPER postgres_fdw
3
OPTIONS (
4
  host '192.168.1.100',
5
  dbname 'target_db',
6
  port '5432'
7
);

1
CREATE USER MAPPING FOR postgres
2
SERVER remote_server
3
OPTIONS (
4
  user 'postgres',
5
  password 'remote-password'
6
);

1
-- 导入单个表
2
IMPORT FOREIGN SCHEMA public
3
LIMIT TO (table_name)
4
FROM SERVER remote_server
5
INTO public;
6

7
-- 导入多个表
8
IMPORT FOREIGN SCHEMA public
9
LIMIT TO (table1, table2, table3)
10
FROM SERVER remote_server
11
INTO public;
12

13
-- 导入整个 schema（慎用）
14
IMPORT FOREIGN SCHEMA public
15
FROM SERVER remote_server
16
INTO public;

1
-- 查看所有外部服务器
2
SELECT * FROM pg_foreign_server;
3

4
-- 查看导入的外部表
5
SELECT * FROM information_schema.foreign_tables;
6

7
-- 修改服务器连接信息
8
ALTER SERVER remote_server
9
OPTIONS (SET host '192.168.1.200', SET port '5432', SET dbname 'new_db');
10

11
-- 修改用户映射密码
12
ALTER USER MAPPING FOR CURRENT_USER
13
SERVER remote_server
14
OPTIONS (SET password 'new-password');
15

16
-- 增加批量获取行数（优化性能）
17
ALTER SERVER remote_server OPTIONS (ADD fetch_size '50000');
18

19
-- 删除外部表
20
DROP FOREIGN TABLE table_name;
21

22
-- 删除用户映射
23
DROP USER MAPPING FOR postgres SERVER remote_server;
24

25
-- 删除服务器（CASCADE 同时删除依赖的外部表）
26
DROP SERVER remote_server CASCADE;
27

28
-- 删除扩展
29
DROP EXTENSION postgres_fdw;