PostgreSQL subagent

[pvo]

PostgreSQL is supported database but there was no subagent for PostgreSQL monitoring therefore I crated one.

The skeleton of the PostgreSQL subagent is similar to the Oracle subagent with some small changes.

I do not have Windows development environment and I didn't find any description what is needed therefore I've tested it on Debian only but I don't use any non-Windows specific features therefore I thing after adding of pgsql.vcxproj and pgsql.vcxproj.filters files it will be possible to compile the subagent on Windows and it will work.

Please add the agent and its documentation to the original repositories.

The changes are there as Pull requests.
Code: https://github.com/netxms/netxms/pull/38
Documentation: https://github.com/netxms/netxms-doc/pull/3

[Victor Kirhenshtein]

Subagent is merged into master branch. I've added Visual Studio project for Windows builds and did some minor refactoring and fixes. I also plan to backport it into 3.4 branch.

Best regards,
Victor

[pvo]

The documentation is not merged yet therefore I can correct the same typo in Table name there.  Should I do it or you will do it?

[Victor Kirhenshtein]

Yes, please do.

[Victor Kirhenshtein]

Hi,

current subagent version reports the following SQL error on each poll:

Code: [Select]

SELECT CASE WHEN pg_is_in_recovery() THEN 'YES' ELSE 'NO' END AS in_recovery,  CASE WHEN count(*) > 0 THEN 'YES' ELSE 'NO' END AS is_receiver FROM pg_catalog.pg_stat_wal_receiver
42P01 ERROR:  relation "pg_catalog.pg_stat_wal_receiver" does not exist

Is it possible to somehow detect if this view is present and remove it from polling if not?

Best regards,
Victor

[pvo]

It is strange. I've tested it with versions 12 and 9.6 both without WAL replication and I dint have this message.

Which version did you test it with? If >= 10 do you have the pg_monitor role assigned?

[Victor Kirhenshtein]

It is 9.5.13. I will retest it on 9.6. I'm not sure about support for 9.5 - formally it is supported until 2021, but I don't know how widely it is used.

Best regards,
Victor

[pvo]

The easiest way to solve it is to limit the remaining two REPLICATION SQL queries by minimal version MAKE_PGSQL_VERSION(9, 6, 0) .

[Victor Kirhenshtein]

Added it. Now I have a question regarding these lines:

"SELECT count(*) stanby FROM pg_catalog.pg_stat_replication

 { _T("PostgreSQL.Replication.Stanby(*)"), H_GlobalParameter, _T("REPLICATION/stanby"), DCI_DT_INT64, _T("PostgreSQL/Replication: WAL senders") },

shouldn't it be "standby"? Or even "PostgreSQL.Replication.WALSenders"?

I don't know meaning of this parameter, question is based purely on name and description difference

Best regards,
Victor

[pvo]

It is number of WAL senders which 1:1 corresponds with the number of Standby servers receiving WAL form this master server.
I don't know which name or description is better, but you are right that the parameter name and its description should be unified. This means either rename the parameter to "PostgreSQL.Replication.WALSenders" as you suggest or change the description of the parameter in the documentation to "Number of Standby servers".

[Victor Kirhenshtein]

Should it be "WAL receivers" or "WAL sending targets" then - as those servers receive data from current server, not send data to it?

[pvo]

The exact description of the pg_stat_replication table is: "One row per WAL sender process, showing statistics about replication to that sender's connected standby server."

The query counts the number of the rows in this table therefore it is number of senders (sender processes) on the monitored master server which corresponds with number of Standby servers on the other side receiving the data.

[Victor Kirhenshtein]

Now it makes sense I think PostgreSQL.Replication.WALSenders will be the best name then.

Best regards,
Victor

[pvo]

OK.

Technically is it what is monitored. From the human perspective is more logical to know how many Stanby servers are receiving the WALs therefore I've chosen the name PostgreSQL.Replication.Stanby. But it is name only the number is always the same.