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

,

  1. #1 by 1099 pay stub generator download on January 29, 2013 - 2:28 pm

    Heya i am for the first time here. I found this board and I find It really useful & it helped
    me out much. I hope to give something back and aid others like you helped
    me.

    • #2 by Vikram Rai on January 29, 2013 - 8:19 pm

      Sounds good. glad to know it helped you. If you would like to contribute to the knowledge base. please do send in a post and would be happy to include it in here.

  2. #3 by Suhas on February 1, 2013 - 9:33 am

    Really useful blog , but we need to configure along with pg_config . Else configure will give error .
    ./configure –with-postgresql=/usr/local/postgresql-9.2.2/

    • #4 by Vikram Rai on February 1, 2013 - 7:04 pm

      Very true Suhas. I normally use postgres source install and have pg_config in there to manage all the contrib modules/add ons. Thanks for pointing that out.

      Cheers!!!

  3. #5 by Justin Liu on July 31, 2013 - 2:07 pm

    I followed the steps here and still got this error when compiling the pg_top.
    :”commands.o: In function `show_current_query’:
    /opt/pg_top-3.6.2/commands.c:536: undefined reference to `PQexec’
    /opt/pg_top-3.6.2/commands.c:537: undefined reference to `PQntuples’
    /opt/pg_top-3.6.2/commands.c:545: undefined reference to `PQgetvalue’
    /opt/pg_top-3.6.2/commands.c:551: undefined reference to `PQclear’
    /opt/pg_top-3.6.2/commands.c:552: undefined reference to `PQfinish’
    …..

    • #6 by Vikram Rai on August 1, 2013 - 1:17 am

      Could you give me some more information on this. What version of postgresql have you installed and is it a source install or from rpm? If you are using pg_top with postgres 9.2 did you try to apply the patch in the post? Also are all postgres env variables set correctly?

      • #7 by Justin Liu on August 1, 2013 - 1:54 pm

        1. I installed postgresql 9.2.2 from the source on ubuntu 12.04 (precise)
        2. I downloaded “pg_top-3.6.2.tar.bz2” and then patched with “curl -L http://ln-s.net/+6lb|gzip -dc|patch -p1 -d pg_top-3.6.2″
        3. Then I ran “/opt/pg+top-3.6.2/configure -with-postgresql=/usr/local/pgsql/”. The “configure” ran successfully .
        4. I was getting this error when I tried to run “make” command.

      • #8 by Vikram Rai on August 1, 2013 - 5:35 pm

        By the looks of the error that you had sent earlier it seems like PostgreSQL libraries are not being linked. Have you set the LD_LIBRARY_PATH and PATH variables correctly in the environment variables of the postgres user? Try to set the path correctly and I am pretty sure it should work out fine.

      • #9 by Justin Liu on August 1, 2013 - 6:40 pm

        “make” command failed on this “gcc -Wall -g -L/usr/local/pgsql/lib -lpq -o pg_top color.o commands.o display.o getopt.o screen.o sprompt.o pg.o pg_top.o username.o utils.o version.o m_linux.o -ldl -lm -ltermcap “. But, If I move the order of lib “/usr/local/pgsql/lib” to the end like this “gcc -I/usr/local/pgsql/include -Wall -g -o pg_top color.o commands.o display.o getopt.o screen.o sprompt.o pg.o pg_top.o username.o utils.o version.o m_linux.o -ldl -lm -ltermcap -L/usr/local/pgsql/lib -lpq ” and it will get compile fine.

  4. #10 by Luis G. Moré on December 13, 2013 - 3:31 pm

    I also found this tutorial very useful. I also ran into problems when I tried to compile pg_top; they were related to the gcc compiler itself. But when I installed the dependencies I found out it was needed, pg_top worked like a charm!! ¡Congratulations!

  5. #12 by Harold Y. Prather on December 21, 2013 - 8:43 am

    Very rapidly this site will be famous amid all blogging and site-building viewers,
    due to it’s nice articles or reviews

    • #13 by Vikram Rai on December 31, 2013 - 5:53 pm

      Thank you for the encouragement!! Happy new year. Hope the blog is helpful to all the postgres users. Will be adding more information to the blog next year.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: