如何通过postgres_fdw从本地PostgreSQL连接远程SQL Server？报错求助

问题描述 #

我正在开展一个多数据源对比的大型项目，此前已成功使用postgres_fdw从多个远程PostgreSQL服务器拉取数据到本地PostgreSQL实例。现在尝试连接远程SQL Server，沿用了连接远程PostgreSQL的代码，但执行查询时出现报错：

ERROR: relation "mle_object" does not exist LINE 7: mle_object mo

该查询在目标远程SQL Server上可正常运行。

原代码 #

CREATE EXTENSION
IF
    NOT EXISTS postgres_fdw;
CREATE SERVER
IF
    NOT EXISTS remote_mleci_prod FOREIGN DATA WRAPPER postgres_fdw OPTIONS ( HOST '<HOST>', PORT '<PORT>', dbname'<DBNAME>' );
CREATE USER MAPPING
IF
    NOT EXISTS FOR postgres SERVER remote_mleci_prod OPTIONS ( USER '<DB USER>', PASSWORD '<DB PASSWORD>' );
GRANT USAGE ON FOREIGN SERVER remote_mleci_prod TO local_user;
IMPORT FOREIGN SCHEMA PUBLIC 
LIMIT TO ( mle_object, source_mle_mapping, source_object, mle_enrolments ) 
FROM
    SERVER remote_prod_dda INTO PUBLIC;
SELECT
    mo.mle_object_id AS "MLE OBJECT ID",
    mo.mle_id AS "COURSE ID",
    so.source_object_id AS "SOURCE OBJECT ID",
    so.source_id AS "SOURCE ID" 
FROM
    mle_object mo
    LEFT JOIN source_mle_mapping smm ON smm.mle_object_id = mo.mle_object_id
    LEFT JOIN source_object so ON smm.source_object_id = so.source_object_id 
WHERE
    mo.mle_id = 'I3132-CIVL-11130-1221-1YR-037943'

核心原因 #

postgres_fdw是专门为连接PostgreSQL服务器设计的外部数据包装器，不支持连接SQL Server。沿用PostgreSQL的连接方式，会导致无法正确识别并导入SQL Server的表，最终触发"关系不存在"的错误。

解决步骤 #

1. 安装SQL Server专用的FDW #

推荐使用tds_fdw（基于FreeTDS驱动，兼容性强），先安装系统依赖再创建PostgreSQL扩展：

• 系统依赖安装（以Ubuntu为例）：

  sudo apt-get install freetds-dev freetds-bin
  

• 创建PostgreSQL扩展：

  CREATE EXTENSION IF NOT EXISTS tds_fdw;
  

2. 重新配置远程服务器连接 #

替换原CREATE SERVER语句，适配SQL Server的连接规则：

CREATE SERVER IF NOT EXISTS remote_mleci_prod
FOREIGN DATA WRAPPER tds_fdw
OPTIONS (
    servername '<HOST>', -- SQL Server主机IP或域名
    port '<PORT>', -- 默认端口为1433
    database '<DBNAME>',
    tds_version '7.4' -- 对应SQL Server版本：2016+用7.4，2012用7.3
);

3. 创建用户映射 #

适配SQL Server的身份验证逻辑（以下为SQL Server身份验证示例）：

CREATE USER MAPPING IF NOT EXISTS FOR postgres
SERVER remote_mleci_prod
OPTIONS (
    username '<DB USER>',
    password '<DB PASSWORD>'
);

4. 正确导入外部模式 #

SQL Server默认模式为dbo，而非PostgreSQL的public，需调整导入语句：

IMPORT FOREIGN SCHEMA dbo
LIMIT TO (mle_object, source_mle_mapping, source_object, mle_enrolments)
FROM SERVER remote_mleci_prod INTO public;

若你的表在SQL Server的其他模式下，替换dbo为对应模式名称即可

5. 执行查询验证 #

导入完成后执行原查询，若存在表名大小写识别问题，可给表名添加双引号：

SELECT
    mo.mle_object_id AS "MLE OBJECT ID",
    mo.mle_id AS "COURSE ID",
    so.source_object_id AS "SOURCE OBJECT ID",
    so.source_id AS "SOURCE ID" 
FROM
    "mle_object" mo
    LEFT JOIN "source_mle_mapping" smm ON smm.mle_object_id = mo.mle_object_id
    LEFT JOIN "source_object" so ON smm.source_object_id = so.source_object_id 
WHERE
    mo.mle_id = 'I3132-CIVL-11130-1221-1YR-037943'

额外排查点 #

• 执行IMPORT FOREIGN SCHEMA时检查是否有报错，确认表是否成功导入本地
• 用\d public.mle_object命令查看本地是否存在该外部表
• 确保SQL Server用户对目标表拥有SELECT权限

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