如何用pgloader将Zabbix的MySQL数据库迁移到PostgreSQL数据库？|mysql|key|127.0.0.1|postgresql|zabbix

分享至

感谢本文作者董玉凡，Zabbix工程师

摘要

►今天我们使用一款工具pgloader来进行从Zabbix的MySQL数据库将数据迁移到PostgreSQL数据库。

►pgloader是一款开源软件项目，可以将各种来源的数据加载到PostgreSQL当中，可以支持动态读取的数据，使用 COPY 流式传输协议将数据加载到 PostgreSQL 中，并使用单独的线程来读取和写入数据，由于能够直接从源数据库加载数据，pgloader还支持从其他产品迁移到PostgreSQL。今天我们就借助pgloader这款工具实现从MySQL数据库迁移到PostgreSQL数据库。

►zabbix6.0对于数据库的版本要求如下表所示：

Software Mandatory status Supported versions Comments MySQL/Percona One of 8.0.X Required if MySQL (or Percona) is used as Zabbix backend database. InnoDB engine is required. We recommend using the library for building server/proxy. PostgreSQL One of 13.0-15.X Required if PostgreSQL is used as Zabbix backend database. PostgreSQL 15 is supported since Zabbix 6.0.10.

►使用测试的系统版本、应用版本、数据库版本如下：

操作系统版本：CentOS Linux release 8.0.1905 (Core)

Zabbix版本：6.0.12

MySQL版本：8.0.31

PostgreSQL：13.5

安装zabbix server及其组件和MySQL数据库就不再赘述，可以查看官方文档协助安装。

安装PostgreSQL数据库

创建postgres用户及安装目录

# useradd postgres # mkdir -p /app/postgresql

安装PostgreSQL依赖包

# yum install -y perl-ExtUtils-Embed readline-devel python3 python3-devel gcc-c++ cmake libarchive openssl-devel

部署PostgreSQL

# wget https://ftp.postgresql.org/pub/source/v13.5/postgresql-13.5.tar.gz ##下载安装包 # tar -zxvf postgresql-13.5.tar.gz ##解压安装包 # cd postgresql-13.5/ # ./configure --prefix=/app/postgresql --with-python --with-perl --with-openssl # make &&make install # /app/postgresql/bin/pg_ctl --version ##查看已经安装成功 pg_ctl (PostgreSQL) 13.5

配置环境变量

# mkdir -p /app/postgresql/pgdata ##创建数据库的数据目录 # cat >> /etc/profile << EOF ### postgres ### export PATH=/app/postgresql/bin:$PATH export LD_LIBRARY_PATH=/app/postgresql/lib export PGDATA=/app/postgresql/pgdata EOF # source /etc/profile # pg_ctl --version ##环境变量配置成功 pg_ctl (PostgreSQL) 13.5

给用户目录赋权并创建数据库簇

# chown -R postgres.postgres /app/postgresql ##修改postgresql所属组和所属用户 # su - postgres # initdb ##初始化数据库

为zabbix创建PostgreSQL用户密码和数据库

由于postgresql数据库和pgloader工具装在一台服务器上，所以postgresql不需要配置远程访问，如需要配置在/app/postgresql/pgdata/pg_hba.conf中进行远程访问的配置

# pg_ctl start -D $PGDATA ##启动数据库 # createuser -P zabbix ##输出两次密码 Enter password for new role: Enter it again: # createdb -O zabbix -E Unicode -T template0 zabbix ##创建数据库

安装pgloader工具

下载pgloader安装包

# wget https://codeload.github.com/dimitri/pgloader/tar.gz/refs/tags/v3.6.9 # tar -zxvf v3.6.9

部署pgloader工具

# dnf -y install freetds-devel ##安装依赖包 # cd pgloader-3.6.9/ # chmod 755 bootstrap-centos.sh # ./bootstrap-centos.sh # make pgloader # cp build/bin/pgloader /usr/local/bin/ # pgloader --version pgloader version "3.6.7~devel" compiled with SBCL 2.2.10-1.rhel8

迁移MySQL数据库到PostgreSQL数据库

修改MySQL数据库默认身份验证方式

# echo "default-authentication-plugin=mysql_native_password" >> /etc/my.conf ##pgloader不支持caching_sha2_password身份验证插件,而这个是 MySQL 8 的默认设置，所以需要修改这个配置，如果是MySQL8.0之前的版本无需这步操作 # systemctl restart mysqld ##更改完配置文件重启数据库

下载zabbix源码包并解压

# wget https://cdn.zabbix.com/zabbix/sources/stable/6.0/zabbix-6.0.12.tar.gz ##下载zabbix源码包 # tar -zxvf zabbix-6.0.12.tar.gz # cd zabbix-6.0.12

导入schema.sql到postgresql数据库

►导入zabbix表结构schema.sql到postgresql数据库中，只保留其中的create语句，不需要添加外键约束,包含INSERT INTO dbversion VALUES ('1','6000000','6000017');这条sql都需要删除掉

# vi database/postgresql/schema.sql ##从INSERT INTO dbversion这行开始往下全部删除，参考命令 :.,$d CREATE INDEX sla_service_tag_1 ON sla_service_tag (slaid); CREATE TABLE dbversion ( dbversionid bigint NOT NULL, mandatory integer DEFAULT '0' NOT NULL, optional integer DEFAULT '0' NOT NULL, PRIMARY KEY (dbversionid) INSERT INTO dbversion VALUES ('1','6000000','6000017'); create or replace function hosts_name_upper_upper() returns trigger language plpgsql as $func$ begin update hosts set name_upper=upper(name) where hostid=new.hostid; # psql -Uzabbix -dzabbix -f database/postgresql/schema.sql

迁移MySQL的配置数据

►参考pgloader文档：

# mkdir -p /root/migration # cd /root/migration # vi config.pgloader ##当复制下面配置的时候请去除所有的注释 LOAD DATABASE FROM mysql://zabbix:*****@127.0.0.1:3306/zabbix INTO postgresql://zabbix:*****@127.0.0.1:5432/zabbix WITH include no drop, #当列出此选项时，pgloader在加载数据时将不包含任何DROP语句。 truncate, #当列出这个选项时，pgloader在将数据加载到每个PostgreSQL表之前，对每个PostgreSQL表发出TRUNCATE命令。删除表中的所有行，但表结构及其列、约束、索引等保持不变。新行标识所用的计数值重置为该列的种子 create no tables, #当列出此选项时，pgloader在加载数据之前跳过表的创建，目标表必须已经存在。 #此外，当使用不创建表时，pgloader从当前目标数据库获取元数据并检查类型转换，然后在加载数据之前删除约束和索引，并在加载完成后重新安装它们。 create no indexes, #当列出此选项时，pgloader将跳过创建索引。 no foreign keys, #当列出此选项时，pgloader将跳过创建外键。 reset sequences, #当列出这个选项时，在数据加载结束时，在所有索引都创建完成之后，pgloader将创建的所有PostgreSQL序列重置为它们所附列的当前最大值。 data only #当列出此选项时，pgloader只发出COPY语句，而不进行任何其他处理。 SET maintenance_work_mem TO '1024MB', work_mem to '512MB' #设置maintenance_work_mem和work_mem,根据自己机器的配置来设置，越大迁移越快 EXCLUDING TABLE NAMES MATCHING ~/history.*/, ~/trend.*/ #不迁移history表和trends表 ALTER SCHEMA 'zabbix' RENAME TO 'public'; #将pgloader转换生成的zabbix模式更名为public

# pgloader config.pgloader ##开始迁移所有的配置不包含历史数据 Total import time ✓ 126602 12.5 MB 3.820s ##由于是一个新库没什么数据，耗时较少

查看迁移的配置数

迁移MySQL历史数据

# cd /root/migration # vi data.pgloader ##过滤掉除了history和trends的七张表，每个大版本的表数量不相同，下面过滤的表请按实际版本中表数量过滤 LOAD DATABASE FROM mysql://zabbix:*****@127.0.0.1:3306/zabbix INTO postgresql://zabbix:*****@127.0.0.1:5432/zabbix WITH include no drop, no truncate, create no tables, create no indexes, no foreign keys, reset sequences, data only SET maintenance_work_mem TO '1024MB', work_mem TO '512MB' EXCLUDING TABLE NAMES MATCHING 'acknowledges', 'actions', 'alerts', 'auditlog', 'autoreg_host', 'conditions', 'config', 'config_autoreg_tls', 'corr_condition', 'corr_condition_group', 'corr_condition_tag', 'corr_condition_tagpair', 'corr_condition_tagvalue', 'corr_operation', 'correlation', 'dashboard', 'dashboard_page', 'dashboard_user', 'dashboard_usrgrp', 'dbversion', 'dchecks', 'dhosts', 'drules', 'dservices', 'escalations', 'event_recovery', 'event_suppress', 'event_tag', 'events', 'expressions', 'functions', 'globalmacro', 'globalvars', 'graph_discovery', 'graph_theme', 'graphs', 'graphs_items', 'group_discovery', 'group_prototype', 'ha_node', 'host_discovery', 'host_inventory', 'host_tag', 'hostmacro', 'hosts', 'hosts_groups', 'hosts_templates', 'housekeeper', 'hstgrp', 'httpstep', 'httpstep_field', 'httpstepitem', 'httptest', 'httptest_field', 'httptest_tag', 'httptestitem', 'icon_map', 'icon_mapping', 'ids', 'images', 'interface', 'interface_discovery', 'interface_snmp', 'item_condition', 'item_discovery', 'item_parameter', 'item_preproc', 'item_rtdata', 'item_tag', 'items', 'lld_macro_path', 'lld_override', 'lld_override_condition', 'lld_override_opdiscover', 'lld_override_operation', 'lld_override_ophistory', 'lld_override_opinventory', 'lld_override_opperiod', 'lld_override_opseverity', 'lld_override_opstatus', 'lld_override_optag', 'lld_override_optemplate', 'lld_override_optrends', 'maintenance_tag', 'maintenances', 'maintenances_groups', 'maintenances_hosts', 'maintenances_windows', 'media', 'media_type', 'media_type_message', 'media_type_param', 'module', 'opcommand', 'opcommand_grp', 'opcommand_hst', 'opconditions', 'operations', 'opgroup', 'opinventory', 'opmessage', 'opmessage_grp', 'opmessage_usr', 'optemplate', 'problem', 'problem_tag', 'profiles', 'proxy_autoreg_host', 'proxy_dhistory', 'proxy_history', 'regexps', 'report', 'report_param', 'report_user', 'report_usrgrp', 'rights', 'role', 'role_rule', 'script_param', 'scripts', 'service_alarms', 'service_problem', 'service_problem_tag', 'service_status_rule', 'service_tag', 'services', 'services_links', 'sessions', 'sla', 'sla_excluded_downtime', 'sla_schedule', 'sla_service_tag', 'sysmap_element_trigger', 'sysmap_element_url', 'sysmap_shape', 'sysmap_url', 'sysmap_user', 'sysmap_usrgrp', 'sysmaps', 'sysmaps_element_tag', 'sysmaps_elements', 'sysmaps_link_triggers', 'sysmaps_links', 'tag_filter', 'task', 'task_acknowledge', 'task_check_now', 'task_close_problem', 'task_data', 'task_remote_command', 'task_remote_command_result', 'task_result', 'timeperiods', 'token', 'trigger_depends', 'trigger_discovery', 'trigger_queue', 'trigger_tag', 'triggers', 'users', 'users_groups', 'usrgrp', 'valuemap', 'valuemap_mapping', 'widget', 'widget_field' ALTER SCHEMA 'zabbix' RENAME TO 'public';

# pgloader data.pgloader ##只有一台zabbixserver的监控数据，数据很少，用时很少 2022-12-08T17:08:58.009000+08:00 LOG pgloader version "3.6.7~devel" 2022-12-08T17:08:58.071001+08:00 LOG Migrating from # 2022-12-08T17:08:58.071001+08:00 LOG Migrating into # 2022-12-08T17:08:58.223003+08:00 WARNING Source column "public"."history_uint"."value" is casted to type "bigint" which is not the same as "numeric", the type of current target database column "public"."history_uint"."value". 2022-12-08T17:08:58.223003+08:00 WARNING Source column "public"."trends_uint"."value_min" is casted to type "bigint" which is not the same as "numeric", the type of current target database column "public"."trends_uint"."value_min". 2022-12-08T17:08:58.223003+08:00 WARNING Source column "public"."trends_uint"."value_avg" is casted to type "bigint" which is not the same as "numeric", the type of current target database column "public"."trends_uint"."value_avg". 2022-12-08T17:08:58.223003+08:00 WARNING Source column "public"."trends_uint"."value_max" is casted to type "bigint" which is not the same as "numeric", the type of current target database column "public"."trends_uint"."value_max". 2022-12-08T17:08:58.471008+08:00 LOG report summary reset table name errors rows bytes total time ----------------------- --------- --------- --------- -------------- fetch meta data 0 7 0.076s ----------------------- --------- --------- --------- -------------- public.history 0 8008 286.8 kB 0.110s public.history_uint 0 2429 74.1 kB 0.067s public.trends_uint 0 50 1.6 kB 0.082s public.history_text 0 2 52.6 kB 0.117s public.trends 0 142 8.4 kB 0.020s public.history_str 0 12 0.9 kB 0.036s public.history_log 0 0 0.025s ----------------------- --------- --------- --------- -------------- COPY Threads Completion 0 4 0.171s Reset Sequences 0 0 0.027s Install Comments 0 0 0.000s ----------------------- --------- --------- --------- -------------- Total import time ✓ 10643 424.5 kB 0.198s

查看迁移的历史数据

# psql -Uzabbix # \c zabbix # zabbix=> select * from history; itemid | clock | value | ns --------+------------+------------------------+----------- 10073 | 1670483513 | 0.8324606300857088 | 661499763 10073 | 1670483573 | 1.0157088072329086 | 718082482 10073 | 1670483693 | 0.8991120422218923 | 907381983 10073 | 1670483753 | 1.015714184025525 | 963646786 10073 | 1670483813 | 1.0329172154884476 | 19686404 10073 | 1670483873 | 1.0158031677606336 | 70690315 10073 | 1670483933 | 1.0157542389272938 | 124586880 10073 | 1670483993 | 1.015691219317195 | 182209551 10073 | 1670484053 | 1.0156428524089065 | 242692284

设置外键约束

►由于两次迁移只迁移了配置数据和历史数据，对应的外键约束却没有设置，现在开始设置外键约束

# cd /root/ # tar -zxvf zabbix-6.0.12.tar.gz ##由于之前的步骤更改过shcema.sql文件，现在重新解压源码包 # cd zabbix-6.0.12 # cat schema.sql |tail -n +2090 > altertable.sql > altertable.sql ##将所有的ALTER以及另一段sql放入altertable.sql中 # psql -Uzabbix -dzabbix -f database/postgresql/altertable.sql # psql -Uzabbix # \c zabbix # \d+ items Foreign-key constraints: "c_items_1" FOREIGN KEY (hostid) REFERENCES hosts(hostid) ON DELETE CASCADE "c_items_2" FOREIGN KEY (templateid) REFERENCES items(itemid) ON DELETE CASCADE "c_items_3" FOREIGN KEY (valuemapid) REFERENCES valuemap(valuemapid) "c_items_4" FOREIGN KEY (interfaceid) REFERENCES interface(interfaceid) "c_items_5" FOREIGN KEY (master_itemid) REFERENCES items(itemid) ON DELETE CASCADE

zabbix-server连接PostgreSQL数据库

停止zabbix server并卸载

# systemctl stop zabbix-server zabbix-agent nginx php-fpm ##停止所有的应用 # dnf remove zabbix-server-mysql zabbix-web-mysql zabbix-nginx-conf zabbix-sql-scripts zabbix-selinux-policy -y

重新安装PostgreSQL的zabbix server

# dnf -y install zabbix-server-pgsql zabbix-web-pgsql zabbix-nginx-conf zabbix-sql-scripts zabbix-selinux-policy

修改zabbix-server配置文件的数据库密码

►编辑配置文件 /etc/zabbix/zabbix_server.conf

DBPassword=password ##创建mysql的zabbix用户设置的密码

配置nginx

►编辑配置文件 /etc/nginx/conf.d/zabbix.conf

# listen 8080; # server_name example.com;

删除前端连接数据库的配置文件

# rm /etc/zabbix/web/zabbix.conf.php ##由于之前是连接mysql的前端配置文件所以需要删除掉

启动zabbix server、php、nginx和agent并设置开机自启动

# systemctl restart zabbix-server zabbix-agent nginx php-fpm # systemctl enable zabbix-server zabbix-agent nginx php-fpm

登录zabbix前端查看

►此文件仅供参考作用，如果用到生产环境切记要先测试！！！

有奖征稿中，欢迎联系小Z。

在线课放送《如何实现Prometheus对K8S的监控》

扫一扫｜加入技术交流群

微信号｜17502189550

备注“使用Zabbix年限+企业+姓名”

5000+用户已加入！

一个人走得快，一群人走得远！

特别声明：以上内容(如有图片或视频亦包括在内)为自媒体平台“网易号”用户上传并发布，本平台仅提供信息存储服务。

Notice: The content above (including the pictures and videos if any) is uploaded and posted by a user of NetEase Hao, which is a social media platform and only provides information storage services.