Recently I discovered monit for FreeBSD, a monitoring system that is highly configurable and can be used to monitor various system happenings including service checks, disk space usage and process health. I installed it on all of the systems we have that require such monitoring. It has been very helpful in letting us know when things go offline.
The one issue I have noticed with it has to do with monitoring PostgreSQL. The PostgreSQL part of our config is setup like so:
# POSTGRESQL
check host PostgreSQL with address 127.0.0.1
if failed ping then alert
if failed port 5432 protocol pgsql then alert
So when it can’t connect to PostgreSQL we will receive an email message about it. Great! However if you look in the /var/log/messages you will notice something like this every 30 seconds (since we have monit setup to check everything in 30 second intervals):
Nov 1 00:12:41 blackbox postgres[70271]: [2-1] FATAL: role "root" does not exist
To fix this and start cleaning up our log its pretty straight forward. Create that role:
psql -U pgsql template1 create role root login nocreaterole nocreatedb nosuperuser noinherit;
After this we now start seeing this error in the log:
Nov 9 12:47:50 blackbox postgres[94875]: [2-1] FATAL: database "root" does not exist
To fix this we simple create that database:
create database root;
I also added this to the pg_hba.conf:
# TYPE DATABASE USER ADDRESS METHOD ... host root root 127.0.0.1/32 trust ...
That fixed our issues and now we no longer see those /var/log/messages. Monit wants to always connect using the “root” user and there’s no way to configure it to use a different user. So I came up with this as the workaround.
My-IT.log by Steve Dickinson 