Nginx + PHP 7.4 + TimescaleDB 을 이용하여 ZABBIX 5.0 LTS 를 설치하는 방법에 대한 예제
얼마전 ZABBIX 5.0 LTS 버전이 출시되었습니다. (https://umount.net/zabbix-5-0-lts-release/)
이 글은 TimescaleDB 를 사용하여 ZABBIX 5.0 LTS 설치를 하기 위한 매뉴얼입니다. TimescaleDB 는 PostgreSQL 을 베이스로 하여 확장 모듈 추가를 통해 설치되는 SQL을 지원하는 오픈소스 시계열 데이터베이스 입니다.
TimescaleDB 는 현재 Zabbix Proxy 에서는 지원되지 않습니다. (https://www.zabbix.com/documentation/current/manual/appendix/install/timescaledb)
이 매뉴얼은 마이그레이션 매뉴얼이 아니며, ZABBIX 5.0 LTS 의 처음 설치를 위한 매뉴얼입니다.
// Nginx 최신버전 및 PHP 7.4, OpenSSL 1.1.1g 설치가 선행되어야 합니다.
// Nginx, PHP, OpenSSL 설치 매뉴얼은 아래 링크를 참조해 주시면 됩니다.
Nginx 설치 (OpenSSL 1.1.1g 포함)
PHP 7.4 설치 (OpenSSL 1.1.1g 포함)
설치 테스트 사양
OS : CentOS 7
IP : 172.16.11.99
CPU : 4 Core
RAM : 8 GB
DISK : 300 GB
Nginx 1.18.0
PHP 7.4.5
PostgreSQL 12.2
TimescaleDB 1.7.0
Cmake 3.17.2
OpenSSL 1.1.1g
ZABBIX 5.0.0 LTS
1. TimescaleDB 설치
의존성 패키지 설치
[root@172–16–11–99 ~]# yum -y install gcc gcc-c++ autoconf make zlib-devel readline-devel libxml2-devel libxslt-devel libicu-devel systemd-devel bison bison-devel flex flex-devel
PostgreSQL 다운로드 및 설치
[root@172–16–11–99 ~]# cd /usr/local/src/
[root@172–16–11–99 src]# wget https://ftp.postgresql.org/pub/source/v12.2/postgresql-12.2.tar.gz
[root@172–16–11–99 src]# tar xvzf postgresql-12.2.tar.gz
[root@172–16–11–99 src]# cd postgresql-12.2
[root@172–16–11–99 postgresql-12.2]# export PKG_CONFIG_PATH=/usr/local/openssl-1.1.1g/lib/pkgconfig
[root@172–16–11–99 postgresql-12.2]# ./configure –prefix=/data/apps/src/postgresql-12.2 –with-icu –with-systemd –with-libxml –with-libxslt –with-openssl
[root@172–16–11–99 postgresql-12.2]# make
[root@172–16–11–99 postgresql-12.2]# make install
[root@172–16–11–99 postgresql-12.2]# useradd -r pguser
[root@172–16–11–99 postgresql-12.2]# mkdir -p /data/apps/src/postgresql-12.2/data
[root@172–16–11–99 postgresql-12.2]# chown -Rf pguser.pguser /data/apps/src/postgresql-12.2
[root@172–16–11–99 postgresql-12.2]# chmod 700 /data/apps/src/postgresql-12.2/bin/*
[root@172–16–11–99 postgresql-12.2]# su – pguser
[pguser@172–16–11–99 ~]$ /data/apps/src/postgresql-12.2/bin/initdb –encoding=UTF8 –lc-collate=C -D /data/apps/src/postgresql-12.2/data
The files belonging to this database system will be owned by user “pguser”.
This user must also own the server process.
The database cluster will be initialized with locales
COLLATE: C
CTYPE: en_US.UTF–8
MESSAGES: en_US.UTF–8
MONETARY: en_US.UTF–8
NUMERIC: en_US.UTF–8
TIME: en_US.UTF–8
The default text search configuration will be set to “english”.
Data page checksums are disabled.
fixing permissions on existing directory /data/apps/src/postgresql-12.2/data … ok
creating subdirectories … ok
selecting dynamic shared memory implementation … posix
selecting default max_connections … 100
selecting default shared_buffers … 128MB
selecting default time zone … Asia/Seoul
creating configuration files … ok
running bootstrap script … ok
performing post-bootstrap initialization … ok
syncing data to disk … ok
initdb: 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:
/data/apps/src/postgresql-12.2/bin/pg_ctl -D /data/apps/src/postgresql-12.2/data -l logfile start
[pguser@172–16–11–99 ~]$ exit
systemd 파일 생성
: vi /etc/systemd/system/postgresql.service
[Unit]
Description=PostgreSQL database server
Documentation=man:postgres(1)
[Service]
Type=notify
User=pguser
ExecStart=/data/apps/src/postgresql-12.2/bin/postgres -D /data/apps/src/postgresql-12.2/data
ExecReload=/bin/kill -HUP $MAINPID
KillMode=mixed
KillSignal=SIGINT
TimeoutSec=0
[Install]
WantedBy=multi-user.target
systemd 등록 및 실행
[root@172–16–11–99 postgresql-12.2]# systemctl enable postgresql
[root@172–16–11–99 postgresql-12.2]# systemctl start postgresql
PostgreSQL 에 TimescaleDB 를 올리기 위해서는 CMake 3.4 버전 이상이 필요하므로 Cmake 를 설치해야 합니다.
Cmake 다운로드 및 설치
[root@172–16–11–99 postgresql-12.2]# cd /usr/local/src
[root@172–16–11–99 src]# https://github.com/Kitware/CMake/releases/download/v3.17.2/cmake-3.17.2.tar.gz
[root@172–16–11–99 src]# tar xvzf cmake-3.17.2.tar.gz
[root@172–16–11–99 src]# cd cmake-3.17.2
[root@172–16–11–99 cmake-3.17.2]# ./bootstrap –prefix=/usr/local/cmake-3.17.2
[root@172–16–11–99 cmake-3.17.2]# make
[root@172–16–11–99 cmake-3.17.2]# make install
bin 경로 export
// 새로 설치한 경로를 먼저 읽어야 하기 때문에 PATH 를 export 할때 반드시 $PATH 를 맨 뒤에 넣어주어야 합니다.
[root@172–16–11–99 cmake-3.17.2]# export PATH=/usr/local/cmake-3.17.2/bin/:/data/apps/src/postgresql-12.2/bin:$PATH
TimescaleDB 다운로드 및 설치
[root@172–16–11–99 cmake-3.17.2]# cd /usr/local/src
[root@172–16–11–99 src]# wget https://github.com/timescale/timescaledb/archive/1.7.0.tar.gz
[root@172–16–11–99 src]# tar xvzf 1.7.0.tar.gz
[root@172–16–11–99 src]# cd timescaledb-1.7.0
[root@172–16–11–99 timescaledb-1.7.0]# ./bootstrap -DREGRESS_CHECKS=OFF -DOPENSSL_ROOT_DIR=/usr/local/openssl-1.1.1g
[root@172–16–11–99 timescaledb-1.7.0]# cd build && make
[root@172–16–11–99 build]# make install
[root@172–16–11–99 build]# chown -Rf pguser.pguser /data/apps/src/postgresql-12.2
timescaledb-tune 툴은 손쉽게 데이터베이스 기본 설정 및 최적화 설정을 도와주는 툴입니다.
timescaledb-tune 설치
[root@172–16–11–99 build]# yum install golang
[root@172–16–11–99 build]# go get github.com/timescale/timescaledb-tune/cmd/timescaledb-tune
[root@172–16–11–99 build]# mv /root/go/bin/timescaledb-tune /usr/local/bin/
[root@172–16–11–99 build]# rm -Rf /root/go
timescaledb-tune 실행
[root@172–16–11–99 build]# /usr/local/bin/timescaledb-tune -pg-config=/data/apps/src/postgresql-12.2/bin/pg_config -conf-path=/data/apps/src/postgresql-12.2/data/postgresql.conf
Using postgresql.conf at this path:
/data/apps/src/postgresql-12.2/data/postgresql.conf
Writing backup to:
/tmp/timescaledb_tune.backup202005131630
shared_preload_libraries needs to be updated
Current:
#shared_preload_libraries = ”
Recommended:
shared_preload_libraries = ‘timescaledb’
Is this okay? [(y)es/(n)o]: y
success: shared_preload_libraries will be updated
Tune memory/parallelism/WAL and other settings? [(y)es/(n)o]: y
Recommendations based on 7.61 GB of available memory and 4 CPUs for PostgreSQL 12
Memory settings recommendations
Current:
shared_buffers = 128MB
#effective_cache_size = 4GB
#maintenance_work_mem = 64MB
#work_mem = 4MB
Recommended:
shared_buffers = 1948MB
effective_cache_size = 5846MB
maintenance_work_mem = 997764kB
work_mem = 4988kB
Is this okay? [(y)es/(s)kip/(q)uit]: y
success: memory settings will be updated
Parallelism settings recommendations
Current:
missing: timescaledb.max_background_workers
#max_worker_processes = 8
#max_parallel_workers_per_gather = 2
#max_parallel_workers = 8
Recommended:
timescaledb.max_background_workers = 8
max_worker_processes = 15
max_parallel_workers_per_gather = 2
max_parallel_workers = 4
Is this okay? [(y)es/(s)kip/(q)uit]: y
success: parallelism settings will be updated
WAL settings recommendations
Current:
#wal_buffers = -1
min_wal_size = 80MB
Recommended:
wal_buffers = 16MB
min_wal_size = 512MB
Is this okay? [(y)es/(s)kip/(q)uit]: y
success: WAL settings will be updated
Miscellaneous settings recommendations
Current:
#default_statistics_target = 100
#random_page_cost = 4.0
#checkpoint_completion_target = 0.5
max_connections = 1000
#max_locks_per_transaction = 64
#autovacuum_max_workers = 3
#autovacuum_naptime = 1min
#effective_io_concurrency = 1
Recommended:
default_statistics_target = 500
random_page_cost = 1.1
checkpoint_completion_target = 0.9
max_connections = 100
max_locks_per_transaction = 64
autovacuum_max_workers = 10
autovacuum_naptime = 10
effective_io_concurrency = 200
Is this okay? [(y)es/(s)kip/(q)uit]: y
success: miscellaneous settings will be updated
Saving changes to: /data/apps/src/postgresql-12.2/data/postgresql.conf
systemd 파일 수정
: vi /etc/systemd/system/postgresql.service
[Unit]
Description=PostgreSQL database server
Documentation=man:postgres(1)
[Service]
Environment=LD_LIBRARY_PATH=/usr/local/openssl-1.1.1g/lib/:/data/apps/src/postgresql-12.2/lib/
Type=notify
User=pguser
ExecStart=/data/apps/src/postgresql-12.2/bin/postgres -D /data/apps/src/postgresql-12.2/data
ExecReload=/bin/kill -HUP $MAINPID
KillMode=mixed
KillSignal=SIGINT
TimeoutSec=0
[Install]
WantedBy=multi-user.target
PostgreSQL 재실행
[root@172–16–11–99 build]# systemctl restart postgresql
zabbix DB 생성
[root@172–16–11–99 build]# su – pguser
[pguser@172–16–11–99 ~]$ /data/apps/src/postgresql-12.2/bin/createdb zabbix
[pguser@172–16–11–99 ~]$ /data/apps/src/postgresql-12.2/bin/psql zabbix
zabbix DB 에서 timescale 확장을 사용하도록 설정
zabbix=# CREATE EXTENSION IF NOT EXISTS timescaledb CASCADE;
WARNING:
WELCOME TO
_____ _ _ ____________
|_ _(_) | | | _ \ ___ \
| | _ _ __ ___ ___ ___ ___ __ _| | ___| | | | |_/ /
| | | | _ ` _ \ / _ \/ __|/ __/ _` | |/ _ \ | | | ___ \
| | | | | | | | | __/\__ \ (_| (_| | | __/ |/ /| |_/ /
|_| |_|_| |_| |_|\___||___/\___\__,_|_|\___|___/ \____/
Running version 1.7.0
For more information on TimescaleDB, please visit the following links:
1. Getting started: https://docs.timescale.com/getting-started
2. API reference documentation: https://docs.timescale.com/api
3. How TimescaleDB is designed: https://docs.timescale.com/introduction/architecture
Note: TimescaleDB collects anonymous reports to better understand and assist our users.
For more information and how to disable, please see our docs https://docs.timescaledb.com/using-timescaledb/telemetry.
CREATE EXTENSION
zabbix DB 비밀번호 생성
zabbix=# create role zabbix with login password ‘YourDBPassword’;
CREATE ROLE
zabbix=# alter database zabbix owner to zabbix;
ALTER DATABASE
superuser(pguser) 계정의 비밀번호 생성
zabbix=# alter user pguser with password ‘SuperUserDBPassword’;
ALTER ROLE
zabbix=# \q
[pguser@172–16–11–99 ~]$ exit
TIP. 보안 설정
: vi /data/apps/src/postgresql-12.2/data/postgresql.conf
// 암호화 옵션에 주석제거 후 scram-sha-256 설정
#password_encryption = md5 -> password_encryption = scram-sha-256
: vi /data/apps/src/postgresql-12.2/data/pg_hba.conf
// 아래에서 trust 부분을
local all all trust
host all all 127.0.0.1/32 trust
host all all ::1/128 trust
// scram-sha-256 로 변경
local all all scram-sha-256
host all all 127.0.0.1/32 scram-sha-256
host all all ::1/128 scram-sha-256
[root@172–16–11–99 /]# systemctl restart postgresql
TIP. OS 쉘 접속처럼 PostgreSQL 에서도 쉘 접속시 프롬프트 표시가 다릅니다.
// superuser (pguser) Role 로 접속 (프롬프트가 # 으로 표시)
[pguser@172–16–11–99 ~]$ /data/apps/src/postgresql-12.2/bin/psql zabbix
Password for user pguser:
psql (12.2)
Type “help” for help.
zabbix=# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
———–+——–+———-+———+————-+——————-
postgres | pguser | UTF8 | C | en_US.UTF–8 |
template0 | pguser | UTF8 | C | en_US.UTF–8 | =c/pguser +
| | | | | pguser=CTc/pguser
template1 | pguser | UTF8 | C | en_US.UTF–8 | =c/pguser +
| | | | | pguser=CTc/pguser
zabbix | zabbix | UTF8 | C | en_US.UTF–8 |
(4 rows)
// DB Owner (zabbix) Role 로 접속 (프롬프트가 > 으로 표시)
[pguser@172–16–11–99 ~]$ /data/apps/src/postgresql-12.2/bin/psql zabbix -U zabbix
Password for user zabbix:
psql (12.2)
Type “help” for help.
zabbix=> \l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
———–+——–+———-+———+————-+——————-
postgres | pguser | UTF8 | C | en_US.UTF–8 |
template0 | pguser | UTF8 | C | en_US.UTF–8 | =c/pguser +
| | | | | pguser=CTc/pguser
template1 | pguser | UTF8 | C | en_US.UTF–8 | =c/pguser +
| | | | | pguser=CTc/pguser
zabbix | zabbix | UTF8 | C | en_US.UTF–8 |
(4 rows)
