Multiple errors while restoring a PostgreSQL DB (same versions on backup and restore)

TimescaleDB and PostgreSQL
1

Hello,

I’m trying to restore a backup of a PostgreSQL DB on the same PC where backup was taken. But I’m getting quite a few errors (even though it says at the end “warning ignored”) and the data is restored almost correctly with missing primary keys in tables. Below are the three different types of error I get during the restore and process watcher updates after it. The error 2 and 3 happen for multiple tables, but as an illustration I have just isolated the ones associated to “loads” table:

  1. pg_restore: error: COPY failed for table “metadata”: ERROR: duplicate key value violates unique constraint “metadata_pkey”
    pg_restore: processing data for table “_timescaledb_catalog.metadata”
    pg_restore: while PROCESSING TOC:
    pg_restore: from TOC entry 3539; 0 36672697 TABLE DATA metadata postgres
    pg_restore: error: COPY failed for table “metadata”: ERROR: duplicate key value violates unique constraint “metadata_pkey”
    DETAIL: Key (key)=(exported_uuid) already exists.
    CONTEXT: COPY metadata, line 1

  2. pg_restore: error: could not execute query: ERROR: operation not supported on chunk tables
    pg_restore: creating CONSTRAINT “_timescaledb_internal._hyper_9_129_chunk 129_11_PK_loads”
    pg_restore: from TOC entry 3837; 2606 36673870 CONSTRAINT _hyper_9_129_chunk 129_11_PK_loads postgres
    pg_restore: error: could not execute query: ERROR: operation not supported on chunk tables
    Command was: ALTER TABLE ONLY _timescaledb_internal._hyper_9_129_chunk
    ADD CONSTRAINT “129_11_PK_loads” PRIMARY KEY (machine_id, “time”);

  3. pg_restore: error: could not execute query: ERROR: operation not supported on hypertables that have compression enabled
    pg_restore: creating CONSTRAINT “public.loads PK_loads”
    pg_restore: from TOC entry 3834; 2606 36673919 CONSTRAINT loads PK_loads postgres
    pg_restore: error: could not execute query: ERROR: operation not supported on hypertables that have compression enabled
    Command was: ALTER TABLE ONLY public.loads
    ADD CONSTRAINT “PK_loads” PRIMARY KEY (machine_id, “time”);

I’m like super new to Timescale, so my apologies in advance if I missed something basic to do here. I hope to keep learning from the community. Please let me know if any further information is required on the restore or errors. Thanks in advance!

PostgerSQL 13 and Timescale ver 2.11.0

2

welcome @rajatsanklecha , can you share how you did the backup?

Did you follow our backup/restore instructions?