18
Administrative Tasks
Monitoring
Copyright
© Postgres Professional, 2017–2026
Authors: Egor Rogov, Pavel Luzanov, Ilya Bashtanov, Aleksei Beresnev
Translated by: Liudmila Mantrova, Alexander Meleshko, Elena Sharafutdinova
Cover photo by Oleg Bartunov (Nepal peak, 7143 m, Lhonak)
Use of course materials
Non-commercial use of course materials (presentations, demonstrations) is
allowed without restrictions. Commercial use is possible only with the written
permission of Postgres Professional. It is prohibited to make changes to the
course materials.
Feedback
Please send your feedback, comments and suggestions to:
edu@postgrespro.ru
Disclaimer
Postgres Professional assumes no responsibility for any damages and losses,
including loss of income, caused by direct or indirect, intentional or accidental
use of course materials. Postgres Professional company specifically disclaims
any warranties on course materials. Course materials are provided “as is,” and
Postgres Professional company has no obligations to provide maintenance,
support, updates, enhancements, or modifications.
2
Topics
OS Tools
Server Statistics
Server Message Log
External Monitoring Systems
3
OS Tools
Processes
ps, pgrep...
update_process_title parameter for updating the status of processes
cluster_name parameter for setting the cluster name
Resource usage
iostat, vmstat, sar, top...
Disk space
df, du, quota...
PostgreSQL runs on an operating system and to a certain extent depends
on its configuration.
PostgreSQL process information is accessible via OS tools. The server
parameter update_process_title (on by default) displays the state of each
process next to its title. The cluster_name parameter specifies the instance
name used to identify it among running processes.
Various tools are available to monitor the use of system resources (CPU,
RAM, disks) in Unix: iostat, vmstat, sar, top, etc.
Disk space monitoring is also necessary. The space occupied by the
database on disk can be viewed both from the database itself (see the Data
Organization module) and from the OS (with the du command). The amount
of disk space available is also displayed with the df command in the OS. If
disk quotas are used, they must also be taken into account.
The tools and approaches to monitoring differ significantly between various
OS and file systems, so we will not discuss them in detail.
4
Server Statistics
Current Activities
Monitoring of Command Execution
Cumulative Statistics
System Processes
Additional Statistics
There are two primary sources of information about the state of the system.
The first one is statistical information collected by PostgreSQL and
presented via pg_stat_* views.
5
Current Activities
Views
pg_stat_activity current per-process activities,
including waits and locks
pg_locks locks detailes
Settings
track_activities = on
The current activities of all backends and background processes are
displayed in the pg_stat_activity view. This view depends on the
track_activities parameter (enabled by default).
Another important view that shows current locks is called pg_locks.
7
Command Execution
Views
pg_stat_progress_analyze ANALYZE
pg_stat_progress_vacuum VACUUM, including autovacuum
pg_stat_progress_cluster CLUSTER, VACUUM FULL
pg_stat_progress_create_index CREATE INDEX, REINDEX
pg_stat_progress_basebackup Create base backup
pg_stat_progress_copy COPY
You can monitor the progress of some potentially long-running commands
using the corresponding views.
The structures of the views are described in the documentation:
Base backup and COPY will be discussed in Backup Overview lesson.
9
Cumulative Statistics
Views
pg_stat_database database-wide statistics
pg_stat_all_tables/indexes accesses to specific table or index
pg_statio_all_tables/indexes I/O on a specific table or index
pg_stat_io cluster-wide I/O statistics
pg_stat_wal generation of WAL records
pg_stat_bgwriter background writer
pg_stat_checkpointer checkpoints
Settings
track_counts = on collect table and index access statistics
track_io_timing = off monitor block read and write timing statistics
track_wal_io_timing = off monitor write timing of WAL operations
The cumulative statistics system in PostgreSQL collects and provides data
on server operations. Cumulative statistics track access to tables and
indexes at both the disk block and row levels. Additionally, they record
details such as the number of rows, vacuum and analyze operations for
each table.
PostgreSQL also collects statistics on system operations and background
processes.
The amount of information collected is controlled by several server
parameters, since the more information is collected, the greater the
overhead.
10
Architecture
server
process
server
process
backend shared
memory
transaction
statistics
between transactions
cumulative
statistics
PGDATA/pg_stat/
statistics
snapshot
stats_fetch_consistency
normal
server shutdown
cumulative
statistics
none
cache
snapshot
Backends collect statistics in running transactions. The process stores this
data in shared memory, updating it at most once per second (compile-time
setting).
Cumulative statistics are flushed to PGDATA/pg_stat/ during a normal server
shutdown and reloaded upon startup. In case of a crash shutdown, all
counters are reset.
Backend may cache statistical data. The caching level is controlled by the
stats_fetch_consistency parameter:
●
None — no caching, statistics reside only in shared memory
●
Cache — statistics for a single object are cached
●
Snapshot — statistics for the entire database are cached
Cache is the default mode, which balances consistency and performance
efficiency.
Cached statistics are not refreshed and are discarded at the end of a
transaction, or when pg_stat_clear_snapshot() is called.
Due to latency and caching, the backend will not always have the latest
statistics, but it is seldom necessary.
12
Additional Statistics
Supplied extensions
pg_stat_statements query statistics
pgstattuple row versions statistics
pg_buffercache buffer cache status
…
Third-party extensions
pg_wait_sampling statistics for waits
pg_stat_kcache CPU and I/O statistics
…
There are extensions, both additional supplied and third-party, that enable
the collection of additional statistics.
For example, the pg_stat_statements extension collects information about
queries executed by the system, pg_buffercache provides tools for
monitoring the buffer cache, etc.
Many key extensions are discussed in more detail in the DBA2 and DEV2
courses.
13
Server Message Log
What to Log?
Where to Log?
Logging Collector
Log Analysis
The other primary source of information about the state of the server is the
message log.
14
What to Log?
Settings
log_min_messages level of messages
log_min_duration_statement long command execution time
log_duration command execution time
application_name application name
log_checkpoints checkpoints
log_(dis)connections connections and disconnections
log_lock_waits long lock waits
log_statement command executed
log_temp_files temporary file usage
...
A lot of useful information can be output to the server message log. By
default, almost all output is disabled so as not to turn logging into the
bottleneck for the I/O subsystem. The administrator should decide what
information is important, provide the necessary disk space to store it, and
evaluate the impact of the log output on the overall system performance.
15
Where to Log?
PostgreSQL
log_destination
(list of options)
syslog
syslog
daemon
Windows
event log
s
t
d
e
r
r
e
v
e
n
t
l
o
g
external tools
for log rotation
logrotate
The server log can be forwarded to various destinations, which is
determined primarily by the log_destination parameter (you can list multiple
receivers separated by a comma).
The default stderr value sends messages to the standard error stream as
plain text, which can be redirected to a file. If all the log output goes into a
single file, sooner or later it will grow to an unmanageable size, making
administration and analysis highly inconvenient. Therefore, a log rotation
scheme is usually employed. For example, Ubuntu package uses logrotate
system utility (it is configured by the /etc/logrotate.d/postgresql-common file).
The syslog value forwards messages to the syslog daemon (for Unix
systems), and the eventlog value does the same for the Windows event log.
16
Logging Collector
PostgreSQL
log_destination
(list of options)
logging
collector
cvslog
s
t
d
e
r
r
j
s
o
n
l
o
g
log_filename %H.log %a.log
log_rotation_age 1h 1d
log_rotation_size
log_truncate_on_rotation = on
log_directory/log_filename
plain text
CSV, *.csv
JSON, *.json
24 files a day
7 files a week
and other rotation schemes
The logging collector is an auxiliary process that collects messages sent to
the standard error stream and redirects them to log files in various formats. It
is controlled by the logging_collector parameter and is on by default. The
collector is designed to never lose messages (unlike syslog), therefore it can
become the bottleneck in high-load environments.
For stderr, the messages are written into the file defined by the log_filename
parameter, which is located in the directory defined by the log_directory
parameter. When csvlog is selected as a receiver, the messages will be
output to log_filename.csv file in CSV format. With the jsonlog output
enabled, log files are written in JSON format and use the .json extension.
The logging collector has its own rotation capability.
The log_filename parameter can specify not just a name, but a file name
pattern using designated date and time characters.
The log_rotation_age parameter determines how long a file is used before
the output switches to a new one (and log_rotation_size is the file size at
which to switch to the next one).
The log_truncate_on_rotation flag determines if PostgreSQL should
overwrite existing files or append messages to them.
Different rotation schemes can be defined by using various file name
patterns and switch time combinations.
17
Log Analysis
OS tools
grep, awk...
Special analysis tools
pgBadger — requires a certain log configuration
There are different ways to analyze logs.
You can search for certain information using OS tools or specially designed
scripts.
The de facto standard for log analysis is the PgBadger application
https://github.com/darold/pgbadger, but it imposes certain restrictions on the
contents of the log. In particular, only messages in English are allowed.
19
External Monitoring
Universal monitoring systems
Zabbix, Munin, Cacti...
cloud-based: Okmeter, NewRelic, Datadog...
PostgreSQL monitoring systems
pg_profile, pgpro_pwr
PGObserver
PostgreSQL Workload Analyzer (PoWA)
Open PostgreSQL Monitoring (OPM)
...
In practice, you need a full-fledged monitoring system that collects various
metrics from both PostgreSQL and the operating system, stores the history
of these metrics, displays them as readable graphs, notifies when certain
metrics exceed certain limits, etc.
PostgreSQL does not come with such a system by itself, it only provides the
means by which such information can be acquired. We have gone over them
already. Therefore, for full-scale monitoring, an external system is required.
There are quite a few such systems on the market. Some are universal and
come with PostgreSQL plugins or agents. These include Zabbix, Munin,
Cacti, cloud services such as Okmeter, NewRelic, Datadog, and others.
There are also systems specifically designed for PostgreSQL: PGObserver,
PoWA, OPM, etc. The pg_profile extension allows you to build snapshots of
static data and compare them, identifying resource-intensive operations and
their dynamics. pgpro_pwr is its extended, commercially available version.
An incomplete but representative list of monitoring systems can be viewed
20
Takeaways
Monitoring collects data on server operations both from the
operating system and from the database server points of view
PostgreSQL provides statistics views and message log
Full-scale monitoring in production environment requires an
external system
21
Practice
1. In a new database, create a table with autovacuum turned off,
insert several rows, and then delete all rows.
Look at the table access statistics and reference the values
(n_tup_ins, n_tup_del, n_live_tup, n_dead_tup) against your
activity.
Perform a vacuum, check the statistics again and compare with
the previous figures.
2. Create a deadlock with two transactions.
See what information is recorded in the server message log.
1. Use WITH (autovacuum_enabled = off) clause.
2. Deadlock is a situation when two (or more) transactions are waiting for
each other to complete first. Unlike a normal lock, transactions have no way
to get out of deadlock, and the DBMS has to resolve it by forcibly
interrupting one of the transactions.
The easiest way to reproduce a deadlock is on a table with two rows. The
first transaction changes (and locks) the first row, and the second one locks
the second row. Then the first transaction tries to change the second row,
discovers that it is locked, and starts waiting. And then the second
transaction tries to change the first row, and also waits for the lock to be
released.
