pg_top | PostgreSQL

pg_top is 'top' for PostgreSQL. It is derived from Unix Top. Similar to top,
pg_top allows you to monitor PostgreSQL processes. It also allows you to:

    * View currently running SQL statement of a process.
    * View query plan of a currently running SELECT statement.
    * View locks held by a process.
    * View user table statistics.
    * View user index statistics.

Download pg_top from below link .

https://github.com/markwkm/pg_top/archive/master.zip

In this blog i am building pg_top from source code .

Below are the steps how to install and configure it .

Note : - I am assuming that you have already up and running PostgreSQL server 

Here Installation begins :- 

unzip master.zip cd pg_top-master ##Execute autogen.sh sh autogen.sh ##Make sure you have properly set environment variable if not then execute below export statement else ignore it. export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:/usr/local/Postgresql9.6/lib/ export PATH=/usr/local/Postgresql9.6/bin/:$PATH ##Build the source code and install it. ##If you are not using --prefix then default path of installed binary is /usr/local/bin ./configure --prefix=/usr/local/Postgresql9.6/ make make install

Here Configuration begins : -

Below is various checks of pg_top

[postgres@tushar-test bin]$ ./pg_top --help pg_top monitors a PostgreSQL database cluster. Usage:   pg_top [OPTION]... [NUMBER] Options:   -b, --batch               use batch mode   -c, --show-command        display command name of each process   -C, --color-mode          turn off color mode   -i, --interactive         use interactive mode   -I, --hide-idle           hide idle processes   -n, --non-interactive     use non-interactive mode   -o, --order-field=FIELD   select sort order   -q, --quick-mode          modify schedule priority                             usable only by root   -r, --remote-mode         activate remote mode   -s, --set-delay=SECOND    set delay between screen updates   -T, --show-tags           show color tags   -u, --show-uid            show UID instead of username   -V, --version             output version information, then exit   -x, --set-display=COUNT   set maximum number of displays                             exit once this number is reached   -z, --show-username=NAME  display only processes owned by given                             username   -?, --help                show this help, then exit Connection options:   -d, --dbname=DBNAME       database to connect to   -h, --host=HOSTNAME       database server host or socket directory   -p, --port=PORT           database server port   -U, --username=USERNAME   user name to connect as   -W, --password            force password prompt

• How to make a DB connection with pg_top

Below are some of the run-time switches .

A       - EXPLAIN ANALYZE (UPDATE/DELETE safe)\n\

X       - show user index statistics\n\

L       - show locks held by a process\n\

R       - show user table statistics\n\

S       - show pg_stat_statements statistics\n\

Q       - show current query of a process\n\

t        - Toggle between cumulative or differential statistics when viewing\n\

More run time switches are available on below link .

https://github.com/markwkm/pg_top/blob/master/help.h