Postgres FDW扩展问题：导入外部Schema执行关联查询时出错

问题 #

在使用Postgres FDW扩展从同一数据库服务器导入外部Schema执行关联查询时遇到错误，操作步骤及报错如下：

源数据库操作（创建枚举类型和用户表） #

-- Create the enumerated type
CREATE TYPE UserType AS ENUM ('INTERNAL', 'EXTERNAL');

-- Create the User table using the enumerated type
CREATE TABLE "users" (
    id          SERIAL PRIMARY KEY,
    type        UserType,
    created_at  TIMESTAMP WITHOUT TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

目标数据库（akil）导入外部Schema的脚本 #

CREATE SCHEMA temp_0_schema;

CREATE EXTENSION IF NOT EXISTS postgres_fdw;

CREATE SERVER temp_0_server
FOREIGN DATA WRAPPER postgres_fdw
OPTIONS (dbname 'temp_0', host 'localhost', port '5432');

CREATE USER MAPPING FOR CURRENT_USER
SERVER temp_0_server
OPTIONS (user 'akil', password '');

IMPORT FOREIGN SCHEMA public
FROM SERVER temp_0_server
INTO temp_0_schema;

报错信息 #

ERROR:  type "public.usertype" does not exist
LINE 3:   type public.usertype OPTIONS (column_name 'type'),
               ^
QUERY:  CREATE FOREIGN TABLE users (
  id integer OPTIONS (column_name 'id') NOT NULL,
  type public.usertype OPTIONS (column_name 'type'),
  created_at timestamp without time zone OPTIONS (column_name 'created_at')
) SERVER temp_0_server
OPTIONS (schema_name 'public', table_name 'users');
CONTEXT:  importing foreign table "users" 

SQL state: 42704

问题原因分析 #

• Postgres FDW的IMPORT FOREIGN SCHEMA命令仅自动导入外部表的结构定义，不会同步源数据库中的自定义类型（如这里的UserType枚举）。
• 目标数据库akil的public schema中不存在usertype枚举类型，而导入命令尝试创建外部表时，直接引用了源库的public.usertype类型，目标库无法识别该类型，因此抛出"type does not exist"错误。
• 即使源库和目标库在同一服务器上，二者的自定义类型是完全独立的，不会自动共享，必须先手动在目标库中创建与源库完全一致的自定义类型，才能成功导入使用该类型的外部表。

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