PGHero - dashboard for PostgeSQL database monitoring

Hello everyone. Today I would like to share a recipe for installing the PGHero utility with connecting several databases. PGHero is a simple utility written in Ruby with a minimalistic dashboard for monitoring PostgreSQL database performance.

What PGHero can show us:

statistics on requests: number of calls, average and total execution time (with the ability to store history);
currently active requests;
information about tables: disk space occupied, dates of the last VACUUM and ANALYSE launches;
information about indexes: disk space occupied, presence of duplicated / unused indexes. May also recommend adding an index if you have complex queries with Seq Scan;
statistics on open connections to the database;
displaying basic database settings that affect performance (shared_buffers, work_mem, maintenance_work_mem, etc.)

One of the very convenient features of the utility is to view the dynamics of the average query execution time (based on statistics of the standard PostgreSQL extension – pg_stat_statements).

It looks like this in the PGHero interface:

Database setup

The following steps need to be done for each database that we are going to connect to PGHero.

Requests must be executed under the superuser.

Installing the extension pg_stat_statements (if not already installed):

Open the file postgresql.conf in a text editor and change the line shared_preload_libraries:

shared_preload_libraries="pg_stat_statements"
pg_stat_statements.track_utility = false

Restart the PostgreSQL server:

sudo service postgresql restart

Create an extension and reset statistics:

create extension pg_stat_statements; 
select pg_stat_statements_reset();

Create a separate user for PGHero in the database (in order not to give the utility full rights over the database).

In the next query, we replace these values in angle brackets with our own:

– password for user pghero;

– the name of your database;

– the name of the main role with access to the current database.

CREATE SCHEMA pghero;

-- view queries
CREATE OR REPLACE FUNCTION pghero.pg_stat_activity() RETURNS SETOF pg_stat_activity AS
$$
  SELECT * FROM pg_catalog.pg_stat_activity;
$$ LANGUAGE sql VOLATILE SECURITY DEFINER;

CREATE VIEW pghero.pg_stat_activity AS SELECT * FROM pghero.pg_stat_activity();

-- kill queries
CREATE OR REPLACE FUNCTION pghero.pg_terminate_backend(pid int) RETURNS boolean AS
$$
  SELECT * FROM pg_catalog.pg_terminate_backend(pid);
$$ LANGUAGE sql VOLATILE SECURITY DEFINER;

-- query stats
CREATE OR REPLACE FUNCTION pghero.pg_stat_statements() RETURNS SETOF pg_stat_statements AS
$$
  SELECT * FROM public.pg_stat_statements;
$$ LANGUAGE sql VOLATILE SECURITY DEFINER;

CREATE VIEW pghero.pg_stat_statements AS SELECT * FROM pghero.pg_stat_statements();

-- query stats reset
CREATE OR REPLACE FUNCTION pghero.pg_stat_statements_reset() RETURNS void AS
$$
  SELECT public.pg_stat_statements_reset();
$$ LANGUAGE sql VOLATILE SECURITY DEFINER;

-- improved query stats reset for Postgres 12+ - delete for earlier versions
CREATE OR REPLACE FUNCTION pghero.pg_stat_statements_reset(userid oid, dbid oid, queryid bigint) RETURNS void AS
$$
  SELECT public.pg_stat_statements_reset(userid, dbid, queryid);
$$ LANGUAGE sql VOLATILE SECURITY DEFINER;

-- suggested indexes
CREATE OR REPLACE FUNCTION pghero.pg_stats() RETURNS
TABLE(schemaname name, tablename name, attname name, null_frac real, avg_width integer, n_distinct real) AS
$$
  SELECT schemaname, tablename, attname, null_frac, avg_width, n_distinct FROM pg_catalog.pg_stats;
$$ LANGUAGE sql VOLATILE SECURITY DEFINER;

CREATE VIEW pghero.pg_stats AS SELECT * FROM pghero.pg_stats();

-- create user
CREATE ROLE pghero WITH LOGIN ENCRYPTED PASSWORD '<pghero_password>';
GRANT CONNECT ON DATABASE <db_name> TO pghero;
ALTER ROLE pghero SET search_path = pghero, pg_catalog, public;
GRANT USAGE ON SCHEMA pghero TO pghero;
GRANT SELECT ON ALL TABLES IN SCHEMA pghero TO pghero;

-- grant permissions for current sequences
GRANT SELECT ON ALL SEQUENCES IN SCHEMA public TO pghero;

-- grant permissions for future sequences
ALTER DEFAULT PRIVILEGES FOR ROLE <migrations_user> IN SCHEMA public GRANT SELECT ON SEQUENCES TO pghero;

Installing and running PGHero

Let’s say we have three tables: db_one, db_two and db_three… We want to display statistics for all three in PGHero (along with the history of queries and table sizes). An important point: to store the history of queries and table sizes, you need to create separate tables in one of the databases, where these statistics will be stored.

CREATE TABLE "pghero_query_stats" (
  "id" bigserial primary key,
  "database" text,
  "user" text,
  "query" text,
  "query_hash" bigint,
  "total_time" float,
  "calls" bigint,
  "captured_at" timestamp
);
CREATE INDEX ON "pghero_query_stats" ("database", "captured_at");

CREATE TABLE "pghero_space_stats" (
  "id" bigserial primary key,
  "database" text,
  "schema" text,
  "relation" text,
  "size" bigint,
  "captured_at" timestamp
);
CREATE INDEX ON "pghero_space_stats" ("database", "captured_at");

We will store these tables in the database. db_one (although you can create a separate database for these statistics). Next, we create a configuration file on the server pghero.yml with the following content (substitute the current settings):

# Конфигурационные урлы для наших БД
databases:
  db_one:
    url: postgres://pghero:secret_pass@mydomain.ru:53001/db_one
  db_two:
    url: postgres://pghero:secret_pass@mydomain.ru:53001/db_two
    capture_query_stats: db_one
  db_three:
    url: postgres://pghero:secret_pass@mydomain.ru:53001/db_three
    capture_query_stats: db_one

# Минимальная длительность запросов (в секундах), которые будут считаться долгими
long_running_query_sec: 60

# Минимальная длительность запросов (в миллисекундах), которые будут считаться медленными
slow_query_ms: 250

# Минимальное кол-во вызовов запросов, которые будут считаться медленными
slow_query_calls: 100

# Минимальное количество соединений для показа предупреждения
total_connections_threshold: 100

# Таймаут для explain-запросов
explain_timeout_sec: 10

# Нормализация запросов (замена значений запроса нумерованными параметрами)
filter_data: true

# Basic авторизация
username: pghero
password: secret_pass

# Таймзона
time_zone: "Europe/Moscow"

Let’s move on to the installation. The documentation offers us several ways:

We will use the first method – running as a Docker container. To do this, in the folder with the configuration file pghero.yml you need to add a Docker file with the following content:

docker build -t mypghero .
docker run -ti -p 12345:8080 mypghero

Now we build an image based on the Docker file and launch the container on the desired port:

docker build -t mypghero .
docker run -ti -p 12345:8080 mypghero

The dashboard should now be available at http://123.45.67.89/12345… Do not forget about basic-authorization, we specified the username and password in pghero.yml…

Running cron-jobs to save history

The last stage: you need to configure autorun by the crown of scripts to save the history in the database by requests (capture_query_stats) and table sizes (capture_space_stats).

The documentation recommends running capture_query_stats every 5 minutes, and capture_space_stats – once a day (but here you need to decide on the situation). Run on the command line crontab -e and add lines to run scripts:

*/5 * * * *     /usr/bin/docker run --rm my-pghero bin/rake pghero:capture_query_stats
15 2 * * *     /usr/bin/docker run --rm my-pghero bin/rake pghero:capture_space_stats

That’s all. Thanks for attention.

The demo version of the utility can be look here… Source code and documentation…