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-critical Postgres database server. You can also monitor custom queries from your Postgres database tables and track business-critical KPIs with the Postgres custom database query plugin.
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 cancelled due to conflicts with data recovery occurring 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 Postgres database server monitoring plugin to track the availability and performance of Postgres servers.
- Install the Python module 'psycopg2' to fetch the metrics from the Postgres database.
sudo apt install libpq-dev python3-devsudo pip install psycopg2
- Ensure 'pg_read_all_stats' permission is provided to the user. For example, create a user 'site24x7' with password 'site24x7' and provide 'pg_read_all_stats' permission to the 'site24x7' user created.
- Our Linux server monitoring agent should be installed in the Postgres server to be monitored.
- While adding a plugin, the plugin name and its folder name should be identical.
Install the Postgres custom database query plugin and monitor SQL queries to track business-critical KPIs from your PostgreSQL database tables. The plugin executes a given query and displays the result as performance data. The column name will be displayed as the metric name and the corresponding value of the column will be displayed as the metric value.
- Download and install the Site24x7 server monitoring agent (Linux | Windows) in the server where you plan to run the plugin.
- Ensure you have the psycopg2 module for Python installed in your server.
If it is not installed, execute the following command on your server to install it:pip install psycopg2
- Download and install the latest version of the Site24x7 Linux agent in the server where you plan to run the plugin. If it is installed successfully, you will see a Linux server monitor in the Site24x7 Control Panel. This confirms that the agent is able to communicate with our data center.
- Download the postgres.py file from our GitHub repository.
- To change the existing configuration, edit the '#Config Section' in the 'postgres.py' file. Enter the username and password and grant read-only rights to this user.
CREATE USER username WITH PASSWORD 'yourpassword'
- To check if the plugin is working, execute the command below with appropriate arguments and check for a valid JSON output with applicable metrics and their corresponding value.
- Create a folder with the name 'postgres', under the Site24x7 Linux agent's plugin directory '/opt/site24x7/monagent/plugins/postgres' and place the 'postgres.py' file under '/opt/site24x7/monagent/plugins/postgres/'
The agent will automatically execute the plugin within five minutes and send performance data to the Site24x7 data center.
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")
- Create a folder named postgres_custom_query.
- Download the postgres_custom_query.py and the postgres_custom_query.cfg files from our GitHub repository and place them in the postgres_custom_query folder.
wget https://raw.githubusercontent.com/site24x7/plugins/master/postgres_custom_query/postgres_custom_query.pywget https://raw.githubusercontent.com/site24x7/plugins/master/postgres_custom_query/postgres_custom_query.cfg
- To check if the plugin is working, execute the command below with appropriate arguments and check for a valid JSON output with applicable metrics and their corresponding values.
python3 postgres_custom_query.py --db_name <db_name> --port <port no> --username <username> --password <password> --query <query>
- Add the applicable configurations in the postgres_custom_query.cfg file.[custom_metric_1]db_name='postgres'username=Nonepassword=Nonehostname='localhost'port=5432query="SELECT buffers_checkpoint, buffers_backend, maxwritten_clean, checkpoints_req, checkpoints_timed, buffers_alloc FROM pg_stat_bgwriter;"
- Follow the steps in this article to learn how to run the Python script on a Windows server. You don't need to do this for Linux.
- Move the postgres_custom_query folder to the Site24x7 server monitoring plugin directory.
For Linux: /opt/site24x7/monagent/plugins/
For Windows: C:\Program Files (x86)\Site24x7\WinAgent\monitoring\plugins\The agent will automatically execute the plugin within five minutes and display performance data in Site24x7.To view the plugin monitor and associated performance charts:
- Log in to Site24x7.
- Navigate to Plugins and click the required monitor.
- Add the applicable configurations in the postgres_custom_query.cfg file.
Feel free to contribute to our existing plugin and come up with suggestions or feedback on our Community.