Closed TristisOris closed 9 months ago
@TristisOris Thanks for raising the issue.
The error is normally printed if you haven't set the variable timescaledb.restoring
to on
, which will disable extension-specific hooks.
If you look into the instructions for Migrate from TimescaleDB using pg_dump/restore you can see that timescaledb_pre_restore
is called, which will disable the hooks.
Did you do that prior to doing a restore? I do not think that pg_ugradecluster
does that automatically since it is a PostgreSQL tool.
i done upgrade same way from 14 to 15, so was sure it should work.
Thanks, i'll try to upgrade via restore.
@TristisOris Thanks! I will leave the issue open for now but please tell us if the upgrade works or any improvements that you see would be useful.
pg_dumpall -d "zabbix" --quote-all-identifiers --roles-only --file=roles.sql
pg_dumpall: error: missing "=" after "zabbix" in connection info string
well, lets skip it.
pg_dump -d "zabbix" \
--format=plain \
--quote-all-identifiers \
--no-tablespaces \
--no-owner \
--no-privileges \
--file=dump.sql
pg_dump: warning: there are circular foreign-key constraints on this table:
pg_dump: detail: hypertable
pg_dump: hint: You might not be able to restore the dump without using --disable-triggers or temporarily dropping the constraints.
pg_dump: hint: Consider using a full dump instead of a --data-only dump to avoid this problem.
pg_dump: warning: there are circular foreign-key constraints on this table:
pg_dump: detail: chunk
pg_dump: hint: You might not be able to restore the dump without using --disable-triggers or temporarily dropping the constraints.
pg_dump: hint: Consider using a full dump instead of a --data-only dump to avoid this problem.
pg_dump: warning: there are circular foreign-key constraints on this table:
pg_dump: detail: continuous_agg
pg_dump: hint: You might not be able to restore the dump without using --disable-triggers or temporarily dropping the constraints.
pg_dump: hint: Consider using a full dump instead of a --data-only dump to avoid this problem.
Here already something goes wrong.
then drop curent db, create empty one.
psql zabbix -v ON_ERROR_STOP=1 --echo-errors \
-f dump.sql \
-c "SELECT timescaledb_post_restore();"
...
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
COPY 0
COPY 0
COPY 0
COPY 14
psql:dump.sql:5451: ERROR: null value in column "creation_time" of relation "chunk" violates not-null constraint
DETAIL: Failing row contains (1612, 1, _timescaledb_internal, _hyper_1_1612_chunk, null, f, 0, f, null).
CONTEXT: COPY chunk, line 1: "1612 1 _timescaledb_internal _hyper_1_1612_chunk \N f 0 f"
psql:dump.sql:5451: STATEMENT: COPY "_timescaledb_catalog"."chunk" ("id", "hypertable_id", "schema_name", "table_name", "compressed_chunk_id", "dropped", "status", "osm_chunk") FROM stdin;
looks i'm totaly missed up somewhere.
psql zabbix -v ON_ERROR_STOP=1 --echo-errors \ -f dump.sql \ -c "SELECT timescaledb_post_restore();" ... ALTER TABLE ALTER TABLE ALTER TABLE ALTER TABLE COPY 0 COPY 0 COPY 0 COPY 14 psql:dump.sql:5451: ERROR: null value in column "creation_time" of relation "chunk" violates not-null constraint DETAIL: Failing row contains (1612, 1, _timescaledb_internal, _hyper_1_1612_chunk, null, f, 0, f, null). CONTEXT: COPY chunk, line 1: "1612 1 _timescaledb_internal _hyper_1_1612_chunk \N f 0 f" psql:dump.sql:5451: STATEMENT: COPY "_timescaledb_catalog"."chunk" ("id", "hypertable_id", "schema_name", "table_name", "compressed_chunk_id", "dropped", "status", "osm_chunk") FROM stdin;
looks i'm totaly missed up somewhere.
You are probably missing a timescaledb_pre_restore()
here as well:
psql zabbix -v ON_ERROR_STOP=1 --echo-errors \
-c "SELECT timescaledb_pre_restore();" \
-f dump.sql \
-c "SELECT timescaledb_post_restore();"
pg_dumpall -d "zabbix" --quote-all-identifiers --roles-only --file=roles.sql pg_dumpall: error: missing "=" after "zabbix" in connection info string
well, lets skip it.
It's not -d
to select database for pg_dumpall
. That is for pg_dump
. You should use -l
or -d dbname=zabbix
.
this is works but takes a second, so i confused if it correct.
postgres@zabbix:~$ pg_dumpall -d "$SOURCE" \
--quote-all-identifiers \
--roles-only \
--file=roles.sql
postgres@zabbix:~$ pg_dump -d "$SOURCE" \
--format=plain \
--quote-all-identifiers \
--no-tablespaces \
--no-owner \
--no-privileges \
--file=dump.sql
Yes, -l
a more correct.
pg_dumpall -l zabbix \
--quote-all-identifiers \
--roles-only \
--file=roles.sql
pg_dump -d zabbix \
--format=plain \
--quote-all-identifiers \
--no-tablespaces \
--no-owner \
--no-privileges \
--file=dump.sql
You are probably missing a timescaledb_pre_restore() here as well: because without roles dump that command invalid:
psql $TARGET -v ON_ERROR_STOP=1 --echo-errors \ -c "SELECT timescaledb_pre_restore();" \ -f dump.sql \ -c "SELECT timescaledb_post_restore();" ERROR: function timescaledb_pre_restore() does not exist LINE 1: SELECT timescaledb_pre_restore(); ^ HINT: No function matches the given name and argument types. You might need to add explicit type casts. STATEMENT: SELECT timescaledb_pre_restore();
with roles dump created:
psql $TARGET -v ON_ERROR_STOP=1 --echo-errors \
-f roles.sql \
-c "SELECT timescaledb_pre_restore();" \
-f dump.sql \
-c "SELECT timescaledb_post_restore();"
SET
SET
SET
psql:roles.sql:14: ERROR: role "postgres" already exists
psql:roles.sql:14: STATEMENT: CREATE ROLE "postgres";
Stop on error. If remove that flag, restore is going.
psql $TARGET -v --echo-errors -f roles.sql -c "SELECT timescaledb_pre_restore();" -f dump.sql -c "SELECT timescaledb_post_restore();"
SET
SET
SET
psql:roles.sql:14: ERROR: role "postgres" already exists
ALTER ROLE
psql:roles.sql:16: ERROR: role "zabbix" already exists
ALTER ROLE
ERROR: function timescaledb_pre_restore() does not exist
LINE 1: SELECT timescaledb_pre_restore();
^
HINT: No function matches the given name and argument types. You might need to add explicit type casts.
SET
SET
SET
SET
SET
set_config
------------
(1 row)
SET
SET
SET
SET
COMMENT
psql:dump.sql:37: WARNING:
WELCOME TO
...
ALTER TABLE
ALTER TABLE
psql:dump.sql:142035952: ERROR: operation not supported on hypertables that have compression enabled
psql:dump.sql:142035960: ERROR: operation not supported on hypertables that have compression enabled
ALTER TABLE
ALTER TABLE
..
CREATE INDEX
CREATE INDEX
psql:dump.sql:142036140: ERROR: duplicate key value violates unique constraint "chunk_index_chunk_id_index_name_key"
DETAIL: Key (chunk_id, index_name)=(1574, compress_hyper_13_1574_chunk__compressed_hypertable_13_itemid__) already exists.
psql:dump.sql:142036147: ERROR: duplicate key value violates unique constraint "chunk_index_chunk_id_index_name_key"
DETAIL: Key (chunk_id, index_name)=(1575, compress_hyper_14_1575_chunk__compressed_hypertable_14_itemid__) already exists.
CREATE INDEX
...
ALTER TABLE
ERROR: function timescaledb_post_restore() does not exist
LINE 1: SELECT timescaledb_post_restore();
^
HINT: No function matches the given name and argument types. You might need to add explicit type casts.
postgres@zabbix:~$
@TristisOris what's the output of the below query on the PG15 setup?
select * from _timescaledb_catalog.chunk WHERE creation_time IS NULL;
it looks like the creation_time
column in this table has NULL entries which is causing the issue.
yep
postgres=# \c zabbix
psql (16.1 (Ubuntu 16.1-1.pgdg22.04+1), server 15.5 (Ubuntu 15.5-1.pgdg22.04+1))
You are now connected to database "zabbix" as user "postgres".
zabbix=# select * from _timescaledb_catalog.chunk WHERE creation_time IS NULL;
id | hypertable_id | schema_name | table_name | compressed_chunk_id | dropped | status | osm_chunk | creation_time
----+---------------+-------------+------------+---------------------+---------+--------+-----------+---------------
(0 rows)
@TristisOris
something is not adding up. The above output says that all chunk rows have a valid not-null creation_time
value. However, the dump.sql that was used does not contain it.
psql:dump.sql:5451: ERROR: null value in column "creation_time" of relation "chunk" violates not-null constraint
DETAIL: Failing row contains (1612, 1, _timescaledb_internal, _hyper_1_1612_chunk, null, f, 0, f, null).
CONTEXT: COPY chunk, line 1: "1612 1 _timescaledb_internal _hyper_1_1612_chunk \N f 0 f"
psql:dump.sql:5451: STATEMENT: COPY "_timescaledb_catalog"."chunk" ("id", "hypertable_id", "schema_name", "table_name", "compressed_chunk_id", "dropped", "status", "osm_chunk") FROM stdin;
note the COPY
command above. It's omitting the creation_time
column.. How was this dump.sql
created?
How was this dump.sql created?
pg_dump -d zabbix \ --format=plain \ --quote-all-identifiers \ --no-tablespaces \ --no-owner \ --no-privileges \ --file=dump.sql
Same error happens on direct pg cluster upgrade. Is that empty colums an application issue? And how to solve that.
I tried the below on my PG15 setup with timescaledb 2.13.1 just now:
pg_dump -d $SOURCE --format=plain --quote-all-identifiers --no-tablespaces --no-owner --no-privileges --file=dump.sql
and I can see the creation_time
column appropriately in my dump.sql
file.
-- Data for Name: chunk; Type: TABLE DATA; Schema: _timescaledb_catalog; Owner: -
--
COPY "_timescaledb_catalog"."chunk" ("id", "hypertable_id", "schema_name", "table_name", "compressed_chunk_id", "dropped", "status", "osm_chunk", "creation_time") FROM stdin;
1 1 _timescaledb_internal _hyper_1_1_chunk \N f 0 f 2024-01-16 13:48:54.053231+05:30
2 1 _timescaledb_internal _hyper_1_2_chunk \N f 0 f 2024-01-16 13:48:54.059286+05:30
3 1 _timescaledb_internal _hyper_1_3_chunk \N f 0 f 2024-01-16 13:48:54.062195+05:30
Are you sure that the dump.sql is being generated from the correct PG15 source database?
i have only 1 db on that host, so yes.
@TristisOris can you please confirm what timescaledb version PG15 source database is using?
psql
psql (16.1 (Ubuntu 16.1-1.pgdg22.04+1), server 15.5 (Ubuntu 15.5-1.pgdg22.04+1))
pg_lsclusters
Ver Cluster Port Status Owner Data directory Log file
15 main 5432 online postgres /var/lib/postgresql/15/main /var/log/postgresql/postgresql-15-main.log
16 main 5433 online postgres /var/lib/postgresql/16/main /var/log/postgresql/postgresql-16-main.log
@TristisOris I asked for the timescaledb extension version on the PG15 source database..
My suspicion is the timescaledb version might be lower than 2.13.x on the PG15 side. That might explain why the dump.sql does not contain the creation_time
column.
psql zabbix -c "SELECT extversion FROM pg_extension WHERE extname = 'timescaledb';"
extversion
------------
2.13.1
(1 row)
Wow 👎
Can you please inspect the dump.sql
file and check for a line containing COPY "_timescaledb_catalog"."chunk"
and see if really does not contain the creation_time
column?
hm. it looks good.
Curiouser and curiouser!
Then the dump file that was used earlier was not the same one? Because you mentioned this output:
psql:dump.sql:5451: STATEMENT: COPY "_timescaledb_catalog"."chunk" ("id", "hypertable_id", "schema_name", "table_name", "compressed_chunk_id", "dropped", "status", "osm_chunk") FROM stdin;
which does not contain creation_time
column.
not same, but created with same command. I'm rollback whole vm after experiments.
once again, restore this dump. without roles backup:
psql $TARGET -v --echo-errors \
-f /home/oris/dump.sql \
-c "SELECT timescaledb_post_restore();"
psql:/home/oris/dump.sql:137805927: ERROR: operation not supported on chunk tables
psql:/home/oris/dump.sql:137805927: STATEMENT: ALTER TABLE ONLY "_timescaledb_internal"."_hyper_6_1420_chunk"
ADD CONSTRAINT "1420_1309_trends_pkey" PRIMARY KEY ("itemid", "clock");
with roles backup. without stop on error, due i got warnings about duplicated index.
psql $TARGET -v --echo-errors \
-f roles.sql \
-c "SELECT timescaledb_pre_restore();" \
-f /home/oris/dump.sql \
-c "SELECT timescaledb_post_restore();"
ALTER TABLE
ALTER TABLE
psql:/home/oris/dump.sql:137805943: ERROR: could not create unique index "1544_1431_trends_pkey"
DETAIL: Key (itemid, clock)=(653731, 1705114800) is duplicated.
CONTEXT: parallel worker
psql:/home/oris/dump.sql:137805951: ERROR: could not create unique index "1545_1432_trends_uint_pkey"
DETAIL: Key (itemid, clock)=(646627, 1703530800) is duplicated.
CONTEXT: parallel worker
...
psql:/home/oris/dump.sql:137812474: ERROR: there is no unique constraint matching given keys for referenced table "hosts"
psql:/home/oris/dump.sql:137812482: ERROR: there is no unique constraint matching given keys for referenced table "items"
psql:/home/oris/dump.sql:137812490: ERROR: there is no unique constraint matching given keys for referenced table "graphs"
ALTER TABLE
psql:/home/oris/dump.sql:137812506: ERROR: there is no unique constraint matching given keys for referenced table "services"
ALTER TABLE
psql:/home/oris/dump.sql:137812522: ERROR: there is no unique constraint matching given keys for referenced table "users"
ERROR: function timescaledb_post_restore() does not exist
LINE 1: SELECT timescaledb_post_restore();
^
HINT: No function matches the given name and argument types. You might need to add explicit type casts.
@TristisOris ok, so it looks like the upgrade has succeeded with some errors? For the last error, you'd need to use "SELECT public. timescaledb_post_restore();" .
db restore is succeed, but schema was corrupted.
Unable to determine current Zabbix database version: the table "dbversion" was not found.
1606:20240117:170723.800 Starting Zabbix Server. Zabbix 6.4.10 (revision 4da16fb82f5).
1606:20240117:170723.800 ****** Enabled features ******
1606:20240117:170723.800 SNMP monitoring: YES
1606:20240117:170723.800 IPMI monitoring: YES
1606:20240117:170723.800 Web monitoring: YES
1606:20240117:170723.800 VMware monitoring: YES
1606:20240117:170723.800 SMTP authentication: YES
1606:20240117:170723.800 ODBC: YES
1606:20240117:170723.800 SSH support: YES
1606:20240117:170723.800 IPv6 support: YES
1606:20240117:170723.800 TLS support: YES
1606:20240117:170723.800 ******************************
1606:20240117:170723.800 using configuration file: /etc/zabbix/zabbix_server.conf
1606:20240117:170723.829 [Z3005] query failed: [0] PGRES_FATAL_ERROR:ERROR: permission denied for table users
[select userid from users limit 1]
1606:20240117:170723.829 cannot use database "zabbix": database is not a Zabbix database
try to create it again but failed.
cat /usr/share/zabbix-sql-scripts/postgresql/timescaledb.sql | sudo -u zabbix psql zabbix
NOTICE: PostgreSQL version 16.1 (Ubuntu 16.1-1.pgdg22.04+1) is valid
NOTICE: TimescaleDB extension is detected
NOTICE: TimescaleDB version 2.13.1 is valid
ERROR: table "history" is already a hypertable
CONTEXT: SQL statement "SELECT create_hypertable('history', 'clock', chunk_time_interval => 86400, migrate_data => true)"
PL/pgSQL function inline_code_block line 59 at PERFORM
@TristisOris I cannot on the zabbix
side of things. But it might help to investigate the errors that you see while loading dump.sql
.
psql:/home/oris/dump.sql:137805943: ERROR: could not create unique index "1544_1431_trends_pkey"
DETAIL: Key (itemid, clock)=(653731, 1705114800) is duplicated.
CONTEXT: parallel worker
psql:/home/oris/dump.sql:137805951: ERROR: could not create unique index "1545_1432_trends_uint_pkey"
DETAIL: Key (itemid, clock)=(646627, 1703530800) is duplicated.
it sounds like there are some data consistency issues. It might make sense to do sanity on the dump.sql file and then retry the load till you get a fully successful one.
well, finally succeed with pg_upgradecluster
. Probably issue was with extention version, but weird it didn't help at first time.
What type of bug is this?
Incorrect result
What subsystems and features are affected?
Other
What happened?
pg_restore: error: could not execute query: ERROR: operation not supported on hypertables that have compression enabled
TimescaleDB version affected
2.13.1
PostgreSQL version used
16.1
What operating system did you use?
ubuntu 22
What installation method did you use?
Deb/Apt
What platform did you run on?
On prem/Self-hosted
Relevant log output and stack trace