Thread: Exclude schema during pg_restore

Exclude schema during pg_restore

From

Michael Banck

Date:

31 August 2016, 08:12:28

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.banck@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

Attachment

pg_restore-exclude-schema.patch

Re: Exclude schema during pg_restore

From

Fabrízio de Royes Mello

Date:

31 August 2016, 10:59:29

Em quarta-feira, 31 de agosto de 2016, Michael Banck <mbanck@gmx.net> 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

Re: Exclude schema during pg_restore

From

Michael Banck

Date:

31 August 2016, 13:14:21

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.banck@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

Re: Exclude schema during pg_restore

From

Peter Eisentraut

Date:

02 September 2016, 01:40:02

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

Re: Exclude schema during pg_restore

From

Michael Banck

Date:

02 September 2016, 06:29:43

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.banck@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

Re: Exclude schema during pg_restore

From

Michael Banck

Date:

19 September 2016, 19:25:09

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.banck@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

Attachment

pg_restore-exclude-schema_v2.patch

Re: Exclude schema during pg_restore

From

Peter Eisentraut

Date:

21 September 2016, 00:59:42

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

Re: Exclude schema during pg_restore

From

Michael Banck

Date:

22 September 2016, 17:46:17

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.banck@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

Re: [HACKERS] Exclude schema during pg_restore

From

Michael Banck

Date:

12 September 2018, 20:12:20

Hi,

Am Dienstag, den 20.09.2016, 20:59 -0400 schrieb 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.

I noticed this part of the help text does not mention `-N' when I think
it should:

|The options -I, -n, -P, -t, -T, and --section can be combined and specified
|multiple times to select multiple objects.

Patch attached.

Michael

-- 
Michael Banck
Projektleiter / Senior Berater
Tel.: +49 2166 9901-171
Fax:  +49 2166 9901-100
Email: michael.banck@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

Unser Umgang mit personenbezogenen Daten unterliegt
folgenden Bestimmungen: https://www.credativ.de/datenschutz

Attachment

pg_restore_help_fix.patch

Re: [HACKERS] Exclude schema during pg_restore

From

Peter Eisentraut

Date:

29 October 2018, 10:35:17

On 12/09/2018 19:12, Michael Banck wrote:
>> I added the new option to the help output in pg_restore.
> 
> I noticed this part of the help text does not mention `-N' when I think
> it should:
> 
> |The options -I, -n, -P, -t, -T, and --section can be combined and specified
> |multiple times to select multiple objects.
> 
> Patch attached.

Committed to 10, 11, and master.

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

Re: [HACKERS] Exclude schema during pg_restore

From

Michael Banck

Date:

29 October 2018, 11:21:04

Am Montag, den 29.10.2018, 11:35 +0100 schrieb Peter Eisentraut:
> On 12/09/2018 19:12, Michael Banck wrote:
> Committed to 10, 11, and master.

Thanks!

Michael

-- 
Michael Banck
Projektleiter / Senior Berater
Tel.: +49 2166 9901-171
Fax:  +49 2166 9901-100
Email: michael.banck@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

Unser Umgang mit personenbezogenen Daten unterliegt
folgenden Bestimmungen: https://www.credativ.de/datenschutz