Thread: Dumping database creation options and ACLs
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 ? PS: this email was originally sent to the pgsql-bugs mailing list -- Ronan Dunklau http://dalibo.com - http://dalibo.org
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
Attachment
On Thu, Jun 29, 2017 at 12:30 PM, Adrien Nayrat
<adrien.nayrat@dalibo.com> wrote:
> 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.
Note that some progress has been made on the CURRENT_DATABASE thing:
https://www.postgresql.org/message-id/CAF3+xM+xSswcWQZMP1cjj12gPz8DXHcM9_fT1y-0fVzxi9pmOw@mail.gmail.com
I tend to favor that approach myself, although one point in favor of
your suggestion is that adding another flag to pg_dumpall is a heck of
a lot less work to get to some kind of solution to this issue.
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
On 06/29/2017 06:30 PM, Adrien Nayrat wrote:
> 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.
>
Hello
We have a discussion about this some time ago and we created a wiki page
where we tried to write down some ideas/proposals and links to threads
discussing the subject:
https://wiki.postgresql.org/wiki/Pg_dump_improvements
regards,
-- Rafael Martinez GuerreroCenter for Information TechnologyUniversity of Oslo, Norway
PGP Public Key: http://folk.uio.no/rafael/
On 07/03/2017 05:16 PM, Rafael Martinez wrote: > We have a discussion about this some time ago and we created a wiki page > where we tried to write down some ideas/proposals and links to threads > discussing the subject: > > https://wiki.postgresql.org/wiki/Pg_dump_improvements Thanks for this link! I'll look at this. On 07/03/2017 04:58 PM, Robert Haas wrote: > Note that some progress has been made on the CURRENT_DATABASE thing: > > https://www.postgresql.org/message-id/CAF3+xM+xSswcWQZMP1cjj12gPz8DXHcM9_fT1y-0fVzxi9pmOw@mail.gmail.com > > I tend to favor that approach myself, although one point in favor of > your suggestion is that adding another flag to pg_dumpall is a heck of > a lot less work to get to some kind of solution to this issue. Thanks, I'll look. Even if my approach is simple, the question is "Do we want another flag in pg_dumpall? Is it the role of pg_dumpall?". Regards, -- Adrien NAYRAT http://dalibo.com - http://dalibo.org