About

[postgres-checkup](https://gitlab.com/postgres-ai-team/postgres-checkup) is an ultimate open-source PostgreSQL database health check utility.

Postgres Checkup ([postgres-checkup](https://gitlab.com/postgres-ai-team/postgres-checkup)) is a new-generation diagnostics tool that allows to collect deep analysis of a Postgres database health. It aims to detect and describe all current and potential issues in the fields of database performance, scalability and security, advising how to resolve or prevent them.

See its short descriptionwiththe full list of checks implementedandplanned here:https://gitlab.com/postgres-ai-team/postgres-checkup/wikis/HEALTH-CHECK-v3

Compared to a monitoring system, postgres-checkup goes deeper in database system and environment analysis, it combines numerious database internal characteristicswithdata about resources and OS into multiple comprehensive reports. These reports use formats which are easy readable both by humansandmachines and are extremely oriented to DBA problems solving. Monitoring systems constantly collect telemetry, they help to react to issues quicker and do post-mortem analyses. Checkups are needed for a different purpose: detect issues at very early stage, advising on how to prevent them. This procedure is to be done on a regular basis — weekly, monthly, or quarterly. Additionally, it is recommended to run it right right before and right after any major change in database server.

The observed data is saved in the form of JSON reports, ready to be consumed by machines.

The final reports are .md files, in Markdown format, to be read by humans.

The three key principles behind postgres-checkup:

The main goalis todetect bottlenecks and help to prevent performance and scalability issues.

-*Unobtrusiveness*: postgres-checkup’s impact on the observing systemisclosetozero. It does not use any heavy queries, keeping resource usage very low, avoids having the “observer effect”.

Example

-*Zero install* (on observed machines): it is able to analyze any Linux machine (including virtual machines), as well as Cloud Postgres instanced (such as Amazon RDs or Google Cloud SQL), not requiring any additional setup or any changes. It does, hovewer, require the privileged access (a DBA usually has it anyway).

Let's make a report for a project named`my-site_org-slony`:

Cluster`slony` contains two servers -`db1` and`db2`.

-*Complex analysis*: unlike most monitoring tools, which provide raw data, postgres-checkup combines data from various parts of the system (w.g.: internal Postgres stats are combined with knowledge about system resources in autovacuum setting and behavior analysis). Also, it analyzes the master database server together with all its replicas (e.g. to build the list of unused indexes).

The two kinds of reports postgres-checkup produces for every check:

- JSON reports (*.json) — can be consumed by any program or service, or stores in some database.

- Markdown reports (*.md) — the main format for humans, may contain lists, tables, pictures. Being of native format for GitLab and GitHub, such reports are ready to be used, for instance, in their issue trackers, simplifying workflow. Markdown reports are derived from JSON reports.

Markdown reports can be converted to different formats such as HTML or PDF.

Each report consists of three sections:

1. Observations

1. Conclusions

1. Recommendations

The supported OS of the observer machine (those from which the tool is to be executed):

* Linux (modern RHEL/CentOS or Debian/Ubuntu; others should work as well, but not yet tested);

* MacOS.

It has to have the following programs:

* bash

* psql

* coreutils

* jq >= 1.5

* golang >= 1.8

* awk

* sed

Nothing special has to be istalled on the observed machines. However, these machines must run Linux (again: modern RHEL/CentOS or Debian/Ubuntu; others should work as well, but not yet tested).

Let's make a report for a project named`prod1`:

Cluster`slony` contains two servers -`db1.vpn.local` and`db1.vpn.local`.

Postgres-checkup automatically detects which one is a master:

./checkup -h db1 -p 5432 --username postgres --dbname postgres --projectmy-site_org-slony

./checkup -h db1.vpn.local -p 5432 --username postgres --dbname postgres --projectprod1

./checkup -h db2 -p 5432 --username postgres --dbname postgres --projectmy-site_org-slony -e 1

./checkup -h db2.vpn.local -p 5432 --username postgres --dbname postgres --projectprod1 -e 1

Which literally means: "connect to the server with given credentials, save data into`my-site_org-slony`

Which literally means: "connect to the server with given credentials, save data into`prod1`

project directory as epoch of check`1`. Epoch is a numerical (**integer**) sign of current iteration.

For example: in half a year we can switch to "epoch number`2`".

As a result of postgres-checkup we have got two directories with .json files and .md files:

./artifacts/my-site_org-slony/json_reports/1_2018_12_06T14_12_36_+0300/

./artifacts/my-site_org-slony/md_reports/1_2018_12_06T14_12_36_+0300/

./artifacts/prod1/json_reports/1_2018_12_06T14_12_36_+0300/

./artifacts/prod1/md_reports/1_2018_12_06T14_12_36_+0300/

Each of generated files contains information about "what we check" and collected data for

all instances of the postgres cluster`my-site_org-slony`.

all instances of the postgres cluster`prod1`.

Human-readable report can be found at:

./artifacts/my-site_org-slony/e1_full_report.md

./artifacts/prod1/e1_full_report.md

Open it with your favorite Markdown files viewer or just upload to a service such as gist.github.com.

Requirements

* bash

* psql

* coreutils

* jq >= 1.5

* golang >= 1.8

* awk

* sed