PostgreSQL|一个开源的性能仪表盘工具

微信号：digops

这款工具的名称叫“pghero”，主要功能就是通过web来直接查看性能相关的仪表盘。工具的使用也简单，支持主流的Linux操作系统：Ubuntu、Debian、CentOS/RHEL、SLES。

0.展示

0.1 Queries

0.2 Space

0.3 Connections

0.4 Live Queries

0.5 Maintenance

0.6 Explain

0.7 Tune

1. 安装

这里以centos为例，其他系统可自行去github按照文档来部署：

https://github.com/ankane/pghero/blob/master/guides/Linux.md

部署可单独部署在一台独立的服务器上，不必和PostgreSQL数据库放在一起。

sudo wget -O /etc/yum.repos.d/pghero.repo \
  https://dl.packager.io/srv/pghero/pghero/master/installer/el/$(. /etc/os-release && echo $VERSION_ID).repo
sudo yum -y install pghero

2. 配置

步骤一、要求所监视的数据库需要开启pg_stat_statements
。

开启pg_stat_statements需要数据库重启！！！

具体开启的方法如下：

1）停掉数据库，要在数据库参数文件中postgres.conf
添加如下内容

shared_preload_libraries = 'pg_stat_statements'
pg_stat_statements.track = all
pg_stat_statements.max = 10000
track_activity_query_size = 2048

2）通过数据库源码包，进入contrib/pg_stat_statements
目录，执行make && make install
.

在make时，可能会遇到如下错误

-bash-4.2$ make
Makefile:31: ../../src/Makefile.global: No such file or directory
Makefile:32: /contrib/contrib-global.mk: No such file or directory
make: *** No rule to make target `/contrib/contrib-global.mk'.  Stop.

可通过以下方法执行。

make USE_PGXS=1
make USE_PGXS=1 install

3）启动数据库

pg_ctl start -D $PGDATA

步骤二、创建pghero数据库用户

如果使用超级用户postgres可忽略此步骤。

• 普通用户

create user pghero with password 'pghero';

• 需要具备的对象

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 'secret';
GRANT CONNECT ON DATABASETO 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 ROLEIN SCHEMA public GRANT SELECT ON SEQUENCES TO pghero;

步骤三、在数据库中生成扩展

需要注意，生成扩展要切换到制定监控的数据库上，如下，我在要监控的test数据库下创建：

\c test
CREATE EXTENSION pg_stat_statements

步骤四、设置pghero
配置库

这个配置库，并非是要监控的数据库，而是要存储一些pghero信息的库

sudo pghero config:set DATABASE_URL=postgres://pghero:pghero@192.168.56.180:5432/pghero

步骤五、启动pghero服务

sudo pghero config:set PORT=3001
sudo pghero config:set RAILS_LOG_TO_STDOUT=disabled
sudo pghero scale web=1

这时候已经可以通过IP:3001
web访问了，此时还没有添加要监控的数据库，但可以看到pghero配置库的性能信息。

步骤六、添加监控数据库

这里可以添加独立数据库，可以添加主备模式的数据库。

cd /opt/pghero
cat > pghero.yml<<eof databases:="" 关键业务一:="" url:="" postgres:="" pghero:pghero@192.168.56.180:5432="" test="" 关键业务二:="" pghero:pghero@192.168.56.181:5432="" eof="" cat="" pghero.yml="" |="" sudo="" pghero="" run="" sh="" -c="" "cat="" style="box-sizing: inherit;">config/pghero.yml"
sudo service pghero restart

最后来一张效果图