Hi,
attached is a small patch that adds an -N option to pg_restore, in order
to exclude a schema, in addition to -n for the restriction to a schema.
In principle, this could be extended to -t etc., but I think having this
for schemas would be the most useful with the least effort.
One use case for this would be the need to restore one or more schemas
first (using -n foo), then all the others (now using -N foo) without (i)
having to specify them all with -n and (ii) getting errors due to
already restored objects from the initial schema. While users could be
told to just ignore the errors/warnings, it would be useful for
automation when you would like to check for zero errors/warning, for
example.
I have so far seen two reasons for this use case: (i) Add-ons that are
not yet an extension and install objects in public (e.g. ESRI ArcGIS),
requiring the public schema to be present already on restore of user
schemas and (ii) restoring materialized views that reference objects
from other schemas; as permissions are restored last, no permissions
have been granted for those other schemas yet.
Argueably, those reasons could be dealt with as well, but this seems to
be a generally useful addition to pg_restore, in my opinion.
Michael
--
Projektleiter / Senior Berater
Tel.: +49 2166 9901-171
Fax: +49 2166 9901-100
Email: michael.ba...@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
diff --git a/doc/src/sgml/ref/pg_restore.sgml b/doc/src/sgml/ref/pg_restore.sgml
index c906919..e5eb18e 100644
--- a/doc/src/sgml/ref/pg_restore.sgml
+++ b/doc/src/sgml/ref/pg_restore.sgml
@@ -315,6 +315,17 @@
</varlistentry>
<varlistentry>
+ <term><option>-N <replaceable class="parameter">namespace</replaceable></option></term>
+ <term><option>--exclude-schema=<replaceable class="parameter">schema</replaceable></option></term>
+ <listitem>
+ <para>
+ Do not restore objects that are in the named schema. Multiple schemas
+ to be excluded may be specified with multiple <option>-N</> switches.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
<term><option>-O</option></term>
<term><option>--no-owner</option></term>
<listitem>
diff --git a/src/bin/pg_dump/pg_backup.h b/src/bin/pg_dump/pg_backup.h
index 4afa92f..0a28124 100644
--- a/src/bin/pg_dump/pg_backup.h
+++ b/src/bin/pg_dump/pg_backup.h
@@ -99,6 +99,7 @@ typedef struct _restoreOptions
SimpleStringList indexNames;
SimpleStringList functionNames;
SimpleStringList schemaNames;
+ SimpleStringList schemaExcludeNames;
SimpleStringList triggerNames;
SimpleStringList tableNames;
diff --git a/src/bin/pg_dump/pg_backup_archiver.c b/src/bin/pg_dump/pg_backup_archiver.c
index 05bdbdb..37063ba 100644
--- a/src/bin/pg_dump/pg_backup_archiver.c
+++ b/src/bin/pg_dump/pg_backup_archiver.c
@@ -2674,6 +2674,13 @@ StrictNamesCheck(RestoreOptions *ropt)
exit_horribly(modulename, "schema \"%s\" not found\n", missing_name);
}
+ if (ropt->schemaExcludeNames.head != NULL)
+ {
+ missing_name = simple_string_list_not_touched(&ropt->schemaExcludeNames);
+ if (missing_name != NULL)
+ exit_horribly(modulename, "schema \"%s\" not found\n", missing_name);
+ }
+
if (ropt->tableNames.head != NULL)
{
missing_name = simple_string_list_not_touched(&ropt->tableNames);
@@ -2751,6 +2758,15 @@ _tocEntryRequired(TocEntry *te, teSection curSection, RestoreOptions *ropt)
return 0;
}
+ if (ropt->schemaExcludeNames.head != NULL)
+ {
+ /* If no namespace is specified, it means all. */
+ if (!te->namespace)
+ return 0;
+ if ((simple_string_list_member(&ropt->schemaExcludeNames, te->namespace)))
+ return 0;
+ }
+
if (ropt->selTypes)
{
if (strcmp(te->desc, "TABLE") == 0 ||
diff --git a/src/bin/pg_dump/pg_restore.c b/src/bin/pg_dump/pg_restore.c
index fb08e6b..3be8654 100644
--- a/src/bin/pg_dump/pg_restore.c
+++ b/src/bin/pg_dump/pg_restore.c
@@ -85,6 +85,7 @@ main(int argc, char **argv)
{"data-only", 0, NULL, 'a'},
{"dbname", 1, NULL, 'd'},
{"exit-on-error", 0, NULL, 'e'},
+ {"exclude-schema", 1, NULL, 'N'},
{"file", 1, NULL, 'f'},
{"format", 1, NULL, 'F'},
{"function", 1, NULL, 'P'},
@@ -148,7 +149,7 @@ main(int argc, char **argv)
}
}
- while ((c = getopt_long(argc, argv, "acCd:ef:F:h:I:j:lL:n:Op:P:RsS:t:T:U:vwWx1",
+ while ((c = getopt_long(argc, argv, "acCd:ef:F:h:I:j:lL:n:N:Op:P:RsS:t:T:U:vwWx1",
cmdopts, NULL)) != -1)
{
switch (c)
@@ -196,6 +197,10 @@ main(int argc, char **argv)
simple_string_list_append(&opts->schemaNames, optarg);
break;
+ case 'N': /* Do not dump data for this schema */
+ simple_string_list_append(&opts->schemaExcludeNames, optarg);
+ break;
+
case 'O':
opts->noOwner = 1;
break;
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
