概述
本文通过实际操作,指导使用PostgreSQL作为zabbix的数据库,并将history和trend相关表迁移到TimescaleDB中。
创建用户
登录到PostgreSQL服务器,并切换到postgres用户下
createuser --pwprompt zabbix
根据命令行提示设置密码。
创建zabbix数据库
createdb -O zabbix -E Unicode -T template0 zabbix
-
-O user 指定数据库owner。
-
-E encoding 指定数据库的编码。
-
-T template 指定创建此数据库的模板。
导入数据库
按照顺序依次导入schema.sql、images.sql和data.sql,对于的sql文件可从zabbix的源码包中获取。
psql -d zabbix -U zabbix -f schema.sql
psql -d zabbix -U zabbix -f images.sql
psql -d zabbix -U zabbix -f data.sql
至此已完成了PostgreSQL数据库的创建,接下来就将zabbix server和zabbix web的配置指向该数据库
配置zabbix server
打开zabbix_server.conf配置文件
# DBHost=localhost
DBHost=[pgsql-ip]
### Option: DBName
# the tnsnames.ora file or set to empty string; also see the TWO_TASK environment variable if DBName is set to
# DBName=
DBName=zabbix
### Option: DBSchema
# DBSchema=
DBSchema=public
### Option: DBUser
# DBUser=
DBUser=zabbix
### Option: DBPassword
# DBPassword=
DBPassword=zabbix
### Option: DBSocket
# DBSocket=
### Option: DBPort
# DBPort=
DBPort=5432
重启zabbix-server服务器
配置zabbix web
打开zabbix web配置页面
http://[zabbix_web]:8080/setup.php
配置DB连接
然后一直[下一步],直到配置完成
至此,我们就完成了zabbix的使用PostgreSQL数据库的配置。
使用TimescaleDB作为历史数据存储
TimescaleDB是基于PostgreSQL的一个插件,可以实现时序性数据库。
使用TimescaleDB可以实现自动历史数据自动分区,提高zabbix的查询效率,扩大监控规模。
启用PostgreSQL的TimescaleDB特性
-
编辑postgresql.conf 文件,在末尾添加以下内容,重启postgresql服务
# Add settings for extensions here timescaledb.telemetry_level=basic timescaledb.last_tuned = '2021-08-16T18:25:24Z' timescaledb.last_tuned_version = '0.11.0'
-
使用psql启用timescaledb插件
使用super用户来登录到postgreSQL数据库,切换到zabbix数据库
\c zabbix;
运行结果:
You are now connected to database "zabbix" as user "postgres".
启用timescale插件
create extension "timescaledb";
运行结果:
WARNING: WELCOME TO _____ _ _ ____________ |_ _(_) | | | _ \ ___ \ | | _ _ __ ___ ___ ___ ___ __ _| | ___| | | | |_/ / | | | | _ ` _ \ / _ \/ __|/ __/ _` | |/ _ \ | | | ___ \ | | | | | | | | | __/\__ \ (_| (_| | | __/ |/ /| |_/ / |_| |_|_| |_| |_|\___||___/\___\__,_|_|\___|___/ \____/ Running version 2.4.0 For more information on TimescaleDB, please visit the following links: 1. Getting started: https://docs.timescale.com/timescaledb/latest/getting-started 2. API reference documentation: https://docs.timescale.com/api/latest 3. How TimescaleDB is designed: https://docs.timescale.com/timescaledb/latest/overview/core-concepts 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.timescale.com/timescaledb/latest/how-to-guides/configuration/telemetry. CREATE EXTENSION
-
检查插件使用情况
# \dx List of installed extensions Name | Version | Schema | Description -------------+---------+------------+------------------------------------------------------------------- plpgsql | 1.0 | pg_catalog | PL/pgSQL procedural language timescaledb | 2.4.0 | public | Enables scalable inserts and complex queries for time-series data
导入zabbix timescaledb.sql数据
psql -d zabbix -U zabbix -f timescaledb.sql
运行结果
psql:timescaledb.sql:69: NOTICE: PostgreSQL version 12.7 is valid
psql:timescaledb.sql:69: NOTICE: TimescaleDB extension is detected
psql:timescaledb.sql:69: NOTICE: TimescaleDB version 2.4.0 is valid
psql:timescaledb.sql:69: NOTICE: migrating data to chunks
DETAIL: Migration might take a while depending on the amount of data.
psql:timescaledb.sql:69: NOTICE: migrating data to chunks
DETAIL: Migration might take a while depending on the amount of data.
psql:timescaledb.sql:69: NOTICE: migrating data to chunks
DETAIL: Migration might take a while depending on the amount of data.
psql:timescaledb.sql:69: NOTICE: migrating data to chunks
DETAIL: Migration might take a while depending on the amount of data.
psql:timescaledb.sql:69: NOTICE: migrating data to chunks
DETAIL: Migration might take a while depending on the amount of data.
psql:timescaledb.sql:69: NOTICE: TimescaleDB is configured successfully
DO
这样就完成了zabbix的历史数据往timescaledb数据库配置迁移。
在zabbix web的housekeeper中,多了两项配置,支持对历史记录进行压缩,压缩了可达到90%以上,建议启用,可极大地节省存储空间
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-rU2OhSiS-1629449675626)(C:\Users\yeqing\AppData\Roaming\Typora\typora-user-images\image-20210817113911197.png)]
Tips
我们可以打开timescaledb.sql,可以看到,sql文件中,创建了7张表,并且每个86400秒(即1天)进行数据轮切,如果希望更短时间,可以在导入前修改此文件。
PERFORM create_hypertable('history', 'clock', chunk_time_interval => 86400, migrate_data => true);
PERFORM create_hypertable('history_uint', 'clock', chunk_time_interval => 86400, migrate_data => true);
PERFORM create_hypertable('history_log', 'clock', chunk_time_interval => 86400, migrate_data => true);
PERFORM create_hypertable('history_text', 'clock', chunk_time_interval => 86400, migrate_data => true);
PERFORM create_hypertable('history_str', 'clock', chunk_time_interval => 86400, migrate_data => true);
PERFORM create_hypertable('trends', 'clock', chunk_time_interval => 2592000, migrate_data => true);
PERFORM create_hypertable('trends_uint', 'clock', chunk_time_interval => 2592000, migrate_data => true);
期间小插曲
-
PostgreSQL连接数满了
-
现象
205:20210817:105226.207 database is down: reconnecting in 10 seconds 180:20210817:105226.208 [Z3001] connection to database 'zabbix' failed: [0] FATAL: remaining connection slots are reserved for non-replication superuser connections
-
解决
增加PostgreSQL的最大连接数
在PostgreSQL安装目录中找到postgresql.conf 文件,修改其中的 max_connections 为200,默认为20,然后重启postgresql服务。
-
-
在启用timescale插件时,使用zabbix用户登录执行时报错
-
现象
Must be superuser to create this extension
-
解决
按照出错提示,使用PostgreSQL超级用户,切换到zabbix库,即可为zabbix用户的zabbix库启用timescale特性
-