Monitor PostgreSQL Cluster using pgCenter

October 27, 2021

Monitor PostgreSQL Cluster using pgCenter

pgCenter is a command-line admin tool for observing and troubleshooting PostgreSQL i.e monitor PostgreSQL cluster. Postgres provides various activity statistics about its runtime, such as connections, statements, database operations, replication, resources usage, and more. The general purpose of the statistics is to help DBAs to monitor and troubleshoot Postgres. However, these statistics are provided in textual form retrieved from SQL functions and views, and Postgres doesn’t provide native tools for working with statistics views.

pgCenter’s main goal is to help Postgres DBA work with statistics and provide a convenient way to observe Postgres in runtime.

Key features
1. Top-like interface that allows you to monitor stats changes as you go.
2. Wait events profiler allows seeing what wait events occur during queries execution.
3. pgcenter record & report allows you to collect Postgres statistics into files and build reports later on.

Below are the high-level steps we will follow to set up pgCenter
1. Installing pgCenter package
2. pgCenter top command
3. pgCenter profile command
4. pgCenter record command
5. pgCenter report command

Step 1. Installing pgCenter package: You can use below anyone method to install pgCenter once the installation is completed use command pgcenter -V to check the installed version. And pgcenter –help to get the full list of available options.

Method-1: Install pgCenter using the yum command

[root@test-machine01 tmp]#
[root@test-machine01 tmp]# yum install pgcenter
Loaded plugins: langpacks, ulninfo
                                                                                                                                         | 3.6 kB  00:00:00
pgdg-common                                                                                                                                           | 2.9 kB  00:00:00
pgdg10                                                                                                                                                | 3.6 kB  00:00:00
pgdg11                                                                                                                                                | 3.6 kB  00:00:00
pgdg12                                                                                                                                                | 3.6 kB  00:00:00
pgdg13                                                                                                                                                | 3.6 kB  00:00:00
pgdg95                                                                                                                                                | 3.6 kB  00:00:00
pgdg96                                                                                                                                                | 3.6 kB  00:00:00
(1/8): pgdg-common/7Server/x86_64/primary_db                                                                                                          | 141 kB  00:00:00
(2/8): pgdg12/7Server/x86_64/primary_db                                                                                                               | 227 kB  00:00:00
(3/8): pgdg10/7Server/x86_64/primary_db                                                                                                               | 348 kB  00:00:01
(4/8): pgdg11/7Server/x86_64/primary_db                                                                                                               | 372 kB  00:00:01
(5/8): pgdg96/7Server/x86_64/primary_db                                                                                                               | 337 kB  00:00:00
(6/8): pgdg13/7Server/x86_64/primary_db                                                                                                               | 143 kB  00:00:00
(7/8): ol7_latest/x86_64/updateinfo                                                                                                                   | 3.3 MB  00:00:02
(8/8): ol7_latest/x86_64/primary_db                                                                                                                   |  36 MB  00:00:09
Resolving Dependencies
--> Running transaction check
---> Package pgcenter.x86_64 0:0.4.0-2.rhel7 will be installed
--> Finished Dependency Resolution

Dependencies Resolved

=============================================================================================================================================================================
 Package                                 Arch                                  Version                                      Repository                                  Size
=============================================================================================================================================================================
Installing:
 pgcenter                                x86_64                                0.4.0-2.rhel7                                pgdg-common                                 51 k

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

Total download size: 51 k
Installed size: 179 k
Is this ok [y/d/N]: y
Downloading packages:
pgcenter-0.4.0-2.rhel7.x86_64.rpm                                                                                                                     |  51 kB  00:00:00
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
  Installing : pgcenter-0.4.0-2.rhel7.x86_64                                                                                                                             1/1
  Verifying  : pgcenter-0.4.0-2.rhel7.x86_64                                                                                                                             1/1

Installed:
  pgcenter.x86_64 0:0.4.0-2.rhel7

Complete!
[root@test-machine01 tmp]#

[root@test-machine01 tmp]#
[root@test-machine01 tmp]# su - postgres
Last login: Fri Oct 15 20:04:06 +03 2021 on pts/2
[postgres@test-machine01 ~]$

[postgres@test-machine01 ~]$ pgcenter -V
pgcenter 0.4.0
[postgres@test-machine01 ~]$

[postgres@test-machine01 ~]$ pgcenter --help
pgcenter is the admin tool for PostgreSQL.

Usage:
   pgcenter [OPTION]... [DBNAME [USERNAME]]

General options:
   -?, --help                show this help, then exit.
   -V, --version             print version, then exit.

Remote system stats options:
   -i, --install=LANG        install stats schema and functions into the database.
   -l, --stats-lang=LANG     use specified language functions.
                             available languages: plperlu
   -u, --uninstall           remove stats schema and functions.
   -e, --do-everywhere       install or remove stats schema into the all databases.

Options:
   -h, --host=HOSTNAME       database server host or socket directory
   -p, --port=PORT           database server port (default: "5432")
   -U, --username=USERNAME   database user name (default: "current user")
   -d, --dbname=DBNAME       database name (default: "current user")
   -f, --file=FILENAME       conninfo file (default: "~/.pgcenterrc")
   -w, --no-password         never prompt for password
   -W, --password            force password prompt (should happen automatically)

Report bugs to https://github.com/lesovsky/pgcenter/issues.
[postgres@test-machine01 ~]$

Method-2: Install pgCenter using the manual method

[root@test-machine02 tmp]# wget https://github.com/lesovsky/pgcenter/releases/download/v0.9.2/pgcenter_0.9.2_linux_amd64.rpm
--2021-10-26 13:52:22--  https://github.com/lesovsky/pgcenter/releases/download/v0.9.2/pgcenter_0.9.2_linux_amd64.rpm
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
Connecting to github-releases.githubusercontent.com (github-releases.githubusercontent.com)|185.199.108.154|:443... connected.
HTTP request sent, awaiting response... 200 OK
Length: 5859052 (5.6M) [application/octet-stream]
Saving to: âpgcenter_0.9.2_linux_amd64.rpmâ

100%[===================================================================================================================================>] 5,859,052   1.66MB/s   in 3.5s

2021-10-26 13:52:27 (1.60 MB/s) - âpgcenter_0.9.2_linux_amd64.rpmâ saved [5859052/5859052]

[root@test-machine02 tmp]# ls -ltr
total 8636
-rw-r--r--. 1 root   root   5859052 Jul 18 14:13 pgcenter_0.9.2_linux_amd64.rpm
srwx------. 1 mongod mongod       0 Oct  5 18:55 mongodb-27017.sock
-rw-r--r--. 1 root   root   2974369 Oct 15 19:15 v11.6.tar.gz
drwxrwxr-x. 7 root   root      4096 Oct 15 19:18 pgbadger-11.6
[root@test-machine02 tmp]# 

[root@test-machine02 tmp]# yum localinstall pgcenter_0.9.2_linux_amd64.rpm
Loaded plugins: langpacks, ulninfo
Examining pgcenter_0.9.2_linux_amd64.rpm: pgcenter-0.9.2-1.x86_64
Marking pgcenter_0.9.2_linux_amd64.rpm to be installed
Resolving Dependencies
--> Running transaction check
---> Package pgcenter.x86_64 0:0.9.2-1 will be installed
--> Finished Dependency Resolution
pgdg-common/7Server/x86_64                                                                                                                            | 2.9 kB  00:00:00
pgdg-common/7Server/x86_64/primary_db                                                                                                                 | 146 kB  00:00:00
pgdg10/7Server/x86_64                                                                                                                                 | 3.6 kB  00:00:00
pgdg10/7Server/x86_64/primary_db                                                                                                                      | 350 kB  00:00:01
pgdg11/7Server/x86_64                                                                                                                                 | 3.6 kB  00:00:00
pgdg11/7Server/x86_64/primary_db                                                                                                                      | 376 kB  00:00:01
pgdg12/7Server/x86_64                                                                                                                                 | 3.6 kB  00:00:00
pgdg12/7Server/x86_64/primary_db                                                                                                                      | 229 kB  00:00:00
pgdg13/7Server/x86_64                                                                                                                                 | 3.6 kB  00:00:00
pgdg13/7Server/x86_64/primary_db                                                                                                                      | 147 kB  00:00:00
pgdg95/7Server/x86_64                                                                                                                                 | 3.6 kB  00:00:00
pgdg96/7Server/x86_64                                                                                                                                 | 3.6 kB  00:00:00
pgdg96/7Server/x86_64/primary_db                                                                                                                      | 337 kB  00:00:00
vault.centos.org_centos_7.4.1708_extras_x86_64_                                                                                                       | 3.4 kB  00:00:00

Dependencies Resolved

=============================================================================================================================================================================
 Package                              Arch                               Version                               Repository                                               Size
=============================================================================================================================================================================
Installing:
 pgcenter                             x86_64                             0.9.2-1                               /pgcenter_0.9.2_linux_amd64                              11 M

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

Total size: 11 M
Installed size: 11 M
Is this ok [y/d/N]: y
Downloading packages:
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
  Verifying  : pgcenter-0.9.2-1.x86_64                                                                                                                                   1/1

Installed:
  pgcenter.x86_64 0:0.9.2-1

Complete!
[root@test-machine02 tmp]# su - postgres
Last login: Sat Oct 23 15:04:16 +03 2021 on pts/0
[postgres@test-machine02 ~]$

[postgres@test-machine02 ~]$ pgcenter --version
pgcenter v0.9.2 7ebd54847cbbf72a98cbde39e9afc3845cba9839-release
[postgres@test-machine02 ~]$


[postgres@test-machine02 ~]$
[postgres@test-machine02 ~]$ pgcenter --help
pgCenter is a command line admin tool for PostgreSQL.

Usage:
  pgcenter [flags]
  pgcenter [command] [command-flags] [args]

Available commands:
  config        installs or uninstalls pgcenter stats schema to Postgres
  profile       wait events profiler
  record        record stats to file
  report        make report based on previously saved statistics
  top           top-like stats viewer

Flags:
  -?, --help            show this help and exit
      --version         show version information and exit

Use "pgcenter [command] --help" for more information about a command.

Report bugs to <https://github.com/lesovsky/pgcenter/issues>.
[postgres@test-machine02 ~]$

Step 2. pgCenter top command: pgcenter top provides a top-like interface for Postgres statistics with an extended set of functions that make online monitoring and troubleshooting Postgres much easier. To create load on the cluster we are inserting 100 million random rows. From the below pgcenter top command, you will notice PID 92639 is the same session that is doing an insert. You can type h in pgcenter output screen to get the full list of help options in pgcenter top.

[postgres@test-machine02 ~]$
[postgres@test-machine02 ~]$ pgcenter top  --help
'pgcenter top' is the top-like stats viewer.

Usage:
  pgcenter top [OPTIONS]... [DBNAME [USERNAME]]

Options:
  -d, --dbname DBNAME           database name to connect to
  -h, --host HOSTNAME           database server host or socket directory
  -p, --port PORT               database server port (default 5432)
  -U, --username USERNAME       database user name

General options:
  -?, --help            show this help and exit

Report bugs to <https://github.com/lesovsky/pgcenter/issues>.
[postgres@test-machine02 ~]$

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

sample1=#
sample1=#  insert into t_test values (generate_series(1,100000000),now());
INSERT 0 100000000
sample1=#

[postgres@test-machine02 ~]$ pgcenter top -h test-machine02 -U postgres  -p 5432
Password for user postgres:
pgcenter: 2021-10-26 17:01:48, load average: 0.00, 0.00, 0.00                          state [ok]: test-machine02:5432 postgres@postgres (ver: 13.4, up 4 days 00:13:44, reco
    %cpu:  0.0 us,  0.0 sy,  0.0 ni,  0.0 id,  0.0 wa,  0.0 hi,  0.0 si,  0.0 st         activity:  2/100 conns,  0/0 prepared,  0 idle,  0 idle_xact,  2 active,  0 waiting,
 MiB mem:      0 total,      0 free,      0 used,        0 buff/cached                 autovacuum:  0/3 workers/max,  0 manual,  0 wraparound, 00:00:00 vac_maxtime
MiB swap:      0 total,      0 free,      0 used,      0/0 dirty/writeback             statements:   0 stmt/s, 0.000 stmt_avgtime, 00:00:42 xact_maxtime, 00:00:00 prep_maxti

pid       cl_addr             cl_port   datname   usename   appname   backend_type    wait_etype  wait_event  state     xact_age  query_age  change_age  query
94349     192.168.114.176/32  38802     postgres  postgres            client backend                          active    00:00:00  00:00:00   00:00:00    SELECT pid, client_a
92639                         -1        sample1   postgres  psql      client backend                          active    00:00:42  00:00:42   00:00:42    insert into t_test v

############   Below output you will get after pressing h (help menu) button   ####################                                                                                                                                                                            ,
general actions:
    a,f,r,w     mode: 'a' activity, 'f' functions, 'r' replication, 'w' WAL,                                                                                                i
    s,t,i             's' tables sizes, 't' tables, 'i' indexes.
    d,D               'd' pg_stat_database switch, 'X' pg_stat_database menu.
    x,X               'x' pg_stat_statements switch, 'X' pg_stat_statements menu.                                                                                           a
    p,P               'p' pg_stat_progress_* switch, 'P' pg_stat_progress_* menu.
    Left,Right,<,/    'Left,Right' change column sort, '<' desc/asc sort toggle, '/' set filter.
    Up,Down           'Up' increase column width, 'Down' decrease column width.
    C,E,R       config: 'C' show config, 'E' edit configs, 'R' reload config.
    ~                 start psql session.
    l                 open log file with pager.

extra stats actions:
    B,N,F,L       'B' diskstat, 'N' nicstat, 'F' filesystems, L' logtail.

activity actions:
    -,_         '-' cancel backend by pid, '_' terminate backend by pid.
    n,m         'n' set new mask, 'm' show current mask.
    k,K         'k' cancel group of queries using mask, 'K' terminate group of backends using mask.
    I           show IDLE connections toggle.
    A           change activity age threshold.
    G           get query report.

other actions:
    , Q         ',' show system tables on/off, 'Q' reset postgresql statistics counters.
    z           'z' set refresh interval.
    h,F1        show this tab.
    q,Ctrl+Q    quit.

Type 'q' or 'Esc' to continue.

Step 3. pgCenter profile command: pgcenter profile is the tool for profiling wait events occurred during queries execution. In cases of long queries, you might be interested what this query does. Using EXPLAIN utility you can observe a detailed query execution plan. But if the query spends time waiting’s EXPLAIN will not show that. Using pgcenter profile you can see what wait events occur during query execution. Please note -P 92639 is same insert PID from Step 2.

[postgres@test-machine02 ~]$ pgcenter profile  --help
'pgcenter profile' profiles wait events of running queries.

Usage:
 pgcenter profile [OPTIONS]... [DBNAME [USERNAME]]

Options:
 -d, --dbname DBNAME            database name to connect to
 -h, --host HOSTNAME            database server host or socket directory
 -p, --port PORT                database server port (default 5432)
 -U, --username USERNAME        database user name

 -P, --pid PID                  backend PID to profile to
 -F, --freq FREQ                profile at this frequency (default: 100ms, min: 1ms, max: 1s)
 -s, --strsize SIZE             limit length of print query strings to STRSIZE chars (default: 128)
 -W, --no-workers               don't profile child parallel workers (default: false)

General options:
 -?, --help             show this help and exit

Report bugs to <https://github.com/lesovsky/pgcenter/issues>.
[postgres@test-machine02 ~]$

[postgres@test-machine02 ~]$ pgcenter profile -d sample1 -h test-machine02 -U postgres  -p 5432 -P 92639
Password for user postgres:
LOG: Profiling process 92639 with 100ms sampling
------ ------------ -----------------------------
% time      seconds wait_event                     query: insert into t_test values (generate_series(1,100000000),now());
------ ------------ -----------------------------

 23.74   100.816840 IO.WALInitWrite
 23.71   100.693757 Running
 22.86    97.062093 IO.DataFileExtend
 18.82    79.920575 LWLock.WALWrite
  5.70    24.183814 IO.WALInitSync
  4.01    17.028502 IO.WALSync
  0.70     2.956554 IO.WALWrite
  0.42     1.773102 IO.DataFileWrite
  0.05     0.193801 LWLock.CheckpointerComm
------ ------------ -----------------------------
100.00   424.629038 including workers
         424.629038


[postgres@test-machine02 ~]$

Step 4. pgCenter record command: pgcenter record collect metrics from Postgres to local files. pgcenter record connects to Postgres, reads stats, and writes this information into JSON files into a tar archive. File name contains the name of the statistics view and timestamp when stats have been recorded. Hence, it’s possible to unpack statistics using tar. Once unpacked, stats can be used in any way required.

[postgres@test-machine02 ~]$ pgcenter record  --help
'pgcenter record' connects to PostgreSQL and collects stats into local file.

Usage:
 pgcenter record [OPTIONS]... [DBNAME [USERNAME]]

Options:
 -d, --dbname DBNAME            database name to connect to
 -h, --host HOSTNAME            database server host or socket directory
 -p, --port PORT                database server port (default 5432)
 -U, --username USERNAME        database user name

 -i, --interval DURATION        statistics recording interval (default: 1s)
 -c, --count INT                number of statistics samples to record
 -f, --file FILENAME            file name where statistics to write to (default: pgcenter.stat.tar)
 -a, --append                   append statistics to file (defailt: true)
 -s, --strlimit INT             maximum query length to record (default: 0, no limit)
 -1, --oneshot                  append single statistics snapshot and exit (alias for --interval 0 --count 1)

General options:
 -?, --help             show this help and exit

Report bugs to <https://github.com/lesovsky/pgcenter/issues>.
[postgres@test-machine02 ~]$

[postgres@test-machine02 ~]$
[postgres@test-machine02 ~]$ pgcenter record -f /tmp/stats.tar -U postgres sample1
INFO: pg_stat_statements not found, skip recording it
INFO: some statistics is not supported by the current version of Postgres and will be skipped
INFO: recording to /tmp/stats.tar
[postgres@test-machine02 ~]$
[postgres@test-machine02 ~]$ ls -l /tmp/stats.tar
-rw-------. 1 postgres postgres 1446912 Oct 26 18:16 /tmp/stats.tar
[postgres@test-machine02 ~]$

Step 5. pgCenter report command: pgcenter report reads stats files written by pgcenter record and builds reports. pgcenter report is an addition to pgcenter record. It reads the collected statistics and builds reports out of these data. pgcenter report doesn’t require connection to Postgres, all you need is to specify the file with relevant statistics and choose the type of the report.

[postgres@test-machine02 ~]$ pgcenter report  --help
'pgcenter report' reads statistics from file and prints reports.

Usage:
 pgcenter report [OPTIONS]...

Options:
 -f, --file FILE                read stats from file (default: pgcenter.stat.tar)
 -s, --start TIMESTAMP          starting time of the report (format: [YYYY-MM-DD] HH:MM:SS)
 -e, --end TIMESTAMP            ending time of the report (format: [YYYY-MM-DD] HH:MM:SS)
 -o, --order COLNAME            order values by column
     --desc                     use descendant order (default)
     --asc                      use ascendant order
 -g, --grep COLNAME:PATTERN     filter values in specfied column (format: colname:filtertext)
 -l, --limit INT                print only limited number of rows per sample (default: unlimited)
 -t, --strlimit INT             maximum string size to print (default: 32, 0 disables)

Report options:
 -A, --activity                 show pg_stat_activity statistics
 -R, --replication              show pg_stat_replication statistics

 -T, --tables                   show pg_stat_user_tables statistics
 -I, --indexes                  show pg_stat_user_indexes and pg_statio_user_indexes statistics
 -S, --sizes                    show statistics about tables sizes
 -F, --functions                show pg_stat_user_functions statistics
 -W, --wal                              show pg_stat_wal statistics
 -D, --databases SELECTOR       show pg_stat_database statistics, use additional selector to choose stats:
                                'g' - general; 's' - sessions
 -X, --statements SELECTOR      show pg_stat_statements statistics, use additional selector to choose stats:
                                'm' - timings; 'g' - general; 'i' - io; 't' - temp files io; 'l' - local files io; 'w' - wal statistics
 -P, --progress SELECTOR        show pg_stat_progress_* statistics, use additional selector to choose stats:
                                'v' - vacuum; 'c' - cluster; 'i' - create index; 'a' - analyze; 'b' - basebackup; 'y' - copy

 -d, --describe                 show statistics description, combined with one of the report options

General options:
 -?, --help             show this help and exit

Report bugs to <https://github.com/lesovsky/pgcenter/issues>.
[postgres@test-machine02 ~]$

[postgres@test-machine02 ~]$
[postgres@test-machine02 ~]$ pgcenter report -f /tmp/stats.tar --databases g --grep datname:sample1
INFO: reading from /tmp/stats.tar
INFO: report databases_general
INFO: start from: 0001-01-01 00:00:00 LMT, to: 2021-10-26 18:34:59 +03
datname    backends_total  commits   rollbacks  read,KiB  hits      returned  fetched   inserts   updates   deletes   conflicts  deadlocks  csum_fails  temp_files  temp_bytes  read,ms   write,ms  stats_age
2021/10/26 18:16:18, rate: 163ms
sample1    2               0         0          0         0         0         0         0         0         0         0          0          0           0           0           0.00      0.00      27 days 04:41:28
2021/10/26 18:16:18, rate: 229ms
sample1    2               21        0          48        4609      14622     2381      0         0         0         0          0          0           0           0           0.00      0.00      27 days 04:41:29
2021/10/26 18:16:19, rate: 956ms
sample1    2               14        0          0         4130      12844     2142      0         0         0         0          0          0           0           0           0.00      0.00      27 days 04:41:30
2021/10/26 18:16:20, rate: 1s
sample1    2               14        0          0         4130      12844     2142      0         0         0         0          0          0           0           0           0.00      0.00      27 days 04:41:31
2021/10/26 18:16:21, rate: 1s
sample1    2               14        0          0         4130      12844     2142      0         0         0         0          0          0           0           0           0.00      0.00      27 days 04:41:32

References : –
https://github.com/lesovsky/pgcenterhttps://github.com/lesovsky/pgcenter/blob/master/doc/pgcenter-top-readme.md
https://github.com/lesovsky/pgcenter/blob/master/doc/pgcenter-profile-readme.md
https://github.com/lesovsky/pgcenter/blob/master/doc/pgcenter-record-readme.md
https://github.com/lesovsky/pgcenter/blob/master/doc/pgcenter-report-readme.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.

PGCENTER, POSTGRESQL, TUNING

Monitor PostgreSQL Cluster using pgCenter

Related Articles