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; ```