Postgres Monitoring

Monitor Postgres servers and gain in-depth visibility into critical performance metrics.

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.

Performance Metrics

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).

DB commits

Total number of transactions that have been committed across all databases.

Back-end buffers

Total number of buffers written directly by a back end.

Checkpoint buffers

Total number of buffers written during checkpoints.

Checkpoints timed

Total number of scheduled checkpoints that have been performed.

Prerequisites

  • Install the Python module 'psycopg2' to fetch the metrics from the Postgres database.
    sudo apt install libpq-dev python3-dev
    sudo 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.

Plugin Installation

  • 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.
    wget https://raw.githubusercontent.com/site24x7/plugins/master/postgres/postgres.py
  • 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.

Tip

Manually execute the plugin script using the following command and verify its output:

python postgres.py

View Data in the Site24x7 Web Client

  1. Log in to Site24x7 and go to Server > Plugins > Name of Plugin Monitor.
  2. 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")

Plugin Contribution

Feel free to contribute to our existing plugin and come up with suggestions or feedback on our Community.

Other Integrations

Troubleshooting Tips