Configure and monitor using pg_profile in PostgreSQL

November 9, 2021
()

Configure and monitor using pg_profile in PostgreSQL

This extension for PostgreSQL helps you to find out the most resource-consuming activities in your PostgreSQL databases. Extension is based on statistic views of PostgreSQL and contrib extension pg_stat_statements. A historic repository will be created in your database by this extension. This repository will hold statistic “samples” for your postgres clusters. A sample is taken by calling take_sample() function. PostgreSQL doesn’t have any job-like engine, so you’ll need to use cron. Periodic samples can help you find the most resource-intensive in the past.

Suppose, you have reported performance degradation several hours ago. Resolving such issues, you can build a report between two samples bounding the performance issue periods to see the load profile of your database. pg_profile, installed in one cluster can collect statistics from other clusters, called servers. You just need to define some servers, providing names and connection strings, and make sure connections can be established to all databases of all servers. 


Below are the high-level steps we will follow to setup pg_profile
1. Download pg_profile tar file in pg_profile database   
2. Creating extensions in pg_profile database
3. Update postgresql.conf in pg_profile database
4. Configure remote server to use pg_profile
5. Add remote server in pg_profile database
6. Use pg_profile function to generate sample and report in pg_profile database


Below are setup details and the same will be used in this demonstration.

Sr. No.HostnameIPPortType
1test-machine01192.168.114.1775432pg_profile database
2test-machine02192.168.114.1765432remote server


Step 1. Download pg_profile tar file in pg_profile database: Use OS command wget to download pg_profile extension files directly inside the server. Once tar file is downloaded, extract extension files to PostgreSQL extensions location, which is /usr/pgsql-13/share/extension/.

[root@test-machine01 tmp]# 
[root@test-machine01 tmp]# wget https://github.com/zubkov-andrei/pg_profile/releases/download/0.3.4/pg_profile--0.3.4.tar.gz
--2021-11-04 11:30:37--  https://github.com/zubkov-andrei/pg_profile/releases/download/0.3.4/pg_profile--0.3.4.tar.gz
Resolving github.com (github.com)... 140.82.121.4
Connecting to github.com (github.com)|140.82.121.4|:443... connected.
HTTP request sent, awaiting response... 302 Found
Location: https://github-releases.githubusercontent.com/102480763/dede8447-58fe-425c-8815-0478495b2d6a?X-Amz-Algorithm=AWS4-HMAC-SHA256&X-Amz-Credential=AKIAIWNJYAX4CSVEH53A%2F20211104%2Fus-east-1%2Fs3%2Faws4_request&X-Amz-Date=20211104T083038Z&X-Amz-Expires=300&X-Amz-Signature=3f2b6d54a8866ff5ed72e3420f6a12553ef367e5b395ec8beb553d16ed781ef1&X-Amz-SignedHeaders=host&actor_id=0&key_id=0&repo_id=102480763&response-content-disposition=attachment%3B%20filename%3Dpg_profile--0.3.4.tar.gz&response-content-type=application%2Foctet-stream [following]
--2021-11-04 11:30:38--  https://github-releases.githubusercontent.com/102480763/dede8447-58fe-425c-8815-0478495b2d6a?X-Amz-Algorithm=AWS4-HMAC-SHA256&X-Amz-Credential=AKIAIWNJYAX4CSVEH53A%2F20211104%2Fus-east-1%2Fs3%2Faws4_request&X-Amz-Date=20211104T083038Z&X-Amz-Expires=300&X-Amz-Signature=3f2b6d54a8866ff5ed72e3420f6a12553ef367e5b395ec8beb553d16ed781ef1&X-Amz-SignedHeaders=host&actor_id=0&key_id=0&repo_id=102480763&response-content-disposition=attachment%3B%20filename%3Dpg_profile--0.3.4.tar.gz&response-content-type=application%2Foctet-stream
Resolving github-releases.githubusercontent.com (github-releases.githubusercontent.com)... 185.199.108.154, 185.199.110.154, 185.199.109.154, ...
Connecting to github-releases.githubusercontent.com (github-releases.githubusercontent.com)|185.199.108.154|:443... connected.
HTTP request sent, awaiting response... 200 OK
Length: 159714 (156K) [application/octet-stream]
Saving to: âpg_profile--0.3.4.tar.gzâ

100%[====================================================================================================================================================>] 159,714      719KB/s   in 0.2s

2021-11-04 11:30:39 (719 KB/s) - âpg_profile--0.3.4.tar.gzâ saved [159714/159714]

[root@test-machine01 tmp]#
[root@test-machine01 tmp]# ls -ltr
total 156
-rw-r--r--. 1 root root 159714 Aug 19 18:12 pg_profile--0.3.4.tar.gz
drwx------. 2 root root      6 Nov  4 11:29 vmware-root_870-2731086752
[root@test-machine01 tmp]#
[root@test-machine01 tmp]#

[root@test-machine01 tmp]# tar xzf pg_profile--0.3.4.tar.gz --directory /usr/pgsql-13/share/extension/
[root@test-machine01 tmp]#
[root@test-machine01 tmp]# cd /usr/pgsql-13/share/extension/
[root@test-machine01 extension]# ls -ltr
total 1488
-rw-r--r--. 1 root root    193 Aug 12 13:02 plpgsql.control
-rw-r--r--. 1 root root    658 Aug 12 13:02 plpgsql--1.0.sql
-rw-r--r--. 1 root root    195 Aug 19 18:09 pg_profile.control
-rw-r--r--. 1 root root 795329 Aug 19 18:09 pg_profile--0.3.4.sql
-rw-r--r--. 1 root root 711989 Aug 19 18:09 pg_profile--0.3.3--0.3.4.sql
[root@test-machine01 extension]#
[root@test-machine01 extension]#


Step 2. Creating extensions in pg_profile database: pg_profile extension depends on extensions like plpgsql, dblink, and pg_stat_statements. If you receive the below errors while creating extensions install package postgresql13-contrib using OS yum command. Once the package is installed successfully try to create all the below extensions. Use psql command \dx to check all extensions are installed and loaded in PostgreSQL Cluster.

[root@test-machine01 extension]# su - postgres
Last login: Thu Nov  4 11:34:19 +03 2021 on pts/0
[postgres@test-machine01 ~]$
[postgres@test-machine01 ~]$
[postgres@test-machine01 ~]$ psql
psql (9.2.24, server 13.4)
WARNING: psql version 9.2, server version 13.0.
         Some psql features might not work.
Type "help" for help.

postgres=#
postgres=# CREATE EXTENSION dblink;
ERROR:  could not open extension control file "/usr/pgsql-13/share/extension/dblink.control": No such file or directory
postgres=# CREATE EXTENSION pg_stat_statements;
ERROR:  could not open extension control file "/usr/pgsql-13/share/extension/pg_stat_statements.control": No such file or directory
postgres=#
postgres=# \q
[postgres@test-machine01 ~]$

[root@test-machine01 extension]#  yum install postgresql13-contrib
==============================================================================================================================================================================================
 Package                                              Arch                                   Version                                             Repository                              Size
==============================================================================================================================================================================================
Installing:
 postgresql13-contrib                                 x86_64                                 13.4-1PGDG.rhel7                                    pgdg13                                 609 k

Transaction Summary
==============================================================================================================================================================================================
Install  1 Package

Total download size: 609 k
Installed size: 2.1 M
Is this ok [y/d/N]: y
Downloading packages:
postgresql13-contrib-13.4-1PGDG.rhel7.x86_64.rpm                                                                                                                       | 609 kB  00:00:04
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
  Installing : postgresql13-contrib-13.4-1PGDG.rhel7.x86_64                                                                                                                               1/1
  Verifying  : postgresql13-contrib-13.4-1PGDG.rhel7.x86_64                                                                                                                               1/1

Installed:
  postgresql13-contrib.x86_64 0:13.4-1PGDG.rhel7

Complete!
[root@test-machine01 extension]#

[root@test-machine01 extension]# cd /usr/pgsql-13/share/extension/
[root@test-machine01 extension]#
[root@test-machine01 extension]# ls -ltr
total 2484
-rw-r--r--. 1 root root    193 Aug 12 13:02 plpgsql.control
-rw-r--r--. 1 root root    658 Aug 12 13:02 plpgsql--1.0.sql
-rw-r--r--. 1 root root    173 Aug 12 13:02 dict_int.control
-rw-r--r--. 1 root root    711 Aug 12 13:02 dict_int--1.0.sql
-rw-r--r--. 1 root root    170 Aug 12 13:02 dblink.control
-rw-r--r--. 1 root root   6645 Aug 12 13:02 dblink--1.2.sql
-rw-r--r--. 1 root root   2832 Aug 12 13:02 dblink--1.1--1.2.sql
-rw-r--r--. 1 root root    419 Aug 12 13:02 dblink--1.0--1.1.sql
-rw-r--r--. 1 root root    157 Aug 12 13:02 cube.control
[root@test-machine01 extension]#



[root@test-machine01 extension]#
[root@test-machine01 extension]# su - postgres
Last login: Thu Nov  4 11:38:18 +03 2021 on pts/0
[postgres@test-machine01 ~]$
[postgres@test-machine01 ~]$ psql
psql (9.2.24, server 13.4)
WARNING: psql version 9.2, server version 13.0.
         Some psql features might not work.
Type "help" for help.

postgres=#
postgres=# CREATE EXTENSION dblink;
CREATE EXTENSION
postgres=# CREATE EXTENSION pg_stat_statements;
CREATE EXTENSION
postgres=# CREATE EXTENSION pg_profile;
CREATE EXTENSION
postgres=#
postgres=#
postgres=# \dx
                                            List of installed extensions
        Name        | Version |   Schema   |                              Description
--------------------+---------+------------+------------------------------------------------------------------------
 dblink             | 1.2     | public     | connect to other PostgreSQL databases from within a database
 pg_profile         | 0.3.4   | public     | PostgreSQL load profile repository and report builder
 pg_stat_statements | 1.8     | public     | track planning and execution statistics of all SQL statements executed
 plpgsql            | 1.0     | pg_catalog | PL/pgSQL procedural language
(4 rows)

postgres=#


Step 3. Update postgresql.conf in pg_profile database: Please note after we create extension pg_stat_statements. We also need to load it via postgresql.conf parameter: shared_preload_libraries. You can use psql command \dx+ pg_profile to see all related functions and tables. By default when we create pg_profile extension the local PostgreSQL cluster will be added as a local server. We can verify this using function show_servers().

postgres=#
postgres=# select * from pg_stat_statements;
ERROR:  pg_stat_statements must be loaded via shared_preload_libraries
postgres=#

[postgres@test-machine01 ~]$ cd $PGDATA
[postgres@test-machine01 data]$ vi postgresql.conf
shared_preload_libraries = 'pg_stat_statements' 
:wq!
[postgres@test-machine01 data]$

[postgres@test-machine01 data]$ systemctl restart postgresql-13
==== AUTHENTICATING FOR org.freedesktop.systemd1.manage-units ===
Authentication is required to manage system services or units.
Authenticating as: root
Password:
==== AUTHENTICATION COMPLETE ===
[postgres@test-machine01 data]$
[postgres@test-machine01 data]$ psql
psql (9.2.24, server 13.4)
WARNING: psql version 9.2, server version 13.0.
         Some psql features might not work.
Type "help" for help.

postgres=# select * from pg_stat_statements;
 userid | dbid | queryid | query | plans | total_plan_time | min_plan_time | max_plan_time | mean_plan_time | stddev_plan_time | calls | total_exec_time | min_exec_time | max_exec_time | mea
n_exec_time | stddev_exec_time | rows | shared_blks_hit | shared_blks_read | shared_blks_dirtied | shared_blks_written | local_blks_hit | local_blks_read | local_blks_dirtied | local_blks_wr
itten | temp_blks_read | temp_blks_written | blk_read_time | blk_write_time | wal_records | wal_fpi | wal_bytes
--------+------+---------+-------+-------+-----------------+---------------+---------------+----------------+------------------+-------+-----------------+---------------+---------------+----
------------+------------------+------+-----------------+------------------+---------------------+---------------------+----------------+-----------------+--------------------+--------------
------+----------------+-------------------+---------------+----------------+-------------+---------+-----------
(0 rows)

postgres=#

postgres=# \dx+ pg_profile
                                 Objects in extension "pg_profile"
                                        Object Description
---------------------------------------------------------------------------------------------------
 function check_stmt_all_setting(integer,integer,integer)
 function check_stmt_cnt(integer,integer,integer)
 function cluster_stats_diff_htbl(jsonb,integer,integer,integer,integer,integer)
 function cluster_stats_htbl(jsonb,integer,integer,integer)
 function cluster_stats(integer,integer,integer)
 function cluster_stats_reset_diff_htbl(jsonb,integer,integer,integer,integer,int

postgres=#
postgres=# select * from show_servers() ;
 server_name |          connstr          | enabled | description
-------------+---------------------------+---------+-------------
 local       | dbname=postgres port=5432 | t       |
(1 row)

postgres=#


Step 4. Configure the remote server to use pg_profile: pg_profile, installed in one cluster is able to collect statistics from other clusters, called servers. The only mandatory requirement for server cluster is the ability to connect from pg_profile database using provided server connection string. All other requirements are optional, but they can improve completeness of gathered statistics.

If you need statement statistics in reports, then the database mentioned in the server connection string must have the pg_stat_statements extension configured. Make sure you have the correct entry pg_hba.conf to allow connections to this remote server. Create user profile_mon will use this user to connect from pg_profile database.

[postgres@test-machine02 data]$ pwd
/var/lib/pgsql/13/data
[postgres@test-machine02 data]$ cat pg_hba.conf
# IPv4 local connections:
host    all             all             127.0.0.1/32            scram-sha-256
host    all             all             192.168.114.0/24        scram-sha-256
[postgres@test-machine02 data]$ 

[root@test-machine02 ~]#
[root@test-machine02 ~]# yum install postgresql13-contrib
Loaded plugins: langpacks, ulninfo
Dependencies Resolved

==============================================================================================================================================================================================
 Package                                              Arch                                   Version                                             Repository                              Size
==============================================================================================================================================================================================
Installing:
 postgresql13-contrib                                 x86_64                                 13.4-1PGDG.rhel7                                    pgdg13                                 609 k

Transaction Summary
==============================================================================================================================================================================================
Install  1 Package

Total download size: 609 k
Installed size: 2.1 M
Is this ok [y/d/N]: y
Downloading packages:
postgresql13-contrib-13.4-1PGDG.rhel7.x86_64.rpm                                                                                                                       | 609 kB  00:00:01
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
  Installing : postgresql13-contrib-13.4-1PGDG.rhel7.x86_64                                                                                                                               1/1
  Verifying  : postgresql13-contrib-13.4-1PGDG.rhel7.x86_64                                                                                                                               1/1

Installed:
  postgresql13-contrib.x86_64 0:13.4-1PGDG.rhel7

Complete!
[root@test-machine02 ~]#

[postgres@test-machine02 ~]$ cd $PGDATA
[postgres@test-machine02 data]$
[postgres@test-machine02 data]$ vi postgresql.conf
shared_preload_libraries = 'pg_stat_statements' 
track_activities = on
track_counts = on
track_io_timing = on
track_functions = all 
:wq!

[postgres@test-machine02 data]$
[postgres@test-machine02 data]$  systemctl restart postgresql-13
==== AUTHENTICATING FOR org.freedesktop.systemd1.manage-units ===
Authentication is required to manage system services or units.
Authenticating as: root
Password:
==== AUTHENTICATION COMPLETE ===
[postgres@test-machine02 data]$

[postgres@test-machine02 data]$ psql
psql (13.4)
Type "help" for help.

postgres=# 
postgres=# create role profile_mon login password 'pwd_mon';
CREATE ROLE
postgres=# grant pg_read_all_stats to profile_mon;
GRANT ROLE
postgres=# 
postgres=# grant execute on function pg_stat_statements_reset TO profile_mon;
GRANT
postgres=# CREATE EXTENSION pg_stat_statements;
CREATE EXTENSION
postgres=#
postgres=#
postgres=# ALTER SYSTEM SET pg_stat_statements.track = top;
ALTER SYSTEM
postgres=#  ALTER SYSTEM SET pg_stat_statements.max = 10000;
ALTER SYSTEM
postgres=#


Step 5. Add remote server in pg_profile database: Create password file .pgpass in postgres OS user home directory. We need a password file to avoid specifying the user password in the command line. pg_profile will connect to all databases on a server, thus the password file must use a wildcard as a database name. Once all the above steps are done we are ready to add a remote server in pg_profile. Use function create_server() to add remote server. Use function show_servers() to display registered servers.

[postgres@test-machine01 ~]$
[postgres@test-machine01 ~]$ vi .pgpass
test-machine02:5432:*:profile_mon:pwd_mon
:wq!
[postgres@test-machine01 ~]$
[postgres@test-machine01 ~]$ chmod 600 .pgpass
[postgres@test-machine01 ~]$ ls -lart .pgpass
-rw-------. 1 postgres postgres 42 Nov  7 09:30 .pgpass
[postgres@test-machine01 ~]$

[postgres@test-machine01 ~]$
[postgres@test-machine01 ~]$ psql
psql (9.2.24, server 13.4)
WARNING: psql version 9.2, server version 13.0.
         Some psql features might not work.
Type "help" for help.

postgres=#
postgres=#
postgres=# select * from show_servers() ;
 server_name |          connstr          | enabled | description
-------------+---------------------------+---------+-------------
 local       | dbname=postgres port=5432 | t       |
(1 row)

postgres=# SELECT create_server('test-machine02','host=test-machine02 dbname=postgres port=5432 user=profile_mon');
 create_server
---------------
             3
(1 row)

postgres=#  select * from show_servers() ;
  server_name   |                            connstr                             | enabled | description
----------------+----------------------------------------------------------------+---------+-------------
 local          | dbname=postgres port=5432                                      | t       |
 test-machine02 | host=test-machine02 dbname=postgres port=5432 user=profile_mon | t       |
(2 rows)

postgres=#


postgres=#
postgres=# SELECT set_server_description('test-machine02', 'test-machine02 pg_profile setup');
 set_server_description
------------------------
                      1
(1 row)

postgres=# select * from show_servers() ;
  server_name   |                            connstr                             | enabled |           description
----------------+----------------------------------------------------------------+---------+---------------------------------
 local          | dbname=postgres port=5432                                      | t       |
 test-machine02 | host=test-machine02 dbname=postgres port=5432 user=profile_mon | t       | test-machine02 pg_profile setup
(2 rows)

postgres=#


Step 6. Use pg_profile function to generate sample and report in pg_profile database: Once a remote server is registered successfully. We can use functions like take_sample() to take samples. Function take_sample() will collect a sample for all registered servers. Server samples will be taken serially one by one. To take a sample for a specific server we can pass the server name to function take_sample(). If you have any issue connecting to the remote server, it will be shown while running the take_sample() function like below. Use function like show_samples() to display collected samples.

Once required samples are collected use function get_report() to generate the report from collected samples.

postgres=#
postgres=# select * from take_sample();
     server     | result |   elapsed
----------------+--------+-------------
 local          | OK     | 00:00:00.66
 test-machine02 | OK     | 00:00:07.38
(2 rows)

postgres=#

postgres=# select * from take_sample();
     server     |                                   result                                    |   elapsed
----------------+-----------------------------------------------------------------------------+-------------
 test-machine02 | could not establish connection                                             +| 00:00:00.02
                | SQL statement "SELECT dblink_connect('server_connection',server_connstr)"  +|
                | PL/pgSQL function take_sample(integer,boolean) line 129 at PERFORM         +|
                | PL/pgSQL function take_sample_subset(integer,integer) line 27 at assignment+|
                | SQL function "take_sample" statement 1                                     +|
                | fe_sendauth: no password supplied                                           |
 local          | OK                                                                          | 00:00:00.7
(2 rows)
postgres=#


postgres=# select * from take_sample('test-machine02');
 take_sample
-------------
           0
(1 row)

postgres=#

postgres=# select * from show_samples();
 sample |      sample_time       | sizes_collected | dbstats_reset | clustats_reset | archstats_reset
--------+------------------------+-----------------+---------------+----------------+-----------------
      1 | 2021-11-04 11:55:47+03 | t               |               |                |
      2 | 2021-11-04 11:56:29+03 | t               |               |                |
      3 | 2021-11-04 11:59:40+03 | t               |               |                |
      4 | 2021-11-07 09:47:42+03 | t               |               |                |
      5 | 2021-11-07 09:53:34+03 | t               |               |                |
      6 | 2021-11-07 09:56:43+03 | t               |               |                |
      7 | 2021-11-07 10:11:10+03 | t               |               |                |
(7 rows)

postgres=#

postgres=# select * from show_samples('test-machine02');
 sample |      sample_time       | sizes_collected | dbstats_reset | bgwrstats_reset | archstats_reset
--------+------------------------+-----------------+---------------+-----------------+-----------------
      1 | 2021-11-07 09:56:43+03 | t               |               |                 |
      2 | 2021-11-07 10:11:10+03 | t               |               |                 |
      3 | 2021-11-07 10:14:59+03 | t               |               |                 |
(3 rows)

postgres=#

[postgres@test-machine01 ~]$ psql -Aqtc "SELECT get_report('test-machine02',2,3)" -o test-machine02_2_3.html
[postgres@test-machine01 ~]$
[postgres@test-machine01 ~]$ ls -ltr
total 140
drwx------. 4 postgres postgres     51 Oct 11 09:10 12
drwx------. 4 postgres postgres     51 Oct 11 09:24 13
-rwx------. 1 postgres postgres    121 Oct 11 13:27 delete_old_cluster.sh
-rwx------. 1 postgres postgres    753 Oct 11 13:27 analyze_new_cluster.sh
-rw-r--r--. 1 postgres postgres 131227 Nov  7 10:27 test-machine02_2_3.html
[postgres@test-machine01 ~]$
[postgres@test-machine01 ~]$ pwd
/var/lib/pgsql
[postgres@test-machine01 ~]$

Screenshot of pg_profile sample report

Reference : pg_profile documentation: https://github.com/zubkov-andrei/pg_profile/blob/master/doc/pg_profile.md


This document is just for learning purpose and always validate in the LAB environment first before applying in the LIVE environment.


Hope so you like this article!
Please share your valuable feedback/comments/subscribe and follow us below and don’t forget to click on the bell icon to get the most recent update. Click here to understand more about our pursuit.

Related Articles

,


Loading

How useful was this post?

Click on a star to rate it!

Average rating / 5. Vote count:

No votes so far! Be the first to rate this post.

As you found this post useful...

Follow us on social media!

We are sorry that this post was not useful for you!

Let us improve this post!

Tell us how we can improve this post?

Jamsher Khan

🌐

Hello and welcome to DBsGuru,I’m Jamsher Khan working as Senior Oracle DBA based in KSA-Jeddah, I have working experience in Oracle DBA, SQL Server, MySql, PostgreSQL, Linux, Golden Gate, ODA.Thanks for the visits!Share Learn Grow!

Leave a Reply

Your email address will not be published. Required fields are marked *