系统监控平台部署第二节--zabbix安装时序数据库和数据迁移

在上一章节已经安装了zabbix，本想继续编写报警相关配置文章，但奈何随着监控的系统逐渐增多，在运行了一段时间后发现，随着历史数据的越来越大，一但并发过大或者数据量过大，处理能力就显得捉襟见肘，现在zabbix从4.2版本就开始支持时序数据库，安装也不复杂，类似插件的功能，所以本次记录下在现有条件下时序数据库的安装和数据迁移过程。

一、什么是TimescaleDB

Timscaledb(TSDB)是PostgreSQL扩展,它将基于时间序列的性能和数据管理优化添加至常规PostgreSQL(PG)数据库中。虽然不乏可扩展伸缩的时间序列解决方案,但TimescaleDB最好的部分是传统SQL数据库之外的时间序列感知，实践中这意味着双方的优势可以兼得。数据库知道哪些表必须被视为时间序列数据(所有需要的优化都已到位),同时你又可以继续对时间序列和常规数据库表使用SQLs。应用程序甚至不需要知道在传统的SQL接口下有一个Timscaledb扩展

二、系统现状

• 操作系统：centos8
• 数据库：PostgreSQL12
• 软件版本：zabbix5.4

  三、设置Timescale源

  [root@zabbix5 yum.repos.d]# tee /etc/yum.repos.d/timescale_timescaledb.repo <<EOL enabled=1 [timescale_timescaledb] gpgkey=https://packagecloud.io/timescale/timescaledb/gpgkey name=timescale_timescaledb baseurl=https://packagecloud.io/timescale/timescaledb/el/$(rpm -E %{rhel})/\$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

[root@zabbix5 yum.repos.d]# yum update –y

## 四、安装timescaledb 使用yum install -y timescaledb-postgresql-12默认安装的时候，会将timescaledb-2-loader-postgresql安装为12-2.4.1的版本  为了避免此种情况，我在官网下载了配套的1.7.5版本的timescaledb-2-loader-postgresql，先rpm安装后，再YUM方式安装。当然也可以参考官网的2.X版本的手册，全部更换最新版，本例是要利旧postgresql12的数据库，所以稳妥点还是继续用老版本。 http://img.558idc.com/uploadfile/allimg/centos/searchq=1.7.5 由于我之前直接YUM安装，发现版本不太合适，所以需要先卸载了 yum remove timescaledb-2-loader-postgresql-12-2.4.1-0.el8.x86_64 卸载的时候会提示之前安装的timescaledb三个软件包都需卸载了  卸载完后，先安装下载的安装包，再通过yum方式完成安装 rpm -ivh timescaledb-loader-postgresql-12-1.7.5-0.el7.x86_64.rpm  ## 五、初始化timescaledb 通过脚本进行参数初始化并重启postgresql

[root@zabbix5 tmp]# timescaledb-tune --pg-config=/usr/pgsql-12/bin/pg_configUsing postgresql.conf at this path:/var/lib/pgsql/12/data/postgresql.confIs this correct? [(y)es/(n)o]: yWriting backup to:/tmp/timescaledb_tune.backup202109191638shared_preload_libraries needs to be updatedCurrent:#shared_preload_libraries = ''Recommended:shared_preload_libraries = 'timescaledb'Is this okay? [(y)es/(n)o]: ysuccess: shared_preload_libraries will be updated

Tune memory/parallelism/WAL and other settings? [(y)es/(n)o]: yRecommendations based on 62.64 GB of available memory and 16 CPUs for PostgreSQL 12

Memory settings recommendationsCurrent:shared_buffers = 128MB#effective_cache_size = 4GB#maintenance_work_mem = 64MB#work_mem = 4MBRecommended:shared_buffers = 16036MBeffective_cache_size = 48110MBmaintenance_work_mem = 2047MBwork_mem = 10263kBIs this okay? [(y)es/(s)kip/(q)uit]: ysuccess: memory settings will be updated

Parallelism settings recommendationsCurrent:missing: timescaledb.max_background_workers#max_worker_processes = 8#max_parallel_workers_per_gather = 2#max_parallel_workers = 8Recommended:timescaledb.max_background_workers = 8max_worker_processes = 27max_parallel_workers_per_gather = 8max_parallel_workers = 16Is this okay? [(y)es/(s)kip/(q)uit]: ysuccess: parallelism settings will be updated

WAL settings recommendationsCurrent:#wal_buffers = -1min_wal_size = 80MBRecommended:wal_buffers = 16MBmin_wal_size = 512MBIs this okay? [(y)es/(s)kip/(q)uit]: ysuccess: WAL settings will be updated

Miscellaneous settings recommendationsCurrent:#default_statistics_target = 100#random_page_cost = 4.0#checkpoint_completion_target = 0.5#max_locks_per_transaction = 64#autovacuum_max_workers = 3#autovacuum_naptime = 1min#effective_io_concurrency = 1Recommended:default_statistics_target = 500random_page_cost = 1.1checkpoint_completion_target = 0.9max_locks_per_transaction = 512autovacuum_max_workers = 10autovacuum_naptime = 10effective_io_concurrency = 200Is this okay? [(y)es/(s)kip/(q)uit]: ysuccess: miscellaneous settings will be updatedSaving changes to: /var/lib/pgsql/12/data/postgresql.conf

[root@zabbix5 tmp]# systemctl restart postgresql-12

  执行以下命令为特定的数据库启用TimescaleDB扩展项：

[root@zabbix5 tmp]# echo "CREATE EXTENSION IF NOT EXISTS timescaledb CASCADE;" | sudo -u postgres psql zabbixWARNING:WELCOME TO

---

| () | | | \ \| | _ _ _ | | | | | | |/ /| | | | _ \ / _ \/ __|/ __/ _ | |/ \ | | | \| | | | | | | | | /_ \ (| (_| | | _/ |/ /| |/ /|| ||| || ||\||/\_,||_|/ ____/Running version 1.7.5For more information on TimescaleDB, please visit the following links:

Getting started: https://docs.timescale.com/getting-started

API reference documentation: https://docs.timescale.com/api

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 Server和前端必须关闭。

[root@zabbix5 postgresql]# systemctl stop zabbix-server zabbix-agent nginx php-fpm[root@zabbix5 doc]# cd /usr/share/doc/zabbix-sql-scripts/postgresql/[root@zabbix5 postgresql]# lscreate.sql.gz double.sql schema.sql.gz timescaledb.sql.gz

[root@zabbix5 postgresql]# zcat /usr/share/doc/zabbix-sql-scripts/postgresql/timescaledb.sql.gz | sudo -u zabbix psql zabbixNOTICE: PostgreSQL version 12.7 is validNOTICE: TimescaleDB extension is detectedNOTICE: TimescaleDB version 1.7.5 is validNOTICE: migrating data to chunksDETAIL: Migration might take a while depending on the amount of data.NOTICE: migrating data to chunksDETAIL: Migration might take a while depending on the amount of data.NOTICE: migrating data to chunksDETAIL: Migration might take a while depending on the amount of data.NOTICE: migrating data to chunksDETAIL: Migration might take a while depending on the amount of data.NOTICE: migrating data to chunksDETAIL: Migration might take a while depending on the amount of data.NOTICE: migrating data to chunksDETAIL: Migration might take a while depending on the amount of data.NOTICE: TimescaleDB is configured successfully[root@zabbix5 postgresql]# systemctl start zabbix-server zabbix-agent nginx php-fpm

 在web页面中，可以设置对时序数据库启用压缩和压缩时间设置   OK,至此已经完成zabbix时序数据库的部署工作，后续再继续开展各项优化部署。