postgresql: how do I dump and restore roles for a cluster?

Question

Where are roles stored in a cluster, and how do I dump them?

I did a pg_dump of a db and then loaded it into a different cluster, but I get a lot of these errors:

psql:mydump.sql:3621: ERROR: role "myrole" does not exist 

So apparently the dump of my db does not include roles. I tried dumping the 'postgres' db, but I don't see the roles there either.

Do I need to use pg_dumpall --roles-only ?

Postgresql versions 8.4.8 and 9.1.4 OS: Ubuntu 11.04 Natty

Answer 1

From the CREATE ROLE documentation:

Note that roles are defined at the database cluster level, and so are valid in all databases in the cluster.

Since pg_dump dumps a single database, you can't extract roles with that utility. The pg_dumpall --roles-only command you proposed will do the work - however you may need to filter its output so that only desired roles will be created in the new cluster.

Roles are stored in the pg_authid catalog, which is physically stored in the data/global/ subfolder of a PostgreSQL installation, together with the other cluster-wide tables. You can query the contents of pg_authid through the pg_roles view.

NOTE: you will need superuser rights to dump the roles. Otherwise, you'd get a permission denied on SELECT on pg_authid - and even when a superuser grants SELECT rights, you'd get the same error. In this case, however, you can list the roles by querying pg_authid directly, COPY it to a file and roll some magic to create the necessary CREATE ROLE and ALTER ROLE statements.

• Ethan Furman: Can you add how to restore all roles (i.e. no filtering needed)?
• Fernando Fabreti: to restore all roles, just copy and paste the output of pg_dumpall --roles-only into the desired psql shell. Or specific CREATE ROLE and ALTER ROLE lines
• andilabs: any faster way than pg_dumpall --roles-only ? It does not offer any binary output,and any compression :( It is slow especially when loading back via psql