Installing pgwatch2 for PostgreSQL On Ubuntu1804

最新推荐文章于 2024-12-17 10:45:18 发布

原创

最新推荐文章于 2024-12-17 10:45:18 发布 · 616 阅读

0 ·

CC 4.0 BY-SA版权

本文提供了在Ubuntu1804上从头开始使用Postgres配置DB安装pgwatch2的步骤，包括安装Postgres、创建数据库、设置pgwatch2和Grafana配置、安装InfluxDB、配置Grafana以及启动Web UI。同时指出了可能遇到的问题和解决办法。

Below are sample steps to do a custom install from scratch using a Postgres configuration DB for both pgwatch2 config and Grafana config. NB! pgwatch2 config can also be stored YAML and Grafana can use embedded Sqlite DB so technically only DB that is absolutely needed is the metrics storage DB, here Influx. All examples assuming Ubuntu.

关闭selinux以及firewalld
注意改IP

Install Postgres and create DB-s/roles for pgwatch2/Grafana

sudo apt install postgresql

配置PostgreSQL

sudo vim /etc/postgresql/10/main/postgresql.conf

shared_preload_libraries = 'pg_stat_statements'
track_io_timing = on

# 编辑postgresql.conf配置文件，添加如下两行（前提：装好pg_stat_statements模块

sudo vim /etc/postgresql/10/main/pg_hba.conf
local   all             all                                     peer
host    all             all             127.0.0.1/32            trust
host    all             all             ::1/128                 trust

# 修改认证模式，通过127.0.0.1访问的用户不需要密码认证
# 为了安全请自行配置,pg_hba.conf配置文件详解请自行百度

# 重启数据库
systemctl restart postgresql-10.service

# 克隆源码仓库，有一些东西需要用到
cd /home/guanli
git clone https://github.com/cybertec-postgresql/pgwatch2.git

Default port: 5432

1.1. Create an User and a DB to hold Grafana config

# 切换至postgre用户以便操作数据库
su - postgres

# 创建创建pgwatch2_grafana用户、数据库、设置密码、所属主
psql -c "create user pgwatch2_grafana password 'xyz'"
psql -c "create database pgwatch2_grafana owner pgwatch2_grafana"

1.2. Create an User and a DB to hold pgwatch2 config

# 创建pgwatch2用户、数据库、设置密码、所属主
psql -c "create user pgwatch2 password 'xyz'"
psql -c "create database pgwatch2 owner pgwatch2"

1.3 Roll out the pgwatch2 schema (will holds connection strings of DB-s to be monitored + metric definitions)

# 导入pgwatch2的数据
psql -f pgwatch2/sql/config_store/config_store.sql pgwatch2
psql -f pgwatch2/sql/config_store/metric_definitions.sql pgwatch2

Install InfluxDB (for Postgres as metrics storage DB see instructions here)
```
wget https://dl.influxdata.com/influxdb/releases/influxdb_1.8.0_amd64.deb
sudo dpkg -i influxdb_1.8.0_amd64.deb
```
Take a look/edit the Influx config at /etc/influxdb/influxdb.conf and it’s recommend to create also a separate limited login user e.g. “pgwatch2” to be used by the metrics gathering daemon to store metrics. See here on configuring InfluxDB and here for creating new users.

配置InfluxDB
```
vi /etc/influxdb/influxdb.conf

[http]
enabled = true
bind-address = "127.0.0.1:8086"

- 修改配置文件，将http端的bind-address修改成127.0.0.1
- 因为是做监控用的，所以不做认证，但仅允许本地地址访问

# 启动InfluxDB
sudo systemctl start influxdb
```
创建pgwatch2用户以及同名的数据库，并授权
```
influx

C
```