监控 Postgres 表活动
我需要监控我的 postgres 服务器。如果给定时间后某些表没有变化，我需要收到警报。我一直试图让 xymon 和 nagios 做到这一点，但我一直没能做到。请帮忙
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
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（4）
您可能想查看 pg_stat_user_tables 并注意表的行插入/删除/更新统计信息是否已更改。这是检查监控软件中此类活动的最简单方法。
您还可以通过查看源代码以及最好的 PostgreSQL 监控插件 Nagios 来获得这方面的想法： check_postgres
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
首先，在表上创建一个触发器，该触发器在任何修改语句（INSERT/UPDATE/DELETE）。该触发器应该更新某处的“最后更改”时间戳（例如其他控制表中的字段）。
然后，您需要一个单独的进程，该进程通过某种外部方式定期启动（例如 Unix 上的 cron）。该过程例如每 10 分钟或每小时运行一次——无论您需要什么粒度。它只是检查上次更改的时间戳，以确定自上次检查以来的时间段内是否有任何活动。
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.
这不是一个免费的解决方案，而是 LogicMonitor 的 postgres 监控< /a> 可以轻松完成此操作。
It's not a free solution, but LogicMonitor's postgres monitoring can do this trivially.
如果你有办法在文件一段时间内没有更改时收到警报，那么我有一个不太优雅但可能更简单的解决方案：尝试找出 Postgres 存储有问题的表的文件名（有人应该深入系统Postgres 中的表 - 可能会在一个单独的问题中提出这个问题），然后设置监视工具来监视该文件的修改时间。
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.