[Bug]: pg_restore failed with No function matches the given name and argument types. You might need to add explicit type casts.

fbijlenga commented 1 month ago

What type of bug is this?

Unexpected error

What subsystems and features are affected?

Restore

What happened?

The restore of a zabbix server with postgresql database with a timescaldb extension activated failed with following message: ERROR: function base36_decode(text) does not exist LINE 1: CAST(base36_decode(substring(cuid FROM 2 FOR 8))/1000 AS int... ^ HINT: No function matches the given name and argument types. You might need to add explicit type casts. QUERY: CAST(base36_decode(substring(cuid FROM 2 FOR 8))/1000 AS integer) CONTEXT: PL/pgSQL function public.cuid_timestamp(character varying) line 3 at RETURN COPY _hyper_17_6_chunk, line 1: "clz86cezx0002d73nh7k5nkcd \N System 1722329214 0 47 \N clz86cezv0001d73ncca9stuj clz86cezx0003d73n..."

The backup is done with this command: sudo -u postgres pg_dump -Fp -C zabbix | gzip > zabbix.sql.gz The restore command used is: gunzip -c zabbix.sql.gz | psql -U postgres -v ON_ERROR_STOP=1

I have also tested with pg_dump and pg_restore command, but that result in the same error.

How can i prevent this error, or is this a bug

TimescaleDB version affected

2.15.3

PostgreSQL version used

15.7

What operating system did you use?

Debian 12

What installation method did you use?

Deb/Apt

What platform did you run on?

On prem/Self-hosted

Relevant log output and stack trace

ERROR:  function base36_decode(text) does not exist
LINE 1: CAST(base36_decode(substring(cuid FROM 2 FOR 8))/1000 AS int...
             ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.
QUERY:  CAST(base36_decode(substring(cuid FROM 2 FOR 8))/1000 AS integer)
CONTEXT:  PL/pgSQL function public.cuid_timestamp(character varying) line 3 at RETURN
COPY _hyper_17_6_chunk, line 1: "clz86cezx0002d73nh7k5nkcd      \N      System  1722329214              0       47      \N      clz86cezv0001d73ncca9stuj          clz86cezx0003d73n..."

How can we reproduce the bug?

The following installaion procedure is used:
Zabbix version 7.0.2
https://www.zabbix.com/download?zabbix=6.0&os_distribution=debian&os_version=12&components=server_frontend_agent&db=pgsql&ws=apache
and
https://www.zabbix.com/documentation/current/en/manual/appendix/install/timescaledb

fabriziomello commented 1 month ago

@fbijlenga looks like this problem is unrelated to timescaledb. We don't have any function named base36_decode. Did u checked it with zabbix folks?

fbijlenga commented 1 month ago

@fabriziomello Okay, thanks. It is related to /usr/share/zabbix-sql-scripts/postgresql/timescaledb/schema.sql and yes this is form a zabbix package, zabbix-sql-scripts, that has to be executed to enable timescaldb on zabbix db.

First lines in schema.sql are: DROP FUNCTION IF EXISTS base36_decode(character varying); CREATE OR REPLACE FUNCTION base36_decode(IN base36 varchar)

Than i have to check this further with the zabbix team

fabriziomello commented 1 month ago

@fbijlenga anyway here you can find some useful information of how to use pg_dump/pg_restore to migrate (move) your databases from one location to another: https://docs.timescale.com/self-hosted/latest/migration/entire-database/#migrating-the-entire-database-at-once

fbijlenga commented 1 month ago

I have an issue created for zabbix team: https://support.zabbix.com/browse/ZBX-24955

timescale / timescaledb