lesovsky/pgcenter

pgCenter is a command-line admin tool for observing and troubleshooting Postgres.

• Main goal
• Key features
• Quick start
• Supported statistics
  • PostgreSQL statistics
  • System statistics
• Install notes
• Usage notes
• Development, testing and contribution
• Thanks

Main goal

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 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 working with statistics and provide a convenient way to observe Postgres in runtime.

Key features

• Top-like interface that allows you to monitor stats changes as you go. See details here.
• Configuration management function allows viewing and editing of current configuration files and reloading the service, if needed.
• Logfiles functions allow you to quickly check Postgres logs without stopping statistics monitoring.
• "Poor man’s monitoring" allows you to collect Postgres statistics into files and build reports later on. See details here.
• Wait events profiler allows seeing what wait events occur during queries execution. See details here.

Quick start

Pull Docker image from DockerHub; run pgcenter and connect to the database.

docker pull lesovsky/pgcenter:latest
docker run -it --rm lesovsky/pgcenter:latest pgcenter top -h 1.2.3.4 -U user -d dbname

Supported statistics

PostgreSQL statistics

• summary activity - a compilation/selection of metrics from different sources - postgres uptime, version, recovery status, number of clients grouped by their states, number of (auto)vacuums, statements per second, age of the longest transaction and the longest vacuum;
• pg_stat_activity - activity of connected clients and background processes.
• pg_stat_database - database-wide and sessions statistics, such as number of commits/rollbacks, processed tuples, deadlocks, temporary files, etc.
• pg_stat_replication - replication statistics, like connected standbys, their activity and replication lag.
• pg_stat_user_tables, pg_statio_user_tables - statistics on accesses (including IO) to tables.
• pg_stat_user_indexes, pg_statio_user_indexes - statistics on accesses (including IO) to indexes.
• pg_stat_user_functions - statistics on execution of functions.
• pg_stat_wal - WAL usage statistics.
• pg_stat_statements - statistics on SQL statements executed including time and resources usage.
• statistics on tables sizes based on pg_relation_size() and pg_total_relation_size() functions;
• pg_stat_progress_vacuum - progress of (auto)vacuums operations.
• pg_stat_progress_cluster - progress of CLUSTER and VACUUM FULL operations.
• pg_stat_progress_create_index - progress of CREATE INDEX and REINDEX operations.
• pg_stat_progress_analyze - progress of ANALYZE operations.
• pg_stat_progress_basebackup - progress of basebackup operations.
• pg_stat_progress_copy - progress of COPY operations.

System statistics

pgcenter top also provides system usage information based on statistics from procfs filesystem:

• load average and CPU usage time (user, system, nice, idle, iowait, software, and hardware interrupts, steal);
• memory and swap usage, amount of cached and dirty memory, writeback activity;
• storage devices statistics: IOPS, throughput, latencies, average queue and requests size, devices utilization;
• network interfaces statistics: throughput in bytes and packets, different kind of errors, saturation and utilization.
• mounted filesystems' usage statistics: total size, amount of free/used/reserved space and inodes.

In the case of connecting to remote Postgres, there is possibility to use additional SQL functions used for retrieving /proc statistics from a remote host. For more information, see details here.

Install notes

Packages for DEB, RPM, APK are available on releases page.

Usage notes

pgCenter has been developed to work on Linux and hasn't been tested on other OS (operating systems); therefore, it is not recommended using it on alternative systems because it will not operate properly.

pgCenter supports a wide range of PostgreSQL versions, despite the difference in statistics between each version. If pgCenter is unable to read a particular stat, it will show a descriptive error message.

Ideally, pgCenter requires SUPERUSER database privileges, or at least privileges to view statistics, read settings, logfiles and send signals to other backends. Roles with such privileges (except reading logs) have been introduced in Postgres 10; see details here.

It is recommended to run pgCenter on the same host where Postgres is running. This is because for Postgres, pgCenter is just a simple client application, and it may have the same problems as other applications that work with Postgres, such as network-related problems, slow responses, etc.

It is possible to run pgCenter on one host and connect to Postgres, which runs on another host, but some functions may not work - this fully applies to pgcenter top command.

pgCenter also supports Amazon RDS for PostgreSQL, but as mentioned above, some functions will not work, and also system stats will not be available, because of PostgreSQL RDS instances don't support untrusted procedural languages due to security reasons.

Development, testing and contribution

To help development you are encouraged to:

• provide suggestion/feedback or issue (follow the provided issue template carefully).
• pull requests for bug fixes of improvements; this docs might be helpful.
• star the project

Thanks

• Thank you for using pgCenter!
• Sebastien Godard for sysstat.
• Brendan Gregg and Tim Cook for nicstat.
• Pavel Stěhule for his articles.
• Pavel Alexeev, package maintainer on EPEL testing repo (Fedora/Centos).
• Manuel Rüger, ebuild maintainer on mrueg overlay (Gentoo Linux).
• Anton Novojilov, package maintainer on RHEL/CentOS Linux (Essential Kaos repo).
• Nikolay A. Fetisov, package maintainer at Sisyphus ALT Linux.
• Devrim Gündüz, package maintainer on official PostgreSQL yum repo.