Zabbix database lift + shift PostgreSQL migration

 I've been running Zabbix a few years now, and typically put new versions on different hardware as I explore how much power and network are needed even for a relatively small set of hosts. Underpowered components show up as dropped connections and lost data cycles.

I decides to move up the underlying databases on 2 instances from 14 and 15 to both run on PGSQL 16. As I have the databases on NetBSD, I could not easily use the pg_upgrade tool. That needs the source and target versions to both run on one host (as as as I can see), and the NetBSD standard pkgsrc only permits one version. I didn't have the time or skills to install outside the package source.

So, I went with a database export, re-install the database server software, then import. I didn't rush things, and had less than 2 hours of down-time, using a fairly old hardware box (2014 / AMD Family 16h (686-class), 1996.25 MHz).

Much of the time was upgrading or installing about 700 packages that have gone stale in the several years since I touched that box other than small package installs.

20 minutes for the first try:

---Oct 14 00:20:32: [1/780] upgrading freeglut-3.6.0...

---Oct 14 00:40:31: [780/780] upgrading tex-latex-20241101...

Then, after clearing the deadwood and trying again a bit later after the database, the POST GIS extension (times in GMT):

---Oct 14 01:43:15: [1/14] installing tiff-4.7.0nb3...

---Oct 14 01:44:36: [14/14] installing postgresql16-postgis-3.6.0...

Times in EST5EDT:

DB dump

Under a minute, with SSD.

$ time pg_dump -U zabbixadmin -d zabbix -h localhost > zabbix_dump.dmp 2>zabbix.err

real    0m52.698s

user    0m12.638s

sys     0m3.256s

A medium size home net maybe? Around 1GB.

-rw-r--r--  1 jim  wheel  1131407116 Oct 13 23:04 zabbix_dump.dmp

Move older version aside, so if things go sideways a dropback to the prior version is the "save point." We hope not to test this.

# mv pgsql pgsql_15

$ ls -l /usr/pkg/pg*

ls: /usr/pkg/pgsql_15: Permission denied

780 packages

pkg_install warnings: 2, errors: 329

pkg_install error log can be found in /var/db/pkgin/pkg_install-err.log

reading local summary...

processing local summary...

(time passes)

NetBSD pkgsrc database boot

bash-5.2# /etc/rc.d/pgsql initdb

Initializing PostgreSQL databases.

(then create the role for zabbixadmin)

Zabbix specific database steps

unix:

createdb -O zabbix -E Unicode -T template0 zabbix

sql:

GRANT ALL ON SCHEMA public TO                   zabbixadmin;

GRANT ALL PRIVILEGES on database  zabbix    to  zabbixadmin;

GRANT CONNECT on database         zabbix    to  zabbixadmin;

ALTER ROLE                                      zabbixadmin WITH LOGIN;

Restore

$ time psql -d zabbix < zabbix_dump.dmp >~/zabbix.log 2>~/zabbix.err

real    2m59.684s

user    0m12.635s

sys     0m2.364s

As the saying goes, 2 minutes 59.

The image above illustrates the somewhat minimal downtime, where the 90 minute gap between the arrows is the entire loss of one data element.