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: [email protected]
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
From 7b924aed0db30f5e138ae0050d45159b2d675f6e Mon Sep 17 00:00:00 2001
From: Michael Banck <[email protected]>
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
