Re: [HACKERS] Exclude schema during pg_restore
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
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
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(&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
Re: [HACKERS] Exclude schema during pg_restore
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
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
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
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
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(&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