Re: Dumping roles improvements?

> There seems to be agreement on something for (2), and it won't be hard.
> (1) would probably be much more complex. Essentially we'd need to add a
> new object type for roles, I think. But won't (2) give you most of what
> you need for (1) anyway? AIUI, your problem is that the roles might not
> exist, and so some or all of the dump will fail. But if you have (2) and
> dump the roles without passwords and restore them before running
> pg_restore that wouldn't happen. It won't be one command but it will be
> two or three pretty easy commands.

These serve two different purposes.

The reason I want to have the dependant roles created as part of a
database dump is so that we can ship around dump files as a single file,
and restore them with a single command. This is considerably simpler
than the current requirements, which are:

1. pg_dumpall the roles
2. pg_dump the database
3. tar both files
4. ship file
5. untar both files
6. psql the role file
7. pg_restore the database file

Since the above is something I'm doing on around 11 different machines
between once a day and once a week, eliminating the 4 extra steps would
be really nice.

However, we'd also need CREATE OR REPLACE ROLE to really integrate
shipping database copies. Without that, including roles in the database
dump doesn't help that much.

--
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com