如何查找包含指定ProjectName的Postgres数据库名称

针对你有170个同构Postgres数据库、需要快速定位包含指定ProjectName（支持模糊匹配）记录的库的需求，以下是两种比Bash逐个遍历更高效的方案：

方案一：使用Postgres内置dblink扩展（数据库端批量查询） #

这个方案完全在Postgres内部执行，避免频繁的客户端-服务端交互，效率最高。

步骤1：启用dblink扩展 #

首先确保执行用户有权限安装扩展，执行：

CREATE EXTENSION IF NOT EXISTS dblink;

步骤2：执行动态查询脚本 #

替换%你的匹配关键词%为实际的模糊匹配值，执行以下SQL：

-- 设置要匹配的ProjectName模糊模式
SET search_project = '%你的匹配关键词%';

WITH user_databases AS (
    SELECT datname AS db_name
    FROM pg_database
    WHERE datname NOT IN ('postgres', 'template0', 'template1') -- 排除系统库
      AND datallowconn = true -- 仅包含允许连接的数据库
)
SELECT db_name
FROM user_databases
WHERE EXISTS (
    SELECT 1
    FROM dblink(
        'dbname=' || db_name, -- 构造目标库连接字符串（如需密码/端口可追加：user=xxx password=xxx port=5432）
        'SELECT 1 FROM SM_Project WHERE ProjectName LIKE ''' || current_setting('search_project') || ''' LIMIT 1'
    ) AS match_check(result int)
);

说明 #

• 脚本先从pg_database获取所有可访问的用户数据库
• 通过dblink连接每个库，执行存在性查询（LIMIT 1避免全表扫描，提升速度）
• 最终只返回存在匹配记录的数据库名称

方案二：动态生成SQL脚本批量执行（Shell+Psql组合） #

如果无法使用dblink（比如权限限制），可以用这种方式减少Psql进程启动次数，比逐个循环高效。

执行命令（Shell环境） #

替换your_user和%你的匹配关键词%为实际值：

# 设置模糊匹配关键词
SEARCH_PATTERN='%你的匹配关键词%'

# 生成批量查询SQL脚本
psql -U your_user -d postgres -t -c "
    SELECT 'SELECT ''' || datname || ''' AS db_name FROM ' || datname || '.SM_Project WHERE ProjectName LIKE ''''$SEARCH_PATTERN'''' LIMIT 1;'
    FROM pg_database
    WHERE datname NOT IN ('postgres', 'template0', 'template1')
      AND datallowconn = true
" > query_dbs.sql

# 执行脚本并去重输出结果
psql -U your_user -d postgres -f query_dbs.sql | grep -v '^\s*$' | sort -u

说明 #

• 先通过Psql从系统表生成每个库的查询语句，写入SQL文件
• 一次性执行所有查询，最后过滤空行并去重，得到目标库列表

注意事项 #

• 执行用户需要拥有所有目标数据库的连接权限，以及SM_Project表的查询权限
• 若数据库使用非默认端口/需要密码，需在连接字符串中补充对应参数（比如方案一中的dblink连接串，方案二中的Psql命令加-p 端口或-W）

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