PostgreSQL is an ORDBMS server whose primary function is to store data securely, and allows retrieval at the request of other software applications. Analyze and optimize your Postgres server by configuring our Postgres plugin and proactively monitor the availability and performance of business-crtical Postgres database server.
This document details how to configure the PostgreSQL plugin and the monitoring metrics for providing in-depth visibility into the performance, availability, and usage stats of Postgres servers.
Postgres performance monitoring metrics:
Take informed troubleshooting decisions by keeping track of critical Postgres metrics including:
Idle user count
The total number of users/back ends connected to the database that are currently idle.
Active user count
The total number of users/back ends connected to the database that are currently in active state. Along with the idle user count metric, this metric will allow us to get the whole picture about the different server users.
Database conflict count
The total number of queries canceled due to conflicts with data recovery ocurring across all databases in the Postgres server.
Database cache usage ratio
The usage rate of cache is calculated using the formula SUM (blks_hit) / SUM (blks_read).
Total number of transactions that have been committed across all databases.
Total number of buffers written directly by a back end.
Total number of buffers written during checkpoints.
Total number of scheduled checkpoints that have been performed.
- Install the Python module "psycopg2" to fetch the statistics from the Postgres database.
sudo apt install libpq-dev python3-devsudo pip install psycopg2
Postgres plugin installation:
- Create a directory with the name "postgres", under the Site24x7 Linux agent's plugin directory - /opt/site24x7/monagent/plugins/postgres
- Download the file "postgres.py" from our GitHub repository and place it under the "postgres" directory
- Commands to perform the above step:
cd /opt/site24x7/monagent/plugins/mkdir postgrescd postgreswget https://raw.githubusercontent.com/site24x7/plugins/master/postgres/postgres.py
Configure Postgres to support statistics:
- Create a username with password based authentication and grant super user rights to this user
CREATE USER username WITH PASSWORD 'yourpassword' SUPERUSER
- Make sure the Postgres database server is configured to allow password and MD5 authenticated connections.
Postgres plugin configuration:
- The default python path given in the plugin script is #!/usr/bin/python. If you wish to provide an alternate python path, replace the existing one preceded by the shebang character "#!".
- Change the value of global variables 'userName' , 'passWord' to the value configured in above steps
Eg : userName = "username" passWord = "yourPassword"
- Also set the appropriate values for variables "db" , "hostName" and "port"
- Save the changes and restart the agent.
Monitor additional metrics:
- To monitor additional metrics, edit the "postgres.py" file and add the new metrics that need monitoring
- Increment the plugin version value in the file "postgres.py" to view the newly added metrics ( For e.g., change the default plugin version from PLUGIN_VERSION = "1" to "PLUGIN_VERSION = "2")
- Redis plugin - Monitor performance metrics of your Redis databases
- VoltDB plugin - Monitor performance metrics of your VoltDB databases
- Lighttpd plugin - Analyze performance of your Lighttpd server
- Nagios plugin - Execute thousands of Nagios plugins in Site24x7 without the need of running a Nagios server
- Out-of-the-box plugins - Monitor your entire app stack with our extensive list of integrations
- Create custom plugins - Create custom Linux and Windows plugins and monitor custom attributes