Cara memigrasi Zabbix dari MySQL ke PostgreSQL dengan waktu henti minimal

Mengingat fakta bahwa Zabbix telah mendukung TimescaleDB untuk beberapa waktu sekarang, dan sekarang rilis baru LTS Zabbix telah dirilis, banyak yang mungkin tertarik dengan cara bermigrasi dari MySQL ke PostgreSQL.

Terlepas dari teks dalam gambar, sangat mungkin untuk mengambil dan memigrasi Zabbix dari MySQL ke PostgreSQL. Ada banyak resep untuk migrasi semacam itu di Internet, misalnya:

Laporan dari Video Meetup Zabbix

dari saluran Youtube Dmitry Lambert

, downtime , , .

, .

, Zabbix 4.0. , , , .

, Zabbix : ( ). Zabbix . : , , ?

, , PostgreSQL. , , .

:

• PostgreSQL, 12.
• PgLoader 3.6.2. 3.6.1 - .

PostgreSQL , .

, , :

• , PostgreSQL : https://habr.com/ru/post/505108/
• , : https://pgtune.leopard.in.ua/
• : https://postgrespro.ru/docs/postgrespro/12/index.html

PostgreSQL.

Zabbix 4.0, , :

https://github.com/lesovsky/zabbix-extensions/tree/master/files/postgresql

, Zabbix, nodata, .. .

. , .

— Zabbix server:

systemctl stop zabbix-server

, Zabbix, schema.sql, database/postgresql zabbix, 2 :

CREATE, ALTER.

2 : create.sql, alter.sql

create.sql :

cat create.sql | psql -Uzabbix zabbix 

pgloader — zabbix.load.config

LOAD DATABASE
 FROM mysql://zabbix:zabbix-password@localhost/zabbix
 INTO postgresql://zabbix:zabbix-password@192.168.1.1:5432/zabbix
WITH include no drop,
truncate,
create no tables,
create no indexes,
no foreign keys,
reset sequences,
data only
SET MySQL PARAMETERS
 max_execution_time = '0'
SET PostgreSQL PARAMETERS
 maintenance_work_mem TO '1024MB', work_mem to '128MB'
EXCLUDING TABLE NAMES MATCHING ~/history.*/, ~/trend.*/
ALTER SCHEMA 'zabbix' RENAME TO 'public';

EXCLUDING TABLE NAMES MATCHING ~/history.*/, ~/trend.*/

:

pgloader zabbix.load.config

, , ( ):

alter.sql :

cat alter.sql | psql -Uzabbix zabbix 

, , alter.sql trend history. 4.0 . , , .. , alter.sql

Zabbix, MySQL , PostgreSQL. CentOS 7, , :

yum remove zabbix-server-mysql zabbix-web-mysql
yum install zabbix-server-pgsql zabbix-web-pgsql php-pgsql

:

https://www.zabbix.com/documentation/4.0/manual/installation/install_from_packages

Web Zabbix, :

rm /etc/zabbix/web/zabbix.conf.php

:

/etc/httpd/conf.d/zabbix.conf

zabbix_server.conf: DBHost, DBPort, DBUser, DBName, DBPassword.

Zabbix Server!

systemctl start zabbix-server

web- zabbix (http(s)://ip/zabbix), .

web- , , , .. . . , — .

, , , . , , , .

— .

, .

, .

Zabbix . Zabbix ( , ), . API 200 Zabbix icmp web checks . 1000 NVPS.

, Zabbix server PostgreSQL .

.

zabbix.load.data pgloader:

LOAD DATABASE
 FROM mysql://zabbix:zabbix-password@localhost/zabbix
 INTO postgresql://zabbix:zabbix-password@192.168.1.1:5432/zabbix
WITH include no drop,
no truncate,
create no tables,
create no indexes,
no foreign keys,
reset sequences,
data only,
prefetch rows = 5000,
multiple readers per thread

SET MySQL PARAMETERS
 max_execution_time = '0',
 net_read_timeout = ‘86400’,
 net_write_timeout = ‘86400’
SET PostgreSQL PARAMETERS
 maintenance_work_mem TO '1024MB', work_mem to '128MB'
INCLUDING ONLY TABLE NAMES MATCHING ~/history.*/, ~/trend.*/
ALTER SCHEMA 'zabbix' RENAME TO 'public';

:

no truncate

, . , .

INCLUDING ONLY TABLE NAMES MATCHING ~/history.*/, ~/trend.*/

, .

pgloader zabbix.load.data

, . 150 4-5 .

. , CPU PostgreSQL , ( load average 1 ):

Zabbix SELECT. , , web check, SELECT.

. zabbix.load.data:

prefetch_rows = 1000,
workers = 1,
concurrency = 1,
single reader per thread

, . .

PostgreSQL . , , pgloader:

tuples, PostgreSQL :

, , PostgreSQL . HighLoad:

https://www.youtube.com/watch?v=3h48iowNbwo

, , PostgreSQL (single-page cleanup). tuple. , tuple , CPU.

— , , , :

begin;
create table x(n numeric);

CPU:

— .

, . ? , , , .

. , PostgreSQL .

Zabbix. Zabbix . /. , .

, Zabbix Server PostgreSQL. , ! CPU, . - PostgreSQL . PostgreSQL ?

, Zabbix MySQL PostgreSQL downtime . , , .

, . , - .

feedback, .