Thread: pg_restore man page question

The man page of pg_restore says

    --disable-triggers
        This option is relevant only when performing a
        data-only restore. It instructs pg_restore to execute
        commands to temporarily disable triggers on the
        target tables while the data is reloaded. [...]

This hint seems overly narrow: when doing a restore of schema
PLUS data (IOW, not a schema-only restore) it should apply,
too, shouldn't it ?

Otherwise it would suggest that pg_restore does this:

- restore basic table structure
- restore data
- restore constraints and triggers and ...

Karsten
--
GPG key ID E4071346 @ eu.pool.sks-keyservers.net
E167 67FD A291 2BEA 73BD  4537 78B9 A9F9 E407 1346

On 03/07/2016 09:17 AM, Karsten Hilbert wrote:
> The man page of pg_restore says
>
>     --disable-triggers
>         This option is relevant only when performing a
>         data-only restore. It instructs pg_restore to execute
>         commands to temporarily disable triggers on the
>         target tables while the data is reloaded. [...]
>
> This hint seems overly narrow: when doing a restore of schema
> PLUS data (IOW, not a schema-only restore) it should apply,
> too, shouldn't it ?

No because triggers are restored AFTER data. This explicitly applies to
data being restored to an existing schema that has triggers on the table
the data is being loaded into.

Sincerely,

JD


--
Command Prompt, Inc.                  http://the.postgres.company/
                         +1-503-667-4564
PostgreSQL Centered full stack support, consulting and development.
Everyone appreciates your honesty, until you are honest with them.

On Mon, Mar 07, 2016 at 11:03:39AM -0700, David G. Johnston wrote:

> > The man page of pg_restore says
> >
> >         --disable-triggers
> >                 This option is relevant only when performing a
> >                 data-only restore. It instructs pg_restore to execute
> >                 commands to temporarily disable triggers on the
> >                 target tables while the data is reloaded. [...]
> >
> > This hint seems overly narrow: when doing a restore of schema
> > PLUS data (IOW, not a schema-only restore) it should apply,
> > too, shouldn't it ?
> >
> > Otherwise it would suggest that pg_restore does this:
> >
> > - restore basic table structure
> > - restore data
> > - restore constraints and triggers and ...
> >
>
> ... ​indexes​
>
>
> ​Why is the surprising?

It's not surprising to me. I was making sure which way things
are because the documentation I consulted wasn't clear on
that for me. The way things turn out to be -- so much the
better !

Karsten
--
GPG key ID E4071346 @ eu.pool.sks-keyservers.net
E167 67FD A291 2BEA 73BD  4537 78B9 A9F9 E407 1346

On 03/07/2016 10:22 AM, Karsten Hilbert wrote:
> On Mon, Mar 07, 2016 at 11:03:39AM -0700, David G. Johnston wrote:
>
>>> The man page of pg_restore says
>>>
>>>          --disable-triggers
>>>                  This option is relevant only when performing a
>>>                  data-only restore. It instructs pg_restore to execute
>>>                  commands to temporarily disable triggers on the
>>>                  target tables while the data is reloaded. [...]
>>>
>>> This hint seems overly narrow: when doing a restore of schema
>>> PLUS data (IOW, not a schema-only restore) it should apply,
>>> too, shouldn't it ?
>>>
>>> Otherwise it would suggest that pg_restore does this:
>>>
>>> - restore basic table structure
>>> - restore data
>>> - restore constraints and triggers and ...
>>>
>>
>> ... ​indexes​
>>
>>
>> ​Why is the surprising?
>
> It's not surprising to me. I was making sure which way things
> are because the documentation I consulted wasn't clear on
> that for me. The way things turn out to be -- so much the
> better !

Yeah, you have to dig into one of the switch definitions to get an answer:

http://www.postgresql.org/docs/9.5/interactive/app-pgrestore.html

--section=sectionname

     Only restore the named section. The section name can be pre-data,
data, or post-data. This option can be specified more than once to
select multiple sections. The default is to restore all sections.

     The data section contains actual table data as well as large-object
definitions. Post-data items consist of definitions of indexes,
triggers, rules and constraints other than validated check constraints.
Pre-data items consist of all other data definition items.

>
> Karsten
>


--
Adrian Klaver
adrian.klaver@aklaver.com