PostgreSQL postgres_fdw 跨库查询与推下谓词实践

---

title: PostgreSQL postgres_fdw 跨库查询与推下谓词实践

keywords: postgres_fdw, CREATE SERVER, USER MAPPING, IMPORT FOREIGN SCHEMA, pushdown

description: 使用 postgres_fdw 配置跨库访问，导入外部模式与表，验证查询谓词推下与性能改进。

tags:

• CREATE SERVER
• FDW
• IMPORT FOREIGN SCHEMA
• PostgreSQL
• USER MAPPING
• postgres_fdw
• pushdown
• 数据库

categories:

• 文章资讯
• 技术教程

---

初始化与服务器映射：

CREATE EXTENSION IF NOT EXISTS postgres_fdw;
CREATE SERVER remotesrv FOREIGN DATA IMPORTED FROM postgres OPTIONS (host '10.0.0.2', dbname 'app', port '5432');
CREATE USER MAPPING FOR current_user SERVER remotesrv OPTIONS (user 'replicator', password 'secret');

导入外部模式或定义外部表：

IMPORT FOREIGN SCHEMA public LIMIT TO (users, orders) FROM SERVER remotesrv INTO public;
CREATE FOREIGN TABLE public.users_remote (
  id bigint,
  name text
) SERVER remotesrv OPTIONS (schema_name 'public', table_name 'users');

查询与推下验证：

EXPLAIN VERBOSE SELECT * FROM public.users_remote WHERE id > 1000;