Archive for category Monitoring PostgreSQL

pg_top

pg_top is an utility just like the UNIX top command but it has much more to offer and I find it very useful to monitor a PostgreSQL database.

It is tedious to run a top and then grep the user postgres and look for the commands running. Then also have another session of pg_stat_activity to figure out what the queries are doing. Instead it is easier to just run pg_top and get all the below with just one command

View currently running SQL statement of a process.

View query plan of a currently running SQL statement.

View locks held by a process.

View user table statistics.

View user index statistics.

Lets first install the tool:

--> wget http://pgfoundry.org/frs/download.php/1780/pg_top-3.6.2.tar.bz2

--> tar -xjvf pg_top-3.6.2.tar.bz2

--> cd pg_top-3.6.2

--> ./configure --prefix='path to where you want it installed'

--> make

--> make install

If you have environment variables configured for the postgres user it should pick up the pgport and binaries

If not you can use pg_top as

pg_top -p 5432 -U postgres -d database -W

Where ‘-d’ specifies the database to connect to, ‘-U’ specifies the database user, and ‘-W’ will prompt for the password if a password is required to connect.

Use the ‘Q’ key and enter a PID once pg_top is started.

To see the Query Plan use the ‘E’ key and enter a PID once pg_top is started. The ‘E’ commands runs ‘EXPLAIN’ and ‘EXPLAIN ANALYZE’ can be used by using the ‘A’ key followed by a PID.

Locks Acquired

Use the ‘L’ key and enter a PID once pg_top is started.

Table Statistics

Use the ‘R’ key to display user table statistics. Use ‘t’ to toggle between displaying cumulative and differential statistics.

Index Statistics

Use the ‘X’ key to display user index statistics. Use ‘t’ to toggle between displaying cumulative and differential statistics.

Update for Postgres 9.2: I tried to use pg_top for postgres 9.2 and ran into an issue where pg_top does not work well with this version of postgres(mainly because some of the columns in pg_stat_activity have been changed eg: procpid changed to pid, current_query changed to query and state has been added). I took the latest version and still no luck so figured I search this on google and found a link worked for me. Here is what I did to make it work:

downloaded the latest source of pgtop http://pgfoundry.org/frs/download.php/1780/pg_top-3.6.2.tar.bz2

untar it and used the following patch before compiling it:

curl -L http://ln-s.net/+6lb|gzip -dc|patch -p1 -d pg_top-3.6.2

then use the nomal configure make and make install

Advertisements

,

13 Comments

Monitoring_PostgreSQL

Lets talk about the most widely used monitoring system for PostgreSQL. There are other tools available, some paid and do a really good job but I would like to use something that is free for the general public and reliable. Nagios!!
Nagios is an open source system of widely used network monitoring, which monitors the equipment (hardware) and services (software) that you specify, alerting them when their behavior is not desired.

Configuring the monitoring server:

Requirements

Web Server (This manual assumes that you use Apache)

Php (On the web interface)
Gcc
Libgd (a graphics library is needed to display the statusmap)
Perl
Mail Server

Installation:

Create a user nagios

useradd-m nagios
passwd nagios

Install nagios

wget <a href="http://sourceforge.net/projects/nagios/files/nagios-3.x/nagios-3.4.1/nagios-3.4.1.tar.gz">http://sourceforge.net/projects/nagios/files/nagios-3.x/nagios-3.4.1/nagios-3.4.1.tar.gz</a>
tar -xzvf nagios-3.4.1.tar.gz
cd nagios-3.4.1
. / Configure - with-command-group = nagios
make all
make install
make install-init
make install-config
make install-commandmode

Configuration:

Edit the file / usr / local / nagios / etc / objects / contacts.cfg
This change is to indicate to email alerts notify each set.
Replace “nagios @ localhost” by the post of administrator

Configure the web interface:

make install-webconf
htpasswd-c / usr / local / nagios / etc / htpasswd.users nagiosadmin
nagios.nagios chown / usr / local / nagios / etc / htpasswd.users
chmod 664 / usr / local / nagios / etc / htpasswd.users
service httpd restart

Install Nagios Plugins:

wget <a href="http://sourceforge.net/projects/nagiosplug/files/nagiosplug/1.4.15/nagios-plugins-1.4.15.tar.gz/download">http://sourceforge.net/projects/nagiosplug/files/nagiosplug/1.4.15/nagios-plugins-1.4.15.tar.gz/download</a>
tar -xzvf nagios-plugins-1.4.15.tar.gz
cd nagios-plugins-1.4.15
. / Configure - with-nagios-user = nagios - with-nagios-group = nagios - without-pgsql
make
make install

Start Nagios:

cd / etc / init.d
chkconfig - add nagios
chkconfig nagios on

To check that all is well
/ Usr / local / nagios / bin / nagios-v / usr / local / nagios / etc / nagios.cfg

Finally:

service nagios start

At this point it is possible to enter Nagios, opening the browser and giving as address: http://localhost/nagios
Enter nagiosadmin user and a key that when you set the web interface.
If I install on the same machine that will be monitored to installation of the plugin for PostgreSQL, otherwise continue with the next section.

Set the machine to be monitored:

Requirements

PostgreSQL (The instructions in this manual were tested using version 8.3.7)
Xinetd
Gcc
Perl

Create a user Nagios
useradd-m nagios
passwd nagios

<strong>Install Nagios Plugins:</strong>
The PostgreSQL plugin then install it manually to make sure to use the latest version.

wget <a href="http://sourceforge.net/projects/nagiosplug/files/nagiosplug/1.4.15/nagios-plugins-1.4.15.tar.gz/download">http://sourceforge.net/projects/nagiosplug/files/nagiosplug/1.4.15/nagios-plugins-1.4.15.tar.gz/download</a>
tar -xzvf nagios-plugins-1.4.15.tar.gz
cd nagios-plugins-1.4.15
. / Configure - with-nagios-user = nagios - with-nagios-group = nagios - without-pgsql
make
make install

nagios.nagios chown-R / usr / local / nagios

Install the module Nagios nrpe:

wget <a href="http://sourceforge.net/projects/nagios/files/nrpe-2.x/nrpe-2.13/nrpe-2.13.tar.gz">http://sourceforge.net/projects/nagios/files/nrpe-2.x/nrpe-2.13/nrpe-2.13.tar.gz</a>
tar -xzvf nrpe-2.13.tar.gz
cd nrpe-2.13
. / Configure
make all
make install-plugin
make install-daemon
make install-daemon-config
make install-xinetd

Configure the module nrpe:

Edit the file / etc / xinet.d / nrpe
modify the value of field only_from "by the server ip to be monitored
Edit the file / etc / services
add the following line "nrpe 5666/tcp # NRPE"
Configuring the server monitoring services to check on the remote machine

Test the module nrpe:

/ Usr / local / nagios / libexec / server check_nrpe-H
Edit the file / usr / local / nagios / etc / objects / commands.cfg

Add the following lines at end of file
define command (
command_name check_nrpe
command_line $ USER1 $ / HOSTADDRESS check_nrpe-H $ ARG1 $-c $ $
)
We must tell that we will monitor Nagios another machine by editing the file / usr / local / nagios / etc / nagios.cfg Add the following line in the section "OBJECT CONFIGURATION FILE (S)":

cfg_file = / usr / local / nagios / etc / objects / dbhost.cfg

Creating the definition of a file host dbhost.cfg

define host (
use linux-server
dbserver host_name
pgsql alias 8.3
IP.del.servidor.PostgreSQL address
)

service nagios restart

Install PostgreSQL plugin to monitor (on the server to be monitored)

Download the file from check_postgres.tar.gz http://bucardo.org/check_postgres
check_postgres.tar.gz gunzip-dc | tar xvf -
cd check_postgres
check_postgres.pl cp / usr / local / nagios / libexec /.
perl / usr / local / nagios / libexec / check_postgres.pl-symlinks
To install checks must create an entry for each service that we will monitor the file / usr / local / nagios / etc / nrpe.cfg similar to (must be added in the section "COMMAND DEFINITIONS"):

command [check_postgres_locks] = / usr / local / nagios / libexec / check_postgres_locks-w 2-c 3
Where the command can be taken http://bucardo.org/check_postgres/check_postgres.pl.html

Then you should specify the services to monitor, creating entries like the following in / usr / local / nagios / etc / objects / dbhost.cfg in the monitoring server:

define service (
use generic-service
dbserver host_name
PGSQL service_description locks
check_command check_nrpe! check_postgres_locks
)
define service (
use generic-service
dbserver host_name
service_description CPU Load
check_command check_nrpe! check_load
)
define service (
use generic-service
dbserver host_name
Current users service_description
check_command check_nrpe! check_users
)
define service (
use generic-service
dbserver host_name
Total service_description Processes
check_command check_nrpe! check_total_procs
)
define service (
use generic-service
dbserver host_name
service_description Zombie Processes
check_command check_nrpe! check_zombie_procs
)

, ,

Leave a comment

%d bloggers like this: