[Bug]: pg_dump generates truncate only for hypertables which fails during pg_restore

[toutas]

What type of bug is this?

Unexpected error

What subsystems and features are affected?

Backup, Partitioning, Restore

What happened?

When I pg_dump my selfhosted database that contains many tables along with one single hypertable which was generated using

SELECT create_hypertable('satelliteproductssatellitezones', 'image_date', chunk_time_interval => INTERVAL '3 months', migrate_data => true);

with the following pg_dump command

pg_dump -Fc -Z zstd:9 -h localhost -U postgres {{ database_name }} > {{ dump_path }}/{{ backup_dump_name }}'

the generated dump throws errors during restore with pg_restore regarding the ONLY keyword. Here is an excerpt:

pg_restore -j 4 --clean --create --if-exists --format=c -U postgres -d postgres {{ container_volume_path }}/{{ dump_file }}

pg_restore: error: could not execute query: ERROR:  cannot truncate only a hypertable
HINT:  Do not specify the ONLY keyword, or use truncate only on the chunks directly.
Command was: TRUNCATE TABLE ONLY public.satelliteproductssatellitezones;

pg_restore: error: could not execute query: ERROR:  current transaction is aborted, commands ignored until end of transaction block
Command was: COPY public.satelliteproductssatellitezones (id, satelliteproduct_id, satellitezone_id, predictiontasks_processstatus_id, trainingdatatasks_processstatus_id, image_date) FROM stdin;
pg_restore: error: could not execute query: ERROR:  ONLY option not supported on hypertable operations
Command was: ALTER TABLE ONLY public.satelliteproductssatellitezones
    ADD CONSTRAINT satelliteproductssatellitezones_pkey PRIMARY KEY (id, image_date);

I have been looking around for documentation to determine if I am doing something wrong, or if this way of performing backup/restore is just not supported? The backup and restore docs mention using pg_dump and pg_restore, so I am wondering why this is not working or does not have any documentation to point out backups should not be done this way with timescaledb.

At this point I assume the best practice would be to generate directory dump and use sed to remove all occurences of only, but I hope if that is the recommended approach it will be documented somewhere as it seems like a bug that pg_dump/pg_restore is recommended but not quite working.

TimescaleDB version affected

2.15.3

PostgreSQL version used

PostgreSQL 16.3 (Ubuntu 16.3-1.pgdg22.04+1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 11.4.0-1ubuntu1~22.04) 11.4.0, 64-bit

What operating system did you use?

Ubuntu 20.04.6 LTS (GNU/Linux 5.4.0-189-generic x86_64)

What installation method did you use?

Docker

What platform did you run on?

On prem/Self-hosted, Other

Relevant log output and stack trace

pg_restore: error: could not execute query: ERROR:  cannot truncate only a hypertable
HINT:  Do not specify the ONLY keyword, or use truncate only on the chunks directly.
Command was: TRUNCATE TABLE ONLY public.satelliteproductssatellitezones;

pg_restore: error: could not execute query: ERROR:  current transaction is aborted, commands ignored until end of transaction block
Command was: COPY public.satelliteproductssatellitezones (id, satelliteproduct_id, satellitezone_id, predictiontasks_processstatus_id, trainingdatatasks_processstatus_id, image_date) FROM stdin;
pg_restore: error: could not execute query: ERROR:  ONLY option not supported on hypertable operations
Command was: ALTER TABLE ONLY public.satelliteproductssatellitezones
    ADD CONSTRAINT satelliteproductssatellitezones_pkey PRIMARY KEY (id, image_date);


pg_restore: error: could not execute query: ERROR:  duplicate key value violates unique constraint "chunk_index_chunk_id_index_name_key"
DETAIL:  Key (chunk_id, index_name)=(10, _hyper_1_10_chunk_satelliteproductssatellitezones_sz_id_sp_id_i) already exists.
Command was: CREATE UNIQUE INDEX satelliteproductssatellitezones_sz_id_sp_id_image_date_ak ON public.satelliteproductssatellitezones USING btree (satellitezone_id, satelliteproduct_id, image_date);


pg_restore: error: could not execute query: ERROR:  duplicate key value violates unique constraint "chunk_index_chunk_id_index_name_key"
DETAIL:  Key (chunk_id, index_name)=(10, _hyper_1_10_chunk_spsz_pt_unassigned_idx) already exists.
Command was: CREATE INDEX spsz_pt_unassigned_idx ON public.satelliteproductssatellitezones USING btree (satelliteproduct_id) WHERE (predictiontasks_processstatus_id = 1);


pg_restore: error: could not execute query: ERROR:  duplicate key value violates unique constraint "chunk_index_chunk_id_index_name_key"
DETAIL:  Key (chunk_id, index_name)=(10, _hyper_1_10_chunk_spsz_satelliteproduct_id_idx) already exists.
Command was: CREATE INDEX spsz_satelliteproduct_id_idx ON public.satelliteproductssatellitezones USING btree (satelliteproduct_id);


pg_restore: error: could not execute query: ERROR:  duplicate key value violates unique constraint "chunk_index_chunk_id_index_name_key"
DETAIL:  Key (chunk_id, index_name)=(10, _hyper_1_10_chunk_spsz_tdt_unassigned_idx) already exists.
Command was: CREATE INDEX spsz_tdt_unassigned_idx ON public.satelliteproductssatellitezones USING btree (satelliteproduct_id) WHERE (trainingdatatasks_processstatus_id = 1);


pg_restore: error: could not execute query: ERROR:  ONLY option not supported on hypertable operations
Command was: ALTER TABLE ONLY public.satelliteproductssatellitezones
    ADD CONSTRAINT fk_satelliteproductssatellitezones_satellitezones FOREIGN KEY (satellitezone_id) REFERENCES public.satellitezones(id);


pg_restore: error: COPY failed for table "_hyper_1_1_chunk": ERROR:  invalid INSERT on the root table of hypertable "_hyper_1_1_chunk"
HINT:  Make sure the TimescaleDB extension has been preloaded.
CONTEXT:  COPY _hyper_1_1_chunk, line 1: "764910124	37300259	754043	6	6	2021-07-11 00:00:00"
pg_restore: error: COPY failed for table "_hyper_1_2_chunk": ERROR:  invalid INSERT on the root table of hypertable "_hyper_1_2_chunk"
HINT:  Make sure the TimescaleDB extension has been preloaded.
CONTEXT:  COPY _hyper_1_2_chunk, line 1: "765069017	37309004	728808	6	6	2024-06-06 00:00:00"
pg_restore: error: COPY failed for table "_hyper_1_3_chunk": ERROR:  invalid INSERT on the root table of hypertable "_hyper_1_3_chunk"
HINT:  Make sure the TimescaleDB extension has been preloaded.
CONTEXT:  COPY _hyper_1_3_chunk, line 1: "803252402	39414181	751270	6	6	2022-12-03 00:00:00"
pg_restore: error: COPY failed for table "_hyper_1_4_chunk": ERROR:  invalid INSERT on the root table of hypertable "_hyper_1_4_chunk"
HINT:  Make sure the TimescaleDB extension has been preloaded.
CONTEXT:  COPY _hyper_1_4_chunk, line 1: "772474200	37695143	786935	6	6	2021-09-30 00:00:00"
pg_restore: error: COPY failed for table "_hyper_1_5_chunk": ERROR:  invalid INSERT on the root table of hypertable "_hyper_1_5_chunk"
HINT:  Make sure the TimescaleDB extension has been preloaded.
CONTEXT:  COPY _hyper_1_5_chunk, line 1: "777159202	37967693	764891	6	6	2021-12-29 00:00:00"
pg_restore: error: COPY failed for table "_hyper_1_6_chunk": ERROR:  invalid INSERT on the root table of hypertable "_hyper_1_6_chunk"
HINT:  Make sure the TimescaleDB extension has been preloaded.
CONTEXT:  COPY _hyper_1_6_chunk, line 1: "782769870	38300710	800159	6	6	2022-03-29 00:00:00"
pg_restore: error: COPY failed for table "_hyper_1_7_chunk": ERROR:  invalid INSERT on the root table of hypertable "_hyper_1_7_chunk"
HINT:  Make sure the TimescaleDB extension has been preloaded.
CONTEXT:  COPY _hyper_1_7_chunk, line 1: "790918251	38733120	884539	6	6	2022-06-27 00:00:00"
pg_restore: error: COPY failed for table "_hyper_1_8_chunk": ERROR:  invalid INSERT on the root table of hypertable "_hyper_1_8_chunk"
HINT:  Make sure the TimescaleDB extension has been preloaded.
CONTEXT:  COPY _hyper_1_8_chunk, line 1: "804128042	39467932	648997	6	6	2022-12-24 00:00:00"
pg_restore: error: COPY failed for table "_hyper_1_9_chunk": ERROR:  invalid INSERT on the root table of hypertable "_hyper_1_9_chunk"
HINT:  Make sure the TimescaleDB extension has been preloaded.
CONTEXT:  COPY _hyper_1_9_chunk, line 1: "807402378	39663087	775768	6	6	2023-04-01 00:00:00"
pg_restore: error: COPY failed for table "_hyper_1_10_chunk": ERROR:  invalid INSERT on the root table of hypertable "_hyper_1_10_chunk"
HINT:  Make sure the TimescaleDB extension has been preloaded.
CONTEXT:  COPY _hyper_1_10_chunk, line 1: "814428871	40013632	649070	6	6	2023-06-22 00:00:00"
pg_restore: error: COPY failed for table "_hyper_1_11_chunk": ERROR:  invalid INSERT on the root table of hypertable "_hyper_1_11_chunk"
HINT:  Make sure the TimescaleDB extension has been preloaded.
CONTEXT:  COPY _hyper_1_11_chunk, line 1: "822184020	40405139	648889	6	6	2023-09-20 00:00:00"
pg_restore: error: COPY failed for table "_hyper_1_12_chunk": ERROR:  invalid INSERT on the root table of hypertable "_hyper_1_12_chunk"
HINT:  Make sure the TimescaleDB extension has been preloaded.
CONTEXT:  COPY _hyper_1_12_chunk, line 1: "408929331	17816730	636918	9	6	2023-12-28 00:00:00"
pg_restore: error: COPY failed for table "_hyper_1_13_chunk": ERROR:  invalid INSERT on the root table of hypertable "_hyper_1_13_chunk"
HINT:  Make sure the TimescaleDB extension has been preloaded.
CONTEXT:  COPY _hyper_1_13_chunk, line 1: "442304389	20154460	703557	9	9	2018-11-16 00:00:00"
pg_restore: error: COPY failed for table "_hyper_1_14_chunk": ERROR:  invalid INSERT on the root table of hypertable "_hyper_1_14_chunk"
HINT:  Make sure the TimescaleDB extension has been preloaded.
CONTEXT:  COPY _hyper_1_14_chunk, line 1: "834790322	41121508	673109	6	6	2019-02-23 00:00:00"
pg_restore: error: COPY failed for table "_hyper_1_16_chunk": ERROR:  invalid INSERT on the root table of hypertable "_hyper_1_16_chunk"
HINT:  Make sure the TimescaleDB extension has been preloaded.
CONTEXT:  COPY _hyper_1_16_chunk, line 1: "834965663	41131615	674425	6	6	2020-12-24 00:00:00"
pg_restore: error: COPY failed for table "_hyper_1_15_chunk": ERROR:  invalid INSERT on the root table of hypertable "_hyper_1_15_chunk"
HINT:  Make sure the TimescaleDB extension has been preloaded.
CONTEXT:  COPY _hyper_1_15_chunk, line 1: "834856720	41125418	751642	6	6	2020-04-26 00:00:00"
pg_restore: error: COPY failed for table "_hyper_1_17_chunk": ERROR:  invalid INSERT on the root table of hypertable "_hyper_1_17_chunk"
HINT:  Make sure the TimescaleDB extension has been preloaded.
CONTEXT:  COPY _hyper_1_17_chunk, line 1: "461625897	21188149	698261	6	9	2019-05-08 00:00:00"
pg_restore: error: COPY failed for table "_hyper_1_18_chunk": ERROR:  invalid INSERT on the root table of hypertable "_hyper_1_18_chunk"
HINT:  Make sure the TimescaleDB extension has been preloaded.
CONTEXT:  COPY _hyper_1_18_chunk, line 1: "476398082	21866066	698261	6	9	2019-08-12 00:00:00"
pg_restore: error: COPY failed for table "_hyper_1_19_chunk": ERROR:  invalid INSERT on the root table of hypertable "_hyper_1_19_chunk"
HINT:  Make sure the TimescaleDB extension has been preloaded.
CONTEXT:  COPY _hyper_1_19_chunk, line 1: "520108156	24097956	698261	6	9	2020-07-13 00:00:00"
pg_restore: error: COPY failed for table "_hyper_1_20_chunk": ERROR:  invalid INSERT on the root table of hypertable "_hyper_1_20_chunk"
HINT:  Make sure the TimescaleDB extension has been preloaded.
CONTEXT:  COPY _hyper_1_20_chunk, line 1: "561640684	26247463	698261	6	9	2021-06-02 00:00:00"
pg_restore: error: COPY failed for table "_hyper_1_21_chunk": ERROR:  invalid INSERT on the root table of hypertable "_hyper_1_21_chunk"
HINT:  Make sure the TimescaleDB extension has been preloaded.
CONTEXT:  COPY _hyper_1_21_chunk, line 1: "490091918	22573414	700919	6	9	2019-11-28 00:00:00"
pg_restore: error: COPY failed for table "_hyper_1_22_chunk": ERROR:  invalid INSERT on the root table of hypertable "_hyper_1_22_chunk"
HINT:  Make sure the TimescaleDB extension has been preloaded.
CONTEXT:  COPY _hyper_1_22_chunk, line 1: "504253309	23363236	700919	6	9	2020-04-03 00:00:00"
pg_restore: error: COPY failed for table "_hyper_1_23_chunk": ERROR:  invalid INSERT on the root table of hypertable "_hyper_1_23_chunk"
HINT:  Make sure the TimescaleDB extension has been preloaded.
CONTEXT:  COPY _hyper_1_23_chunk, line 1: "551122095	25740410	700919	6	9	2021-03-24 00:00:00"
pg_restore: error: COPY failed for table "_hyper_1_24_chunk": ERROR:  invalid INSERT on the root table of hypertable "_hyper_1_24_chunk"
HINT:  Make sure the TimescaleDB extension has been preloaded.
CONTEXT:  COPY _hyper_1_24_chunk, line 1: "839542819	41298728	767516	6	6	2024-06-16 00:00:00"
pg_restore: error: could not execute query: ERROR:  ONLY option not supported on hypertable operations
Command was: ALTER TABLE ONLY public.satelliteproductssatellitezones
    ADD CONSTRAINT fk_satelliteproductssatellitezones_satelliteproducts FOREIGN KEY (satelliteproduct_id) REFERENCES public.satelliteproducts(id);

How can we reproduce the bug?

I have not attempted to reproduce it from scratch in an empty database, but I can reproduce the bug from my existing database not containing any hypertables. I am using the docker image timescale/timescaledb-ha:pg16.3-ts2.15.3.

1. converting an existing table to a hypertable using the command

SELECT create_hypertable('tablename', 'timestampcolumn', chunk_time_interval => INTERVAL '3 months', migrate_data => true);

2. dump the complete database containing the hypertable with the following pg_dump flags

docker exec postgrescontainer bash -c 'pg_dump -Fc -Z zstd:9 -h localhost -U postgres databasename > /var/lib/postgresql/data/backup.dump'

3. on a new fresh postgres container with the backup.dump file present in the data directory run

docker exec freshpostgrescontainer  bash -c 'pg_restore -j 4 --clean --create --if-exists --format=c -U postgres -d postgres /var/lib/postgresql/data/backup.dump'

the errors are then generated

[fabriziomello]

@toutas when restoring from a dump you should put the instance in the restoring mode. Have a look the documentation: https://docs.timescale.com/migrate/latest/pg-dump-and-restore/

[toutas]

@toutas when restoring from a dump you should put the instance in the restoring mode. Have a look the documentation: https://docs.timescale.com/migrate/latest/pg-dump-and-restore/

I did end up doing that, but it makes no difference.

I fixed it by running pg_restore -f - to pipe the output to sed and remove all ONLY occurences for hypertables sed "s/ONLY {{ hypertable }}/{{ hypertable }}/g"

still think it should be documented that this is necessary

[fabriziomello]

@toutas when restoring from a dump you should put the instance in the restoring mode. Have a look the documentation: https://docs.timescale.com/migrate/latest/pg-dump-and-restore/

I did end up doing that, but it makes no difference.

I fixed it by running pg_restore -f - to pipe the output to sed and remove all ONLY occurences for hypertables sed "s/ONLY {{ hypertable }}/{{ hypertable }}/g"

still think it should be documented that this is necessary

The safe and correct way (as the document link I've sent before) is do something like:

1. Put your database into the restoring mode:

psql -d your_database -c 'SELECT timescaledb_pre_restore();'

2. Run the restore:

pg_restore ...

3. Put back your database into the normal mode:

psql -d your_database -c 'SELECT timescaledb_post_restore();'

[fabriziomello]

@toutas you need to pay attention to run the "pre" and "post" restore commands into the same database you'll restore your database.

[toutas]

@toutas when restoring from a dump you should put the instance in the restoring mode. Have a look the documentation: https://docs.timescale.com/migrate/latest/pg-dump-and-restore/

I did end up doing that, but it makes no difference. I fixed it by running pg_restore -f - to pipe the output to sed and remove all ONLY occurences for hypertables sed "s/ONLY {{ hypertable }}/{{ hypertable }}/g" still think it should be documented that this is necessary

The safe and correct way (as the document link I've sent before) is do something like:

1. Put your database into the restoring mode:

psql -d your_database -c 'SELECT timescaledb_pre_restore();'

2. Run the restore:

pg_restore ...

3. Put back your database into the normal mode:

psql -d your_database -c 'SELECT timescaledb_post_restore();'

I am well aware of the safe and correct way as I mentioned, I am already doing it. It does not fix the issue at hand though, so not sure why you are pointing it out?

This is the command I am running when restoring. without sed to remove ONLY keyword from DDL touching hypertables it does not work. If I remove that part it fails restoring with the errors mentioned in my main issue post.

@fabriziomello

so what you are saying is I need to run it not on the initial postgres database, but create my target database before restoring?

[fabriziomello]

so what you are saying is I need to run it not on the initial postgres database, but create my target database before restoring?

Exactly... because this "timescaledb_pre_restore()" will disable timescaledb hooks in order to don't raise exceptions on the statements that are failing during the restore. In a normal operation those statements are blocked by timescaledb.

[toutas]

so what you are saying is I need to run it not on the initial postgres database, but create my target database before restoring?

Exactly... because this "timescaledb_pre_restore()" will disable timescaledb hooks in order to don't raise exceptions on the statements that are failing during the restore. In a normal operation those statements are blocked by timescaledb.

Fair, I will try it out and confirm it fixes the issue.

Normally pg_dump and pg_restore do not require the target database to be present, so would be nice with a notice in the documentation to people using the --create flag like I am for restoring to fresh instances.

[fabriziomello]

Normally pg_dump and pg_restore do not require the target database to be present, so would be nice with a notice in the documentation to people using the --create flag like I am for restoring to fresh instances.

I know but unfortunately the pg_dump options --create and --clean will not work properly with timescaledb... this is another advice for you to don't use it and manually create the new target database.

[github-actions[bot]]

Dear Author,

This issue has been automatically marked as stale due to lack of activity. With only the issue description that is currently provided, we do not have enough information to take action. If you have or find the answers we would need, please reach out. Otherwise, this issue will be closed in 30 days.

Thank you!

[github-actions[bot]]

Dear Author,

We are closing this issue due to lack of activity. Feel free to add a comment to this issue if you can provide more information and we will re-open it.

Thank you!