PMM监控postgresql

1.pmm客户端下载安装

wget https://repo.percona.com/pmm2-client/yum/release/7/RPMS/x86_64/pmm2-client-2.27.0-6.el7.x86_64.rpm
rpm -ivh pmm2-client-2.27.0-6.el7.x86_64.rpm

　　

2.创建用于pg监控的数据库用户

create user pmm with superuser encrypted password 'pmm_xx';
alter user pmm connection limit 5;

　　

3.编辑pg_hba.conf文件

local   all             pmm                                md5
# type  database        user        address                method

　　

4.重新加载配置

su - postgres
psql -c "select pg_reload_conf()"

　　

5.登录验证

psql postgres pmm -c "\conninfo"

　　

6.添加pg_stat_statements扩展
编辑配置文件

vi postgresql.conf
shared_preload_libraries = 'pg_stat_statements'
track_activity_query_size = 2048 # Increase tracked query string size
pg_stat_statements.track = all # Track all statements including nested
track_io_timing = on # Capture read/write stats

　　

7.重启pg

8.安装扩展

psql postgres postgres -c "CREATE EXTENSION pg_stat_statements SCHEMA public"

　　

9.注册节点到pmm

pmm-admin config --server-insecure-tls --server-url=https://admin:admin@192.168.56.1:443 192.168.56.2 generic 192.168.56.2

　　

10.添加pg实例

pmm-admin add postgresql --username=pmm --password=pmm_xx --service-name=192.168.56.2 --host=192.168.56.2 --port=5432