hi all
i need to monitor my postgres server. i need to get an alarm if there is no change in certain tables after a given time. i've been trying to get xymon and nagios to do this and i have not been able to. please help
+3
A:
First, create a trigger on the table that activates on any modification statement (INSERT/UPDATE/DELETE). This trigger should update a "last-changed" timestamp somewhere (e.g. a field in some other control table).
Then, you'll need a separate process that is started regularly by some external means (e.g. cron on Unix). This process is run e.g. every 10 minutes, or every hour -- whatever granularity you need. It simply checks the last-changed timestamp to determine whether there has been any activity in the period since the last check.
j_random_hacker
2010-01-13 09:45:00
+1
A:
It's not a free solution, but LogicMonitor's postgres monitoring can do this trivially.
Steve Francis
2010-01-13 18:06:48
A:
If you have a means to get an alert when a file does not change in some time, then I have a less elegant, but probably simpler solution: try to find out the filename where Postgres stores the table in question (someone should dig into system tables in Postgres - maybe ask this in a separate question) and then have your monitoring tool set up to watch the modify time of that file.
Bandi-T
2010-01-18 22:30:03
This won't work for two reasons. The file will be modified by system processes such as VACUUM, and a process called hint bit updates--both of which write to the table and update the modification time without any actual data changes.You can easily find out what directory corresponds to a table by looking at things in the system catalogs like pg_class and pg_databsae. There's an underdocumented field called an oid that you'll find matching the directory and file names. But the actual files in there are in 1GB chunks, so as your database grows the file name(s) used will change under you.
Greg Smith
2010-01-20 07:22:40
Yes, all of the above is true, there lies the inelegancy. So don't do this if you can do any of the solutions described in other answers.
Bandi-T
2010-01-20 14:59:05
A:
You probably want to look at pg_stat_user_tables and note whether the statistics for row insertion/deletion/updates have changed for the table. That's the easiest way to check for this sort of activity in monitoring software.
You might also get ideas in this area from looking at the source code to the best of the PostgreSQL monitoring plug-in, the Nagios one: check_postgres
Greg Smith
2010-01-20 07:17:19