Zabbix 4.2 + TimescaleDB
Zabbix 4.2 的新功能部份已支援 TimescaleDB,故留下這個小筆記,方便日後使用
(1) OS CentOS 7
(2) PostgreSQL 11
https://docs.timescale.com/v1.3/getting-started/installation/rhel-centos/installation-yum
PostgreSQL repository & Install
# yum install https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm
# yum install postgresql11
# yum install postgresql11-server
# /usr/pgsql-11/bin/postgresql-11-setup initdb
# systemctl enable postgresql-11
# systemctl start postgresql-11
Add PATH
PATH=$PATH:/usr/pgsql-11/bin/
export PATH
PGDATA=/var/lib/pgsql/11/data
export PGDATA
# vi /var/lib/pgsql/11/data/pg_hba.conf
local all all md5
# or local all all trust
host all all 127.0.0.1/32 md5
# vi /var/lib/pgsql/11/data/postgresql.conf
# - Connection Settings -
listen_addresses = '*' # what IP address(es) to listen on;
# comma-separated list of addresses;
# defaults to 'localhost'; use '*' for all
# (change requires restart)
port = 5432 # (change requires restart)
max_connections = 100 # (change requires restart)
(3) TimescaleDB 1.3
TimescaleDB repository & Install
sudo cat > /etc/yum.repos.d/timescale_timescaledb.repo <<EOL
[timescale_timescaledb]
name=timescale_timescaledb
baseurl=https://packagecloud.io/timescale/timescaledb/el/7/\$basearch
repo_gpgcheck=1
gpgcheck=0
enabled=1
gpgkey=https://packagecloud.io/timescale/timescaledb/gpgkey
sslverify=1
sslcacert=/etc/pki/tls/certs/ca-bundle.crt
metadata_expire=300
EOL
# yum update -y
# yum install -y timescaledb-postgresql-11
(4) Configure database
# timescaledb-tune
[root@centos75 ~]# timescaledb-tune
Using postgresql.conf at this path:
/var/lib/pgsql/11/data/postgresql.conf
Is this correct? [(y)es/(n)o]: y
Writing backup to:
/tmp/timescaledb_tune.backup201906151823
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 3.69 GB of available memory and 4 CPUs for PostgreSQL 11
Memory settings recommendations
Current:
shared_buffers = 128MB
#effective_cache_size = 4GB
#maintenance_work_mem = 64MB
#work_mem = 4MB
Recommended:
shared_buffers = 966208kB
effective_cache_size = 2830MB
maintenance_work_mem = 483104kB
work_mem = 2415kB
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
max_wal_size = 1GB
Recommended:
wal_buffers = 16MB
min_wal_size = 4GB
max_wal_size = 8GB
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_locks_per_transaction = 64
#effective_io_concurrency = 1
Recommended:
default_statistics_target = 500
random_page_cost = 1.1
checkpoint_completion_target = 0.9
max_locks_per_transaction = 64
effective_io_concurrency = 200
Is this okay? [(y)es/(s)kip/(q)uit]: y
success: miscellaneous settings will be updated
Saving changes to: /var/lib/pgsql/11/data/postgresql.conf
[root@centos75 ~]#
# systemctl restart postgresql-11
(4) Zabbix 4.2
https://www.zabbix.com/download?zabbix=4.2&os_distribution=centos&os_version=7&db=postgresql
Install Zabbix repository
# rpm -Uvh https://repo.zabbix.com/zabbix/4.2/rhel/7/x86_64/zabbix-release-4.2-1.el7.noarch.rpm
Install Zabbix server, frontend, agent
# yum -y install zabbix-server-pgsql zabbix-web-pgsql zabbix-agent
Create initial database
# sudo -u postgres createuser --pwprompt zabbix
# sudo -u postgres createdb -O zabbix zabbix
Import initial schema and data. You will be prompted to enter your newly created password.
# zcat /usr/share/doc/zabbix-server-pgsql*/create.sql.gz | sudo -u zabbix psql zabbix
========================================
https://www.zabbix.com/documentation/4.2/manual/appendix/install/timescaledb
[root@centos75 zabbix-server-pgsql-4.2.3]# pwd
/usr/share/doc/zabbix-server-pgsql-4.2.3
[root@centos75 zabbix-server-pgsql-4.2.3]# ll
total 2292
-rw-r--r-- 1 root root 98 Jun 7 18:18 AUTHORS
-rw-r--r-- 1 root root 1000750 Jun 7 18:19 ChangeLog
-rw-r--r-- 1 root root 17990 Jun 7 18:18 COPYING
-rw-r--r-- 1 root root 1305981 Jun 7 18:26 create.sql.gz
-rw-r--r-- 1 root root 52 Jun 7 18:18 NEWS
-rw-r--r-- 1 root root 1317 Jun 7 18:19 README
-rw-r--r-- 1 root root 219 Jun 7 18:19 timescaledb.sql.gz
[root@centos75 zabbix-server-pgsql-4.2.3]#
root@centos75 zabbix-server-pgsql-4.2.3]# echo "CREATE EXTENSION IF NOT EXISTS timescaledb CASCADE;" | sudo -u postgres psql zabbix
WARNING:
WELCOME TO
_____ _ _ ____________
|_ _(_) | | | _ \ ___ \
| | _ _ __ ___ ___ ___ ___ __ _| | ___| | | | |_/ /
| | | | _ ` _ \ / _ \/ __|/ __/ _` | |/ _ \ | | | ___ \
| | | | | | | | | __/\__ \ (_| (_| | | __/ |/ /| |_/ /
|_| |_|_| |_| |_|\___||___/\___\__,_|_|\___|___/ \____/
Running version 1.3.1
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
[root@centos75 zabbix-server-pgsql-4.2.3]# zcat /usr/share/doc/zabbix-server-pgsql-4.2.3/timescaledb.sql.gz | sudo -u zabbix psql zabbix
create_hypertable
----------------------
(1,public,history,t)
(1 row)
create_hypertable
---------------------------
(2,public,history_uint,t)
(1 row)
create_hypertable
--------------------------
(3,public,history_log,t)
(1 row)
create_hypertable
---------------------------
(4,public,history_text,t)
(1 row)
create_hypertable
--------------------------
(5,public,history_str,t)
(1 row)
create_hypertable
---------------------
(6,public,trends,t)
(1 row)
create_hypertable
--------------------------
(7,public,trends_uint,t)
(1 row)
UPDATE 1
[root@centos75 zabbix-server-pgsql-4.2.3]#
=======================================
Configure the database for Zabbix server
vi /etc/zabbix/zabbix_server.conf
--> DBPassword=password
Configure PHP for Zabbix frontend
vi /etc/httpd/config.d/zabbix.conf
--> php_value date.timezone Asia/Taipei
Start Zabbix server and agent processes
Start Zabbix server and agent processes and make it start at system boot:
# systemctl restart zabbix-server zabbix-agent httpd
# systemctl enable zabbix-server zabbix-agent httpd
其它參考的 URL
https://www.opensourcetech.tokyo/entry/20190510/1557484230
host all all 127.0.0.1/32 md5
# vi /var/lib/pgsql/11/data/postgresql.conf
# - Connection Settings -
listen_addresses = '*' # what IP address(es) to listen on;
# comma-separated list of addresses;
# defaults to 'localhost'; use '*' for all
# (change requires restart)
port = 5432 # (change requires restart)
max_connections = 100 # (change requires restart)
(3) TimescaleDB 1.3
TimescaleDB repository & Install
sudo cat > /etc/yum.repos.d/timescale_timescaledb.repo <<EOL
[timescale_timescaledb]
name=timescale_timescaledb
baseurl=https://packagecloud.io/timescale/timescaledb/el/7/\$basearch
repo_gpgcheck=1
gpgcheck=0
enabled=1
gpgkey=https://packagecloud.io/timescale/timescaledb/gpgkey
sslverify=1
sslcacert=/etc/pki/tls/certs/ca-bundle.crt
metadata_expire=300
EOL
# yum update -y
# yum install -y timescaledb-postgresql-11
(4) Configure database
# timescaledb-tune
[root@centos75 ~]# timescaledb-tune
Using postgresql.conf at this path:
/var/lib/pgsql/11/data/postgresql.conf
Is this correct? [(y)es/(n)o]: y
Writing backup to:
/tmp/timescaledb_tune.backup201906151823
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 3.69 GB of available memory and 4 CPUs for PostgreSQL 11
Memory settings recommendations
Current:
shared_buffers = 128MB
#effective_cache_size = 4GB
#maintenance_work_mem = 64MB
#work_mem = 4MB
Recommended:
shared_buffers = 966208kB
effective_cache_size = 2830MB
maintenance_work_mem = 483104kB
work_mem = 2415kB
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
max_wal_size = 1GB
Recommended:
wal_buffers = 16MB
min_wal_size = 4GB
max_wal_size = 8GB
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_locks_per_transaction = 64
#effective_io_concurrency = 1
Recommended:
default_statistics_target = 500
random_page_cost = 1.1
checkpoint_completion_target = 0.9
max_locks_per_transaction = 64
effective_io_concurrency = 200
Is this okay? [(y)es/(s)kip/(q)uit]: y
success: miscellaneous settings will be updated
Saving changes to: /var/lib/pgsql/11/data/postgresql.conf
[root@centos75 ~]#
# systemctl restart postgresql-11
(4) Zabbix 4.2
https://www.zabbix.com/download?zabbix=4.2&os_distribution=centos&os_version=7&db=postgresql
Install Zabbix repository
# rpm -Uvh https://repo.zabbix.com/zabbix/4.2/rhel/7/x86_64/zabbix-release-4.2-1.el7.noarch.rpm
Install Zabbix server, frontend, agent
# yum -y install zabbix-server-pgsql zabbix-web-pgsql zabbix-agent
Create initial database
# sudo -u postgres createuser --pwprompt zabbix
# sudo -u postgres createdb -O zabbix zabbix
Import initial schema and data. You will be prompted to enter your newly created password.
# zcat /usr/share/doc/zabbix-server-pgsql*/create.sql.gz | sudo -u zabbix psql zabbix
========================================
https://www.zabbix.com/documentation/4.2/manual/appendix/install/timescaledb
[root@centos75 zabbix-server-pgsql-4.2.3]# pwd
/usr/share/doc/zabbix-server-pgsql-4.2.3
[root@centos75 zabbix-server-pgsql-4.2.3]# ll
total 2292
-rw-r--r-- 1 root root 98 Jun 7 18:18 AUTHORS
-rw-r--r-- 1 root root 1000750 Jun 7 18:19 ChangeLog
-rw-r--r-- 1 root root 17990 Jun 7 18:18 COPYING
-rw-r--r-- 1 root root 1305981 Jun 7 18:26 create.sql.gz
-rw-r--r-- 1 root root 52 Jun 7 18:18 NEWS
-rw-r--r-- 1 root root 1317 Jun 7 18:19 README
-rw-r--r-- 1 root root 219 Jun 7 18:19 timescaledb.sql.gz
[root@centos75 zabbix-server-pgsql-4.2.3]#
root@centos75 zabbix-server-pgsql-4.2.3]# echo "CREATE EXTENSION IF NOT EXISTS timescaledb CASCADE;" | sudo -u postgres psql zabbix
WARNING:
WELCOME TO
_____ _ _ ____________
|_ _(_) | | | _ \ ___ \
| | _ _ __ ___ ___ ___ ___ __ _| | ___| | | | |_/ /
| | | | _ ` _ \ / _ \/ __|/ __/ _` | |/ _ \ | | | ___ \
| | | | | | | | | __/\__ \ (_| (_| | | __/ |/ /| |_/ /
|_| |_|_| |_| |_|\___||___/\___\__,_|_|\___|___/ \____/
Running version 1.3.1
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
[root@centos75 zabbix-server-pgsql-4.2.3]# zcat /usr/share/doc/zabbix-server-pgsql-4.2.3/timescaledb.sql.gz | sudo -u zabbix psql zabbix
create_hypertable
----------------------
(1,public,history,t)
(1 row)
create_hypertable
---------------------------
(2,public,history_uint,t)
(1 row)
create_hypertable
--------------------------
(3,public,history_log,t)
(1 row)
create_hypertable
---------------------------
(4,public,history_text,t)
(1 row)
create_hypertable
--------------------------
(5,public,history_str,t)
(1 row)
create_hypertable
---------------------
(6,public,trends,t)
(1 row)
create_hypertable
--------------------------
(7,public,trends_uint,t)
(1 row)
UPDATE 1
[root@centos75 zabbix-server-pgsql-4.2.3]#
=======================================
Configure the database for Zabbix server
vi /etc/zabbix/zabbix_server.conf
--> DBPassword=password
Configure PHP for Zabbix frontend
vi /etc/httpd/config.d/zabbix.conf
--> php_value date.timezone Asia/Taipei
Start Zabbix server and agent processes
Start Zabbix server and agent processes and make it start at system boot:
# systemctl restart zabbix-server zabbix-agent httpd
# systemctl enable zabbix-server zabbix-agent httpd
其它參考的 URL
https://www.opensourcetech.tokyo/entry/20190510/1557484230
