Postgres monitoring.

Monitor Postgres servers using Site24x7 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.

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

DB rollbacks

Total number of transactions that have been rolled back across all databases.

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.

How it works?

  • Log-in to your Site24x7 account. Sign up here if you don't have one
  • Download and install the latest version of Site24x7 Linux agent
  • Install the Postgres plugin
  • The agent will execute the Postgres plugin and push the data to the Site24x7 server.

Prerequisites:

  • The plugin requires Python module "psycopg2" to fetch the statistics from the Postgres database.

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 postgres
    cd postgres
    wget 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:

  • Replace the shebang character "#!" in line 1 to the appropriate path for python's version where you have installed psycopg2 in your system
  • Eg : #!/usr/bin/python2
  • 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.
  • /etc/init.d/site24x7monagent restart

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

Related plugins:

  • 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
Trusted

World's leading companies

Client

Don't have a Site24x7 account? Sign up now!