Postgresql: how to dump and restore roles for a cluster

dumppostgresqlrestorerole

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

Best Answer

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.

Related Question