Come join our live training webinar every other Wednesday at 11am PST and hear LogicMonitor experts explain best practices and answer common questions. We understand these are uncertain times, and we are here to help!
LogicMonitor will connect to your PostgreSQL database in order to accurately assess its performance, and pull statistical information.
LogicMonitor will initially assume that any device with a server on port 5432 is running PostgreSQL. Until you give LogicMonitor permission to connect to the databases it finds, it will raise a warning for that device.
If your collector is not running on the PostgreSQL server, you need to ensure PostgreSQL is set to:
listen_addresses = "*"
host all logicmonitor 10.1.1.206/32 md5
This would allow connections to all databases from the host 10.1.1.206, for user logicmonitor, if authenticated by an md5 encrypted password. Ensure to the put the IP address of your collector host in place of 10.1.1.206.
It is recommended that you create a specific user account for LogicMonitor to use to connect to the database.
Perform these operations as the super user on your PostgreSQL database:
create user logicmonitor with password ‘password’;
This will be enough to enable most monitoring. However, there are two exceptions:
alter role logicmonitor superuser;
Without the SuperUser privilege, all servers will appear busy, and maximum query time will always be 0. (Note: depending on the sensitivity of the data in the database, it may not be appropriate to grant superuser privilege to the logicmonitor user: for example, if the database contains personal health information.)
Define these properties so that they apply to the device within LogicMonitor:
By default, PostgreSQL reports very little statistical information. To fully realize the power of LogicMonitor PostgreSQL monitoring, you should enable statistics to be collected. (This does impose a small amount of overhead in the database.)
For PostgreSQL 8.2 and prior, ensure these settings are in postgresql.conf:
stats_start_collector = on
stats_command_string = on
stats_block_level = on
stats_row_level = on
For PostgreSQL 8.3 and later, enable statistics collection by these settings in postgresql.conf:
track_counts = on
track_activities = on
If you have a Warm Standby server, you should set up replication monitoring. There are various kinds of HA for PostgreSQL – however, in this section it is assumed that PostgreSQL is set up for Warm Standby Using Point-In-Time Recovery (PITR), using pg_standby. LogicMonitor can monitor this form of database replication, but there are some complications:
Given these constraints, the recommended setup for monitoring a Warm Standby Server is to perform these actions of the standby server:
* * * * * /usr/local/pgsql/bin/pg_controldata /usr/local/pgsql/data > \
(Note that paths may need adjusting for the location of the pg_controldata binary, and the path to the data directory. Ensure that the output file is readable by the user of the snmpd daemon.)
extend postgres-replication-lag /bin/bash -c \
'/bin/echo $(( $(date -u +%s) - $(date -u -d"$(cat /usr/local/pgsql/metadata | \
grep "Time of latest checkpoint" | sed "s/.*checkpoint: *//")" +%s) ))'
extend postgres-replication-state /bin/bash -c \
'/bin/echo $( cat /usr/local/pgsql/metadata | grep "cluster state" ) '
Finally, we must associate the PostgreSQL replication monitor in LogicMonitor with the relevant host(s). To do so, add the tag PostgresReplica to the System.categories property of the device.
It is recommended that you change the query that is timed in the PostgreSQL Query execution time datasource to one that is representative of your application’s performance. To do so, locate the ‘Postgres Query execution time’ datasource from Settings | LogicModules | Datasources OR select edit global definition from the datasource on the Devices page
Change the Query field on the form that is displayed to a query that will represent your application performance, and click submit.
In This Article