Re: Missing Trigger after pgdump install

Production version:
PostgreSQL 9.5.0 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.4.7
20120313 (Red Hat 4.4.7-16), 64-bit

Sandbox version:
"PostgreSQL 9.5.14, compiled by Visual C++ build 1800, 64-bit"

---
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Susan E Hurst
Principal Consultant
Brookhurst Data LLC
Email: susan(dot)hurst(at)brookhurstdata(dot)com
Mobile: 314-486-3261

On 2019-08-16 16:24, Adrian Klaver wrote:
> 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(dot)hurst(at)brookhurstdata(dot)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
>>
>>