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
