Skip to content

lesovsky/pgcenter

Repository files navigation

Web site GitHub release Github Actions Go Report Card


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


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