Postgres monitoring in DPA

Question

Hi all,

we have activated with DPA ver. 2020.2 the monitoring of some Postgres instances, anyway we've noticed in Postgres daily logs that there is a query of DPA returning syntax error:

STATEMENT:  SELECT sysdate FROM dual
ERROR:  relation "dual" does not exist at character 21

That is because there is no DUAL in Postgres, seems an Oracle query this one, in effect in Postgres should be used one of these:

select CURRENT_TIME
select CURRENT_DATE
select CURRENT_TIMESTAMP

I think we hit a bug of DPA, any ideas how to solve it?

Thanks

Regards

Andrea Gnemmi

Senior DBA

Logispin Austria GmbH

jaminsql · Answer

We had a few reports on this that made their way to us in support cases also.

That the query is using Oracle syntax, this is because there is an edition of PostgreSQL (EnterpriseDB Advanced Server) that allows to be installed with Oracle syntax support for easier migration from Oracle database. DPA is doing this check to know whether the syntax is supported or not on the instance, which results in an error logged in PostgreSQL if it's not supported.

This will be fixed in a future release of DPA by restricting it to be done only on monitor startup and only in case of EDB Advanced Server edition. It'd still fail if EDB Advanced Server is installed without the support.

For the time being, the frequency of the checking can be decreased in following file (DPA restart required to apply the changes)

<DPA_INSTALLATION>\iwc	omcat\webapps\iwc\WEB-INF\classes\properties\idc-monitor-db-postgresql.properties

by setting following properties to higher values (in milliseconds)

com.confio.idc.monitor.postgresql.settingsPoll.startDelay
com.confio.idc.monitor.postgresql.settingsPoll.repeatInterval

Those properties controls all the checks done regularly by DPA regarding the PostgreSQL configuration, so if the interval is longer and some configuration changes (e.g. the amount SQL text available in pg_stat_activity or pg_stat_statements enabled/disabled state), DPA might not identify the change soon.If changes are not done often in DPA settings it should be safe to increase the intervals. Checks for parameter changes will still be done on monitor startup.

This will change to 24 hours delay and 24 hours frequency of the configuration job (value is in milliseconds and can be adjusted).

com.confio.idc.monitor.postgresql.settingsPoll.startDelay=86400000

com.confio.idc.monitor.postgresql.settingsPoll.repeatInterval=86400000