How to migrate Zabbix from MySQL to PostgreSQL with minimal downtime

In light of the fact that Zabbix has been supporting TimescaleDB for some time now, and now a new LTS release of Zabbix has been released, many are probably interested in how to migrate from MySQL to PostgreSQL.

Despite the text in the picture, it is quite possible to just take and migrate Zabbix from MySQL to PostgreSQL. There are many recipes for such a migration on the Internet, for example:

Report from Zabbix Meetup

Video from Youtube channel 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, .