netbsd

Upgrade TimescaleDB extension

Bartosz Maciejewski

— 4 min read
Upgrade TimescaleDB extension

TimescaleDB is extension for PostgreSQL database that greatly improve performance of database by automatically partitioning data into time-based chunks to support faster performance at scale.

I use it with my Zabbix server when partitioning is a must when you have large amount of statistics.

However update of TimescaleDB is a bit tricky. My setup is 100% NetSBD based so I naturally use pkgsrc for mackage management. Normally you just upgrade packages with your desired method - I use pkg_rolling replace.

After TimescaleDB from databases/postgresql-timescaledb upgrade is done it always produce this kind of logs in errlog from Postgres:

2022-11-01 15:31:19.946 CET [9072] STATEMENT:  SET search_path='public'
2022-11-01 15:31:20.950 CET [14353] ERROR:  could not access file "$libdir/timescaledb-2.6.0": No such file or directory
2022-11-01 15:31:20.950 CET [14353] STATEMENT:  SET search_path='public'
2022-11-01 15:31:21.987 CET [11226] ERROR:  could not access file "$libdir/timescaledb-2.6.0": No such file or directory
2022-11-01 15:31:21.987 CET [11226] STATEMENT:  SET search_path='public'
2022-11-01 15:31:23.045 CET [15001] ERROR:  could not access file "$libdir/timescaledb-2.6.0": No such file or directory
2022-11-01 15:31:23.045 CET [15001] STATEMENT:  SET search_path='public'
2022-11-01 15:31:23.994 CET [26161] ERROR:  could not access file "$libdir/timescaledb-2.6.0": No such file or directory
2022-11-01 15:31:23.994 CET [26161] STATEMENT:  SET search_path='public'

Ok, this looks normal, new version installed old vanished, lets restart Postgres:

2022-11-01 15:13:55.235 CET [11909] LOG:  starting PostgreSQL 13.8 on x86_64--netbsd, compiled by gcc (nb4 20200810) 7.5.0, 64-bit
2022-11-01 15:13:55.235 CET [11909] LOG:  listening on IPv6 address "::1", port 5432
2022-11-01 15:13:55.235 CET [11909] LOG:  listening on IPv4 address "127.0.0.1", port 5432
2022-11-01 15:13:55.237 CET [11909] LOG:  listening on Unix socket "/tmp/.s.PGSQL.5432"
2022-11-01 15:13:55.251 CET [4490] LOG:  database system was shut down at 2022-11-01 15:13:54 CET
2022-11-01 15:13:55.289 CET [11909] LOG:  database system is ready to accept connections
2022-11-01 15:13:55.315 CET [5275] LOG:  TimescaleDB background worker launcher connected to shared catalogs
2022-11-01 15:13:55.423 CET [4758] ERROR:  could not access file "$libdir/timescaledb-2.6.0": No such file or directory
2022-11-01 15:13:55.437 CET [11909] LOG:  background worker "TimescaleDB Background Worker Scheduler" (PID 4758) exited with exit code 1
2022-11-01 15:13:56.160 CET [4938] ERROR:  could not access file "$libdir/timescaledb-2.6.0": No such file or directory
2022-11-01 15:13:56.160 CET [4938] STATEMENT:  select oid from pg_type where typname='bytea'
2022-11-01 15:14:11.141 CET [6547] ERROR:  could not access file "$libdir/timescaledb-2.6.0": No such file or directory
2022-11-01 15:14:11.141 CET [6547] STATEMENT:  SET search_path='public'

Same thing. Downgrading to 2.6.0 from 2.8.0 (kudos for NetBSD packageing system if you have left previous built version) resolves issue.

pkg_add -vuu /usr/pkgsrc/packages/All/postgresql13-timescaledb-2.6.0.tgz

Now to actual solution. TimescaleDB extension is installed per database not for PostgreSQL as a whole. So update is divided into two stages.

Stage 1 - actual package upgrade

Stage 2 - update to newer version inside database

To look what version is currently running we need to go to database that is configured for timescaledb - in my case it is zabbix db.

u-SYS-mon01# su - pgsql
$ psql zabbix
psql (13.8)
Type "help" for help.

zabbix=# \dx
                                      List of installed extensions
    Name     | Version |   Schema   |                            Description
-------------+---------+------------+-------------------------------------------------------------------
 plpgsql     | 1.0     | pg_catalog | PL/pgSQL procedural language
 timescaledb | 2.6.0   | public     | Enables scalable inserts and complex queries for time-series data
(2 rows)

zabbix=#

This is pre update of package. Now if we update package and will look into it again:

u-SYS-mon01# su - pgsql
$ psql zabbix
psql (13.8)
Type "help" for help.

zabbix=# \dx
ERROR:  could not access file "$libdir/timescaledb-2.6.0": No such file or directory

Oops.

And now we need to update actual extension in database with:

ALTER EXTENSION timescaledb UPDATE;

But, it needs to be done in certain way. This command have to be FIRST after connecting with psql, and psql itself need to be run with -X switch to prevent any local .psqlrc to trigger any use of extension. So finally we have:

  1. While postgresql and old version is running - update timescaledb package
  2. connect to database that is using timescaled with psql -X, ex: psql -X zabbix
  3. Update extension in database with: ALTER EXTENSION timescaledb UPDATE;
  4. Restart postgres and check errlog if it stared with new version of timescaledb and without errors:
u-SYS-mon01# /etc/rc.d/pgsql restart
Restarting pgsql.
u-SYS-mon01# tail /usr/pkg/pgsql/errlog
2022-11-01 15:53:39.239 CET [27933] LOG:  background worker "TimescaleDB Background Worker Launcher" (PID 14345) exited with exit code 1
2022-11-01 15:53:39.240 CET [8890] LOG:  shutting down
2022-11-01 15:53:39.539 CET [27933] LOG:  database system is shut down
2022-11-01 15:53:39.706 CET [22422] LOG:  starting PostgreSQL 13.8 on x86_64--netbsd, compiled by gcc (nb4 20200810) 7.5.0, 64-bit
2022-11-01 15:53:39.707 CET [22422] LOG:  listening on IPv6 address "::1", port 5432
2022-11-01 15:53:39.707 CET [22422] LOG:  listening on IPv4 address "127.0.0.1", port 5432
2022-11-01 15:53:39.708 CET [22422] LOG:  listening on Unix socket "/tmp/.s.PGSQL.5432"
2022-11-01 15:53:39.719 CET [8755] LOG:  database system was shut down at 2022-11-01 15:53:39 CET
2022-11-01 15:53:39.741 CET [22422] LOG:  database system is ready to accept connections
2022-11-01 15:53:39.766 CET [25996] LOG:  TimescaleDB background worker launcher connected to shared catalogs
This looks good

5. Connect again normally to database and check version there:

u-SYS-mon01# su - pgsql
$ psql zabbix
psql (13.8)
Type "help" for help.

zabbix=# \dx
                                      List of installed extensions
    Name     | Version |   Schema   |                            Description
-------------+---------+------------+-------------------------------------------------------------------
 plpgsql     | 1.0     | pg_catalog | PL/pgSQL procedural language
 timescaledb | 2.8.0   | public     | Enables scalable inserts and complex queries for time-series data
(2 rows)

zabbix=#
TimescaleDB extension 2.6.0 -> 2.8.0 upgrade completed

Read more