Re: [HACKERS] Exclude schema during pg_restore

[Michael Banck]

Hi,

On Tue, Sep 20, 2016 at 08:59:37PM -0400, Peter Eisentraut wrote:
> On 9/19/16 3:23 PM, Michael Banck wrote:
> > Version 2 attached.
> 
> Committed, thanks.
 
Thanks!

> I added the new option to the help output in pg_restore.

Oh, sorry I missed that.


Michael

-- 
Michael Banck
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


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Exclude schema during pg_restore

[Peter Eisentraut]

On 9/19/16 3:23 PM, Michael Banck wrote:
> Version 2 attached.

Committed, thanks.

I added the new option to the help output in pg_restore.

-- 
Peter Eisentraut  http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Exclude schema during pg_restore

[Michael Banck]

Hi,

sorry, it took me a while to find time to look at this.

On Thu, Sep 01, 2016 at 09:39:56PM -0400, Peter Eisentraut wrote:
> On 8/31/16 4:10 AM, Michael Banck wrote:
> > 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.
> 
> I think this is a good idea, and the approach looks sound.  However,
> something doesn't work right.  If I take an empty database and dump it,
> it will dump the plpgsql extension.  If I run pg_dump in plain-text mode
> with -N, then the plpgsql extension is also dumped (since it is not in
> the excluded schema).  But if I use the new pg_restore -N option, the
> plpgsql extension is not dumped.  Maybe this is because it doesn't have
> a schema, but I haven't checked.

I was afraid that this might need major code surgery, but in the end it
seems this was just a thinko on my part in tocEntryRequired(). For the
exclude-schema case, we shouldn't skip objects without a namespace (like
the plpgsql extension you mentioned above).
 
> pg_dump does not apply --strict-names to -N, but your patch for
> pg_restore does that.  I think that should be made the same as pg_dump.

Ok, I've removed that hunk.

Version 2 attached.


Michael

-- 
Michael Banck
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 @@
  
 
  
+  -N namespace
+  --exclude-schema=schema
+  
+   
+Do not restore objects that are in the named schema.  Multiple schemas
+to be excluded may be specified with multiple -N switches.
+   
+  
+ 
+
+ 
   -O
   --no-owner
   
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..0081d2f 100644
--- a/src/bin/pg_dump/pg_backup_archiver.c
+++ b/src/bin/pg_dump/pg_backup_archiver.c
@@ -2751,6 +2751,9 @@ _tocEntryRequired(TocEntry *te, teSection curSection, RestoreOptions *ropt)
 			return 0;
 	}
 
+	if ((ropt->schemaExcludeNames.head != NULL) && te->namespace && simple_string_list_member(>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(>schemaNames, optarg);
 break;
 
+			case 'N':			/* Do not dump data for this schema */
+simple_string_list_append(>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

Re: [HACKERS] Exclude schema during pg_restore

[Michael Banck]

Am Donnerstag, den 01.09.2016, 21:39 -0400 schrieb Peter Eisentraut:
> On 8/31/16 4:10 AM, Michael Banck wrote:
> > 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.
> 
> I think this is a good idea, and the approach looks sound.  However,
> something doesn't work right.  If I take an empty database and dump it,
> it will dump the plpgsql extension.  If I run pg_dump in plain-text mode
> with -N, then the plpgsql extension is also dumped (since it is not in
> the excluded schema).  But if I use the new pg_restore -N option, the
> plpgsql extension is not dumped.  Maybe this is because it doesn't have
> a schema, but I haven't checked.

Thanks for the testing and feedback, I hadn't thought of issues with
extensions when I tested myself.  I will take a look.

> pg_dump does not apply --strict-names to -N, but your patch for
> pg_restore does that.  I think that should be made the same as pg_dump.

Aye.


Thanks,

Michael

-- 
Michael Banck
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




-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Exclude schema during pg_restore

[Peter Eisentraut]

On 8/31/16 4:10 AM, Michael Banck wrote:
> 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.

I think this is a good idea, and the approach looks sound.  However,
something doesn't work right.  If I take an empty database and dump it,
it will dump the plpgsql extension.  If I run pg_dump in plain-text mode
with -N, then the plpgsql extension is also dumped (since it is not in
the excluded schema).  But if I use the new pg_restore -N option, the
plpgsql extension is not dumped.  Maybe this is because it doesn't have
a schema, but I haven't checked.

pg_dump does not apply --strict-names to -N, but your patch for
pg_restore does that.  I think that should be made the same as pg_dump.

-- 
Peter Eisentraut  http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Exclude schema during pg_restore

[Michael Banck]

Hi,

Am Mittwoch, den 31.08.2016, 07:59 -0300 schrieb Fabrízio de Royes
Mello:

> Please add it to the next open commitfest.

I had done so already: https://commitfest.postgresql.org/10/762/


Regards,

Michael

-- 
Michael Banck
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




-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Exclude schema during pg_restore

[Fabrízio de Royes Mello]

Em quarta-feira, 31 de agosto de 2016, Michael Banck 
escreveu:

> 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.
>
>
Please add it to the next open commitfest.

Regards,



-- 
Fabrízio de Royes Mello
Consultoria/Coaching PostgreSQL
>> Timbira: http://www.timbira.com.br
>> Blog: http://fabriziomello.github.io
>> Linkedin: http://br.linkedin.com/in/fabriziomello
>> Twitter: http://twitter.com/fabriziomello
>> Github: http://github.com/fabriziomello

[HACKERS] Exclude schema during pg_restore

[Michael Banck]

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 @@
  
 
  
+  -N namespace
+  --exclude-schema=schema
+  
+   
+Do not restore objects that are in the named schema.  Multiple schemas
+to be excluded may be specified with multiple -N switches.
+   
+  
+ 
+
+ 
   -O
   --no-owner
   
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(>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(>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(>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(>schemaNames, optarg);
 break;
 
+			case 'N':			/* Do not dump data for this schema */
+simple_string_list_append(>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