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.
Related Articles
- Access the MSSQL database from PostgreSQL using TDS Foreign Data Wrapper (TDS_FDW)
- Access MySql database from PostgreSQL using MySQL Foreign Data Wrapper (MYSQL_FDW)
- Configure and monitor using pg_profile in PostgreSQL
- Monitor PostgreSQL Cluster using pgCenter
- Install and Configure pg_repack in PostgreSQL
