[PATCH] Add --create-only option to pg_dump/pg_dumpall
Hi,
There is (to my knowledge) no direct way to get the `CREATE DATABASE`
and assorted `GRANT foo ON DATABASE` etc. commands out of a pg_dump
without having to edit the TOC or filter the SQL output with e.g. grep.
It is not part of pg_dumpall -g, and if one uses pg_dump / pg_dumpall -s
-C, one gets all definitions for all database objects.
So I propose a small additional option --create-only, which only dumps
the create-related commands, e.g.:
postgres=# CREATE DATABASE test;
CREATE DATABASE
postgres=# CREATE USER test;
CREATE ROLE
postgres=# GRANT CONNECT ON DATABASE test TO test;
GRANT
postgres=# \q
postgres@kohn:~$ pg_dump --create-only -p 65432 -d test -h /tmp | egrep -v '^($|--|SET)'
SELECT pg_catalog.set_config('search_path', '', false);
CREATE DATABASE test WITH TEMPLATE = template0 ENCODING = 'UTF8' LOCALE = 'de_DE.UTF-8';
ALTER DATABASE test OWNER TO postgres;
\connect test
SELECT pg_catalog.set_config('search_path', '', false);
GRANT CONNECT ON DATABASE test TO test;
postgres@kohn:~$
Michael
--
Michael Banck
Projektleiter / Senior Berater
Tel.: +49 2166 9901-171
Fax: +49 2166 9901-100
Email: michael.banck@credativ.de
credativ GmbH, HRB Mönchengladbach 12080
USt-ID-Nummer: DE204566209
Trompeterallee 108, 41189 Mönchengladbach
Geschäftsführung: Dr. Michael Meskes, Jörg Folz, Sascha Heuer
Unser Umgang mit personenbezogenen Daten unterliegt
folgenden Bestimmungen: https://www.credativ.de/datenschutz
Attachments:
0001-Add-create-only-option-to-pg_dump-pg_dumpall.patchtext/x-patch; charset=UTF-8; name=0001-Add-create-only-option-to-pg_dump-pg_dumpall.patchDownload
From 7b924aed0db30f5e138ae0050d45159b2d675f6e Mon Sep 17 00:00:00 2001
From: Michael Banck <michael.banck@credativ.de>
Date: Thu, 31 Dec 2020 16:12:31 +0100
Subject: [PATCH] Add --create-only option to pg_dump/pg_dumpall.
This makes pg_dump only output the database creation and assorted commands
(notably also ALTER DATABASE [...] SET [...]). If only the database-specific
settings are desired, this makes dumping large databases or schemas much
easier.
---
src/bin/pg_dump/pg_backup.h | 1 +
src/bin/pg_dump/pg_dump.c | 14 +++++++++++---
src/bin/pg_dump/pg_dumpall.c | 8 +++++++-
3 files changed, 19 insertions(+), 4 deletions(-)
diff --git a/src/bin/pg_dump/pg_backup.h b/src/bin/pg_dump/pg_backup.h
index eea9f30a79..6560a611fc 100644
--- a/src/bin/pg_dump/pg_backup.h
+++ b/src/bin/pg_dump/pg_backup.h
@@ -172,6 +172,7 @@ typedef struct _dumpOptions
int outputClean;
int outputCreateDB;
+ int outputCreateDBOnly;
bool outputBlobs;
bool dontOutputBlobs;
int outputNoOwner;
diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c
index eb988d7eb4..8b60f91ffe 100644
--- a/src/bin/pg_dump/pg_dump.c
+++ b/src/bin/pg_dump/pg_dump.c
@@ -363,6 +363,7 @@ main(int argc, char **argv)
*/
{"attribute-inserts", no_argument, &dopt.column_inserts, 1},
{"binary-upgrade", no_argument, &dopt.binary_upgrade, 1},
+ {"create-only", no_argument, &dopt.outputCreateDBOnly, 1},
{"column-inserts", no_argument, &dopt.column_inserts, 1},
{"disable-dollar-quoting", no_argument, &dopt.disable_dollar_quoting, 1},
{"disable-triggers", no_argument, &dopt.disable_triggers, 1},
@@ -703,6 +704,9 @@ main(int argc, char **argv)
if (!plainText)
dopt.outputCreateDB = 1;
+ if (dopt.outputCreateDBOnly)
+ dopt.outputCreateDB = 1;
+
/*
* On Windows we can only have at most MAXIMUM_WAIT_OBJECTS (= 64 usually)
* parallel jobs because that's the maximum limit for the
@@ -917,9 +921,12 @@ main(int argc, char **argv)
if (dopt.outputCreateDB)
dumpDatabase(fout);
- /* Now the rearrangeable objects. */
- for (i = 0; i < numObjs; i++)
- dumpDumpableObject(fout, dobjs[i]);
+ if (!dopt.outputCreateDBOnly)
+ {
+ /* Now the rearrangeable objects. */
+ for (i = 0; i < numObjs; i++)
+ dumpDumpableObject(fout, dobjs[i]);
+ }
/*
* Set up options info to ensure we dump what we want.
@@ -1019,6 +1026,7 @@ help(const char *progname)
printf(_(" -B, --no-blobs exclude large objects in dump\n"));
printf(_(" -c, --clean clean (drop) database objects before recreating\n"));
printf(_(" -C, --create include commands to create database in dump\n"));
+ printf(_(" --create-only only dump commands to create database\n"));
printf(_(" -E, --encoding=ENCODING dump the data in encoding ENCODING\n"));
printf(_(" -n, --schema=PATTERN dump the specified schema(s) only\n"));
printf(_(" -N, --exclude-schema=PATTERN do NOT dump the specified schema(s)\n"));
diff --git a/src/bin/pg_dump/pg_dumpall.c b/src/bin/pg_dump/pg_dumpall.c
index 007a3d0f9a..7eaa4d1901 100644
--- a/src/bin/pg_dump/pg_dumpall.c
+++ b/src/bin/pg_dump/pg_dumpall.c
@@ -67,6 +67,7 @@ static bool dosync = true;
static int binary_upgrade = 0;
static int column_inserts = 0;
+static int create_only = 0;
static int disable_dollar_quoting = 0;
static int disable_triggers = 0;
static int if_exists = 0;
@@ -126,6 +127,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},
+ {"create-only", no_argument, &create_only, 1},
{"disable-dollar-quoting", no_argument, &disable_dollar_quoting, 1},
{"disable-triggers", no_argument, &disable_triggers, 1},
{"exclude-database", required_argument, NULL, 6},
@@ -637,6 +639,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(_(" --create-only dump only the commands to create database\n"));
printf(_(" --disable-dollar-quoting disable dollar quoting, use SQL standard quoting\n"));
printf(_(" --disable-triggers disable triggers during data-only restore\n"));
printf(_(" --exclude-database=PATTERN exclude databases whose name matches PATTERN\n"));
@@ -1521,7 +1524,10 @@ dumpDatabases(PGconn *conn)
}
}
else
- create_opts = "--create";
+ if (create_only)
+ create_opts = "--create-only";
+ else
+ create_opts = "--create";
if (filename)
fclose(OPF);
--
2.20.1
On 01.03.21 11:12, Michael Banck wrote:
postgres@kohn:~$ pg_dump --create-only -p 65432 -d test -h /tmp | egrep -v '^($|--|SET)'
SELECT pg_catalog.set_config('search_path', '', false);
CREATE DATABASE test WITH TEMPLATE = template0 ENCODING = 'UTF8' LOCALE = 'de_DE.UTF-8';
ALTER DATABASE test OWNER TO postgres;
\connect test
SELECT pg_catalog.set_config('search_path', '', false);
GRANT CONNECT ON DATABASE test TO test;
I find this option name confusing, because evidently it prints out
things that are not CREATE commands. For example, an intuitive idea of
"create only" might be to omit GRANT commands.
The following review has been posted through the commitfest application:
make installcheck-world: tested, passed
Implements feature: tested, passed
Spec compliant: tested, passed
Documentation: not tested
Hi
I have tried the patch and the new option is able to control the contents of pg_dump outputs to include only create db related commands. I also agree that the option name is a little misleading to the user so I would suggest instead of using "create-only", you can say something maybe like "createdb-only" because this option only applies to CREATE DATABASE related commands, not CREATE TABLE or other objects. In the help menu, you can then elaborate more that this option "dump only the commands related to create database like ALTER, GRANT..etc"
Cary Huang
-------------
HighGo Software Inc. (Canada)
cary.huang@highgo.ca
www.highgo.ca
Hi,
Am Montag, den 29.03.2021, 17:59 +0000 schrieb Cary Huang:
I have tried the patch and the new option is able to control the
contents of pg_dump outputs to include only create db related
commands.
Thanks for testing!
I also agree that the option name is a little misleading to the user
so I would suggest instead of using "create-only", you can say
something maybe like "createdb-only" because this option only applies
to CREATE DATABASE related commands, not CREATE TABLE or other
objects. In the help menu, you can then elaborate more that this
option "dump only the commands related to create database like ALTER,
GRANT..etc"
Well I have to say I agree with Peter that the option name I came up
with is pretty confusing, not sure createdb-only is much better as it
also includes GRANTs etc.
I think from a technical POV it's useful as it closes a gap between
pg_dumpall -g and pg_dump -Fc $DATABASE in my opinion, without having to
potentially schema-dump and filter out a large number of database
objects.
Anybody else have some opinions on what to call this best? Maybe just a
short option and some explanatory text in --help along with it?
Michael
--
Michael Banck
Projektleiter / Senior Berater
Tel.: +49 2166 9901-171
Fax: +49 2166 9901-100
Email: michael.banck@credativ.de
credativ GmbH, HRB Mönchengladbach 12080
USt-ID-Nummer: DE204566209
Trompeterallee 108, 41189 Mönchengladbach
Geschäftsführung: Dr. Michael Meskes, Jörg Folz, Sascha Heuer
Unser Umgang mit personenbezogenen Daten unterliegt
folgenden Bestimmungen: https://www.credativ.de/datenschutz
On Tue, Mar 30, 2021 at 6:02 PM Michael Banck <michael.banck@credativ.de> wrote:
Hi,
Am Montag, den 29.03.2021, 17:59 +0000 schrieb Cary Huang:
I have tried the patch and the new option is able to control the
contents of pg_dump outputs to include only create db related
commands.Thanks for testing!
I also agree that the option name is a little misleading to the user
so I would suggest instead of using "create-only", you can say
something maybe like "createdb-only" because this option only applies
to CREATE DATABASE related commands, not CREATE TABLE or other
objects. In the help menu, you can then elaborate more that this
option "dump only the commands related to create database like ALTER,
GRANT..etc"Well I have to say I agree with Peter that the option name I came up
with is pretty confusing, not sure createdb-only is much better as it
also includes GRANTs etc.I think from a technical POV it's useful as it closes a gap between
pg_dumpall -g and pg_dump -Fc $DATABASE in my opinion, without having to
potentially schema-dump and filter out a large number of database
objects.Anybody else have some opinions on what to call this best? Maybe just a
short option and some explanatory text in --help along with it?
Maybe --database-globals or something like that?
Other than the name (which might be influenced by this), shouldn't
this functionality be in pg_restore as well? That is, if I make a
pg_dump in custom format, I would want to be able to extract that
information from the dump as well?
--
Magnus Hagander
Me: https://www.hagander.net/
Work: https://www.redpill-linpro.com/
Hi,
I have reviewed and tested the patch. Following are a few comments.
1.
The main objective of this patch is to get the dump which consists of SQLs
related to
CREATEDB only. I have tested the patch and it generates a proper dump file.
But my
concern is, it should execute the code which is necessary. But I see that
the code
is preparing some data which we may not dump. So I feel we should avoid
executing
such flows. Please correct me if I am wrong.
2.
I also agree that the option name is a little misleading to the user
so I would suggest instead of using "create-only", you can say
something maybe like "createdb-only" because this option only applies
to CREATE DATABASE related commands, not CREATE TABLE or other
objects. In the help menu, you can then elaborate more that this
option "dump only the commands related to create database like ALTER,
GRANT..etc"Well I have to say I agree with Peter that the option name I came up
with is pretty confusing, not sure createdb-only is much better as it
also includes GRANTs etc.
I agree with Cary that we should name this as 'createdb-only' and provide a
brief
description in help.
3.
if (!plainText)
dopt.outputCreateDB = 1;
+ if (dopt.outputCreateDBOnly)
+ dopt.outputCreateDB = 1;
+'dopt.outputCreateDBOnly' if block can be merged with '!plainText' if block.
4.
static int binary_upgrade = 0;
static int column_inserts = 0;
+static int create_only = 0;
static int disable_dollar_quoting = 0;
The variable 'create_only' should be changed to 'createdb_only' to match
with
similar variable used in pg_dump.c.
Thanks and Regards,
Nitin Jadhav
On Tue, Mar 30, 2021 at 9:32 PM Michael Banck <michael.banck@credativ.de>
wrote:
Show quoted text
Hi,
Am Montag, den 29.03.2021, 17:59 +0000 schrieb Cary Huang:
I have tried the patch and the new option is able to control the
contents of pg_dump outputs to include only create db related
commands.Thanks for testing!
I also agree that the option name is a little misleading to the user
so I would suggest instead of using "create-only", you can say
something maybe like "createdb-only" because this option only applies
to CREATE DATABASE related commands, not CREATE TABLE or other
objects. In the help menu, you can then elaborate more that this
option "dump only the commands related to create database like ALTER,
GRANT..etc"Well I have to say I agree with Peter that the option name I came up
with is pretty confusing, not sure createdb-only is much better as it
also includes GRANTs etc.I think from a technical POV it's useful as it closes a gap between
pg_dumpall -g and pg_dump -Fc $DATABASE in my opinion, without having to
potentially schema-dump and filter out a large number of database
objects.Anybody else have some opinions on what to call this best? Maybe just a
short option and some explanatory text in --help along with it?Michael
--
Michael Banck
Projektleiter / Senior Berater
Tel.: +49 2166 9901-171
Fax: +49 2166 9901-100
Email: michael.banck@credativ.decredativ GmbH, HRB Mönchengladbach 12080
USt-ID-Nummer: DE204566209
Trompeterallee 108, 41189 Mönchengladbach
Geschäftsführung: Dr. Michael Meskes, Jörg Folz, Sascha HeuerUnser Umgang mit personenbezogenen Daten unterliegt
folgenden Bestimmungen: https://www.credativ.de/datenschutz
On 9 Apr 2021, at 15:34, Nitin Jadhav <nitinjadhavpostgres@gmail.com> wrote:
I have reviewed and tested the patch. Following are a few comments.
This review has gone unanswered since April, has been WoA since early April and
the patch no longer applies. I'm marking this Returned with Feedback, a new
version can be submitted for a future CF once the issues have been resolved.
--
Daniel Gustafsson https://vmware.com/