How pgmetrics Can Transform PostgreSQL Monitoring for DBAs

Monitoring a PostgreSQL database is essential to ensure smooth operations, maintain high performance, and prevent unexpected downtime. While PostgreSQL provides built-in statistics views like pg_stat_activity and pg_stat_database, interpreting these manually can be cumbersome, especially for large production environments. This is where pgmetrics comes into play.

pgmetrics is an open-source, command-line utility designed to gather detailed statistics about PostgreSQL servers and databases, presenting them in a comprehensive, organized, and readable format. Aggregating critical information from multiple sources allows DBAs to understand the state of their database cluster quickly and efficiently.

What is pgmetrics?

pgmetrics collects key metrics about PostgreSQL servers, including cluster information, system resources, configuration parameters, WAL activity, active connections, locks, vacuum progress, tablespaces, roles, and per-database statistics.

Unlike running multiple SQL queries to fetch individual statistics, pgmetrics generates a full report in one command, saving time and reducing the chances of missing important data. It also formats the output clearly, making it easier to spot trends and potential issues.

For DBAs managing production databases, pgmetrics serves as a centralized monitoring tool, providing both high-level overviews and in-depth details for troubleshooting or optimization purposes.

Why is pgmetrics Useful for DBAs?

Database administrators are responsible for keeping databases healthy, performing timely maintenance, optimizing performance, and quickly identifying and resolving issues. pgmetrics offers several advantages in this context:

1. Comprehensive Insights:

pgmetrics collects information about server uptime, PostgreSQL version, transaction IDs, active connections, WAL files, and system resources such as memory, CPU, and swap usage. This provides a complete snapshot of the database environment.

2. Performance Monitoring:

It shows cache hit ratios, transaction rates, rows changed, and temporary file usage. These metrics help DBAs identify performance bottlenecks, inefficient queries, and resource contention before they escalate.

3. Configuration Review:

pgmetrics reports on key PostgreSQL configuration settings, including shared_buffers, work_mem, autovacuum settings, and WAL configurations. This helps DBAs verify that the database is tuned for optimal performance and aligned with workload requirements.

4. Database Health Check:

By monitoring locks, deadlocks, and long-running transactions, pgmetrics allows DBAs to proactively manage potential issues that could affect database stability.

5. Simplified Reporting:

Running pgmetrics is straightforward, requiring a single command to generate a detailed report. This is particularly useful for regular monitoring, audits, or sharing reports with stakeholders.

6. Cross-Database Monitoring:

In multi-database clusters, pgmetrics provides per-database statistics, including transaction activity, cache hit rates, rows modified, and installed extensions. This allows DBAs to monitor usage patterns and resource consumption for each database individually.

7. Proactive Maintenance:

By tracking vacuum progress, autovacuum activity, and table statistics, pgmetrics helps DBAs plan maintenance tasks efficiently and avoid issues such as transaction ID wraparound or table bloat.

Installing pgmetrics

Installing pgmetrics is simple. Before installation, ensure the postgres user has a password:

ALTER USER postgres WITH PASSWORD 'cool';

Follow these steps to install and run pgmetrics:

1. Download the latest release:

wget https://github.com/rapidloop/pgmetrics/releases/download/v1.18.0/pgmetrics_1.18.0_linux_amd64.tar.gz

2. Extract the archive:

tar xvf pgmetrics_1.18.0_linux_amd64.tar.gz
cd pgmetrics_1.18.0_linux_amd64

3. Switch to the postgres user:

sudo su postgres

4. Check available options:

./pgmetrics --help

5. Run pgmetrics to generate a report:

./pgmetrics

Here it will ask password for the user in postgres, so this is why we set password earlier

After running the command, pgmetrics produces a detailed report containing cluster information, system stats, configuration settings, WAL files, active backends, vacuum progress, roles, tablespaces, and database-specific metrics.

Example Insights from pgmetrics

A typical pgmetrics report provides:

PostgreSQL Cluster Info:

Server version, start time, timeline, last checkpoint, REDO LSN, active backends, and transaction IDs.

You can get the result of postgres cluster info like this 

pgmetrics run at: 4 Oct 2025 9:50:38 AM (now)
PostgreSQL Cluster:
    Name:                17/main
    Server Version:      17.6 (Ubuntu 17.6-1.pgdg22.04+1)
    Server Started:      4 Oct 2025 8:35:15 AM (1 hour ago)
    System Identifier:   7556105493778697077
    Timeline:            1
    Last Checkpoint:     4 Oct 2025 9:45:15 AM (5 minutes ago)
    REDO LSN:            4F/228336A0
    Checkpoint LSN:      4F/228336F8 (88 B since REDO)
    Transaction IDs:     oldest = 730, next = 210192, range = 209462
    Notification Queue:  0.0% used
    Active Backends:     1 (max 100)
    Recovery Mode?       no

System Information and configuration parameters:

CPU cores, memory usage, swap, load averages, and disk cache details.

You can get the result of System Information and configuration parameters like below. 

System Information:
    Hostname:            cybrosys
    CPU Cores:           16 x AMD Ryzen 7 7735HS with Radeon Graphics
    Load Average:        0.92
    Memory:              used=8.0 GiB, free=540 MiB, buff=414 MiB, cache=4.7 GiB
    Swap:                used=1.3 MiB, free=30 GiB
    +---------------------------------+-----------------+
    |                         Setting |           Value |
    +---------------------------------+-----------------+
    |                  shared_buffers | 16384 (128 MiB) |
    |                        work_mem |  4096 (4.0 MiB) |
    |            maintenance_work_mem |  65536 (64 MiB) |
    |                    temp_buffers |  1024 (8.0 MiB) |
    |             autovacuum_work_mem |              -1 |
    |                 temp_file_limit |              -1 |
    |            max_worker_processes |               8 |
    |          autovacuum_max_workers |               3 |
    | max_parallel_workers_per_gather |               2 |
    |        effective_io_concurrency |               1 |
    +---------------------------------+-----------------+

WAL Activity:

Number of WAL files, checkpoint details, WAL archiving status, and file sizes.

You can get the result of wal activity related information like below.

WAL Files:
    WAL Archiving?       no
    WAL Files:           57
    +--------------------+----------------+
    |            Setting |          Value |
    +--------------------+----------------+
    |          wal_level |        replica |
    |    archive_timeout |              0 |
    |    wal_compression |            off |
    |       max_wal_size | 1024 (1.0 GiB) |
    |       min_wal_size |    80 (80 MiB) |
    | checkpoint_timeout |            300 |
    |   full_page_writes |             on |
    |      wal_keep_size |              0 |
    +--------------------+----------------+

Background Writer and Vacuum Progress:

Checkpoint rates, total writes, autovacuum activity, and maintenance memory usage.

You can get the result of background writer and vacuum progress related results like below.

BG Writer:
    Checkpoint Rate:     0.00 per min
    Average Write:       0 B per checkpoint
    Total Checkpoints:   0 sched (0.0%) + 0 req (0.0%) = 0
    Total Write:         1.8 GiB, @ 7.3 KiB per sec
    Buffers Allocated:   11422430 (87 GiB)
    Buffers Written:     0 chkpt (0.0%) + 237342 bgw (100.0%) + 0 be (0.0%)
    Clean Scan Stops:    1465
    BE fsyncs:           0
    Counts Since:        1 Oct 2025 9:58:25 AM (2 days ago)
    +------------------------------+--------------+
    |                      Setting |        Value |
    +------------------------------+--------------+
    |               bgwriter_delay |     200 msec |
    |         bgwriter_flush_after | 64 (512 KiB) |
    |        bgwriter_lru_maxpages |          100 |
    |      bgwriter_lru_multiplier |            2 |
    |                   block_size |         8192 |
    |           checkpoint_timeout |      300 sec |
    | checkpoint_completion_target |          0.9 |
    +------------------------------+--------------+

Locks and Active Backends:

 Backends:
    Total Backends:      1 (1.0% of max 100)
    Problematic:         0 waiting on locks, 0 waiting on other, 0 xact too long, 0 idle in xact
Locks:
    +------------+-------------+-------+
    |  Lock Type | Not Granted | Total |
    +------------+-------------+-------+
    |   relation |           0 |     4 |
    | virtualxid |           0 |     1 |
    +------------+-------------+-------+
    |            |           0 |     5 |
    +------------+-------------+-------+