Search Postgresql Archives

Re: Missing Trigger after pgdump install

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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@xxxxxxxxxxxxxxxxxx
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





[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux