博观而约取，厚积而薄发。这篇文章主要讲述系统监控平台部署第二节--zabbix安装时序数据库和数据迁移相关的知识，希望能为你提供帮助。
[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_config
Using postgresql.conf at this path:
/var/lib/pgsql/12/data/postgresql.conf
Is this correct? [(y)es/(n)o]: y
Writing backup to:
/tmp/timescaledb_tune.backup202109191638
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 62.64 GB of available memory and 16 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 = 16036MB
effective_cache_size = 48110MB
maintenance_work_mem = 2047MB
work_mem = 10263kB
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 = 27
max_parallel_workers_per_gather = 8
max_parallel_workers = 16
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_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_locks_per_transaction = 512
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: /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; " | s
udo -u
postgres psql zabbix
WARNING:
WELCOME TO
|()| ||\\ \\
| | _ _ _| | | | | | |/ /
| | | |
_ \\ / _ \\/ __|/ __/ _ | |/ \\ | | | \\| | | | | | | | |/_ \\ (| (_| | |_/ |/ /| |/ /
|| ||| || ||\\||/\\_,||_|/ ____/
Running version 1.7.5
For 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
For more information and how to disable, please see our docs https://docs.timescaledb.com/using-timescaledb/telemetry.
【系统监控平台部署第二节--zabbix安装时序数据库和数据迁移】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]# ls
create.sql.gzdouble.sqlschema.sql.gztimescaledb.sql.gz
[root@zabbix5 postgresql]# zcat /usr/share/doc/zabbix-sql-scripts/postgresql/timescaledb.sql.gz | sudo -u zabbix psql zabbix
NOTICE:PostgreSQL version 12.7 is valid
NOTICE:TimescaleDB extension is detected
NOTICE:TimescaleDB version 1.7.5 is valid
NOTICE:migrating data to chunks
DETAIL:Migration might take a while depending on the amount of data.
NOTICE:migrating data to chunks
DETAIL:Migration might take a while depending on the amount of data.
NOTICE:migrating data to chunks
DETAIL:Migration might take a while depending on the amount of data.
NOTICE:migrating data to chunks
DETAIL:Migration might take a while depending on the amount of data.
NOTICE:migrating data to chunks
DETAIL:Migration might take a while depending on the amount of data.
NOTICE:migrating data to chunks
DETAIL:Migration might take a while depending on the amount of data.
NOTICE:TimescaleDB is configured successfully
[root@zabbix5 postgresql]# systemctl startzabbix-server zabbix-agent nginx php-fpm
在web页面中，可以设置对时序数据库启用压缩和压缩时间设置
OK,至此已经完成zabbix时序数据库的部署工作，后续再继续开展各项优化部署。推荐阅读
- 使用locust测试MQTT协议
- HarmonyOS Sample 之 Bluetooth 传统蓝牙的使用
- 搭建FastDFS分布式集群通过Nginx+Lua+GraphicsMagick实现动态压缩图片
- Windows Server 2016部署MDT服务器----测试映像部署
- 测试开发之前端篇-CSS层叠式样式表
- 9.13-9.19上周精彩回顾
- Java技术指南「并发编程专题」Fork/Join框架基本使用和原理探究（原理及源码篇）
- 基于微信小程序的疫苗预约系统 疫苗小程序毕业设计课题选题 毕设项目作品 毕业设计论文
- TiDB集群运维之版本升级