Обсуждение: Missing Trigger after pgdump install
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 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 -- ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Susan E Hurst Principal Consultant Brookhurst Data LLC Email: susan.hurst@brookhurstdata.com Mobile: 314-486-3261
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 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
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 2019-08-16 16:24, Adrian Klaver wrote:
> 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
>>
>>
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 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?
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