Re: Missing Trigger after pgdump install - Mailing list pgsql-general

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.hurst@brookhurstdata.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.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
>> 
>>