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
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.
