Monitoring and managing a PostgreSQL database is an essential part of database administration. To simplify this task, PostgreSQL developers often rely on specialized tools that provide insights into real-time performance and system metrics. One such powerful command-line tool is pgCenter, a lightweight monitoring utility that works directly from the terminal.
In this blog, you’ll learn what pgCenter is, how to install it, how to use it to monitor PostgreSQL, and what kind of statistics it provides. This is a step-by-step developer guide for setting up and understanding pgCenter in action.
What is pgCenter?
pgCenter is an open-source command-line administration tool for PostgreSQL. It allows you to monitor database performance in real time, view system resource usage, and analyze internal database statistics directly from your terminal.
It provides a live dashboard similar to the Linux top command but specifically designed for PostgreSQL. This makes it extremely useful for database administrators who want quick insights into performance without using complex GUIs.
Install pgCenter from Source
To install pgCenter manually, you can clone its official GitHub repository and build it locally. Run the following commands in your terminal:
git clone https://github.com/lesovsky/pgcenter.git
cd pgcenter
make build
Once the build is complete, check the version and help menu to confirm installation:
./bin/pgcenter --version
You can see result like this
pgcenter v0.9.2 c4d5111-master
You can use the --help flag to see all related flags and their purpose.
./bin/pgcenter --help
You can see the result like given below
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>.
You should see a message confirming pgCenter is installed successfully, along with available commands like top, record, report, and profile.
Connect to PostgreSQL
After installation, you can connect pgCenter to your PostgreSQL database using the following command:
./bin/pgcenter top -h localhost -U postgres -d postgres
This command starts the pgCenter interactive dashboard.
- -h specifies the database host.
- -U specifies the database user.
- -d specifies the database name.
Running pgCenter with Docker
If you prefer not to build pgCenter manually, you can use Docker for quick setup. Pull the latest Docker image and run it directly:
docker pull lesovsky/pgcenter:latest
Then, connect to your PostgreSQL instance:
docker run -it --rm lesovsky/pgcenter:latest pgcenter top -h 1.2.3.4 -U user -d dbname
Replace the host, user, and database name with your own details. This method is convenient because it does not require manual compilation or installation on your system.
Exploring the pgCenter Interface
When you run pgCenter, you’ll see a terminal interface similar to this:
The interface displays live statistics about PostgreSQL and system performance. Let’s break down what each section represents.
Header Section:
Displays system-wide information such as CPU load average, memory usage, swap utilization, and PostgreSQL server state. You can also see PostgreSQL version, uptime, connection count, and the number of active queries.
Activity Section:
Shows details about currently connected clients and running queries. Each row includes information such as:
- Process ID (pid)
- Client address and port
- Database and user names
- Backend type (e.g., client backend)
- Current state and wait events
- Query execution times
- SQL statement being executed
This real-time view helps administrators quickly identify slow queries, blocked sessions, and performance bottlenecks.
Supported Statistics in pgCenter
pgCenter provides access to a wide range of PostgreSQL and system-level statistics, which are valuable for performance analysis and troubleshooting.
PostgreSQL Statistics
Summary Activity: Displays PostgreSQL version, uptime, recovery mode, number of clients, vacuum processes, transaction age, and statement rate.
- pg_stat_activity: Shows activity of connected clients and background processes.
- pg_stat_database: Provides database-wide metrics like commits, rollbacks, deadlocks, and temporary files.
- pg_stat_replication: Displays replication-related metrics such as connected standbys and replication lag.
- pg_stat_user_tables and pg_statio_user_tables: Present statistics on table accesses and I/O performance.
- pg_stat_user_indexes and pg_statio_user_indexes: Give insights into index usage and related I/O operations.
- pg_stat_user_functions: Tracks execution statistics for functions.
- pg_stat_wal: Monitors Write-Ahead Logging (WAL) activity.
- pg_stat_statements: Shows performance data for SQL statements, including time and resource usage.
- pg_stat_progress_vacuum, cluster, create_index, analyze, basebackup, and copy: Display progress for various background operations such as VACUUM, CLUSTER, CREATE INDEX, and COPY.
- Table size statistics: Based on PostgreSQL functions like pg_relation_size() and pg_total_relation_size(), providing size information for tables and indexes.
System Statistics
In addition to PostgreSQL metrics, pgCenter also provides system-level statistics by reading data from the /proc filesystem.
These include:
- Load average and CPU utilization (user, system, idle, and wait times)
- Memory and swap usage, cached memory, and writeback data
- Storage device statistics such as IOPS, throughput, latency, and utilization
- Network interface statistics, including packet and byte throughput, errors, and saturation
- Filesystem statistics showing total size, used space, free space, and inodes
For remote PostgreSQL connections, pgCenter can also retrieve /proc-based statistics using additional SQL functions, allowing remote performance monitoring.
Why pgCenter is Useful for Database Administrators
pgCenter simplifies real-time PostgreSQL monitoring in a terminal environment. It eliminates the need for graphical dashboards while still providing detailed performance information.
Key Benefits:
- Monitors live PostgreSQL sessions and queries
- Identifies slow queries and locks quickly
- Shows both PostgreSQL and system-level performance in one place
- Lightweight and requires no extra dependencies
- Supports recording and reporting statistics for later analysis
Database administrators can use pgCenter to detect performance issues, track long-running transactions, and understand overall system load—all from a single command-line interface.
Conclusion
pgCenter is a versatile and efficient tool for monitoring PostgreSQL performance. Whether you build it from source or run it through Docker, it provides real-time visibility into both database and system metrics.
With features like session tracking, query profiling, and system statistics, pgCenter gives administrators a complete overview of what’s happening inside their PostgreSQL servers. If you’re looking for a simple yet powerful CLI tool for PostgreSQL monitoring, pgCenter is an excellent choice to add to your toolkit.