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
/messages/by-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
/messages/by-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
Attachments:
p1.patchtext/x-patch; name=p1.patchDownload
diff --git a/src/bin/pg_dump/pg_dumpall.c b/src/bin/pg_dump/pg_dumpall.c
index b14bb8e..35fa22d 100644
--- a/src/bin/pg_dump/pg_dumpall.c
+++ b/src/bin/pg_dump/pg_dumpall.c
@@ -68,6 +68,7 @@ static bool dosync = true;
static int binary_upgrade = 0;
static int column_inserts = 0;
+static int createdb_only = 0;
static int disable_dollar_quoting = 0;
static int disable_triggers = 0;
static int if_exists = 0;
@@ -121,6 +122,7 @@ main(int argc, char *argv[])
{"attribute-inserts", no_argument, &column_inserts, 1},
{"binary-upgrade", no_argument, &binary_upgrade, 1},
{"column-inserts", no_argument, &column_inserts, 1},
+ {"createdb-only", no_argument, &createdb_only, 1},
{"disable-dollar-quoting", no_argument, &disable_dollar_quoting, 1},
{"disable-triggers", no_argument, &disable_triggers, 1},
{"if-exists", no_argument, &if_exists, 1},
@@ -504,13 +506,13 @@ main(int argc, char *argv[])
*/
if (output_clean)
{
- if (!globals_only && !roles_only && !tablespaces_only)
+ if (!globals_only && !roles_only && !tablespaces_only && !createdb_only)
dropDBs(conn);
- if (!roles_only && !no_tablespaces)
+ if (!roles_only && !no_tablespaces && !createdb_only)
dropTablespaces(conn);
- if (!tablespaces_only)
+ if (!tablespaces_only && !createdb_only)
dropRoles(conn);
}
@@ -518,7 +520,7 @@ main(int argc, char *argv[])
* Now create objects as requested. Be careful that option logic here
* is the same as for drops above.
*/
- if (!tablespaces_only)
+ if (!tablespaces_only && !createdb_only)
{
/* Dump roles (users) */
dumpRoles(conn);
@@ -531,7 +533,7 @@ main(int argc, char *argv[])
}
/* Dump tablespaces */
- if (!roles_only && !no_tablespaces)
+ if (!roles_only && !no_tablespaces && !createdb_only)
dumpTablespaces(conn);
/* Dump CREATE DATABASE commands */
@@ -539,14 +541,14 @@ main(int argc, char *argv[])
dumpCreateDB(conn);
/* Dump role/database settings */
- if (!tablespaces_only && !roles_only)
+ if (!tablespaces_only && !roles_only && !createdb_only)
{
if (server_version >= 90000)
dumpDbRoleConfig(conn);
}
}
- if (!globals_only && !roles_only && !tablespaces_only)
+ if (!globals_only && !roles_only && !tablespaces_only && !createdb_only)
dumpDatabases(conn);
PQfinish(conn);
@@ -594,6 +596,7 @@ help(void)
printf(_(" -x, --no-privileges do not dump privileges (grant/revoke)\n"));
printf(_(" --binary-upgrade for use by upgrade utilities only\n"));
printf(_(" --column-inserts dump data as INSERT commands with column names\n"));
+ printf(_(" --createdb-only CREATE and ACL databases commands\n"));
printf(_(" --disable-dollar-quoting disable dollar quoting, use SQL standard quoting\n"));
printf(_(" --disable-triggers disable triggers during data-only restore\n"));
printf(_(" --if-exists use IF EXISTS when dropping objects\n"));
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:
/messages/by-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
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
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 Guerrero
Center for Information Technology
University of Oslo, Norway
PGP Public Key: http://folk.uio.no/rafael/
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
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:
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:
/messages/by-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