shared_preload_libraries = 'pg_stat_statements'
pg_stat_statements.track_utility = false

sudo service postgresql restart

create extension pg_stat_statements; 
select pg_stat_statements_reset();

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;

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");

# Конфигурационные урлы для наших БД
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"

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

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

*/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