Re: Missing Trigger after pgdump install

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?

The log file named db_create_log.txt is where nothing useful appeared.

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