在上一章节已经安装了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 <<EOLenabled=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的数据库，所以稳妥点还是继续用老版本。https://packagecloud.io/app/timescale/timescaledb/search?q=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 updatedTune memory/parallelism/WAL and other settings? [(y)es/(n)o]: yRecommendations based on 62.64 GB of available memory and 16 CPUs for PostgreSQL 12Memory 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 updatedParallelism 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 updatedWAL 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 updatedMiscellaneous 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: 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 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时序数据库的部署工作，后续再继续开展各项优化部署。
不如意的时候不要尽往悲伤里钻，想想有笑声的日子吧
