📚 Install and Create and use pg_profile extension¶

🔍 Prepare：¶

## OS and Software environment：

Environment name Version

OS Rocky Linux 9.x

DB PostgreSQL 16.X

## Download below packages：

Package name Download address

pg_profile-4.3.tar.gz Click Download RPM Package

### 1. Pre-Installation(postgres)

1.1 Before enabling Statistics Collector parameter for postgresql.conf, you need pg_stat_statements extension (refer to pg_stat_statements enabled.docx)
1.2 Add below information to postgresql.conf

vi /pgdata/ZSRPAD/postgresql.conf
track_activities = on
track_counts = on
track_io_timing = on
track_functions = all

track_activities and track_counts= on(default) track_activities: Enables the collection of information on the currently executing command of each session track_counts: Enables collection of statistics on database activity track_io_timing: Enables timing of database I/O calls

### 2. Installation(postgres)

2.1 Upload the file to destination folder (/pgdata/source/pg_profile)

mkdir -m 707 -p /pgdata/source/pg_profile
cd /pgdata/source/pg_profile
ll

2.2 Unzip the file to specific folder(/usr/pgsql-16/share/extension)

tar -zxvf pg_profile--4.3.tar.gz -C /usr/pgsql-16/share/extension/
ll

2.3 create a schema for pg_profile and create extension on this schema

psql -p 5435

\c
CREATE EXTENSION dblink;
CREATE EXTENSION pg_stat_statements;
CREATE SCHEMA profile;
CREATE EXTENSION pg_profile SCHEMA profile;

ERROR: extension “XXXXXX” already exists mean you have created this extension before. You can ignore it

2.4 Create snapshot and generate report

su - postgres
psql -p 5435

SELECT profile.snapshot();

SELECT profile.show_samples();

### 2.5. Generate report by snapshot ID（postgress）

2.5.1 Checking how many snapshots

su - postgres
psql -p 5435

SELECT profile.show_samples();

2.5.2 choose what Snapshots you want to generate report (ex. ID:1~3)

su - postgres
psql -p 5435 -qtc "select profile.get_report(1,3)"  --output /tmp/awr_report_postgres_1_3.html

### 2.6. Set up cron job to generate snapshot every 30 mins（postgress）

crontab -l
#Automated snapshots every 30 mins  
*/30 * * * * /usr/pgsql-16/bin/psql -d postgres -p 5435 -c 'SELECT profile.snapshot()' > /tmp/pg_SID_awr.log 2>&1

### 2.7. Get the report from server. It looks like below（postgress）