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.tgzNow 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 directoryOops.
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:
- While postgresql and old version is running - update timescaledb package
- connect to database that is using timescaled with psql -X, ex: psql -X zabbix
- Update extension in database with: ALTER EXTENSION timescaledb UPDATE;
- 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
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=#