HomePostgreSQL Monitoring
PostgreSQL Monitoring

PostgreSQL is a powerful, open source object-relational database system. It can handle workloads ranging from small single-machine applications to large Internet-facing applications (or for data warehousing) with many concurrent users. Nodelizer helps you to monitor following metrics about PostgreSQL installed on you server.

Metrics collected by PostgreSQL Integration
Metric
– Description
Max Connections
– The maximum number of client connections allowed to this database.
Database Size
– Disk space used by this database
Tables Count
– The number of user tables in this database.
Connections
– Number of backends currently connected to this database.
Percent Usage Connections
– The number of connections to this database as a fraction of the maximum number of allowed connections.
Transactions Commit
– Number of transactions in this database that have been committed per second
Transactions Rollback
– Number of transactions in this database that have been rolled back per second.
Disk Read
– Number of disk blocks read in this database, per second.
Buffer Hit
– Number of times disk blocks were found already in the buffer cache, so that a read was not necessary, per second.
Tuples Returned
– Number of rows returned by queries in this database per second
Tuples Fetched
– Number of rows fetched by queries in this database per second.
Tuples Inserted
– Number of rows inserted by queries in this database per second.
Tuples Updated
– Number of rows updated by queries in this database per second
Tuples Deleted
– Number of rows deleted by queries in this database per second
Checkpoints Timed
– Number of scheduled checkpoints that have been performed
Checkpoints Requested
– Number of requested checkpoints that have been performed.
Buffers Checkpoint
– Number of buffers written during checkpoints
Buffers Clean
– Number of buffers written by the background writer.
Max Written Clean
– Number of times the background writer stopped a cleaning scan because it had written too many buffers.
Buffers Backend
– Number of buffers written directly by a backend
Buffers Backend Fsync
– Number of times a backend had to execute its own fsync call
Buffers Allocated
– Number of buffers allocated
Locks
– The number of locks active for this database.
Configuring

rptmonitor uses psycopg2 module to interact with PostgreSQL. So this module should be installed for monitoring PostgreSQL. The psycopg2 package is the current mature implementation.

  1. Installation Options :

    • Install from a package :

      • Linux

        • On debian based distributions :

          sudo apt-get install python-psycopg2
        • On redhat based distributions :

          sudo yum install python-psycopg2
      • Mac OS X

        • Available as a fink package :

          fink install psycopg2-py27
        • Available on MacPorts :

          sudo port install py27-psycopg2
    • Use a Python package manager (easy_install, pip)

      pip install psycopg2

      Note : Installing pip on CentOS / RHEL / Ubuntu / Debian

    • Use the source package

      • download a copy of Psycopg source files from the Psycopg download page. Once unpackaged, to compile and install the package you can run :

        python setup.py build

        sudo python setup.py install

  2. Connect to psql and create at least a read-only monitoring user with proper access to your PostgreSQL Server :

    CREATE USER repute WITH PASSWORD '';

    GRANT SELECT ON pg_stat_database TO repute;

  3. Edit the agent’s config.cfg file :

    • uncomment (remove # symbol) & Fill in values for variables –

      • postgresql_server
      • postgresql_dbnm
      • postgresql_usrnm
      • postgresql_pswd
    • You can also customize port if not running the default, through postgresql_port config variable.
  4. Restart rptmonitor :

    • For (deb/rpm)package supported Linux distros :

      sudo /etc/init.d/rptmonitor restart
    • For openSUSE(11.4+) with systemd :

      sudo systemctl restart rptmonitor.service
    • For other Linux distro, FreeBSD8+, Mac OS X+ :

      sudo python /usr/local/bin/rptmonitor/monitor.py restart