Re: Dumping roles improvements?

Josh Berkus <josh(at)agliodbs(dot)com> writes:
> (1) I cannot produce a single file in custom dump format which includes
> both a single database and all of the roles I need to build that database.

I would see addressing this with the proposal to have pg_dumpall able to
issue an archive of -Fc dumps, that pg_restore would know how to handle.
Then we could have some option to list or exclude databases you want in
this pg_dumpall -Fc format.

It's been said that extending custom format to handle several databases
would be complex, but I don't think it's necessary. A simple archive,
tar formatted for example, containing the custom format file of all
selected databases, would be user friendly enough. All the more if you
add a -j option to control how many databases you dump at the same time.

The main drawback is that you need to prepare a directory with the
globals.sql file and a custom format file per database, and only when
all of those are finished can you pack them into the tar format.

Again, good enough for me.

> (2) I cannot dump a set of roles without md5 passwords.
>
> Both of these are things I need to support dev/stage/testing integration
> at multiple sites.

I don't handle that in pg_staging, but the other features you need are
all implemented into that tool. It knows how to fetch the per cluster
settings then init your staging cluster with that and only then restore
your database (with a pgbouncer layer in between so that you can have
more than one available in parallel and an easy switch from one to the
other, like db -> db_20111013 rather than db_20111012).

https://github.com/dimitri/pg_staging

Regards,
--
Dimitri Fontaine
http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support