如何获取PostgreSQL 12中数据库mydb实际使用的角色列表？

方法一：直接查询源数据库系统表 #

在源机器的psql中连接到mydb数据库，执行以下SQL语句，提取当前数据库实际使用的角色（排除系统自带的pg_catalog和information_schema相关角色）：

WITH used_roles AS (
    -- 表/视图/索引等对象的所有者
    SELECT DISTINCT rolname
    FROM pg_class c
    JOIN pg_roles r ON c.relowner = r.oid
    WHERE c.relnamespace NOT IN (SELECT oid FROM pg_namespace WHERE nspname IN ('pg_catalog', 'information_schema'))
    
    UNION
    
    -- 对象权限中涉及的角色（含授予方和被授予方）
    SELECT DISTINCT rolname
    FROM pg_class c
    JOIN pg_namespace n ON c.relnamespace = n.oid
    JOIN pg_roles r ON (
        has_table_privilege(r.oid, c.oid, 'SELECT,INSERT,UPDATE,DELETE,TRUNCATE,REFERENCES,TRIGGER')
        OR has_sequence_privilege(r.oid, c.oid, 'SELECT,USAGE,UPDATE')
        OR has_function_privilege(r.oid, c.oid, 'EXECUTE')
    )
    WHERE n.nspname NOT IN ('pg_catalog', 'information_schema')
    
    UNION
    
    -- 序列对象的所有者
    SELECT DISTINCT rolname
    FROM pg_sequence s
    JOIN pg_roles r ON s.seqowner = r.oid
    WHERE s.seqnamespace NOT IN (SELECT oid FROM pg_namespace WHERE nspname IN ('pg_catalog', 'information_schema'))
    
    UNION
    
    -- 函数/存储过程的所有者
    SELECT DISTINCT rolname
    FROM pg_proc p
    JOIN pg_roles r ON p.proowner = r.oid
    WHERE p.pronamespace NOT IN (SELECT oid FROM pg_namespace WHERE nspname IN ('pg_catalog', 'information_schema'))
    
    UNION
    
    -- Schema的所有者
    SELECT DISTINCT rolname
    FROM pg_namespace n
    JOIN pg_roles r ON n.nspowner = r.oid
    WHERE n.nspname NOT IN ('pg_catalog', 'information_schema')
)
SELECT rolname FROM used_roles ORDER BY rolname;

查询结果即为mydb实际依赖的所有角色。

方法二：从已有的备份文件中提取角色 #

如果你已经生成了mydb.dump.sql备份文件，可以直接通过命令行工具提取其中涉及的角色：

Linux/macOS 环境 #

执行以下命令：

grep -E "OWNER TO|GRANT .* TO|ROLE " mydb.dump.sql | grep -v "pg_catalog\|information_schema" | awk '{print $NF}' | sort | uniq

该命令会过滤备份文件中所有涉及角色的行，提取角色名并去重排序。

Windows 命令行环境 #

执行以下命令（适配Windows语法）：

findstr /i "OWNER TO GRANT TO ROLE " mydb.dump.sql | findstr /v "pg_catalog information_schema" > temp.txt
for /f "tokens=*" %%a in (temp.txt) do (
    for /f "tokens=3,4,5" %%b in ("%%a") do (
        if "%%b"=="TO" echo %%c
        if "%%d"=="TO" echo %%e
    )
) | sort | unique

后续操作：创建缺失角色并恢复数据库 #

1. 获取源角色的属性（可选，保证角色属性一致）：
   在源机器的psql中执行以下SQL，获取角色的详细属性：

SELECT rolname, rolsuper, rolcreaterole, rolcreatedb, rolcanlogin, rolpassword 
FROM pg_roles 
WHERE rolname IN ('角色1', '角色2', ...); -- 替换为你提取到的角色列表

2. 在目标机器创建角色：
   连接到目标机器的PostgreSQL，执行CREATE ROLE语句创建缺失角色，例如：

-- 创建普通角色
CREATE ROLE myrole;

-- 创建带登录权限的角色（如果源角色有此属性）
CREATE ROLE myloginrole LOGIN PASSWORD 'your_password';

-- 创建带特殊属性的角色（如超级用户、可创建数据库）
CREATE ROLE myadminrole LOGIN PASSWORD 'admin_password' SUPERUSER CREATEDB;

3. 执行恢复命令：
   角色创建完成后，重新执行恢复命令即可：

psql -d mydb -f mydb.dump.sql

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