Configure PostgreSQL Monitoring on Zabbix 5.0

Zabbix 5.0 에서 PostgreSQL 모니터링을 구성하는 방법에 대한 예제

참조: https://www.zabbix.com/integrations/postgresql

PostgreSQL 및 OpenSSL 의 경로 및 설정상태는 이전 포스팅을 기준으로 작성되었습니다.

PostgreSQL 설정

Library 경로 설정

PostgreSQL 및 TimescaleDB 을 설치하실때 OpenSSL 을 별도의 경로에 직접 컴파일 설치하신 분은 psql 을 실행시킬때에도 OpenSSL 의 Library 를 참조하기 때문에 경로 설정을 해주셔야 합니다.

해당사항이 없는분은 건너뛰시고 모니터링 계정 생성부터 시작해 주시면 됩니다. 또한 매번 export 해주는게 귀찮으신분은 ld.so.conf.d 에 직접 설정을 해주셔도 됩니다. 저의 경우에는 필요할때만 export 해주는것을 선호합니다. 이럴경우 systemd 파일을 만들때도 반드시 Environment 를 이용하여 지정해 주어야 합니다.

LD_LIBRARY_PATH 를 export 하는 방법

[root@172-16-11-100 /]# export LD_LIBRARY_PATH=/usr/local/openssl-1.1.1g/lib/

ld.so.conf.d 에 등록하는 방법
: vi /etc/ld.so.conf.d/openssl-1.1.1g.conf

/usr/local/openssl-1.1.1g/lib/

적용

[root@172-16-11-100 /]# ldconfig

확인

[root@172-16-11-100 /]# ldconfig -p | grep ssl
libssl3.so (libc6,x86-64) => /lib64/libssl3.so
libssl.so.10 (libc6,x86-64) => /lib64/libssl.so.10
libssl.so.1.1 (libc6,x86-64) => /usr/local/openssl-1.1.1g/lib/libssl.so.1.1
libssl.so (libc6,x86-64) => /usr/local/openssl-1.1.1g/lib/libssl.so
libssl.so (libc6,x86-64) => /lib64/libssl.so
libevent_openssl-2.0.so.5 (libc6,x86-64) => /lib64/libevent_openssl-2.0.so.5
libcrypto.so.1.1 (libc6,x86-64) => /usr/local/openssl-1.1.1g/lib/libcrypto.so.1.1
libcrypto.so (libc6,x86-64) => /usr/local/openssl-1.1.1g/lib/libcrypto.so

모니터링 계정 생성

Superuser 로 접속하여 모니터링용 계정 생성 및 롤을 지정해 줍니다. 저의 경우 pguser 가 Superuser 계정입니다.

[root@172-16-11-100 /]# /data/apps/src/postgresql-12.2/bin/psql postgres -U pguser
Password for user pguser:
psql (12.2)
Type "help" for help.

postgres=# CREATE USER zbx_monitor INHERIT;
CREATE ROLE
postgres=# GRANT pg_monitor TO zbx_monitor;
GRANT ROLE
postgres=# \q

pg_hba.conf 에서 위에서 생성한 모니터링 계정인 zbx_monitor 계정을 trust host 로 등록해 줍니다.
: vi /data/apps/src/postgresql-12.2/data/pg_hba.conf

# IPv4 local connections:
host all zbx_monitor 127.0.0.1/32 trust
host all all 127.0.0.1/32 scram-sha-256

PostgreSQL 재시작

[root@172-16-11-100 /]# systemctl restart postgresql

Zabbix Agent 설정

PostgreSQL 의 모니터링을 위한 sql 파일을 다운로드 받아야 합니다. github 의 ZABBIX 소스안에 있기 때문에 해당 소스를 받아야 합니다.

[root@172-16-11-100 /]# cd /usr/local/src/
[root@172-16-11-100 src]# git clone https://github.com/zabbix/zabbix.git
[root@172-16-11-100 src]# mkdir -p /etc/zabbix/bin/postgresql/sql/
[root@172-16-11-100 src]# cp -rp /usr/local/src/zabbix/templates/db/postgresql/postgresql/*.sql /etc/zabbix/bin/postgresql/sql/

유저 파라미터를 등록해 줍니다. 저와 다른방식으로 설치하신 분들은 경로를 자신의 설정에 맞게 적절히 수정해 주셔야 합니다.
: vi /etc/zabbix/zabbix_agentd.conf.d/postgresql.conf

UserParameter=pgsql.bgwriter[*], /data/apps/src/postgresql-12.2/bin/psql -qtAX -h "$1" -p "$2" -U "$3" -d "$4" -f "/etc/zabbix/bin/postgresql/sql/pgsql.bgwriter.sql"

UserParameter=pgsql.connections.sum[*], /data/apps/src/postgresql-12.2/bin/psql -qtAX -h "$1" -p "$2" -U "$3" -d "$4" -f "/etc/zabbix/bin/postgresql/sql/pgsql.connections.sum.sql"
UserParameter=pgsql.connections[*], /data/apps/src/postgresql-12.2/bin/psql -qtAX -h "$1" -p "$2" -U "$3" -d "$4" -f "/etc/zabbix/bin/postgresql/sql/pgsql.connections.sql"
UserParameter=pgsql.connections.prepared[*], /data/apps/src/postgresql-12.2/bin/psql -qtAX -h "$1" -p "$2" -U "$3" -d "$4" -f "/etc/zabbix/bin/postgresql/sql/pgsql.connections.prepared.sql"

UserParameter=pgsql.dbstat.sum[*], /data/apps/src/postgresql-12.2/bin/psql -qtAX -h "$1" -p "$2" -U "$3" -d "$4" -f "/etc/zabbix/bin/postgresql/sql/pgsql.dbstat.sum.sql"
UserParameter=pgsql.dbstat[*], /data/apps/src/postgresql-12.2/bin/psql -qtAX -h "$1" -p "$2" -U "$3" -d "$4" -f "/etc/zabbix/bin/postgresql/sql/pgsql.dbstat.sql"

UserParameter=pgsql.transactions[*], /data/apps/src/postgresql-12.2/bin/psql -qtAX -h "$1" -p "$2" -U "$3" -d "$4" -f "/etc/zabbix/bin/postgresql/sql/pgsql.transactions.sql"
UserParameter=pgsql.config.hash[*], /data/apps/src/postgresql-12.2/bin/psql -qtAX -h "$1" -p "$2" -U "$3" -d "$4" -f "/etc/zabbix/bin/postgresql/sql/pgsql.config.hash.sql"
UserParameter=pgsql.wal.stat[*], /data/apps/src/postgresql-12.2/bin/psql -qtAX -h "$1" -p "$2" -U "$3" -d "$4" -f "/etc/zabbix/bin/postgresql/sql/pgsql.wal.stat.sql"
UserParameter=pgsql.locks[*], /data/apps/src/postgresql-12.2/bin/psql -qtAX -h "$1" -p "$2" -U "$3" -d "$4" -f "/etc/zabbix/bin/postgresql/sql/pgsql.locks.sql"
UserParameter=pgsql.queries[*], /data/apps/src/postgresql-12.2/bin/psql -qtAX -h "$1" -p "$2" -U "$3" -d "$4" -v tmax=$5 -f "/etc/zabbix/bin/postgresql/sql/pgsql.query.time.sql"
UserParameter=pgsql.uptime[*], /data/apps/src/postgresql-12.2/bin/psql -qtAX -h "$1" -p "$2" -U "$3" -d "$4" -f "/etc/zabbix/bin/postgresql/sql/pgsql.uptime.sql"
UserParameter=pgsql.cache.hit[*], /data/apps/src/postgresql-12.2/bin/psql -qtAX -h "$1" -p "$2" -U "$3" -d "$4" -f "/etc/zabbix/bin/postgresql/sql/pgsql.cache.hit.sql"
UserParameter=pgsql.scans[*], /data/apps/src/postgresql-12.2/bin/psql -qtAX -h "$1" -p "$2" -U "$3" -d "$4" -f "/etc/zabbix/bin/postgresql/sql/pgsql.scans.sql"
UserParameter=pgsql.frozenxid[*], /data/apps/src/postgresql-12.2/bin/psql -qtAX -h "$1" -p "$2" -U "$3" -d "$4" -f "/etc/zabbix/bin/postgresql/sql/pgsql.frozenxid.sql"

UserParameter=pgsql.discovery.db[*], /data/apps/src/postgresql-12.2/bin/psql -qtAX -h "$1" -p "$2" -U "$3" -d "$4" -f "/etc/zabbix/bin/postgresql/sql/pgsql.discovery.db.sql"
UserParameter=pgsql.db.size[*], /data/apps/src/postgresql-12.2/bin/psql -qtAX -h "$1" -p "$2" -U "$3" -d "$4" -c "SELECT pg_database_size('$5')"
UserParameter=pgsql.ping[*], /data/apps/src/postgresql-12.2/bin/pg_isready -h "$1" -p "$2" -U "$3" -d "$4"
UserParameter=pgsql.ping.time[*], /data/apps/src/postgresql-12.2/bin/psql -qtAX -h "$1" -p "$2" -U "$3" -d "$4" -f "/etc/zabbix/bin/postgresql/sql/pgsql.ping.time.sql"
UserParameter=pgsql.version[*], /data/apps/src/postgresql-12.2/bin/psql -qtAX -h "$1" -p "$2" -U "$3" -d "$4" -c "SELECT version();"

UserParameter=pgsql.replication.count[*], /data/apps/src/postgresql-12.2/bin/psql -qtAX -h "$1" -p "$2" -U "$3" -d "$4" -c "SELECT count(*) FROM pg_stat_replication"
UserParameter=pgsql.replication.recovery_role[*], /data/apps/src/postgresql-12.2/bin/psql -qtAX -h "$1" -p "$2" -U "$3" -d "$4" -f "/etc/zabbix/bin/postgresql/sql/pgsql.replication.recovery_role.sql"
UserParameter=pgsql.replication.lag.sec[*], /data/apps/src/postgresql-12.2/bin/psql -qtAX -h "$1" -p "$2" -U "$3" -d "$4" -f "/etc/zabbix/bin/postgresql/sql/pgsql.replication.lag.sql"
UserParameter=pgsql.replication.status[*], /data/apps/src/postgresql-12.2/bin/psql -qtAX -h "$1" -p "$2" -U "$3" -d "$4" -f "/etc/zabbix/bin/postgresql/sql/pgsql.replication.status.sql"

ZABBIX Agent 재시작

[root@172-16-11-100 src]# systemctl restart zabbix_agent2

Zabbix 프론트엔드 설정

1. 호스트에 템플릿을 추가해 줍니다. 추가할 템플릿명은 Template DB PostgreSQL 입니다.

2. 위 매뉴얼을 그대로 따라했을 경우에는 추가로 매크로 설정을 해주실 필요가 없습니다. 필요한 경우 매크로를 직접 입력해 주시면 됩니다. 주요 매크로는 아래 표에 있는 4가지이며, 자세한 매크로 관련 값은 맨위에 있는 참조 URL 에서 확인 가능합니다.

3. 설정이 모두 끝나고 데이터가 들어오기 시작하면 아래 스크린샷과 같은 형식으로 확인이 가능합니다.