PostgreSQL AWR How to install and config extension pg_profile

Download Files:

1. Download and Install pg_profile from github

-- https://github.com/zubkov-andrei/pg_profile/tree/master
# tar xzf pg_profile--4.3.tar.gz --directory $(pg_config --sharedir)/extension/

2. Download and Install pg_stat_kcache library (options)

-- Google find pg_stat_kcache_xx.rpm
# rpm -ivh pg_stat_kcache_14-2.2.2-1PGDG.rhel8.x86_64.rpm

3. Download and Install pg_wait_sampling library (options)

-- Google find pg_wait_sampling rpm
# rpm -ivh pg_wait_sampling_14-1.1.4-1.rhel8.x86_64.rpm 

Setting:

1. Setting shared_preload_libraries and pg_profile parameter in postgresql.conf

# vim $PGDATA/postgresql.conf

--Add pg_profile parameter  pg_stat_statements library
shared_preload_libraries = 'pg_stat_statements,pg_stat_kcache,pg_wait_sampling'

# pg_profile
track_activities = on
track_counts = on
track_io_timing = on
track_wal_io_timing = on      # Since Postgres 14
track_functions = all

2. Restart PostgreSQL Instance

$ pg_ctl restart -D $PGDATA

or

# systemctl restart postgresql-14.service

3. Enable pg_profile and pg_stat_statements extension on all database and enable pg_profile in postgres

--Create script to enable extension
# vi enable_pg_extension.sh

for DB in $(psql -t -c "SELECT datname FROM pg_database WHERE datname NOT IN ('template0', 'template1')"); do
  echo "Database:" $DB
  psql -d $DB -t -c "CREATE EXTENSION IF NOT EXISTS pg_stat_statements"
  psql -d $DB -t -c "CREATE EXTENSION IF NOT EXISTS pg_stat_kcache"
  psql -d $DB -t -c "CREATE EXTENSION IF NOT EXISTS pg_wait_sampling"
done

psql -d postgres -t -c "CREATE EXTENSION IF NOT EXISTS dblink"
psql -d postgres -t -c "CREATE SCHEMA IF NOT EXISTS profile"
psql -d postgres -t -c "CREATE EXTENSION IF NOT EXISTS pg_profile SCHEMA profile"

--Run script to enable extension
# sh enable_pg_extension.sh

4. Set up a cron to perform pg_profile samples with the desired frequency

call procedure

-- Setting crontab direct to call procedure
$ crontab -e

*/60 * * * * psql -U postgres -d postgres -c 'SELECT profile.take_sample()' > /dev/null 2>&1

call shell

-- Setting crontab to call shell
$ vim pg_profile_take_sample.sh

#!/bin/sh

standby_flg=`psql -U postgres -h localhost -At -c "SELECT pg_is_in_recovery();"`
if [ ${standby_flg} == 'f' ]; then
  psql -U postgres -d postgres -c 'SELECT profile.take_sample()'
fi

$ crontab -e
*/60 * * * * sh pg_profile_take_sample.sh > /dev/null 2>&1

5. To produce a report, simply call the get_report function, providing the samples or times bounding the required interval

-- Show sample list
$ psql -Aqtc "SELECT profile.show_samples()"
$ psql -Aqtc "SELECT profile.get_report(480,482)" -o /tmp/report_480_482.html