pgwatch2 之一 setup
os: ubuntu 16.04
db: postgresql 9.6.8
db: influxdb 1.7.5
grafana: 6.1.3
python3
pgwatch2 是使用 grafana 做展示界面,用来监控 postgresql 的工具.其相关数据存储在 PostgreSQL,InfluxDB .
一些端口信息
5432 - Postgres configuration (or metrics storage) DB
8080 - Management Web UI (monitored hosts, metrics, metrics configurations)
8081 - Gatherer healthcheck / statistics on number of gathered metrics (JSON).
3000 - Grafana dashboarding
8086 - InfluxDB API (when using the InfluxDB version)
8088 - InfluxDB Backup port (when using the InfluxDB version)
版本
# lsb_release -a
No LSB modules are available.
Distributor ID: Ubuntu
Description: Ubuntu 16.04.5 LTS
Release: 16.04
Codename: xenial
#
# su - postgres
$ psql -c "select version();"
version
----------------------------------------------------------------------------------------------------------------------------------------------
PostgreSQL 9.6.8 on x86_64-pc-linux-gnu (Ubuntu 9.6.8-1.pgdg16.04+1), compiled by gcc (Ubuntu 5.4.0-6ubuntu1~16.04.9) 5.4.0 20160609, 64-bit
(1 row)
参考<<ubuntu 16.04 安装 postgresql 的优化设置>>,<<
ubuntu 16.04 + apt/make + postgresql 11>>
下载安装
download
# mkdir -p /usr/pgwatch2/
# cd /usr/pgwatch2/
# wget https://github.com/cybertec-postgresql/pgwatch2/archive/v1.5.1.zip
# unzip v1.5.1.zip
# ln -s ./pgwatch2-1.5.1 ./pgwatch2
# ls -l
total 2956
lrwxrwxrwx 1 root root 16 Apr 16 15:16 pgwatch2 -> ./pgwatch2-1.5.1
drwxrwxr-x 8 root root 4096 Feb 12 02:08 pgwatch2-1.5.1
-rw-rw-r-- 1 root root 3022619 Apr 16 14:39 v1.5.1.zip
create postgresql user
# su - postgres
$ psql -c "create user pgwatch2_grafana password 'xyz'";
psql -c "create database pgwatch2_grafana owner pgwatch2_grafana";
$ psql -c "create user pgwatch2 password 'xyz'";
psql -c "create database pgwatch2 owner pgwatch2";
$ psql -f /usr/pgwatch2/pgwatch2-1.5.1/pgwatch2/sql/config_store/config_store.sql pgwatch2 ;
psql -f /usr/pgwatch2/pgwatch2-1.5.1/pgwatch2/sql/config_store/metric_definitions.sql pgwatch2 ;
Install InfluxDB
# INFLUX_LATEST=$(curl -so- https://api.github.com/repos/influxdata/influxdb/tags | grep -Eo '"v[0-9\.]+"' | grep -Eo '[0-9\.]+' | sort -nr | head -1)
# wget https://dl.influxdata.com/influxdb/releases/influxdb_${INFLUX_LATEST}_amd64.deb
# dpkg -i influxdb_${INFLUX_LATEST}_amd64.deb
# dpkg -l |grep -i influx
ii influxdb 1.7.5-1 amd64 Distributed time-series database.
# dpkg -L influxdb | grep -v "/usr/share/"
/.
/etc
/etc/logrotate.d
/etc/logrotate.d/influxdb
/etc/influxdb
/etc/influxdb/influxdb.conf
/var
/var/lib
/var/lib/influxdb
/var/log
/var/log/influxdb
/usr
/usr/lib
/usr/lib/influxdb
/usr/lib/influxdb/scripts
/usr/lib/influxdb/scripts/influxdb.service
/usr/lib/influxdb/scripts/init.sh
/usr/bin
/usr/bin/influx_stress
/usr/bin/influx_tsm
/usr/bin/influx
/usr/bin/influxd
/usr/bin/influx_inspect
编辑 /etc/influxdb/influxdb.conf
# vi /etc/influxdb/influxdb.conf
[http]
enabled = true
bind-address = "127.0.0.1:8086"
# influx
CREATE USER pgwatch2 WITH PASSWORD 'xyz'
CREATE DATABASE pgwatch2
use pgwatch2
GRANT ALL ON pgwatch2 to "pgwatch2"
configuring InfluxDB:
参考 https://docs.influxdata.com/influxdb/latest/administration/config/
creating new users:
参考 https://docs.influxdata.com/influxdb/latest/query_language/authentication_and_authorization/#user-types-and-privileges
Install Grafana
# GRAFANA_LATEST=$(curl -so- https://api.github.com/repos/grafana/grafana/tags | grep -Eo '"v[0-9\.]+"' | grep -Eo '[0-9\.]+' | sort -nr | head -1)
# wget https://s3-us-west-2.amazonaws.com/grafana-releases/release/grafana_${GRAFANA_LATEST}_amd64.deb
# dpkg -i grafana_${GRAFANA_LATEST}_amd64.deb
安装 Grafana deb 包时会提示一些依赖
# apt install libfontconfig fontconfig-config fonts-dejavu-core \
ttf-bitstream-vera fonts-freefont-ttf gsfonts-x11 gsfonts \
xfonts-utils libfontenc1 libxfont1 x11-common xfonts-encodings
# dpkg -l |grep -i grafana
ii grafana 6.1.3 amd64 Grafana
# dpkg -L grafana |grep -v "/usr/share/"
/.
/usr
/usr/sbin
/usr/sbin/grafana-server
/usr/sbin/grafana-cli
/usr/share
/usr/lib
/usr/lib/systemd
/usr/lib/systemd/system
/usr/lib/systemd/system/grafana-server.service
/etc
/etc/init.d
/etc/init.d/grafana-server
/etc/grafana
/etc/default
/etc/default/grafana-server
配置
Configure Grafana config
Configure Grafana config to use our pgwatch2_grafana DB
# vi /etc/grafana/grafana.ini
[database]
type = postgres
host = 127.0.0.1:5432
name = pgwatch2_grafana
user = pgwatch2_grafana
password = xyz
可以参考如下两个文件
/usr/share/grafana/conf/defaults.ini
/usr/share/grafana/conf/sample.ini
Set up the Influx datasource as default
# systemctl restart postgresql.service;
systemctl restart influxdb.service;
systemctl restart grafana-server.service;
# netstat -lntp|egrep -Ei "postgre|influx|grafana"
tcp 0 0 127.0.0.1:8086 0.0.0.0:* LISTEN 10661/influxd
tcp 0 0 127.0.0.1:8088 0.0.0.0:* LISTEN 10661/influxd
tcp 0 0 0.0.0.0:5432 0.0.0.0:* LISTEN 9593/postgres
tcp6 0 0 :::3000 :::* LISTEN 9990/grafana-server
然后登录 http://192.168.56.99:3000/login 用户名与密码均为 admin
Use the Grafana UI (Admin -> Data sources) 添加 influxdb 信息(参考 influxdb 部分)
Add pgwatch2 predefined dashboards to Grafana
This could be done by importing the JSON-s from the “grafana_dashboards” folder manually (Import Dashboard from the Grafana top menu) or via the Docker bootstrap script (pgwatch2/bootstrap/set_up_grafana_dashboards.sh). Script needs some adjustment for connect data and file paths though and also the “grafana_datasource.sql” part should be commented out if already executed in the previous step.
这个可以导入 https://github.com/cybertec-postgresql/pgwatch2/blob/v1.5.1/grafana_dashboards/v5/db-overview/dashboard.json
随后就会出现 DB overview 页面.
Install Python 3 and start the Web UI
# ps -ef|grep -i grafana-server
grafana 9990 1 0 12:36 ? 00:00:02 /usr/sbin/grafana-server --config=/etc/grafana/grafana.ini --pidfile=/var/run/grafana/grafana-server.pid --packaging=deb cfg:default.paths.logs=/var/log/grafana cfg:default.paths.data=/var/lib/grafana cfg:default.paths.plugins=/var/lib/grafana/plugins cfg:default.paths.provisioning=/etc/grafana/provisioning
postgres 10988 7401 0 14:56 pts/0 00:00:00 grep -i grafana-server
# cd /var/lib/grafana/
# ls -l
total 452
-rw-r----- 1 grafana grafana 450560 Apr 16 12:08 grafana.db
drwxr-x--- 2 grafana grafana 4096 Apr 16 11:48 plugins
drwx------ 2 grafana grafana 4096 Apr 16 11:48 png
# cd /usr/pgwatch2/pgwatch2-1.5.1
# apt install python3 python3-dev python3-pip libssl-dev openssl \
libffi-dev libffi6 libxml2 libxml2-dev libxslt1-dev zlib1g-dev
# pip3 install -U -r webpy/requirements.txt
pip3 安装的module 在 /usr/local/lib/python3.5/dist-packages/
Start the Web UI
# cd /usr/pgwatch2/pgwatch2-1.5.1/webpy
# python3 web.py
[16/Apr/2019:15:33:39] ENGINE Listening for SIGTERM.
2019-04-16 15:33:39,935 INFO 20220 [16/Apr/2019:15:33:39] ENGINE Listening for SIGTERM.
[16/Apr/2019:15:33:39] ENGINE Listening for SIGHUP.
2019-04-16 15:33:39,935 INFO 20220 [16/Apr/2019:15:33:39] ENGINE Listening for SIGHUP.
[16/Apr/2019:15:33:39] ENGINE Listening for SIGUSR1.
2019-04-16 15:33:39,935 INFO 20220 [16/Apr/2019:15:33:39] ENGINE Listening for SIGUSR1.
[16/Apr/2019:15:33:39] ENGINE Bus STARTING
2019-04-16 15:33:39,935 INFO 20220 [16/Apr/2019:15:33:39] ENGINE Bus STARTING
[16/Apr/2019:15:33:39] ENGINE Started monitor thread 'Autoreloader'.
2019-04-16 15:33:39,937 INFO 20220 [16/Apr/2019:15:33:39] ENGINE Started monitor thread 'Autoreloader'.
[16/Apr/2019:15:33:40] ENGINE Serving on http://0.0.0.0:8080
2019-04-16 15:33:40,045 INFO 20220 [16/Apr/2019:15:33:40] ENGINE Serving on http://0.0.0.0:8080
[16/Apr/2019:15:33:40] ENGINE Bus STARTED
2019-04-16 15:33:40,045 INFO 20220 [16/Apr/2019:15:33:40] ENGINE Bus STARTED
在前端运行,后面要写个 systemctl 或者写入 /etc/rc.local
Configure DB-s to monitor from “/dbs” page
看到上面一步最后一行输出为 ENGINE Bus STARTED ,就可以在页面 http://192.168.56.99:8080/ 配置被监控数据库的信息.注意 Preset config 选择 full
是被监控数据库
是被监控数据库
是被监控数据库
NB! To get most out of your metrics some wrappers/extensions are required on the DB-s under monitoring. See section Steps to configure your database for monitoring on setup information.
https://github.com/cybertec-postgresql/pgwatch2#steps-to-configure-your-database-for-monitoring
确保被监控数据库上的参数
# su - postgres
$ vi postgresql.conf
shared_preload_libraries='pg_stat_statements'
以下在被监控数据库上执行
# su - postgres
$ psql -c "create user pgwatch2 with password 'secret';"
psql -c "alter user pgwatch2 connection limit 3;"
psql -c "create database pgwatch2 owner pgwatch2";
psql -d pgwatch2 -c "CREATE EXTENSION pg_stat_statements;"
psql -d pgwatch2 -c "CREATE EXTENSION plpythonu;"
(这里发现个问题,postgresql-contrib-9.6 9.6.8-1.pgdg16.04+1 没有包含 plpythonu extension,通过源码编译获得了该 so,sql,control文件)
# cp /usr/pgsql-9.6/lib/plpython*.so /usr/lib/postgresql/9.6/lib
# cp /usr/pgsql-9.6/share/extension/plpython* /usr/share/postgresql/9.6/extension
以下在 pgwatch2 机器上执行
# su - postgres
$ psql -h 192.168.56.92 -U postgres -f /usr/pgwatch2/pgwatch2-1.5.1/pgwatch2/sql/metric_fetching_helpers/stat_activity_wrapper.sql pgwatch2
$ psql -h 192.168.56.92 -U postgres -f /usr/pgwatch2/pgwatch2-1.5.1/pgwatch2/sql/metric_fetching_helpers/stat_statements_wrapper.sql pgwatch2
$ psql -h 192.168.56.92 -U postgres -f /usr/pgwatch2/pgwatch2-1.5.1/pgwatch2/sql/metric_fetching_helpers/cpu_load_plpythonu.sql pgwatch2
$ psql -h 192.168.56.92 -U postgres -f /usr/pgwatch2/pgwatch2-1.5.1/pgwatch2/sql/metric_fetching_helpers/psutil_disk_io_total.sql pgwatch2
$ psql -h 192.168.56.92 -U postgres -f /usr/pgwatch2/pgwatch2-1.5.1/pgwatch2/sql/metric_fetching_helpers/psutil_disk.sql pgwatch2
$ psql -h 192.168.56.92 -U postgres -f /usr/pgwatch2/pgwatch2-1.5.1/pgwatch2/sql/metric_fetching_helpers/psutil_mem.sql pgwatch2
$ psql -h 192.168.56.92 -U postgres -f /usr/pgwatch2/pgwatch2-1.5.1/pgwatch2/sql/metric_fetching_helpers/psutil_cpu.sql pgwatch2
$ psql -h 192.168.56.92 -U postgres -f /usr/pgwatch2/pgwatch2-1.5.1/pgwatch2/sql/metric_fetching_helpers/table_bloat_approx.sql pgwatch2
Install Go and compile the gatherer
Check for the latest Go version from https://golang.org/dl/
install Go (latest version preferably, but at least 1.10)
# wget https://dl.google.com/go/go1.11.linux-amd64.tar.gz
# tar -C /usr/local -xzf go1.11.linux-amd64.tar.gz
# export PATH=$PATH:/usr/local/go/bin
或者直接 apt install
# apt install golang-1.10 golang-go.crypto-dev golang-golang-x-crypto-dev
# vi /etc/profile
PATH=$PATH:/usr/lib/postgresql/9.6/bin:/usr/lib/go-1.10/bin
# ln -s /usr/share/gocode/src/golang.org /usr/lib/go-1.10/src/golang.org
创建软链的原因请看本文最后
# cd /usr/pgwatch2/pgwatch2-1.5.1/pgwatch2
# ./build_gatherer.sh
getting project dependencies...
package golang.org/x/crypto/pbkdf2: directory "/usr/lib/go-1.10/src/golang.org/x/crypto/pbkdf2" is not using a known version control system
running 'go build pgwatch2.go' ...
an executable named “pgwatch2” should be generated
Run the gatherer
运行上一步生成的 pgwatch2 ,就会从被监控数据库获取指标数据
# cd /usr/pgwatch2/pgwatch2-1.5.1/pgwatch2
# ./pgwatch2 --help
# ./pgwatch2 --verbose \
--host=127.0.0.1 --port=5432 --dbname=pgwatch2 --user=pgwatch2 --password=xyz \
--ihost=127.0.0.1 --iport=8086 --idbname2=pgwatch2 --iuser=pgwatch2 --ipassword=xyz
Congrats! Now the metrics should start flowing in and after some minutes one should already see some graphs in Grafana.
此时,在 DB overview 就已经能看到采集到的数据了.
Install and configure SystemD init scripts
Install and configure SystemD init scripts for the Gatherer and the Web UI here and here or make sure to hatch up some “init scripts” so that the pgwatch2 daemon and the Web UI would be started automatically when the system reboots. For externally packaged components (Grafana, Influx, Postgres) it should be the case already.
如下脚本
https://github.com/cybertec-postgresql/pgwatch2/tree/master/webpy/startup-scripts
https://github.com/cybertec-postgresql/pgwatch2/tree/master/pgwatch2/startup-scripts
systemctl enable
# systemctl restart postgresql.service;
systemctl enable postgresql.service;
systemctl restart influxdb.service;
systemctl enable influxdb.service;
systemctl restart grafana-server.service;
systemctl enable grafana-server.service;
参考:
https://www.cybertec-postgresql.com/services/
https://github.com/cybertec-postgresql/pgwatch2
中间在运行 build_gatherer.sh 生成 pgwatch2 时报错.
# ./build_gatherer.sh
getting project dependencies...
package golang.org/x/crypto/pbkdf2: unrecognized import path "golang.org/x/crypto/pbkdf2" (https fetch: Get https://golang.org/x/crypto/pbkdf2?go-get=1: dial tcp 216.239.37.1:443: i/o timeout)
running 'go build pgwatch2.go' ...
pgwatch2.go:38:2: cannot find package "golang.org/x/crypto/pbkdf2" in any of:
/usr/lib/go-1.10/src/golang.org/x/crypto/pbkdf2 (from $GOROOT)
/root/go/src/golang.org/x/crypto/pbkdf2 (from $GOPATH)
# dpkg -L golang-golang-x-crypto-dev |grep -i pbkdf2
/usr/share/gocode/src/golang.org/x/crypto/pbkdf2
/usr/share/gocode/src/golang.org/x/crypto/pbkdf2/pbkdf2_test.go
/usr/share/gocode/src/golang.org/x/crypto/pbkdf2/pbkdf2.go
通过创建软链接解决
# ln -s /usr/share/gocode/src/golang.org /usr/lib/go-1.10/src/golang.org