The PGWATCH2 of PostgreSQL monitoring

Objective:

Although the author has released the Docker version, it can be built with just one click (One command).

But in the spirit of learning or to do their own toss over, a key although good but not clear ah.

The official has already written the installation very detailed, but after all is English ...

PGWATCH2 Official Address: HTTPS://GITHUB.COM/CYBERTEC-POSTGRESQL/PGWATCH2

System environment:

CentOS 7.5

IP: 192.168.1.2

Grafana-5.1.4

PostgreSQL-10, pg_stat_statements module

InfluxDB-1.5.3

Python3

# Close selinux and firewalld.

##Install the above application yourself! ! ! This article does not provide an installation process! ! !

First, configure PostgreSQL

Vi /var/lib/pgsql/10/data/postgresql.conf

Shared_preload_libraries = ‘pg_stat_statements‘

Track_io_timing = on

#Edit the postgresql.conf configuration file, add the following two lines (premise: install the pg_stat_statements module

Vi /var/lib/pgsql/10/data/pg_hba.conf

Local all all peer

Host all all 127.0.0.1/32 trust

Host all all ::1/128 trust

#Modify the authentication mode, users who access through 127.0.0.1 do not need password authentication.

#Please configure it yourself for security, pg_hba.conf configuration file please explain Baidu~~

Systemctl restart postgresql-10.service

# Restart the database

Mkdir /app && cd /app

Git clone https://github.com/cybertec-postgresql/pgwatch2.git

#To clone the source repository, there are a few things that need to be used.

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 /app/pgwatch2/pgwatch2/sql/datastore_setup/config_store.sql pgwatch2

Psql -f /app/pgwatch2/pgwatch2/sql/datastore_setup/metric_definitions.sql pgwatch2

#Switch to the postgre user to operate the database.

#Create pgwatch2_grafana, pgwatch2 users and set a password.

#Create pgwatch2_grafana, pgwatch2 database and set the owner.

# Import pgwatch2 data.

Second, the configuration influxdb

Vi /etc/influxdb/influxdb.conf

[http]

Enabled = true

Bind-address = "127.0.0.1:8086"

# Modify the configuration file and change the bind-address of http to 127.0.0.1.

# Because it is used for monitoring, it does not authenticate, but only allows local address access.

Systemctl start influxdb

#StartInfluxDB

Influx

CREATE USER "pgwatch2" WITH PASSWORD ‘xyz‘

CREATE DATABASE pgwatch2

Use pgwatch2

GRANT ALL ON pgwatch2 to "pgwatch2"

#Create a pgwatch2 user and a database of the same name, and authorize.

Third, configuration Grafana

Vi /etc/grafana/grafana.ini

[database]

Type = postgres

Host = 127.0.0.1:5432

Name = pgwatch2_grafana

User = pgwatch2_grafana

Password = xyz

#Save Grafana's data in postgresql

Http://192.168.1.2:3000

#Access Grafana's web page for configuration

#Default username and password: admin

#Start to configure the dashboard

#In the source repository: pgwatch2/grafana_dashboards/v5/ is all the dashboard content, each folder is a dashboard

# Each folder contains the dashboard.json file, you can import this file. #Here to import the effect I will only import one: db-overview/dashboard.json

The #github page will copy better and copy all the content in the page:

#https://raw.githubusercontent.com/cybertec-postgresql/pgwatch2/master/grafana_dashboards/v5/db-overview/dashboard.json

Fourth. Configuration Pgwatch2

Cd /app/pgwatch2/

Pip3 install -U -r webpy/requirements.txt

Cd webpy

Python3 web.py

#Enter pgwatch directory, pip install some dependent libraries

#Executing web.py

#Access the web page for the next step of configuration

#Configure the information of the monitored database, note that the information stored in the database is filled here! ! !

# You want to create a user in the corresponding database and authorize, then enable the pg_stat_statements module

#only monitors the pgwatch2 database on this machine.

#Configuring the monitored database

#Here I just monitor the pgwatch2 database

Su - postgres

Psql -d pgwatch2 -c "CREATE EXTENSION pg_stat_statements;"

Psql -d pgwatch2 -c "CREATE EXTENSION plpythonu;"

Psql -f /app/pgwatch2/pgwatch2/sql/metric_fetching_helpers/stat_activity_wrapper.sql pgwatch2

Psql -f /app/pgwatch2/pgwatch2/sql/metric_fetching_helpers/stat_statements_wrapper.sql pgwatch2

Psql -f /app/pgwatch2/pgwatch2/sql/metric_fetching_helpers/cpu_load_plpythonu.sql pgwatch2

Psql -f /app/pgwatch2/pgwatch2/sql/metric_fetching_helpers/table_bloat_approx.sql pgwatch2

#compile pgwatch2 program

Yum install go -y

Cd /app/pgwatch2/pgwatch2

./build_gatherer.sh

#Install go language environment, enter pgwatch2 directory, execute build_gatherer.sh to start compiling

#This process will be slower, because you will download things on github, make sure the computer can ping github

# Compilation will generate a pgwatch2 executable file

./pgwatch2

%Run the pgwatch2 program

# Because all the account passwords you just set are the same as the default values of the pgwatch2 program, all can be run directly.

#If you are inconsistent, please use ./pgwatch2 --help to see help.

#Execute without error, then go to Grafana to see the data! ! !

# Finally, configure pgwatch2 to be able to start with systemctl

# paste the following content into /etc/systemd/system/pgwatch2.service

# Then you can start it with systemctl start

#Note, starting this way, all output information will be in /var/log/messages

[Unit]

Description=pgwatch2

After=syslog.target

After=network.target

[Service]

User=root

Restart=on-failure

PIDFile=/tmp/pgwatch2.pid

KillMode=control-group

ExecStart=/app/pgwatch2/pgwatch2/pgwatch2

ExecStop=/bin/kill -SIGTERM $MAINPID

RestartSec=10s

TimeoutSec=0

[Install]

WantedBy=multi-user.target

The PGWATCH2 of PostgreSQL monitoring