PostgreSQL subagent

Started by pvo, July 07, 2020, 02:35:41 PM

Previous topic - Next topic

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


Victor Kirhenshtein

Hi,

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


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

#13
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.