Group,
I have a postgres implementation with approximately 150 databases.
This was not my choice, but what I was given.
What I need to do is backup individual databases and be able to
restore any database along with it's roles. I understand the pg_dump
and restore commands, they are quite clear and easy to use.
The problem is the roles; other than postgres, I have to users, let's
call them "update" and "read". I did the grants for these 2 roles on
each table for each database. The documentation states that pg_dump
doesn't dump roles.
Is there a method to dump just the roles from a database, or otherwise
select them, so I can build a script to reload the roles? Otherwise, I
guess I'll just read the tables for each database and build the
security script from that.
I need to be able to restore individual databases, so pg_dumpall
doesn't look like the route for me. I've looked at the internal tables
and couldn't find anything with has my roles in it.
TIA,
Julie