Upgrade zabbix V3 to V4

[amar3lo]

Hi,

i was trying to migrate a zabbix database 3.0 to 4.0 by following "Upgrade Zabbix v3.4 to v4.2 while moving from PostgreSQL v9.6 to PostgreSQL v11" step

i had to change the following command because my database is partitioned:

sudo -u postgres pg_dump -Fd -j 4 -d zabbix -h 10.0.0.67 -U zabbix --inserts -Z 4 --file=/var/backup/zabbix-configuration --exclude-table=partitions.history --exclude-table=partitions.trends

it worked (i thought).

when i try to restore, the following error appeared:

Command was: INSERT INTO public.history_text VALUES (60218855, 225425, 1526026913, '', 398143845); pg_restore: [archiver (db)] could not execute query: ERROR: INSERT has more expressions than target columns LINE 1: ...ry_text VALUES (60218856, 225426, 1526026913, '', 406441977)... Command was: INSERT INTO public.history_text VALUES (60218856, 225426, 1526026913, '', 406441977); pg_restore: [archiver (db)] could not execute query: ERROR: INSERT has more expressions than target columns LINE 1: ...ry_text VALUES (60218857, 225427, 1526026913, '', 419280804)... Command was: INSERT INTO public.history_text VALUES (60218857, 225427, 1526026913, '', 419280804); pg_restore: [archiver (db)] could not execute query: ERROR: INSERT has more expressions than target columns LINE 1: ...ry_text VALUES (60218858, 225428, 1526026913, '', 427816586)...

should i add public.history and public.trends in the exclude option? or this guide does not work to upgrade zabbix V3.0 to zabbix 4.0 database?

thanks in advance!

[Doctorbal]

@amar3lo ,

Thank you for raising this problem.

Truth be told this section was added by a contributor and I did not fully test it on my behalf. Thus I apologize if this potentially confuses people and causes issues. It is my fault for not fully vetting it.

That being said how did you partition the database? Were you following this guide? It appears you have a schema called "partitions.". I tried to make it simply by using the public.history* and public.trends* schema on the create empty history and trends tables.

You could alter the table's schema simply by running:

ALTER TABLE partitions.history_text SET SCHEMA public;

Does \d+ history_text indeed show the history table in the partitions schema?

But I don't believe the schema is the problem in this particular example.

Based on the following PostgreSQL Application/User Error it appears the table you are trying to insert into is missing a specific column. Can you provide the output for the following SQL command display?

\d+ history_text

It should match this working example (Zabbix 4.0.12, PostgreSQL 11.4, Debian 9 OS) using pg_partman); take particular note of the columns there...

zabbix=# \d+ history_text
                                Table "public.history_text"
 Column |  Type   | Collation | Nullable | Default  | Storage  | Stats target | Description
--------+---------+-----------+----------+----------+----------+--------------+-------------
 itemid | bigint  |           | not null |          | plain    |              |
 clock  | integer |           | not null | 0        | plain    |              |
 value  | text    |           | not null | ''::text | extended |              |
 ns     | integer |           | not null | 0        | plain    |              |
Partition key: RANGE (clock)
Indexes:
    "history_text_1" btree (itemid, clock)
Partitions: history_text_p2019_10_29 FOR VALUES FROM (1572307200) TO (1572393600),
            history_text_p2019_10_30 FOR VALUES FROM (1572393600) TO (1572480000),
            history_text_p2019_10_31 FOR VALUES FROM (1572480000) TO (1572566400),
            history_text_p2019_11_01 FOR VALUES FROM (1572566400) TO (1572652800),
            history_text_p2019_11_02 FOR VALUES FROM (1572652800) TO (1572739200),
            history_text_p2019_11_03 FOR VALUES FROM (1572739200) TO (1572825600),
            history_text_p2019_11_04 FOR VALUES FROM (1572825600) TO (1572912000),
            history_text_p2019_11_05 FOR VALUES FROM (1572912000) TO (1572998400),
            history_text_p2019_11_06 FOR VALUES FROM (1572998400) TO (1573084800),
            history_text_p2019_11_07 FOR VALUES FROM (1573084800) TO (1573171200),
            history_text_p2019_11_08 FOR VALUES FROM (1573171200) TO (1573257600),
            history_text_p2019_11_09 FOR VALUES FROM (1573257600) TO (1573344000),
            history_text_p2019_11_10 FOR VALUES FROM (1573344000) TO (1573430400),
            history_text_p2019_11_11 FOR VALUES FROM (1573430400) TO (1573516800),
            history_text_p2019_11_12 FOR VALUES FROM (1573516800) TO (1573603200),
            history_text_p2019_11_13 FOR VALUES FROM (1573603200) TO (1573689600),
            history_text_default DEFAULT

Best Regards, Andreas

[amar3lo]

the partitioning was made it by folowing this tutorial:

https://zabbix.org/wiki/Docs/howto/zabbix2_postgresql_autopartitioning

this is a \d+ from my current production database:

i'm just want to export the configurations like maps, hosts and templates. the data itself can be avoided.

[Doctorbal]

@amar3lo ,

Thanks for the details.

I actually did follow that tutorial in the past and because of heavy DB locking caused by the triggers deprecated it and looked into using pg_partman.

From the output of your DESCRIBE TABLE (\d+):

1. You schema is evidently public. Did you any changes since your initial post to cause it to be public or was it always public?
2. From the DESCRIBE TABLE (\d+) output of public.history_text you have an additional column called id with a PRIMARY KEY index associated with it as well as a UNIQUE index associated with it. Why is that?

I would suggest looking into dropping the additional index associated with the id column and also removing the constraint (primary key) and then remove the id column. I don't know where it is used but maybe you have a very good reason.

I would also test this in a dev environment with the same setup as in your production to see if it causes any issues.

Example SQL commands to drop the unneeded index and PRIMARY KEY on public.history_text table and column:

zabbix=# DROP INDEX history_text_2;
zabbix=# ALTER TABLE public.history_text DROP CONSTRAINT history_text_pkey;

Now to answer your inquiry on exporting and importing the configurations and avoid the data try the following. I kept it simple and used the COPY method with success:

$ sudo -u postgres pg_dump -Fc --file=/var/backups/postgresql/zabbix-configuration.dump -d zabbix -U zabbix -h 127.0.0.1 --exclude-table=history* --exclude-table=trends*
$ sudo -u postgres pg_restore -j 8 -d zabbix /var/backups/postgresql/zabbix-configuration.dump -U zabbix -h 127.0.0.1

This should hopefully work as you technically aren't inserting data into the history* or trends* tables.

Let me know how it goes.

Best Regards, Andreas

[amar3lo]

R 1: it was always public, i follow the standard procedure to install the initial schema back in the day.. #zcat zabbix schema...

R 2: i have no idea. its is a original pure install zabbix 3.0... i look over the register and find nothing on this. we have 'zabbix extras' but i look over the documentations and he is pure API call, i didn't find any clue on database interaction

its is a fishy situation.. for now, i will just backup the configuration and restore in a new server, then i will manually export the configuration from the web to a brand new database created based on your guide.

once again, thanks for the fast response and attention.

[Doctorbal]

@amar3lo ,

Sounds good. Good luck and let me know if there are any other issues are face that I might be able to help with.

I will go ahead and close out this issue.

Thanks & Best Regards, Andreas