Re: Missing Trigger after pgdump install

On 8/16/19 1:00 PM, Susan Hurst wrote:
> The dump command used by the DBA to create the pgdump file is:
> 
> pg_dump --clean --if-exists --create --format=plain --no-owner 
> --no-tablespaces \
>          --file=${BKUPDIR}/${TS}.${USER}.pg 2>&1        \
>         | tee -a  ${LOGDIR}/${TS}_biar_dump.log
> 
> No noticeable difference when -b is added, but we're not sure what this 
> is for.  What should we look for?

This should echo the errors below. Not sure where that actually ends up 
on Windows.

What are the versions of Postgres you are using on the dump/restore ends?


> Here is a snippet from the postgres server log that shows an error 
> message that the view devops.subscribers does not exist, however 
> according to the line numbers the view was created before the trigger.
> 
> Error from Postgres server log (postgresql-2019-08-16_140110.log):
> 2019-08-16 14:04:24 CDT ERROR:  relation "devops.subscribers" does not 
> exist
> 2019-08-16 14:04:24 CDT STATEMENT:  create trigger subscribers_iur_trg
>                   instead of update
>                   on devops.subscribers
>                   for each row
>                   execute procedure devops.subscribers_update();
> 
> CREATE VIEW subscribers appears on line 11,968 in the dump file
> 
> 
> CREATE FUNCTION subscribers_update() appears on line 2,466
> 
> 
> CREATE TRIGGER subscribers_iur_trg appears on line 5,457,362
> 
> ---
> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
> Susan E Hurst
> Principal Consultant
> Brookhurst Data LLC
> Email: susan.hurst@brookhurstdata.com
> Mobile: 314-486-3261
> 
> On 2019-08-16 13:37, Adrian Klaver wrote:
>> On 8/16/19 11:27 AM, Susan Hurst wrote:
>>> What scenarios can cause a single trigger to be omitted when 
>>> populating an empty database from a pgdump file?
>>>
>>> We have nightly backups of our production database that we load into 
>>> a fresh, empty database in our sandbox using the pgdump file.
>>>
>>> psql.exe -h localhost -U mi601db -p 5432 -o 
>>> C:<filepath>db_create_output.txt -L C:\<filepath>\db_create_log.txt 
>>> -d skyfall < C:<filepath>\mi601db.pg
>>
>> What is the dump command?
>>
>> What happens if you add -b to above?
>>
>> Which log file are you referring to below, the one generated above or
>> the Postgres server log?
>>
>>>
>>> All objects and data appear in the new database as expected, except 
>>> for a single trigger named subscribers_iur_trg.  The trigger exists 
>>> in production and in the pgdump file.  I can add it manually with no 
>>> errors but it's always missing after our automated process.  Nothing 
>>> useful appears in the log file. The dependent function, 
>>> devops.subscribers_update() is present and accounted for as is the 
>>> view, devops.subscribers.
>>>
>>> CREATE TRIGGER subscribers_iur_trg
>>>    INSTEAD OF UPDATE
>>>    ON devops.subscribers
>>>    FOR EACH ROW
>>>    EXECUTE PROCEDURE devops.subscribers_update();
>>>
>>> We've checked everything we can think of but we're still missing the 
>>> trigger every day.
>>>
>>> Thanks for your help!
>>>
>>> Sue
> 
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com