PostgreSQL连接指定数据库时如何查询其他数据库的表名

问题原因 #

PostgreSQL 的 information_schema 元数据视图仅对当前连接的数据库可见，你当前连接的是 new_site 库，因此该视图中不存在 old_site 库的任何元数据，筛选table_catalog = 'old_site'自然返回空结果。

解决方案 #

方案1：直接切换数据库连接（最简单）

如果允许断开当前连接切换到目标库，操作成本最低：

1. 断开当前 new_site 连接，使用 postgres 用户重新连接到 old_site 数据库
2. 执行以下查询即可获取所有普通表名：

SELECT table_name 
FROM information_schema.tables 
WHERE table_schema = 'public' -- 可按需调整schema筛选条件
  AND table_type = 'BASE TABLE'; -- 过滤视图、系统表，仅返回用户创建的普通表

方案2：使用dblink跨库查询（无需切换连接，单次查询适用）

如果需要在当前new_site连接下直接查询old_site的表，可使用PostgreSQL官方提供的dblink扩展实现跨库查询：

1. 首先在当前new_site库中安装dblink扩展：

CREATE EXTENSION IF NOT EXISTS dblink;

2. 执行跨库查询语句获取old_site的表名：

SELECT * FROM dblink(
    'dbname=old_site user=postgres', -- 目标库连接参数，有密码可追加 password=你的密码
    $$SELECT table_name FROM information_schema.tables WHERE table_schema = 'public' AND table_type = 'BASE TABLE'$$
) AS res(table_name text);

方案3：使用postgres_fdw映射元数据（长期跨库操作适用）

如果需要频繁查询old_site的元数据或业务数据，可通过外部数据包装器postgres_fdw将目标库的元数据表映射到当前库，后续查询无需重复写连接逻辑：

1. 安装postgres_fdw扩展：

CREATE EXTENSION IF NOT EXISTS postgres_fdw;

2. 创建指向old_site的外部服务器：

CREATE SERVER old_site_svr
FOREIGN DATA WRAPPER postgres_fdw
OPTIONS (dbname 'old_site', host 'localhost');

3. 创建用户映射，关联postgres用户的访问权限：

CREATE USER MAPPING FOR postgres
SERVER old_site_svr
OPTIONS (user 'postgres', password '你的postgres密码'); -- 无密码可删除password项

4. 映射old_site的information_schema.tables到当前库的外部表：

CREATE FOREIGN TABLE old_site_tables (
    table_name text,
    table_schema text,
    table_type text
) SERVER old_site_svr
OPTIONS (schema_name 'information_schema', table_name 'tables');

5. 后续直接查询映射后的外部表即可获取old_site的表名：

SELECT table_name FROM old_site_tables
WHERE table_schema = 'public' AND table_type = 'BASE TABLE';

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