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

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.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