Missing Trigger after pgdump install

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

--
Adrian Klaver
adrian.klaver@aklaver.com

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

--
Adrian Klaver
adrian.klaver@aklaver.com

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 8/16/19 3:18 PM, Susan Hurst wrote:

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"

I going to say it has something to do with this:

https://www.postgresql.org/docs/9.5/release-9-5-12.html

"Avoid use of insecure search_path settings in pg_dump and other client
programs (Noah Misch, Tom Lane)"

Are you using the 9.5.0 or 9.5.14 version of pg_dump to dump from the
production server?

--
Adrian Klaver
adrian.klaver@aklaver.com

We're using the 9.5.14 in the sandbox to extract data and objects from
the pgdump that was created in the 9.5.0 version. Hope I answered your
question correctly. If not, let me know and I'll try again.

Our biggest concern is that there may be other silent issues that we
have not yet discovered. Thanks for the info you just provided, Adrian.
We'll read up on the path settings to see if we can find an answer
there.

Sue

---
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Susan E Hurst
Principal Consultant
Brookhurst Data LLC
Email: susan.hurst@brookhurstdata.com
Mobile: 314-486-3261

On 8/16/19 4:45 PM, Susan Hurst wrote:

We're using the 9.5.14 in the sandbox to extract data and objects from
the pgdump that was created in the 9.5.0 version.  Hope I answered
your question correctly.  If not, let me know and I'll try again.

Our biggest concern is that there may be other silent issues that we
have not yet discovered.  Thanks for the info you just provided,
Adrian.  We'll read up on the path settings to see if we can find an
answer there.

Sue

---
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Susan E Hurst
Principal Consultant
Brookhurst Data LLC
Email: susan.hurst@brookhurstdata.com
Mobile: 314-486-3261

On 2019-08-16 17:29, Adrian Klaver wrote:

On 8/16/19 3:18 PM, Susan Hurst wrote:

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"

I going to say it has something to do with this:

https://www.postgresql.org/docs/9.5/release-9-5-12.html

"Avoid use of insecure search_path settings in pg_dump and other
client programs (Noah Misch, Tom Lane)"

Are you using the 9.5.0 or 9.5.14 version of pg_dump to dump from the
production server?

In case Adrian has gone hojme I'll chime in:  I believe his question is
asking which installation/version of pgdump is used in the command you
provided earlier.  If it's run from the production machine it would
likely be the 9.5.0 version, but could also be the 9.5.14 version.  All
depending on the PATH and host and postgres installation.

On 8/16/19 3:45 PM, Susan Hurst wrote:

We're using the 9.5.14 in the sandbox to extract data and objects from
the pgdump that was created in the 9.5.0 version.  Hope I answered your
question correctly.  If not, let me know and I'll try again.

As Rob pointed out I was wanting to know what pg_binary was used to grab
the schema and data from the 9.5.0 instance?

Our biggest concern is that there may be other silent issues that we
have not yet discovered.  Thanks for the info you just provided, Adrian.
 We'll read up on the path settings to see if we can find an answer there.

In a post upstream you had:

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

Is that direct from the pg_dump file?

In other words are the above not schema qualified in the file?

In the 9.5.14 instance when you do in psql:

\d subscribers

what do you get?

Sue

---
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Susan E Hurst
Principal Consultant
Brookhurst Data LLC
Email: susan.hurst@brookhurstdata.com
Mobile: 314-486-3261

On 2019-08-16 17:29, Adrian Klaver wrote:

On 8/16/19 3:18 PM, Susan Hurst wrote:

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"

I going to say it has something to do with this:

https://www.postgresql.org/docs/9.5/release-9-5-12.html

"Avoid use of insecure search_path settings in pg_dump and other
client programs (Noah Misch, Tom Lane)"

Are you using the 9.5.0 or 9.5.14 version of pg_dump to dump from the
production server?

--
Adrian Klaver
adrian.klaver@aklaver.com

Susan Hurst <susan.hurst@brookhurstdata.com> writes:

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();

This log file is definitely where you should be looking for more
information. Is that the *only* message that appears while trying
to restore the dump file?

Is the pg_dump version that's being used to create the dump file 9.5.0,
or something later? (Use pg_dump --version if you're unsure --- but
I'm suspicious that it's 9.5.0, if it's from the source server
installation.)

Trawling the commit log, I see a number of post-9.5.0 bug fixes related to
pg_dump's handling of views that have index dependencies. I wonder whether
any of those are related to this. The symptoms don't seem to quite match,
but ...

CREATE VIEW subscribers appears on line 11,968 in the dump file

Is it possible that you've got more than one view named "subscribers",
in different schemas? If so this might not be the relevant entry.

regards, tom lane