Upgrade to PSQL12 and Zabbix 5.0.x

[mjtrangoni]

Hi @Doctorbal,

While trying to prepare the upgrade to Zabbix 5.0, I faced the issue of the new double precision.

This requires PSQL >= 12, as said here. See the patch here.

What is your opinion about this? Would you mind making a paragraph for this case here.

I have also to test this well before the final move. Is this correct?

-- history
CREATE TABLE public.history
(
    itemid bigint NOT NULL,
    clock integer NOT NULL DEFAULT 0,
    value DOUBLE PRECISION NOT NULL DEFAULT '0.0000',
    ns integer NOT NULL DEFAULT 0
) PARTITION BY RANGE (clock);

CREATE INDEX history_1 ON public.history USING btree (itemid, clock);

-- trends
CREATE TABLE public.trends
(
    itemid bigint NOT NULL,
    clock integer NOT NULL DEFAULT 0,
    num integer NOT NULL DEFAULT 0,
    value_min DOUBLE PRECISION NOT NULL DEFAULT 0.0000,
    value_avg DOUBLE PRECISION NOT NULL DEFAULT 0.0000,
    value_max DOUBLE PRECISION NOT NULL DEFAULT 0.0000,
    CONSTRAINT trends_pkey PRIMARY KEY (itemid, clock)
) PARTITION BY RANGE (clock);

[rustequal]

Are "btree" indexes still relevant ? or is it better to use "BRIN" ?

[Doctorbal]

@mjtrangoni ,

Great to hear that you are taking the initiative of upgrading to Zabbix 5.0! I attended some of their online webinars and it looks fantastic. I have not tried to upgrade yet.

My opinion is that you are making a good choice. It is feasible to upgrade BUT always test the upgrade first! Test at least for a few weeks and ensure all your custom scripts and integrations work correctly. Otherwise you risk spending more time fixing issues and AFAIK there is no way to downgrade. Take a pg_dump of the production instance, pg_restore it on your dev instance and run through some common scenarios. Also check that pg_partman and other postgres modules work correctly.

Regarding SCHEMA changes in Zabbix 5.0 (create.sql.gz), it appears you are right:

CREATE TABLE history (
	itemid                   bigint                                    NOT NULL,
	clock                    integer         DEFAULT '0'               NOT NULL,
	value                    DOUBLE PRECISION DEFAULT '0.0000'          NOT NULL,
	ns                       integer         DEFAULT '0'               NOT NULL
);
CREATE INDEX history_1 ON history (itemid,clock);

CREATE TABLE trends (
	itemid                   bigint                                    NOT NULL,
	clock                    integer         DEFAULT '0'               NOT NULL,
	num                      integer         DEFAULT '0'               NOT NULL,
	value_min                DOUBLE PRECISION DEFAULT '0.0000'          NOT NULL,
	value_avg                DOUBLE PRECISION DEFAULT '0.0000'          NOT NULL,
	value_max                DOUBLE PRECISION DEFAULT '0.0000'          NOT NULL,
	PRIMARY KEY (itemid,clock)
);

Thus what you mentioned should be correct. Partitioning on the clock column should work well.

I will look to upgrade my development instance to version 5.0 and PostgreSQL version 12 in the next few weeks and update the notes here.

@rustequal , regarding BRIN indexes that is purely subjective. The only reason I would suggest BRIN indexes would be if the partition table is very big. PostgreSQL index defaults to B-tree indexes.

Let me know if you have any more questions.

Thanks & Best Regards,
Andreas

[Doctorbal]

• Update notes for fresh install Zabbix version 5.0 LTS
• Update notes for upgrade from version 4.0 LTS to Zabbix version 5.0 LTS

[Jonybat]

I just upgraded PostgreSQL in my 4.4 installation from 11 to 12 and there was a small issue that i overlooked that might be worth mentioning.

You need to make sure you have pg_partman >= 4.2.0 and that you upgrade the extension in pg:

SELECT * FROM pg_available_extensions WHERE name = 'pg_partman';

name | default_version | installed_version | comment
pg_partman | 4.4.0 | 4.0.0 | Extension to manage partitioned tables by time or ID

ALTER EXTENSION pg_partman UPDATE TO '4.4.0';

Otherwise you will get errors like:

postgres@zabbix ERROR: column "relhasoids" does not exist

[Doctorbal]

@Jonybat ,

Thanks for mentioning that. I will make sure to include that in the notes.

Regards,
Andreas

• Update notes to address minimum version of pg_partman 4.2.0 for PostgreSQL version 12.

[mjtrangoni]

PSQL 12.3 delivers 4.4.0 already

zabbix=> select * from pg_available_extensions WHERE name = 'pg_partman';
    name    | default_version | installed_version |                       comment                        
------------+-----------------+-------------------+------------------------------------------------------
 pg_partman | 4.4.0           | 4.4.0             | Extension to manage partitioned tables by time or ID
(1 row)

[Jonybat]

Yes, as i said above, in my case it was an upgrade from PSQL 11, and i had to upgrade the extension manually in psql.

I also ran into the issue described here in pgpartman/pg_partman#91

Scheduled partman maintenances were failing with deadlocks, but running maintenances manually from psql would work. I changed the constraint_valid to false for all partitions, as described in the last comment of that issue, and it started working. This never happened before the upgrade to PSQL 12, so i guess its also worth mentioning.

[rouba002]

This is little off/topic, but I am really curious about the upgrade as there are few interesting changes in v12, performance and size of the tables. Still had no time to check in my test instance.

https://www.cybertec-postgresql.com/en/b-tree-index-improvements-in-postgresql-v12/

And for real bleeding edge - anyone bold enough to check v13?
https://www.cybertec-postgresql.com/en/b-tree-index-deduplication/

[Doctorbal]

I still haven't had the time either to test out the upgrade process. PostgreSQL v13 also comes with some interesting native logical replication support for partitioned tables; reference.

[Doctorbal]

I haven't looked at this due to my priorities but hope to revisit this soon.

In particular I would like to migrate the Zabbix infrastructure to cloud (Azure) in order for a more reliable environment.

Right now Azure supports Single Server PostgreSQL version 11 with extensions pg_partman and TimescaleDB (single server extensions). That is awesome. BUT I wish it were PostgreSQL 12 supported too and available in the Flexible Server option; it isn't as of this writing (flexible server extensions).

Additionally writing the terraform templates for this will make migration easier.

IMHO using timescaleDB is a better option than pg_partman purely because:

1. TimescaleDB has a whole team of developers supporting it opposed to the sole (but very savvy) individual for pg_partman.
2. Zabbix supports it! Woah!

Again the only thing holding me back is PostgreSQL 11 only support for Azure in Single Server instance (not preferred) but I'll see where I can go with it.