如何仅备份PostgreSQL中的角色与用户？恢复数据库遇角色错误求对应备份命令

Got it, let's break down how to handle this role-related backup/restore issue you're hitting—super common when moving databases between instances, so you're not alone here!

一、仅备份PostgreSQL角色与用户 #

If you just need to export all roles (users, groups, and their permission settings), the official and easiest way is using pg_dumpall with the --roles-only flag:

# Export all roles to a SQL file (replace roles_backup.sql with your preferred filename)
pg_dumpall --roles-only -U postgres -f roles_backup.sql

• -U postgres: Use the superuser postgres (required for role management operations)
• -f roles_backup.sql: Writes the output to a file instead of printing to the console

For more granular control (like exporting only specific roles), you can query PostgreSQL's system tables directly to generate creation statements:

# Export a single role's definition (replace your_target_role with the role name)
SELECT pg_get_userdef('your_target_role');

# Export all non-system default roles (exclude built-in superusers)
SELECT pg_get_userdef(rolname) FROM pg_roles 
WHERE rolname NOT IN ('postgres', 'pg_signal_backend', 'pg_read_all_settings');

Just copy the query results into a SQL file to save your role definitions.

二、先备份角色再执行完整数据库备份流程 #

To avoid role-missing errors during restoration, follow this ordered workflow:

1. First: Backup all roles
   Run the pg_dumpall --roles-only command from above to capture every user, group, and permission setting first.

2. Second: Backup your target database
   Use pg_dump for single-database backups (or pg_dumpall --databases-only for all databases):

# Backup a single database in compressed custom format (more efficient than plain SQL)
pg_dump -U postgres -d your_database_name -F c -f db_backup.dump

• -F c: Uses custom format, which supports compression and selective restoration
• -d your_database_name: Specifies the database you want to backup

3. Critical Restoration Order
   When restoring, always restore roles first, then the database—this eliminates the "role does not exist" errors:

# Restore roles first
psql -U postgres -f roles_backup.sql

# Then restore the database (the -C flag creates the database if it doesn't exist)
pg_restore -U postgres -d your_database_name -C db_backup.dump

三、Quick Notes to Avoid Headaches #

• Always run these commands as a superuser (like postgres)—role operations require elevated permissions.
• pg_dumpall --roles-only includes password hashes (not plaintext) for roles, so passwords will stay intact after restoration.
• If migrating across PostgreSQL versions, double-check compatibility for role-related syntax (though recent versions are mostly consistent).

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