Re: Dumping database creation options and ACLs

On 12/08/2014 04:21 PM, Ronan Dunklau wrote:
> Hello.
>
> As of now, the only way to restore database options and ACLs is to use
> pg_dumpall without the globals options. The often recommended pg_dumpall -g +
> individual dumps of the target databases doesn't restore those.
>
> Since pg_dump/pg_restore offer the ability to create the database, it should do
> so with the correct owner, options and database ACLs.
>
> There was some discussion about those issues a while ago (see
> http://www.postgresql.org/message-id/11646.1272814212@sss.pgh.pa.us for
> example). As I understand it, the best way to handle that would be to push
> these modifications in pg_dump, but it is unclear how it should be done with
> regards to restoring to a different database.
>
> In the meantime, it would be great to add an option to pg_dumpall allowing to
> dump this information. We could add the db creation in the output of
> pg_dumpall -g, and add a specific --createdb-only option (similar to --roles-
> only and --tablespaces-only).
>
> Would such a patch be welcome ?
>
>
>

Hello,

As reported by Ronan there's no other option than using pg_dumpall to restore
database options and ACLs.

So, we use this trick to stop pg_dumpall before \connect and then use pg_restore:

pg_dumpall -s | sed -rn '/^\\connect/{q}; p' > database+grants.sql

Of course, it is not graceful as we just need results of pg_dumpall -g and what
the dumpCreateDB() function outputs.

What do you think about adding an option like --createdb-only (as suggested by
Ronan) for this? I'm not fully satisfied with this name though, I'll be happy
if you have a better suggestion.

Attached a naive patch.

--
Adrien NAYRAT

http://dalibo.com - http://dalibo.org