pg_restore with option --clean fails

[EtienneM]

Relevant system information:

• OS: Debian Stretch
• PostgreSQL version (output of postgres --version): postgres (PostgreSQL) 12.4 (Debian 12.4-1.pgdg90+1)
• TimescaleDB version (output of \dx in psql): 1.7.4
• Installation method: source

Describe the bug

We fail to restore a backup on the same database the dump was taken.

To Reproduce
Steps to reproduce the behavior:

1. Start a PostgreSQL database and enable TimescaleDB
2. Create a table sensor_data and make it a hypertable
3. Insert some data (691208 rows)
4. Take a dump:
   1. pg_dump --host host.example.com --port 30014 --dbname biniou_5636 --user admin --format c --no-owner --no-privileges --file /tmp/backup.pgsql
   2. We see the notice reported in circular warning while backup using pg_dump with version 1.5.1 #1581. They should be harmless.
5. Insert some more data
6. Restore the backup on the same database to get back at the state of the step 4:
   1. Execute psql -c "SELECT timescaledb_pre_restore()" "postgres://admin:..."
   2. Execute pg_restore --host host.example.com --port 30014 --dbname biniou_5636 --user admin --clean --if-exists --no-owner --no-privileges /tmp/backup.pgsql

Expected behavior
The backup should be restored without issue

Actual behavior
The execution of the pg_restore command leads to many errors:

pg_restore: while PROCESSING TOC:
pg_restore: from TOC entry 2; 3079 16389 EXTENSION timescaledb (no owner)
pg_restore: error: could not execute query: ERROR:  extension "timescaledb" has already been loaded with another version
DETAIL:  The loaded version is "1.7.4".
HINT:  Start a new session and execute CREATE EXTENSION as the first command. Make sure to pass the "-X" flag to psql.
Command was: CREATE EXTENSION IF NOT EXISTS timescaledb WITH SCHEMA public;


pg_restore: from TOC entry 3372; 0 0 COMMENT EXTENSION timescaledb
pg_restore: error: could not execute query: ERROR:  extension "timescaledb" does not exist
Command was: COMMENT ON EXTENSION timescaledb IS 'Enables scalable inserts and complex queries for time-series data';


pg_restore: from TOC entry 253; 1259 16942 TABLE _hyper_1_1_chunk biniou_5636
pg_restore: error: could not execute query: ERROR:  schema "_timescaledb_internal" does not exist
LINE 1: CREATE TABLE _timescaledb_internal._hyper_1_1_chunk (
                     ^
Command was: CREATE TABLE _timescaledb_internal._hyper_1_1_chunk (
    CONSTRAINT constraint_1 CHECK ((("time" >= '2020-09-10 00:00:00+00'::timestamp with time zone) AND ("time" < '2020-09-17 00:00:00+00'::timestamp with time zone)))
)
INHERITS (public.sensor_data);

pg_restore: from TOC entry 254; 1259 16952 TABLE _hyper_1_2_chunk biniou_5636
pg_restore: error: could not execute query: ERROR:  schema "_timescaledb_internal" does not exist
LINE 1: CREATE TABLE _timescaledb_internal._hyper_1_2_chunk (
                     ^
Command was: CREATE TABLE _timescaledb_internal._hyper_1_2_chunk (
    CONSTRAINT constraint_2 CHECK ((("time" >= '2020-09-17 00:00:00+00'::timestamp with time zone) AND ("time" < '2020-09-24 00:00:00+00'::timestamp with time zone)))
)
INHERITS (public.sensor_data);


pg_restore: from TOC entry 255; 1259 16962 TABLE _hyper_1_3_chunk biniou_5636
pg_restore: error: could not execute query: ERROR:  schema "_timescaledb_internal" does not exist
LINE 1: CREATE TABLE _timescaledb_internal._hyper_1_3_chunk (
                     ^
Command was: CREATE TABLE _timescaledb_internal._hyper_1_3_chunk (
    CONSTRAINT constraint_3 CHECK ((("time" >= '2020-09-24 00:00:00+00'::timestamp with time zone) AND ("time" < '2020-10-01 00:00:00+00'::timestamp with time zone)))
)
INHERITS (public.sensor_data);

[...]

pg_restore: from TOC entry 3097; 0 16837 TABLE DATA cache_inval_bgw_job admin
pg_restore: error: could not execute query: ERROR:  schema "_timescaledb_cache" does not exist
Command was: COPY _timescaledb_cache.cache_inval_bgw_job  FROM stdin;
pg_restore: from TOC entry 3096; 0 16840 TABLE DATA cache_inval_extension admin
pg_restore: error: could not execute query: ERROR:  schema "_timescaledb_cache" does not exist
Command was: COPY _timescaledb_cache.cache_inval_extension  FROM stdin;

[...]

Additional context
I can see that your documentation (https://docs.timescale.com/latest/using-timescaledb/backup#pg_dump-pg_restore) only mentions the use case of restoring a backup in a fresh and empty database. Hence I'm wondering if our use case is simply not supported, or if we missed an important step. Does any one of you tried what we are trying to achieve?

Note that we currently use this process to restore some PostgreSQL databases (without TimescaleDB) and it works like a charm.

[k-rus]

@EtienneM
I guess the problem is that --clean will drop the database objects first, which seems to drop the extension too. So option --clean is not supported.

[EtienneM]

OK thanks for the clarification. If I don't use the --clean option, I need to drop the tables beforehand. I tried with a simple setup. I have two tables sensors and sensor_data with a few data (34 MiB). Here is what I try to restore my database:

DROP TABLE sensors CASCADE; 
DROP TABLE sensor_data;

\! pg_restore --no-owner --no-privileges --host $HOSTNAME --port $PORT --dbname $DB_NAME --user admin /var/lib/postgresql/*pgsql

Doing this raises a few errors because some operations are not supported on chunk tables and on hypertables:

NOTICE:  drop cascades to 7 other objects
DETAIL:  drop cascades to constraint sensor_data_sensor_id_fkey on table sensor_data
drop cascades to constraint 1_1_sensor_data_sensor_id_fkey on table _timescaledb_internal._hyper_1_1_chunk
drop cascades to constraint 2_2_sensor_data_sensor_id_fkey on table _timescaledb_internal._hyper_1_2_chunk
drop cascades to constraint 3_3_sensor_data_sensor_id_fkey on table _timescaledb_internal._hyper_1_3_chunk
drop cascades to constraint 4_4_sensor_data_sensor_id_fkey on table _timescaledb_internal._hyper_1_4_chunk
drop cascades to constraint 5_5_sensor_data_sensor_id_fkey on table _timescaledb_internal._hyper_1_5_chunk
drop cascades to constraint 6_6_sensor_data_sensor_id_fkey on table _timescaledb_internal._hyper_1_6_chunk
DROP TABLE
DROP TABLE                                                                                               
biniou_2520=#                   
biniou_2520=# \! pg_restore --no-owner --no-privileges --host $HOSTNAME --port $PORT --dbname $DB_NAME --user admin /var/lib/postgresql/*pgsql
pg_restore: while PROCESSING TOC:
pg_restore: from TOC entry 3215; 2606 16945 FK CONSTRAINT _hyper_1_1_chunk 1_1_sensor_data_sensor_id_fkey admin
pg_restore: error: could not execute query: ERROR:  operation not supported on chunk tables
Command was: ALTER TABLE ONLY _timescaledb_internal._hyper_1_1_chunk
    ADD CONSTRAINT "1_1_sensor_data_sensor_id_fkey" FOREIGN KEY (sensor_id) REFERENCES public.sensors(id);
                                                    

pg_restore: from TOC entry 3216; 2606 16955 FK CONSTRAINT _hyper_1_2_chunk 2_2_sensor_data_sensor_id_fkey admin
pg_restore: error: could not execute query: ERROR:  operation not supported on chunk tables
Command was: ALTER TABLE ONLY _timescaledb_internal._hyper_1_2_chunk
    ADD CONSTRAINT "2_2_sensor_data_sensor_id_fkey" FOREIGN KEY (sensor_id) REFERENCES public.sensors(id);


pg_restore: from TOC entry 3217; 2606 16965 FK CONSTRAINT _hyper_1_3_chunk 3_3_sensor_data_sensor_id_fkey admin
pg_restore: error: could not execute query: ERROR:  operation not supported on chunk tables
Command was: ALTER TABLE ONLY _timescaledb_internal._hyper_1_3_chunk
    ADD CONSTRAINT "3_3_sensor_data_sensor_id_fkey" FOREIGN KEY (sensor_id) REFERENCES public.sensors(id);


pg_restore: from TOC entry 3218; 2606 16975 FK CONSTRAINT _hyper_1_4_chunk 4_4_sensor_data_sensor_id_fkey admin
pg_restore: error: could not execute query: ERROR:  operation not supported on chunk tables
Command was: ALTER TABLE ONLY _timescaledb_internal._hyper_1_4_chunk
    ADD CONSTRAINT "4_4_sensor_data_sensor_id_fkey" FOREIGN KEY (sensor_id) REFERENCES public.sensors(id);


pg_restore: from TOC entry 3219; 2606 16985 FK CONSTRAINT _hyper_1_5_chunk 5_5_sensor_data_sensor_id_fkey admin
pg_restore: error: could not execute query: ERROR:  operation not supported on chunk tables
Command was: ALTER TABLE ONLY _timescaledb_internal._hyper_1_5_chunk
    ADD CONSTRAINT "5_5_sensor_data_sensor_id_fkey" FOREIGN KEY (sensor_id) REFERENCES public.sensors(id);


pg_restore: from TOC entry 3220; 2606 16995 FK CONSTRAINT _hyper_1_6_chunk 6_6_sensor_data_sensor_id_fkey admin
pg_restore: error: could not execute query: ERROR:  operation not supported on chunk tables
Command was: ALTER TABLE ONLY _timescaledb_internal._hyper_1_6_chunk
    ADD CONSTRAINT "6_6_sensor_data_sensor_id_fkey" FOREIGN KEY (sensor_id) REFERENCES public.sensors(id);

pg_restore: from TOC entry 3214; 2606 16934 FK CONSTRAINT sensor_data sensor_data_sensor_id_fkey admin
pg_restore: error: could not execute query: ERROR:  ONLY option not supported on hypertable operations
Command was: ALTER TABLE ONLY public.sensor_data
    ADD CONSTRAINT sensor_data_sensor_id_fkey FOREIGN KEY (sensor_id) REFERENCES public.sensors(id);


pg_restore: warning: errors ignored on restore: 7

Despite these errors, I can see that my data is available in the database but I fear some indexes might be missing. Do you know if these errors are bad or if I can deal with it?

[k-rus]

@EtienneM Sorry for the delay in the reply. My guess is that only foreign key constraints are missing. I don't know if it is related that you restore into the database where the tables were dropped before. Requires an investigation on our side.

You might want to create the foreign key constraint on the hypertable manually.

[EtienneM]

You might want to create the foreign key constraint on the hypertable manually.

The thing is that these missing foreign key constraints had been created by TimescaleDB and I have no idea what they are supposed to look like. Some of these missing constraints are defined on chunk tables. Is there a TimescaleDB command to re-create the foreign keys of all the chunk tables?

[k-rus]

@EtienneM TimescaleDB doesn't create any foreign key constraints. So it was manually created by someone in your database (or if you use some ORM, it might be created by it). I see no other way how they appeared in the original database.

[ilteen]

THANK YOU @EtienneM!! If it weren't for this issue, i'd probably still be trying to fix this... I also dumped with -c and tried to restore from that dump, already lost hours searching for a solution. Finally it works, omitting -c solved all issues.

[muntdan]

I encounter similar error due to CONSTRAINT UNIQUE (not FK).
This was due to not running the SELECT timescaledb_pre_restore(); before pg_restore and SELECT timescaledb_post_restore(); after as mentioned: https://docs.timescale.com/timescaledb/latest/how-to-guides/backup-and-restore/pg-dump-and-restore/

[konskov]

Hi @danmunteanuevo, thanks for commenting. Are you also still encountering this issue, or did you find a solution?