Zabbix监控PgSQL

作者: Habit_1027 | 来源:发表于2023-06-28 14:27 被阅读0次

Linux监控平台介绍、zabbix监控介绍、安装zabbix（
zabbix搭建安全服务器集群
zabbix 键值
Zabbix使用LLD自动发现规则发现监控docker容器（下）
Zabbix监控nginx性能(记录)
zabbix 使用二
通过Zabbix监控VMware vCenter和vSphere
为什么使用zabbix？
zabbix服务器监控和触发器表达式
zabbix监控

一、安装PgSQL

1.1 下载安装包

下载地址：http://www.postgresql.org/ftp/source/

1.2 下载依赖包

[root@localhost ~]#  yum install -y perl-ExtUtils-Embed readline-devel zlib-devel pam-devel libxml2-devel libxslt-devel openldap-devel python-devel gcc-c++ openssl-devel cmake

1.3 编译安装

[root@localhost ~]# tar -xf postgresql-12.0.tar.gz
[root@localhost ~]# cd postgresql-12.0/
[root@localhost ~]# mkdir -p /opt/software/pgsql/postgresql/data
[root@localhost postgresql-12.0]# ./configure --prefix=/opt/software/pgsql/postgresql
[root@localhost postgresql-12.0]# make && make install

1.4 创建用户组postgres并创建用户postgres

[root@localhost ~]# groupadd postgres
[root@localhost ~]# useradd -g postgres postgres

1.5 给postgresql数据目录授权

[root@localhost ~]# cd /opt/software/pgsql/postgresql
[root@localhost postgresql]# chown postgres:postgres data

1.6 配置环境变量

[root@localhost ~]# cat /etc/profile.d/my_env.sh
export PGHOME=/opt/software/pgsql/postgresql
export PGDATA=/opt/software/pgsql/postgresql/data
PATH=$PATH:$HOME/bin:$PGHOME/bin
[root@localhost ~]# source /etc/profile.d/my_env.sh

1.7 切换postgres用户并使用initdb初使用化数据库

[root@localhost ~]# su - postgres
 
[postgres@localhost ~]$ initdb
The files belonging to this database system will be owned by user "postgres".
This user must also own the server process.
 
The database cluster will be initialized with locale "zh_CN.UTF-8".
The default database encoding has accordingly been set to "UTF8".
initdb: could not find suitable text search configuration for locale "zh_CN.UTF-8"
The default text search configuration will be set to "simple".
 
Data page checksums are disabled.
 
fixing permissions on existing directory /pgsql/postgresql/data ... ok
creating subdirectories ... ok
selecting default max_connections ... 100
selecting default shared_buffers ... 128MB
selecting dynamic shared memory implementation ... posix
creating configuration files ... ok
running bootstrap script ... ok
performing post-bootstrap initialization ... ok
syncing data to disk ... ok
 
WARNING: enabling "trust" authentication for local connections
You can change this by editing pg_hba.conf or using the option -A, or
--auth-local and --auth-host, the next time you run initdb.
 
Success. You can now start the database server using:
 
    pg_ctl -D /pgsql/postgresql/data -l logfile star

1.8 修改配置文件

postgresql.conf 配置PostgreSQL数据库服务器的相应的参数。
pg_hba.conf 配置对数据库的访问权限。

[root@localhost data]# pwd
/opt/software/pgsql/postgresql/data
[root@localhost data]# ls
base          pg_dynshmem    pg_logical    pg_replslot   pg_stat      pg_tblspc    pg_wal                postgresql.conf  serverlog
global        pg_hba.conf    pg_multixact  pg_serial     pg_stat_tmp  pg_twophase  pg_xact               postmaster.opts
pg_commit_ts  pg_ident.conf  pg_notify     pg_snapshots  pg_subtrans  PG_VERSION   postgresql.auto.conf  postmaster.pid
[root@localhost data]#

image.png

1.9 配置PostgreSQL开机自启

[root@localhost start-scripts]# pwd
/root/postgresql-12.0/contrib/start-scripts
[root@localhost start-scripts]# ls
freebsd  linux  macos
[root@localhost start-scripts]# chmod a+x linux
[root@localhost start-scripts]# cp linux /etc/init.d/postgresql

修改/etc/init.d/postgresql文件的两个变量

prefix 设置为postgresql的安装路径
PGDATA 设置为postgresql的数据目录路径

[root@localhost start-scripts]# cat /etc/init.d/postgresql | grep -A 5 "Installation prefix"
# Installation prefix
prefix=/opt/software/pgsql/postgresql

# Data directory
PGDATA="/opt/software/pgsql/postgresql/data"

[root@localhost start-scripts]#

设置postgresql服务开机自启

[root@localhost start-scripts]# chkconfig --add postgresql

启动

[root@localhost start-scripts]# service postgresql start

测试

执行命令能进去则安装成功

[root@localhost start-scripts]# psql -U postgres -d postgres
psql (12.0)
Type "help" for help.

postgres=#

以上是PostgreSQL的安装

二、监控---PostgreSQL配置

2.1 创建用户

需要在 PostgreSQL 数据库建立监控专用的用户，由于 PostgreSQL 版本不同相关命令会有一定差别，创建一个 zbx_monitor 用户密码为 zbx_monitor。
PostgreSQL 10 以上版本

su - postgres
psql
CREATE USER zbx_monitor WITH PASSWORD 'zbx_monitor' INHERIT;
GRANT pg_monitor TO zbx_monitor;

PostgreSQL 9.6 版本及以下

su - postgres
psql
CREATE USER zbx_monitor WITH PASSWORD 'zbx_monitor';
GRANT SELECT ON pg_stat_database TO zbx_monitor;
ALTER USER zbx_monitor WITH SUPERUSER;

1.3 配置访问策略

编辑 pg_hba.conf 文件，并添加如下内容

host all zbx_monitor 127.0.0.1/32 trust
host all zbx_monitor 0.0.0.0/0 md5
host all zbx_monitor ::0/0 md5

如果 Zabbix agent 和 PostgreSQL 在不同机器，需要配置密码文件，需要创建.pgpass 文件，并存放在 zabbix 用户的家目录下，内容如下：

<REMOTE_HOST1>:5432:postgres:zbx_monitor:<PASSWORD>

重启PgSQL

三、监控---Zabbix配置

zabbix官方模板：https://git.zabbix.com/projects/ZBX/repos/zabbix/browse/templates?at=refs%2Fheads%2Frelease%2F5.0

3.1 添加主机并关联模板

image.png
配置主机宏

image.png

3.2 配置agent

PostgreSQL 监控需要在 Zabbix Agent 端添加脚本文件
下载到 Agent 所在机器，添加 Postgresql 监控 SQL 文件

wget https://dl.cactifans.com/zabbix/postgresql.tar.gz
mkdir -p /var/lib/zabbix/
tar zxvf postgresql.tar.gz
cp -r postgresql/postgresql/ /var/lib/zabbix/

添加 UserParameter 文件到 Agent 的 zabbix_agentd.d 目录（根据实际情况修改)

cp -r postgresql/template_db_postgresql.conf  /etc/zabbix/zabbix_agentd.d/

image.png

重启Agent生效

脚本修复

vi /var/lib/zabbix/postgresql/pgsql.dbstat.sql
#修改为以下内容
SELECT json_object_agg(datname, row_to_json(T)) FROM (
        SELECT datname,
                        numbackends,
                        xact_commit,
                        xact_rollback,
                        blks_read,
                        blks_hit,
                        tup_returned,
                        tup_fetched,
                        tup_inserted,
                        tup_updated,
                        tup_deleted,
                        conflicts,
                        temp_files,
                        temp_bytes,
                        deadlocks
        FROM pg_stat_database
        WHERE datname is not NULL
) T