In brief:
Lots of errors (~700) during pg_restore command
pg_restore: error: COPY failed for table “compress_hyper_922_18274_chunk”: ERROR: function 0x7fd4b7be8090 returned NULL
all errors are the same, it change only table name
in pg_dump log file
pg_dump: dumping contents of table “_timescaledb_internal.compress_hyper_922_18274_chunk”
pg_dump: finished item 48877 TABLE DATA compress_hyper_922_18274_chunk
TimescaleDB version: 2.19.2~ubuntu22.04
PostgreSQL version: 17.2-1.pgdg22.04+1
Other software:
OS: ubuntu-22
Platform:
Install method: deb
Environment: [Production, Test, Development]
I verified that timescaledb is the same in testing environment
I created backup using pg_dump with
PG_DUMP_OPTS=–quote-all-identifiers --jobs=14 --format=d --compress=3 --verbose
Before i run this command on each database I turn off background jobs
SELECT public.alter_job(id::integer, scheduled=>false) FROM _timescaledb_config.bgw_job WHERE id >= 1000 and scheduled
and after end turn on (I received very often ACCESS EXCLUSIVE LOCK during pg_dump)
Before I run pg_restore i perform
SELECT timescaledb_pre_restore();
as it is in instruction
I tested on production if data can be query
SELECT count(*) FROM _timescaledb_internal.compress_hyper_1091_14856_chunk;
411519
on instance where backup was tested
SELECT count(*) FROM _timescaledb_internal.compress_hyper_1091_14856_chunk;
0
Any suggestions?
…
pg_restore: processing data for table “_timescaledb_internal.compress_hyper_1087_20038_chunk”
pg_restore: processing data for table “_timescaledb_internal.compress_hyper_1087_20082_chunk”
pg_restore: processing data for table “_timescaledb_internal.compress_hyper_1087_20086_chunk”
pg_restore: processing data for table “_timescaledb_internal.compress_hyper_1087_20110_chunk”
pg_restore: processing data for table “_timescaledb_internal.compress_hyper_1087_20111_chunk”
pg_restore: processing data for table “_timescaledb_internal.compress_hyper_1087_20143_chunk”
pg_restore: processing data for table “_timescaledb_internal.compress_hyper_1087_20144_chunk”
pg_restore: processing data for table “_timescaledb_internal.compress_hyper_1089_18593_chunk”
pg_restore: processing data for table “_timescaledb_internal.compress_hyper_1089_19171_chunk”
pg_restore: processing data for table “_timescaledb_internal.compress_hyper_1089_19671_chunk”
pg_restore: processing data for table “_timescaledb_internal.compress_hyper_1091_14850_chunk”
pg_restore: while PROCESSING TOC:
pg_restore: from TOC entry 47585; 0 524813301 TABLE DATA compress_hyper_1091_14850_chunk npi
pg_restore: error: COPY failed for table “compress_hyper_1091_14850_chunk”: ERROR: function 0x7fd4b
7be8090 returned NULL
CONTEXT: COPY compress_hyper_1091_14850_chunk, line 1, column value_index: “Bg==”
pg_restore: processing data for table “_timescaledb_internal.compress_hyper_1091_14856_chunk”
pg_restore: from TOC entry 47590; 0 524854696 TABLE DATA compress_hyper_1091_14856_chunk npi
pg_restore: error: COPY failed for table “compress_hyper_1091_14856_chunk”: ERROR: function 0x7fd4b
7be8090 returned NULL
CONTEXT: COPY compress_hyper_1091_14856_chunk, line 1, column value_index: “Bg==”
pg_restore: processing data for table “_timescaledb_internal.compress_hyper_1091_14861_chunk”
pg_restore: from TOC entry 47595; 0 524883191 TABLE DATA compress_hyper_1091_14861_chunk npi
pg_restore: error: COPY failed for table “compress_hyper_1091_14861_chunk”: ERROR: function 0x7fd4b
7be8090 returned NULL
CONTEXT: COPY compress_hyper_1091_14861_chunk, line 1, column value_index: “Bg==”
pg_restore: processing data for table “_timescaledb_internal.compress_hyper_1091_14864_chunk”
pg_restore: from TOC entry 47598; 0 524911370 TABLE DATA compress_hyper_1091_14864_chunk npi
pg_restore: error: COPY failed for table “compress_hyper_1091_14864_chunk”: ERROR: function 0x7fd4b
7be8090 returned NULL
CONTEXT: COPY compress_hyper_1091_14864_chunk, line 1, column value_index: “Bg==”
pg_restore: processing data for table “_timescaledb_internal.compress_hyper_1091_14868_chunk”
pg_restore: from TOC entry 47602; 0 524939437 TABLE DATA compress_hyper_1091_14868_chunk npi
pg_restore: error: COPY failed for table “compress_hyper_1091_14868_chunk”: ERROR: function 0x7fd4b
7be8090 returned NULL
CONTEXT: COPY compress_hyper_1091_14868_chunk, line 1, column value_index: “Bg==”
pg_restore: processing data for table “_timescaledb_internal.compress_hyper_1091_14873_chunk”
pg_restore: from TOC entry 47607; 0 524967503 TABLE DATA compress_hyper_1091_14873_chunk npi
pg_restore: error: COPY failed for table “compress_hyper_1091_14873_chunk”: ERROR: function 0x7fd4b
7be8090 returned NULL
…