Open-Future is a company based in Nossegem (Belgium), focused exclusively on the open-source market. Read the case showing how the company upgraded to Zabbix 4.4 and migrated from PostgreSQL table partitioning to PostgreSQL with TimescaleDB to ensure Zabbix support.
Contents
I.Introduction (0:04)
II. Our needs (1:49)
III. How we upgraded Zabbix (3:36)
IV. How we moved to TimescaleDB (4:18)
V. Migration tips (5:34)
VI. TimescaleDB tips (6:20)
VII. Upgrading to PG11 (8:43)
VIII. Tips (11:00)
Our needs
The company decided to upgrade Zabbix and migrate from PostgreSQL table partitioning to PostgreSQL with TimescaleDB to ensure support from Zabbix. The company needed:
-
- to preserve history while dropping trending if necessary as it was hardly used.
- to upgrade Zabbix 4.0 to Zabbix 4.4. Table partitioning was already in place, the company intended to migrate to TimescaleDB, as it is officially supported by Zabbix.
- to upgrade Postgres 10 to Postgres 11.
- to limit migration time as its infrastructure was highly dependent on Zabbix.
How we upgraded Zabbix
Upgrading Zabbix is easy, as you replace the repos and upgrade the binaries, and you are good to go as normally the configuration is preserved, at least on CentOS. Though we had tested it on a test machine with a copy of our DB first.
How we moved to TimescaleDB
We already had our database partitioned, so we needed to move the data from partition tables to TimescaleDB.
-
- We decided to drop the trending tables to speed up the migration process as we didn’t need to preserve trending.
- 2 weeks before migration we stopped all DB scripts needed for DB partitioning to ensure the historical data was already in the correct place.
- For migration, we had to drop the history data in the partitioned tables together with the trending tables.
Migration tips
There are many solutions to migrate the data. TimescaleDB has such tools as timescaledb-parallel-copy — a command-line tool to parallelize PostgreSQL built-in COPY functionality allowing for bulk inserting data into TimescaleDB.
You can write your own script to move the data from the partition tables to the correct location.
TimescaleDB tips
-
- TimescaleDB has its own tools to tune the database — timescaledb-tune.
NOTE. Don’t just copy or apply this tool’s suggestions. timescaledb-tune will try to change your original postgresql.conf file. This tool won’t look into the files you have included into your original postgresql.conf file (or if you have loaded the library), and will think that your DB is on the dedicated machine.
-
- You can encounter errors in your postgresql logs, for instance:
psql: FATAL: out of shared memory
HINT: You might need to increase max_locks_per_transaction.
TimescaleDB relies heavily on table partitioning for scaling time-series workloads, which has implications for lock management. A hypertable needs to acquire locks for many subtables during queries, and this can exhaust the default limits for the number of allowed locks held.
Defining configuration parameters and creating backup
Upgrading to PG11
Upgrading to PG11 can be done in many different ways, such as replicating your database, creating a dump, etc. However, we wanted to upgrade as fast as possible. Some downtime was acceptable as we were down for the Zabbix and TimescaleDB migration.
NOTE. We upgraded on the existing hardware.
We found a solution — pg_upgrade. It helps to upgrade without dumping your DB and uploading it again, which saves a lot of time and space. We only had to install a new PostgreSQL version (binaries).
WIth pg_upgrade you can point to the old and new data folder, and pg_upgrade will move the data and run the upgrade.
Since we had already dropped our trending data, we only had to migrate the history.
pg_upgrade parameters to specify:
/usr/pgsql-11/bin/pg_upgrade -k -b /usr/pgsql-10/bin/ -B /usr/pgsql-11/bin/ -d /data/data/ -D /data/11/ —check
where:
-
- -b — to specify the old data folder.
- -B — to specify the new data folder.
- -k — allows PostgreSQL to use hard links to speed up the migration, as you don’t have to copy the data.
- -c or —check — to check the cluster without migrating the data for any errors.
With these parameters it will only take several minutes to migrate your database.
Tips
-
- TimescaleDB is an extension, so you need to upgrade your database.
- TimescaleDB extension is added to the posgresql.conf file. We added an extra file to be able to see the changes.
- Tuning parameters can be kept in config file, such as timescaledb.conf that you include in your postgresql.conf file so that you can see the settings you can change or add.
Our specs
See also: presentation video and slides.
