gem-hunter

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

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.

• Top-like interface that allows you to monitor stats changes as you go. See details here.
• Per-process system stats (Shift+S): see CPU utilization, IO throughput, and IO wait time per PostgreSQL backend alongside query text — without leaving pgcenter. Instantly identify whether a slow query is CPU-bound or IO-bound.
• 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. Per-process stats are recorded automatically and can be replayed with pgcenter report -N for post-mortem analysis.
• Wait events profiler allows seeing what wait events occur during queries execution. See details here.

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

• 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.

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.

pgcenter top includes a per-process stats screen (press Shift+S) that combines pg_stat_activity with per-backend CPU and IO metrics from /proc/[pid]/stat and /proc/[pid]/io. Shows CPU utilization (%all, %us, %sy), IO throughput (read,KiB/s, write,KiB/s), IO delay (%iodelay), and accumulated totals since process start. Available in local mode only.

These metrics are also captured by pgcenter record and can be replayed with pgcenter report -N for post-mortem analysis.

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.

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

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.

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

• 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.