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.
Configure the PostgreSQL plugin and monitor key performance metrics to get in-depth visibility into the performance, availability, and usage stats of Postgres servers.
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 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.
- 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'
- 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.
Manually execute the plugin script using the following command and verify its output:
View Data in the Site24x7 Web Client
- Log in to Site24x7 and go to Server > Plugins > Name of Plugin Monitor.
- You will be able to view the performance charts on the various metrics for your PostgreSQL server.
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")
Feel free to contribute to our existing plugin and come up with suggestions or feedback on our Community.