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(>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
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 Banckescreveu: > 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