0

After a backup on TablePlus (documentation) with only the --format=custom option, I got the following:

Backup database 'postgres'
Dumping...
warning: there are circular foreign-key constraints on this table:
  hypertable
You might not be able to restore the dump without using --disable-triggers or temporarily dropping the constraints.
Consider using a full dump instead of a --data-only dump to avoid this problem.
warning: there are circular foreign-key constraints on this table:
  chunk
You might not be able to restore the dump without using --disable-triggers or temporarily dropping the constraints.
Consider using a full dump instead of a --data-only dump to avoid this problem.

backup options restore options (restore)

I did not use the --data-only option neither on the backup, nor on the restore (I did try them also, but they fail with the same warnings).

Restore database 'postgres'
Restoring...
  while PROCESSING TOC:
  from TOC entry 3199; 0 16598 TABLE DATA metadata postgres
  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

Restore database completed

There seems to be no --disable-triggers option available and the generated dump file is binary and only 10KB.

Is there anything I can do to successfully perform the backup/dump through TablePlus or are pg_dumpall and pg_restore (question with disable triggers) the only way to go?

CPHPython
  • 339
  • 1
  • 3
  • 11

1 Answers1

1

I'd call that a bug or a limitation in TablePlus. Complain to them; at least the limitation should be documented.

Obviously TablePlus uses pg_dump --data-only under the hood, which prints the warning you see. Since TablePlus doesn't really seem to add much value to pg_dump, my suggestion is that you run pg_dump directly.

Laurenz Albe
  • 51,298
  • 4
  • 39
  • 69