# Lesson 12: Administrative Tasks / Monitoring > Native Markdown source page: Administrative Tasks / Monitoring --- LLMS index: [llms.txt](/en/llms.txt) --- > [!NOTE] > This page is extracted from the official PDF into native Markdown. Slide text, code and speaker notes are kept in reading order; the original PDF and HTML remain the layout references. > **HTML source:** [dba1_12_admin_monitoring.html](https://edu.postgrespro.com/16/dba1-16-en/dba1_12_admin_monitoring.html) > **Archive:** [DBA1-handouts-16-en.zip](https://edu.postgrespro.com/16/DBA1-handouts-16-en.zip) > **PDF pages:** 30 ## Page 1: Administrative Tasks / Monitoring {#source-page-001} ### Copyright © Postgres Professional, 2017–2025 Authors: Egor Rogov, Pavel Luzanov, Ilya Bashtanov, Aleksei Beresnev Translated by: Liudmila Mantrova, Alexander Meleshko, Elena Sharafutdinova Photo: Oleg Bartunov (Phu Monastery and Bhrikuti Peak, Nepal) ### 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. ## Page 2: Topics {#source-page-002} - OS Tools - Сumulative Statistics - Server Message Log - External Monitoring Systems ## Page 3: OS Tools {#source-page-003} **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... ### Speaker notes 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. ## Page 4: Сumulative Statistics {#source-page-004} **Statistics Collection** - Current System Activities - Command Execution Monitoring - Extensions ### Speaker notes There are two primary sources of information about the state of the system. The first one is statistical information collected by PostgreSQL and stored inside the cluster. ## Page 5: Statistics Collection {#source-page-005} **Settings of cumulative statistics** - parameter - action - track\_activities - monitor current commands - track\_counts - collect table and index access statistics - track\_functions - monitor user function calls - off by default - track\_io\_timing - monitor block read and write timing statistics - off by default - track\_wal\_io\_timing - monitor write timing of WAL operations - off by default ### Speaker notes 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. It is also possible to track the number of user function calls and their execution time. The amount of information collected is controlled by several server parameters, since the more information is collected, the greater the overhead. ## Page 6: Architecture {#source-page-006} ![Source slide page 6: Architecture](images/page-006.png){#source-figure-006 caption="Source slide page 6: Architecture"} ### Speaker notes 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 stored in 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. ## Page 7: Cumulative Statistics {#source-page-007} ```sql => CREATE DATABASE admin_monitoring; ``` ```sql CREATE DATABASE ``` ```sql => \c admin_monitoring ``` ```text You are now connected to database "admin_monitoring" as user "student". ``` Enable collection of I/O statistics first: ```sql => ALTER SYSTEM SET track_io_timing=on; ``` ```sql ALTER SYSTEM ``` ```sql => SELECT pg_reload_conf(); ``` ```text pg_reload_conf ---------------- t (1 row) ``` Monitoring server activity only makes sense if there is any activity. We can imitate load with pgbench, a standard benchmarking utility. First, it creates a number of tables and fills them with data. ```sh student$ pgbench -i admin_monitoring ``` ```sql dropping old tables... NOTICE: table "pgbench_accounts" does not exist, skipping NOTICE: table "pgbench_branches" does not exist, skipping NOTICE: table "pgbench_history" does not exist, skipping NOTICE: table "pgbench_tellers" does not exist, skipping creating tables... generating data (client-side)... 100000 of 100000 tuples (100%) done (elapsed 0.27 s, remaining 0.00 s) vacuuming... creating primary keys... done in 0.65 s (drop tables 0.00 s, create tables 0.03 s, client-side generate 0.31 s, vacuum 0.13 s, primary keys 0.18 s). ``` Let’s reset the previously accumulated database statistics. ```sql => SELECT pg_stat_reset(); ``` ```text pg_stat_reset --------------- (1 row) ``` Including instance I/O statistics: ```sql => SELECT pg_stat_reset_shared('io'); ``` ```text pg_stat_reset_shared ---------------------- (1 row) ``` Start the TPC-B test and let it run for a few seconds: ```sh student$ pgbench -T 10 admin_monitoring ``` ```sql pgbench (16.11 (Ubuntu 16.11-1.pgdg24.04+1)) starting vacuum...end. transaction type: scaling factor: 1 query mode: simple number of clients: 1 number of threads: 1 maximum number of tries: 1 duration: 10 s number of transactions actually processed: 1119 number of failed transactions: 0 (0.000%) ``` ## Page 8: Page 8 {#source-page-008} ```text latency average = 8.933 ms initial connection time = 10.450 ms tps = 111.944576 (without initial connection time) ``` Now, let’s check the statistics on table access in terms of rows: ```sql => SELECT * FROM pg_stat_all_tables WHERE relid = 'pgbench_accounts'::regclass \gx ``` ```text -[ RECORD 1 ]-------+------------------------------ relid | 16393 schemaname | public relname | pgbench_accounts seq_scan | 0 last_seq_scan | seq_tup_read | 0 idx_scan | 2238 last_idx_scan | 2025-11-27 13:51:06.378593+03 idx_tup_fetch | 2238 n_tup_ins | 0 n_tup_upd | 1119 n_tup_del | 0 n_tup_hot_upd | 290 n_tup_newpage_upd | 829 n_live_tup | 0 n_dead_tup | 1062 n_mod_since_analyze | 1119 n_ins_since_vacuum | 0 last_vacuum | last_autovacuum | last_analyze | last_autoanalyze | vacuum_count | 0 autovacuum_count | 0 analyze_count | 0 autoanalyze_count | 0 ``` And in terms of tables: ```sql => SELECT * FROM pg_statio_all_tables WHERE relid = 'pgbench_accounts'::regclass \gx ``` ```text -[ RECORD 1 ]---+----------------- relid | 16393 schemaname | public relname | pgbench_accounts heap_blks_read | 0 heap_blks_hit | 7824 idx_blks_read | 273 idx_blks_hit | 5866 toast_blks_read | toast_blks_hit | tidx_blks_read | tidx_blks_hit | ``` There are similar views for indexes: ```sql => SELECT * FROM pg_stat_all_indexes WHERE relid = 'pgbench_accounts'::regclass \gx ``` ```text -[ RECORD 1 ]-+------------------------------ relid | 16393 indexrelid | 16407 schemaname | public relname | pgbench_accounts indexrelname | pgbench_accounts_pkey idx_scan | 2238 last_idx_scan | 2025-11-27 13:51:06.378593+03 idx_tup_read | 3071 idx_tup_fetch | 2238 ``` ```sql => SELECT * FROM pg_statio_all_indexes WHERE relid = 'pgbench_accounts'::regclass \gx ``` ## Page 9: Page 9 {#source-page-009} ```text -[ RECORD 1 ]-+---------------------- relid | 16393 indexrelid | 16407 schemaname | public relname | pgbench_accounts indexrelname | pgbench_accounts_pkey idx_blks_read | 273 idx_blks_hit | 5866 ``` These views can be used to pinpoint unused indexes. Such indexes not only occupy useful space on the disk, but also waste resources on updates every time data in the table changes. There are also views for user-defined and system objects (all, user, sys), current transaction statistics (pg\_stat\_xact\*), and more. You can view global statistics across the whole database: ```sql => SELECT * FROM pg_stat_database WHERE datname = 'admin_monitoring' \gx ``` ```text -[ RECORD 1 ]------------+----------------------------- datid | 16386 datname | admin_monitoring numbackends | 1 xact_commit | 1138 xact_rollback | 0 blks_read | 275 blks_hit | 23537 tup_returned | 18052 tup_fetched | 3371 tup_inserted | 1119 tup_updated | 3358 tup_deleted | 0 conflicts | 0 temp_files | 0 temp_bytes | 0 deadlocks | 0 checksum_failures | checksum_last_failure | blk_read_time | 10.404 blk_write_time | 0.697 session_time | 22702.047 active_time | 9444.514 idle_in_transaction_time | 516.089 sessions | 2 sessions_abandoned | 0 sessions_fatal | 0 sessions_killed | 0 stats_reset | 2025-11-27 13:50:55.98728+03 ``` It provides a lot of data on the number of deadlocks occurred, committed and cancelled transactions, utilization of temporary files, and checksum errors. It also maintains total session count and statistics on sessions terminated for various reasons. The numbackends column specifically indicates the current number of active backend processes connected to this database. For monitoring I/O operations at the server level, administrators can query the pg\_stat\_io view. Let’s first execute a checkpoint operation, then examine the resulting page read and write counts categorized by process type: ```text => CHECKPOINT; ``` ```text CHECKPOINT ``` ```sql => SELECT backend_type, sum(hits) hits, sum(reads) reads, sum(writes) writes FROM pg_stat_io GROUP BY backend_type; ``` ## Page 10: Page 10 {#source-page-010} ```text backend_type | hits | reads | writes ---------------------+-------+-------+-------- background worker | 0 | 0 | 0 client backend | 23723 | 275 | 0 walsender | 0 | 0 | 0 standalone backend | 0 | 0 | 0 autovacuum worker | 559 | 0 | 0 autovacuum launcher | 0 | 0 | 0 background writer | | | 0 startup | 0 | 0 | 0 checkpointer | | | 2874 (9 rows) ``` ## Page 11: Current Activities {#source-page-011} **Configuration** - statistics - parameter - current activities and waits - track\_activities - of backends - on by default - and background processes ### Speaker notes The current activities of all backends and background processes are displayed in the pg\_stat\_activity view. We will focus on it more in the demo. This view depends on the track\_activities parameter (enabled by default). ## Page 12: Current Activities {#source-page-012} ![Source slide page 12: Current Activities](images/page-012.png){#source-figure-012 caption="Source slide page 12: Current Activities"} Let’s imitate a scenario when one process blocks another, and then figure it out using system views. Create a table with one row: ```sql => CREATE TABLE t(n integer); ``` ```sql CREATE TABLE ``` ```sql => INSERT INTO t VALUES(42); ``` ```sql INSERT 0 1 ``` Start two sessions, one of which changes the table and does not complete the transaction: ```sh student$ psql -d admin_monitoring ``` ```text => BEGIN; ``` ```text BEGIN ``` ```sql => UPDATE t SET n = n + 1; ``` ```sql UPDATE 1 ``` And the other tries to change the same row and gets blocked: ```sh student$ psql -d admin_monitoring ``` ```sql => UPDATE t SET n = n + 2; ``` View data about backend processes: ```sql => SELECT pid, query, state, wait_event, wait_event_type, pg_blocking_pids(pid) FROM pg_stat_activity WHERE backend_type = 'client backend' \gx ``` ```sql -[ RECORD 1 ]----+----------------------------------------------------------------------------- pid | 22152 query | UPDATE t SET n = n + 1; state | idle in transaction wait_event | ClientRead wait_event_type | Client pg_blocking_pids | {} -[ RECORD 2 ]----+----------------------------------------------------------------------------- pid | 21005 query | SELECT pid, query, state, wait_event, wait_event_type, pg_blocking_pids(pid)+ | FROM pg_stat_activity + | WHERE backend_type = 'client backend' state | active wait_event | wait_event_type | pg_blocking_pids | {} -[ RECORD 3 ]----+----------------------------------------------------------------------------- pid | 22245 query | UPDATE t SET n = n + 2; state | active wait_event | transactionid wait_event_type | Lock pg_blocking_pids | {22152} ``` The state “idle in transaction” means that the session has started a transaction, but isn’t doing anything at the moment, and the transaction isn’t closed. This could become a problem if the situation comes up regularly (for example, because of poor application code or driver errors), because an open transaction holds a data snapshot and prevents vacuuming. The administrator has a parameter idle\_in\_transaction\_session\_timeout at their disposal to force sessions to close after their transaction is idle for a certain period of time. The idle\_session\_timeout parameter forcibly terminates sessions that remain idle beyond the specified duration. You can also terminate a blocking session manually. First, you need the blocked process ID. The function pg\_blocking\_pids can help you with that: ## Page 13: Page 13 {#source-page-013} ```sql => SELECT pid AS blocked_pid FROM pg_stat_activity WHERE backend_type = 'client backend' AND cardinality(pg_blocking_pids(pid)) > 0; ``` ```text blocked_pid ------------- 22245 (1 row) ``` Instead, you can use a query on the locks table. It will return two rows in this case: one transaction has been granted the lock, while the other is waiting for it. ```sql => SELECT locktype, transactionid, pid, mode, granted FROM pg_locks WHERE transactionid IN ( SELECT transactionid FROM pg_locks WHERE pid = 22245 AND NOT granted ); ``` ```text locktype | transactionid | pid | mode | granted ---------------+---------------+-------+---------------+--------- transactionid | 1871 | 22245 | ShareLock | f transactionid | 1871 | 22152 | ExclusiveLock | t (2 rows) ``` Generally, you have to keep the lock type in mind. Running query can be cancelled with the pg\_cancel\_backend function. The transaction is idle in our case, so we use the pg\_terminate\_backend to terminate the session: ```sql => SELECT pg_terminate_backend(b.pid) FROM unnest(pg_blocking_pids(22245)) AS b(pid); ``` ```text pg_terminate_backend ---------------------- t (1 row) ``` The unnest function is necessary because pg\_blocking\_pids returns an array of process IDs that block the specified process. There is only one in our examples, but there can be multiple. Locks are discussed in more detail in the DBA2 course. Check the backends: ```sql => SELECT pid, query, state, wait_event, wait_event_type FROM pg_stat_activity WHERE backend_type = 'client backend' \gx ``` ```sql -[ RECORD 1 ]---+------------------------------------------------------ pid | 21005 query | SELECT pid, query, state, wait_event, wait_event_type+ | FROM pg_stat_activity + | WHERE backend_type = 'client backend' state | active wait_event | wait_event_type | -[ RECORD 2 ]---+------------------------------------------------------ pid | 22245 query | UPDATE t SET n = n + 2; state | idle wait_event | ClientRead wait_event_type | Client ``` Only two remain, and the blocked one has completed its transaction successfully. The pg\_stat\_activity view shows the information not only about backend processes, but also about the system background processes running on the instance: ```sql => SELECT pid, backend_type, backend_start, state FROM pg_stat_activity; ``` ## Page 14: Page 14 {#source-page-014} ```text pid | backend_type | backend_start | state -------+------------------------------+-------------------------------+-------- 20908 | autovacuum launcher | 2025-11-27 13:50:46.90002+03 | 20909 | logical replication launcher | 2025-11-27 13:50:46.901748+03 | 21005 | client backend | 2025-11-27 13:50:54.68677+03 | active 22245 | client backend | 2025-11-27 13:51:14.373422+03 | idle 20905 | background writer | 2025-11-27 13:50:46.818366+03 | 20904 | checkpointer | 2025-11-27 13:50:46.817735+03 | 20907 | walwriter | 2025-11-27 13:50:46.898508+03 | (7 rows) ``` Compare that to what the OS reports: ```sh student$ sudo head -n 1 /var/lib/postgresql/16/main/postmaster.pid ``` ```text 20903 ``` ```sh student$ ps -o pid,command --ppid 20903 ``` ```text PID COMMAND 20904 postgres: 16/main: checkpointer 20905 postgres: 16/main: background writer 20907 postgres: 16/main: walwriter 20908 postgres: 16/main: autovacuum launcher 20909 postgres: 16/main: logical replication launcher 21005 postgres: 16/main: student admin_monitoring [local] idle 22245 postgres: 16/main: student admin_monitoring [local] idle ``` ## Page 15: Command Execution {#source-page-015} **Views for monitoring command executions** - command - execution - ANALYZE - pg\_stat\_progress\_analyze - CREATE INDEX, REINDEX - pg\_stat\_progress\_create\_index - VACUUM - pg\_stat\_progress\_vacuum - including autovacuuming - CLUSTER, VACUUM FULL - pg\_stat\_progress\_cluster - Create base backup - pg\_stat\_progress\_basebackup - COPY - pg\_stat\_progress\_copy ### Speaker notes 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: Backup is discussed in the Backup module. ## Page 16: Additional Statistics {#source-page-016} **Additional supplied extensions** - pg\_stat\_statements - query statistics - pgstattuple - row versions statistics - pg\_buffercache - buffer cache status - Other extensions - pg\_wait\_sampling - statistics for waits - pg\_stat\_kcache - CPU and I/O statistics - pg\_qualstats - predicate statistics - ... ### Speaker notes 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. ## Page 17: Server Message Log {#source-page-017} **Log Record Configuration** - Log File Rotation - Log Analysis ### Speaker notes The other primary source of information about the state of the server is the message log. ## Page 18: Server Message Log {#source-page-018} **Message receiver (log\\\_destination = list)** - stderr - error stream - csvlog - CSV format (if the collector is enabled) - jsonlog - JSON format (if the collector is enabled) - syslog - the syslog daemon - eventlog - Windows event log - Message collector (logging\_collector = on) - can provide additional info - never loses messages (unlike syslog) - writes stderr, csvlog and jsonlog to the log\_directory/log\_filename ### Speaker notes The server log can be output in various formats and forwarded to various destinations. The format and the destination are determined primarily by the log\_destination parameter (you can list multiple receivers separated by a comma). The stderr value (on by default) sends messages to the standard error stream as plain text. The syslog value forwards messages to the syslog daemon (for Unix systems), and the eventlog value does the same for the Windows event log. The message collector is an auxiliary process that collects additional information from all PostgreSQL processes to supplement the basic log messages. It is designed to keep track of every message, therefore it can become the bottleneck in high-load environments. The message collector is switched on and off by the logging\_collector parameter. When stderr is enabled, 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 the collector is on and csvlog is selected as a receiver, the info will also be output into a CSV file log\_filename.csv. With the jsonlog output enabled, log files are written in JSON format and use the .json file extension. ## Page 19: What to Log? {#source-page-019} **Settings** - information - parameter - level of messages - log\_min\_messages - long command execution time - log\_min\_duration\_statement - command execution time - log\_duration - application name - application\_name - checkpoints - log\_checkpoints - connections and disconnections - log\_(dis)connections - long lock waits - log\_lock\_waits - command executed - log\_statement - temporary file usage - log\_temp\_files - ... ### Speaker notes 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. ## Page 20: Log File Rotation {#source-page-020} **By the message collector** - setting - parameter - file name pattern - log\_filename - rotation time, minutes - log\_rotation\_age - rotation file size, KB - log\_rotation\_size - overwrite file - log\_truncate\_on\_rotation = on - combining file name patterns and rotation times allow for different rotation - schemes: - 'postgresql-%H.log', '1h' - 24 files a day - 'postgresql-%a.log', '1d' - 7 files a week - External tools - logrotate system utility ### Speaker notes If all the log output goes into a single file, sooner or later the file will grow to an unmanageable size, making administration and analysis highly inconvenient. Therefore, a log rotation scheme is usually employed. The message collector has its own rotation tools. Some of the parameters that configure them are listed on the slide. 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. E-CONFIG-LOGGING-WHERE Alternatively, rotation can be managed by external tools. For example, Ubuntu package uses logrotate system utility (it is configured through the /etc/logrotate.d/postgresql-common file). ## Page 21: Log Analysis {#source-page-021} **OS tools** - grep, awk... - Special analysis tools - pgBadger — requires a certain log configuration ### Speaker notes 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 , but it imposes certain restrictions on the contents of the log. In particular, only messages in English are allowed. ## Page 22: Log Analysis {#source-page-022} Let’s consider a simple case. For example, display all messages of the FATAL level: ```sh student$ sudo grep FATAL /var/log/postgresql/postgresql-16-main.log | tail -n 10 ``` ```text 2025-11-27 13:45:53.443 MSK [2670] student@student FATAL: terminating connection due to administrator command 2025-11-27 13:51:16.330 MSK [22152] student@admin_monitoring FATAL: terminating connection due to administrator command ``` The “terminating connection” message is caused by us terminating the blocking process. Logs are usually used to analyse the queries that execute the longest. We can make the log display all executed commands and their execution times: ```sql => ALTER SYSTEM SET log_min_duration_statement=0; ``` ```sql ALTER SYSTEM ``` ```sql => SELECT pg_reload_conf(); ``` ```text pg_reload_conf ---------------- t (1 row) ``` Now, run a command: ```sql => SELECT sum(random()) FROM generate_series(1,1_000_000); ``` ```text sum -------------------- 500117.78944922757 (1 row) ``` Check the log: ```sh student$ sudo tail -n 1 /var/log/postgresql/postgresql-16-main.log ``` ```sql 2025-11-27 13:51:18.277 MSK [21005] student@admin_monitoring LOG: duration: 405.591 ms statement: SELECT sum(random()) FROM generate_series(1,1_000_000); ``` ## Page 23: External Monitoring {#source-page-023} **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) - ... ### Speaker notes 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 here: ## Page 24: Takeaways {#source-page-024} **Monitoring collects data on server operations both from the operating system and from the database points of view** - PostgreSQL provides cumulative statistics and the server message log - Full-scale monitoring requires an external system ## Page 25: Practice {#source-page-025} 1. In a new database, create a table, 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. ### Speaker notes 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. ## Page 26: Table Access Statistics {#source-page-026} Create a database and a table: ```sql => CREATE DATABASE admin_monitoring; ``` ```sql CREATE DATABASE ``` ```sql => \c admin_monitoring ``` ```text You are now connected to database "admin_monitoring" as user "student". ``` ```sql => CREATE TABLE t(n numeric); ``` ```sql CREATE TABLE ``` ```sql => INSERT INTO t SELECT 1 FROM generate_series(1,1000); ``` ```sql INSERT 0 1000 ``` ```sql => DELETE FROM t; ``` ```sql DELETE 1000 ``` Check access statistics. ```sql => SELECT * FROM pg_stat_all_tables WHERE relid = 't'::regclass \gx ``` ```text -[ RECORD 1 ]-------+------------------------------ relid | 16387 schemaname | public relname | t seq_scan | 1 last_seq_scan | 2025-11-27 14:00:50.589242+03 seq_tup_read | 1000 idx_scan | last_idx_scan | idx_tup_fetch | n_tup_ins | 1000 n_tup_upd | 0 n_tup_del | 1000 n_tup_hot_upd | 0 n_tup_newpage_upd | 0 n_live_tup | 0 n_dead_tup | 1000 n_mod_since_analyze | 2000 n_ins_since_vacuum | 1000 last_vacuum | last_autovacuum | last_analyze | last_autoanalyze | vacuum_count | 0 autovacuum_count | 0 analyze_count | 0 autoanalyze_count | 0 ``` We inserted 1000 rows (n\_tup\_ins = 1000), then removed 1000 rows (n\_tup\_del = 1000). No live row versions remain (n\_live\_tup = 0), all 1000 rows are dead (n\_dead\_tup = 1000). Run vacuuming. ```sql => VACUUM; ``` ```sql VACUUM ``` ```sql => SELECT * FROM pg_stat_all_tables WHERE relid = 't'::regclass \gx ``` ## Page 27: Page 27 {#source-page-027} ```text -[ RECORD 1 ]-------+------------------------------ relid | 16387 schemaname | public relname | t seq_scan | 1 last_seq_scan | 2025-11-27 14:00:50.589242+03 seq_tup_read | 1000 idx_scan | last_idx_scan | idx_tup_fetch | n_tup_ins | 1000 n_tup_upd | 0 n_tup_del | 1000 n_tup_hot_upd | 0 n_tup_newpage_upd | 0 n_live_tup | 0 n_dead_tup | 0 n_mod_since_analyze | 2000 n_ins_since_vacuum | 0 last_vacuum | 2025-11-27 14:00:52.37659+03 last_autovacuum | last_analyze | last_autoanalyze | vacuum_count | 1 autovacuum_count | 0 analyze_count | 0 autoanalyze_count | 0 ``` Dead row versions vacuumed (n\_dead\_tup = 0), vacuuming performed one time (vacuum\_count = 1). ### 2. Deadlocks ```sql => INSERT INTO t VALUES (1),(2); ``` ```sql INSERT 0 2 ``` One transaction locks the first row of the table... ```sh student$ psql ``` ```sql => \c admin_monitoring ``` ```text You are now connected to database "admin_monitoring" as user "student". ``` ```text => BEGIN; ``` ```text BEGIN ``` ```sql => UPDATE t SET n = 10 WHERE n = 1; ``` ```sql UPDATE 1 ``` The other locks the second row... ```sh student$ psql ``` ```sql => \c admin_monitoring ``` ```text You are now connected to database "admin_monitoring" as user "student". ``` ```text => BEGIN; ``` ```text BEGIN ``` ```sql => UPDATE t SET n = 200 WHERE n = 2; ``` ```sql UPDATE 1 ``` Now, the first transaction tries to change the second row and waits for it to release... ```sql => UPDATE t SET n = 20 WHERE n = 2; ``` While the second transaction waits for the first row to release... ```sql => UPDATE t SET n = 100 WHERE n = 1; ``` ...and so a deadlock occurs. The server terminates one of the transactions: ```text ERROR: deadlock detected DETAIL: Process 43139 waits for ShareLock on transaction 739; blocked by process 43007. Process 43007 waits for ShareLock on transaction 740; blocked by process 43139. HINT: See server log for query details. CONTEXT: while updating tuple (0,1) in relation "t" ``` ## Page 28: Page 28 {#source-page-028} The other transaction gets unblocked: ```sql UPDATE 1 ``` Check the message log: ```sh student$ sudo tail -n 8 /var/log/postgresql/postgresql-16-main.log ``` ```sql 2025-11-27 14:00:56.806 MSK [43139] student@admin_monitoring ERROR: deadlock detected 2025-11-27 14:00:56.806 MSK [43139] student@admin_monitoring DETAIL: Process 43139 waits for ShareLock on transaction 739; blocked by process 43007. Process 43007 waits for ShareLock on transaction 740; blocked by process 43139. Process 43139: UPDATE t SET n = 100 WHERE n = 1; Process 43007: UPDATE t SET n = 20 WHERE n = 2; 2025-11-27 14:00:56.806 MSK [43139] student@admin_monitoring HINT: See server log for query details. 2025-11-27 14:00:56.806 MSK [43139] student@admin_monitoring CONTEXT: while updating tuple (0,1) in relation "t" 2025-11-27 14:00:56.806 MSK [43139] student@admin_monitoring STATEMENT: UPDATE t SET n = 100 WHERE n = 1; ``` ## Page 29: Practice+ {#source-page-029} 1. Install the pg\_stat\_statements extension. - Execute several queries. - See what information gets into the pg\_stat\_statements view. ### Speaker notes 1. To install the extension, before executing the CREATE EXTENSION command, change the value of the shared\_preload\_libraries parameter, and restart the server. ## Page 30: 1. The pg_stat_statements Extension {#source-page-030} The extension collects planning and execution statistics for all queries. For the extension to work, a module with the same name has to be loaded. To do that, add the module name to shared\_preload\_libraries and restart the server. This is usually done through postgresql.conf, but for the purpose of the demo we will set it using the ALTER SYSTEM command. ```sql => ALTER SYSTEM SET shared_preload_libraries = 'pg_stat_statements'; ``` ```sql ALTER SYSTEM ``` ```sql => \q ``` ```sh student$ sudo pg_ctlcluster 16 main restart ``` ```sh student$ psql ``` ```sql => CREATE DATABASE admin_monitoring; ``` ```sql CREATE DATABASE ``` ```sql => \c admin_monitoring ``` ```text You are now connected to database "admin_monitoring" as user "student". ``` ```sql => CREATE EXTENSION pg_stat_statements; ``` ```sql CREATE EXTENSION ``` Now, run some queries: ```sql => CREATE TABLE t(n numeric); ``` ```sql CREATE TABLE ``` ```sql => SELECT format('INSERT INTO t VALUES (%L)', x) FROM generate_series(1,5) AS x \gexec ``` ```sql INSERT 0 1 INSERT 0 1 INSERT 0 1 INSERT 0 1 INSERT 0 1 ``` ```sql => DELETE FROM t; ``` ```sql DELETE 5 ``` ```sql => DROP TABLE t; ``` ```sql DROP TABLE ``` Check the statistics for the most frequently executed query. ```sql => SELECT query, calls, total_exec_time FROM pg_stat_statements ORDER BY calls DESC LIMIT 1; ``` ```sql query | calls | total_exec_time ---------------------------+-------+----------------- INSERT INTO t VALUES ($1) | 5 | 0.18377 (1 row) ``` The shared library is no longer required — restoring the original parameter value: ```sql => ALTER SYSTEM RESET shared_preload_libraries; ``` ```sql ALTER SYSTEM ``` ```sql => \q ``` ```sh student$ sudo pg_ctlcluster 16 main restart ```