Souce Install Zabbix 5.0 LTS with Nginx + PHP 7.4 + TimescaleDB on CentOS 7 – # 1. TimescaleDB

by 춘삼이 · Published 2020-05-21 · Updated 2020-12-16

Nginx + PHP 7.4 + TimescaleDB 을 이용하여 ZABBIX 5.0 LTS 를 설치하는 방법에 대한 예제

이 글은 TimescaleDB 를 사용하여 ZABBIX 5.0 LTS 설치를 하기 위한 매뉴얼입니다. TimescaleDB 는 PostgreSQL 을 베이스로 하여 확장 모듈 추가를 통해 설치되는 SQL을 지원하는 오픈소스 시계열 데이터베이스 입니다. TimescaleDB 를 ZABBIX 의 DB 로 이용하게 되면 자동분할, 압축 등과 함께 더 뛰어난 성능으로 ZABBIX 를 사용할 수 있습니다. 또한 가장 큰 부하를 주는 housekeeping 으로 인한 DB 서버의 부하를 아주 많이 줄일 수 있습니다.

https://blog.zabbix.com/zabbix-time-series-data-and-timescaledb/6642/

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

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 다운로드

PostgreSQL 다운로드 및 설치

DB 통신에 암호화를 사용하지 않는 분은 OpenSSL 관련 부분들은 다 건너 뛰시고 설치시 옵션을 지우셔도 됩니다.

[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]# export LD_LIBRARY_PATH=/usr/local/openssl-1.1.1g/lib/
[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 --with-includes=/usr/local/openssl-1.1.1g/include/ --with-libraries=/usr/local/openssl-1.1.1g/lib/
[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 ~]$ export LD_LIBRARY_PATH=/usr/local/openssl-1.1.1g/lib/
[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]
Environment=LD_LIBRARY_PATH=/usr/local/openssl-1.1.1g/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

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 다운로드

Cmake 다운로드 및 설치

[root@172-16-11-99 postgresql-12.2]# cd /usr/local/src
[root@172-16-11-99 src]# wget 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 다운로드

TimescaleDB 다운로드 및 설치

PostgreSQL 과 마찬가지로 암호화를 사용하지 않는 분은 OpenSSL 옵션을 지우셔도 됩니다.

[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

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 ~]$ export LD_LIBRARY_PATH=/usr/local/openssl-1.1.1g/lib/:/data/apps/src/postgresql-12.2/lib/
[pguser@172-16-11-99 ~]$ /data/apps/src/postgresql-12.2/bin/createdb zabbix

Zabbix DB 에서 timescale 확장을 사용하도록 설정

[pguser@172-16-11-99 ~]$ /data/apps/src/postgresql-12.2/bin/psql zabbix
psql (12.2)
Type "help" for help.

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)

Souce Install Zabbix 5.0 LTS with Nginx + PHP 7.4 + TimescaleDB on CentOS 7 – # 1. TimescaleDB

Nginx + PHP 7.4 + TimescaleDB 을 이용하여 ZABBIX 5.0 LTS 를 설치하는 방법에 대한 예제

TimescaleDB 설치

You may also like...

Umount Post SearchBar

Souce Install Zabbix 5.0 LTS with Nginx + PHP 7.4 + TimescaleDB on CentOS 7 – # 1. TimescaleDB

Nginx + PHP 7.4 + TimescaleDB 을 이용하여 ZABBIX 5.0 LTS 를 설치하는 방법에 대한 예제

TimescaleDB 설치

You may also like...

Source Install Galera Cluster on MariaDB 10.3

Configure HA with DRBD on CentOS 7 #1 DRBD

Remove Old Kernel Image from CentOS 7

Umount Post SearchBar

태그