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 .
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.
./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 .
