PostgreSQL本地跨库创建物化视图：dblink/FDW之外的更优方案？

一、优化dblink用法，避免手动指定列类型 #

不需要手动定义每个列的类型，可以先通过dblink创建临时表（临时表会自动继承远程表的列类型），再基于临时表创建物化视图：

-- 1. 创建临时表，自动获取远程表的列结构和类型
CREATE TEMPORARY TABLE temp_t1 AS
SELECT colonne1, colonne2, geom 
FROM dblink('hostaddr=127.0.0.1 port=5432 user=user dbname=dbname password=psswrd',
           'SELECT colonne1, colonne2, geom FROM schema.t1') AS t1;

-- 2. 基于临时表创建物化视图
CREATE MATERIALIZED VIEW schema.vm_nom AS
SELECT colonne1, colonne2, geom FROM temp_t1;

临时表会自动匹配远程表的列类型、长度等属性，省去手动定义的麻烦。如果需要刷新物化视图，重复上述步骤即可。

二、使用postgres_fdw（更优方案） #

postgres_fdw是PostgreSQL官方的外部数据包装器，能把远程数据库的表映射成本地外部表，操作起来和本地表完全一致，是跨库访问的推荐方案，步骤如下：

1. 启用postgres_fdw扩展 #

CREATE EXTENSION IF NOT EXISTS postgres_fdw;

2. 创建外部服务器（配置远程数据库连接信息） #

CREATE SERVER remote_db_server
FOREIGN DATA WRAPPER postgres_fdw
OPTIONS (host '127.0.0.1', port '5432', dbname 'dbname');

3. 创建用户映射（关联本地用户和远程数据库用户） #

把当前数据库的用户（替换成你的本地用户名）和远程数据库的用户绑定：

CREATE USER MAPPING FOR your_local_username
SERVER remote_db_server
OPTIONS (user 'remote_user', password 'remote_password');

4. 导入远程表（两种方式） #

方式一：手动创建单个外部表

如果只需要映射特定表，可以手动定义列：

CREATE FOREIGN TABLE schema.remote_t1 (
    colonne1 varchar(24),
    colonne2 varchar(127),
    geom geometry(geometry, 2154)
) SERVER remote_db_server
OPTIONS (schema_name 'schema', table_name 't1');

方式二：批量导入整个schema的表

自动导入远程schema下的所有表为本地外部表，无需手动定义列：

IMPORT FOREIGN SCHEMA schema
FROM SERVER remote_db_server
INTO your_local_schema; -- 替换成你要存放外部表的本地schema

5. 创建物化视图 #

此时远程表已经像本地表一样可以直接查询，创建物化视图非常简单：

CREATE MATERIALIZED VIEW schema.vm_nom AS
SELECT colonne1, colonne2, geom FROM schema.remote_t1;

FDW的优势 #

• 无需每次写冗长的dblink连接字符串和列定义
• 支持PostgreSQL的查询优化器，能生成更高效的执行计划
• 可以对外部表创建索引（物化视图直接在本地存储数据，性能更优）
• 维护成本低，修改远程表结构后，重新执行IMPORT FOREIGN SCHEMA即可同步

内容的提问来源于stack exchange，提问作者yam0925