PostgreSQL使用postgres_fdw连接外部服务器遭拒绝求助

问题详情 #

执行针对postgres_fdw外部表的INSERT语句时，出现如下连接错误：

SQL Error [08001]: ERROR: could not connect to server "regionaldb_server"
  Detail: connection to server at "localhost" (127.0.0.1), port 5404 failed: Connection refused

外部服务器与源服务器部署在同一机器但不同端口，DBeaver可正常连接目标实例，但通过postgres_fdw查询时失败。当前postgres_fdw配置如下：

CREATE EXTENSION IF NOT EXISTS postgres_fdw;

CREATE SERVER regionaldb_server
                  FOREIGN DATA WRAPPER postgres_fdw
                  OPTIONS (host 'localhost', dbname 'platform_test', port '5404');

CREATE USER MAPPING FOR current_user
                  SERVER regionaldb_server
                  OPTIONS (user 'admin', password 'nimda');

CREATE FOREIGN TABLE "users-meta_foreign" (
              id uuid,
              email VARCHAR(500),
              phone VARCHAR(10),
              "internationalCode" VARCHAR(5),
              region VARCHAR(255) NOT NULL,
              "userRoles" TEXT NOT NULL,
              "createdAt" timestamptz NOT NULL,
              "updatedAt" timestamptz NOT NULL,
              "deletedAt" timestamptz
          )
          SERVER regionaldb_server
          OPTIONS (schema_name 'platform.public', table_name 'users-meta');

触发错误的INSERT语句：

INSERT INTO "public"."users-meta_foreign" ("id", "createdAt", "updatedAt", "deletedAt", "email", "phone", "internationalCode", "region", "userRoles") VALUES (DEFAULT, DEFAULT, DEFAULT, DEFAULT, $1, DEFAULT, DEFAULT, $2, $3) RETURNING "id", "createdAt", "updatedAt", "deletedAt";

排查方向与解决方案 #

1. 验证本地直接连接可行性 #

先排除目标实例本身的连接问题，在源服务器上执行以下命令直接连接目标实例：

psql -h localhost -p 5404 -U admin platform_test

• 如果该命令连接失败：问题出在目标实例的监听配置或pg_hba.conf规则，跳至步骤2、3排查。
• 如果该命令连接成功：问题集中在postgres_fdw配置，跳至步骤4、5排查。

2. 检查目标实例的监听配置 #

登录目标实例，执行以下SQL确认监听地址与端口：

SHOW listen_addresses;
SHOW port;

• 确保port值为5404，与fdw配置一致。
• 确保listen_addresses包含localhost或127.0.0.1（若仅设置特定IP，需保证源服务器进程可访问）。
• 修改配置后需重启目标PostgreSQL服务生效。

3. 检查目标实例的pg_hba.conf规则 #

目标实例的pg_hba.conf需允许admin用户从本地发起连接，添加或确认存在如下规则：

# IPv4 local connections
host    platform_test     admin     127.0.0.1/32     scram-sha-256
# IPv6 local connections
host    platform_test     admin     ::1/128          scram-sha-256

修改pg_hba.conf后，需执行SELECT pg_reload_conf();重载配置，无需重启服务。

4. 修正postgres_fdw用户映射 #

CREATE USER MAPPING FOR current_user仅对创建映射时的当前用户生效，若执行INSERT的用户并非该用户，映射会失效。可改为指定具体用户或允许所有用户：

# 指定具体用户（替换为实际执行INSERT的用户名）
CREATE USER MAPPING FOR your_source_server_user
SERVER regionaldb_server
OPTIONS (user 'admin', password 'nimda');

# 或允许所有用户临时测试
CREATE USER MAPPING FOR ALL
SERVER regionaldb_server
OPTIONS (user 'admin', password 'nimda');

5. 修正外部表的schema参数 #

外部表配置中schema_name 'platform.public'格式错误，schema名称应为单个值（如public或platform），根据目标表实际所在schema修正：

ALTER FOREIGN TABLE "users-meta_foreign"
OPTIONS (SET schema_name 'public');

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