Database Technologies

PostgreSQL has pg_stat_activity view which can be use to get the session details. This view gives following information: 1. datid: database OID 2. datname: database name 3. procpid: process ID 4. usesysid: user OID 5. usename: user name 6. application_name: application name 7. client_addr: client's address 8. client_hostname: host name (if available) 9. client_port: Clients port number 10. backend_start: time at which the server process started 11. xact_start: time at which current transaction started 12: query_start: time at which current query began execution started 13: waiting: process's waiting status 14. current_query: text of the current query. Above is good for monitoring sessions in postgresql. However, suppose user wants to know about percentage of CPU & Memory used by a particular session/user, then he can get this information by using plperlu function. To Create the pleperlu function, user needs to have plperlu language installed in DB. For creating the p

Generally DBA, has to login in system to monitor the activities by querying the pg_stat_activity veiw. However if someone wants to do continous monitor for every 2 seconds. He has to run SQL against pg_stat_activity every 2 seconds. So, For such activity, I have made following shell script which can be use with watch command on linux to monitor the activities. #!/bin/bash ## Prepared by Vibhor Kumar (EDB). Q="select procpid,now() - query_start as long, waiting, current_query from pg_stat_activity where procpid <> pg_backend_pid() limit 10;" while getopts "b:U:d:p:" opt; do case $opt in b) BIN=$OPTARG;; U) PGUSER=$OPTARG;; d) DB=$OPTARG;; p) port1=$OPTARG;; *) echo "Usage: $0 -b -d -U -mp -sp ";; esac done if [ -z $BIN ];then echo "Usage: $0 -b bin directory -d database name -U user -p port" exit fi if [ -z $PGUSER ];then echo "Usage: $0 -b bin directory -d database name -U user -p por