Pg_dump with exclude-table and exclude-schema

1

I have business reason for omitting some schema and tables during pg_dump command. During pg_restore command I have errors due to tables and schema that I omitted

pg_dump --jobs=3 --format=d --compress=3 ‘–exclude-table=aaa*’ --exclude-table=bbb --exclude-schema=test2 --no-password --file=/var/lib/postgresql/backup/test test --quote-all-identifiers

pg_restore --format=directory --dbname=“test” “./test” --verbose

pg_restore: from TOC entry 3982; 0 36627276 TABLE DATA _hyper_1_1_chunk postgres
pg_restore: error: could not execute query: ERROR: relation “_timescaledb_internal._hyper_1_1_chunk” does not exist
Command was: COPY “_timescaledb_internal”.“_hyper_1_1_chunk” (“time”, “location”, “device”, “temperature”, “humidity”) FROM stdin;

pg_restore: creating INDEX “_timescaledb_internal._hyper_2_2_chunk_aaabbb_time_idx”
pg_restore: from TOC entry 3822; 1259 36627289 INDEX _hyper_2_2_chunk_aaabbb_time_idx postgres
pg_restore: error: could not execute query: ERROR: relation “_timescaledb_internal._hyper_2_2_chunk” does not exist
Command was: CREATE INDEX “_hyper_2_2_chunk_aaabbb_time_idx” ON “_timescaledb_internal”.“_hyper_2_2_chunk” USING “btree” (“time” DESC);

I think timescaledb additional create some information in own system schema and during pg_dump is not able to delete them? Am I right?

2

Hi @Pawel_S , yes. You may follow the official steps to avoid such problem:

This says Timescale Cloud but it can work with any postgresql origin/destin.

Just make sure you wrap it with pre_restore and post_restore.

If you already have data, I’d also recommend you using timescaledb-parallel-copy and just handle data and structure independently.