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.
Use the ‘L’ key and enter a PID once pg_top is started.
Use the ‘R’ key to display user table statistics. Use ‘t’ to toggle between displaying cumulative and differential 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