ASH TPT Scripts For PostgreSQL

Tanel Poder has published his tpt scripts for Oracle database and helped numerous people all around the world. Yet, there was no similar tool to quickly analyze problem in PostgreSQL. Bertrand Drouvot has developed an active session history implementation like Oracle’ s to sample native PostgreSQL session activity, pgsentinel. In addition, he shared the sample queries to help us. With my repository I wanted to parameterize these ash queries with some other basic PostgreSQL queries, and of course, in order to help and give hints about the scripts I use Ferhat Sengonul’ s approach that he used in his own scripts.

If you are ready let’ s start!

For of all we need to setup a database connection and to do that we need to use setenv.sh script. The usage for getting help is :

./setenv.sh -h
Usage: source setenv.sh -d <db_name> -u <username> [-w <password>] [-h <host>] [-p <port>]

A basic connection example is:

source setenv.sh -d postgres -u postgres -w

Using -w will prompt another line to ask your database user’ s password and thus, it will create an PostgreSQL service file. In addition to this service file, the script set a few environment variables; PGSERVICE, PGSERVICEFILE, PAGER and LESS. PGSERVICE and PGSERVICEFILE variables will be used for connection. On the other hand, PAGER and LESS will be used for formatting the results and to have horizontal scroll. These settings comes from depesz’ s great content.

We are ready to use queries, to see average active session in the instance:

./aas_ash.sql

or if you want to list a average active session for last 15 minutes(default 10 minutes):

./aas_ash.sql "'15 minutes'"

Of course, to list the usage we can use -h option:

./aas_ash.sql -h

Usage Examples:
./aas_ash.sql
./aas_ash.sql "'$time_interval'"

If we want to list database wait event then we can use:

./aas_ash.sql
./aas_ash.sql "'$time_interval'"

The real advantage starts here! If we want to list different columns and use some other predicate then we can use ash.sql itself:

./ash.sql -h
Usage Examples:
./ash.sql
./ash.sql "$cols $predicate $date1 $date2
./ash.sql wait_event 1=1 "now()-interval '15 minutes'"  "now()"
./ash.sql wait_event "datname='pgbench'" "now()-interval '15 minutes'" "now()"

For example;

./ash.sql usename,wait_event "datname='postgres'" "now()-interval '15 minutes'" "now()"

Extra:

Oracle has hist_$ dictionary views to keep history of active session history. I created simple tables(just added one more column as snap_time) and scheduled two jobs to sample every 10 seconds. You can find the definition of tables and the commands to sample pgsentinel in hist_ash_table_ddl.sql file.

Repository: umuttechin/tpt

Please let me know if there is something wrong or needs to change!

Thanks!