circular warning while backup using pg_dump with version 1.5.1

[comicfans]

Relevant system information:

• OS: archlinux 64bit kernel 5.1.15
• PostgreSQL version (output of postgres --version): postgres (PostgreSQL) 11.2
• TimescaleDB version (output of \dx in psql): 1.5.1
• Installation method: compile from source

Describe the bug

before upgrade, I'm using timescaledb 1.3, pg_dump gives no warning

pg_dump -U myname --format=custom -f pg_dmp.bak my_db_name

after upgrading to 1.5.1 (and I've also compressed some chunks), same backup commands shows warnings

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

I wonder if this is expected behavior , or did I miss-configure something ?

[Kobus-Smit]

I've got exactly the same warning today while trying compression on my local db. I'd like to use compression on our prod db (v11.5 (Ubuntu 11.5-1.pgdg18.04+1)) but prefer to wait for feedback first.

Relevant system information:

OS: Windows 10.0.18363.535 PostgreSQL version (output of postgres --version): postgres (PostgreSQL) 11.6 TimescaleDB version (output of \dx in psql): 1.5.1 Installation method: https://docs.timescale.com/latest/getting-started/installation/windows/installation-windows

[cevian]

This warning is expected. It is harmless. We will look at getting rid of it in the future.

[MichaHoffmann]

I would be interested in contributing, would this be a good first issue? If so i would need some pointers where to get started!

[AxelTheGerman]

Glad I found this issue... I don't like scary warnings I don't understand, at least now I know :upside_down_face:

[mfreed]

Additionally, you may receive a WARNING during pg_dump that has caused some conflusion:

DETAIL:  Data for hypertables are stored in the chunks of a
hypertable so COPY TO of a hypertable will not copy any data.
HINT:  Use "COPY (SELECT * FROM <hypertable>) TO ..." to
copy all data in hypertable, or copy each chunk individually.

This doesn't represent a problem/error, just a little confusing. Just flagging here both if other comes across the issue, but also for the team to get feedback. (cc: @bboule )

[pdteodoro]

The same happen to us and we are on version 1.7.1. We got for the backup done with Executing command: /home/postgres/software/postgresql/bin/pg_dump --port=8300 --host=localhost --username=postgres --format=custom --compress=6 --file=soar_dev.DEV.2020_08_09.backup.dump soar_dev >>& /postgresBackup/logs/soar_dev_DEV_backup.200809_050001.log

and we get: pg_dump: NOTICE: there are circular foreign-key constraints on this table: pg_dump: hypertable pg_dump: You might not be able to restore the dump without using --disable-triggers or temporarily dropping the constraints. pg_dump: Consider using a full dump instead of a --data-only dump to avoid this problem. pg_dump: NOTICE: there are circular foreign-key constraints on this table: pg_dump: chunk pg_dump: You might not be able to restore the dump without using --disable-triggers or temporarily dropping the constraints. pg_dump: Consider using a full dump instead of a --data-only dump to avoid this problem. pg_dump: NOTICE: hypertable data are in the chunks, no data will be copied DETAIL: Data for hypertables are stored in the chunks of a hypertable so COPY TO of a hypertable will not copy any data. HINT: Use "COPY (SELECT * FROM ) TO ..." to copy all data in hypertable, or copy each chunk individually. pg_dump: NOTICE: hypertable data are in the chunks, no data will be copied

In which version do you estimate the warnings will be removed?

Thanks, Pilar de Teodoro

[ateuber]

After upgrading from (TimescaleDB 1.6, PostgreSQL 11) to (TimescaleDB 2, PostgreSQL 12) we are also having this problem. If the warnings are expected, what is the best way to suppress them? I usually try to avoid adding grep -v statements to cron jobs.

[krvajal]

I don't think the warning is completely harmless as mentioned above. If I try to restore the dump later on using timescaledb-parallel-copy I get the error described in https://github.com/timescale/timescaledb-parallel-copy/issues/38 which is solved by disabling the triggers.

[joahking]

this issue is happening to me also while trying to download from a managed.timescale.com, I am adding the option --disable-triggers as recommended. My pg_version is: pg_dump (PostgreSQL) 14.4 (Ubuntu 14.4-1.pgdg20.04+1)

I can see in the tcpdump packets capture that my host ACKs a FIN packet sent by timescale which causes my host to close the connection

is there any way to workaround this issue?

[jflambert]

I'm at 2.7.2 and still getting these warnings

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

PLEASE, if it's harmless, find a way to remove these warnings (or make it clear in documentation)

[jlsevillano]

I'm at 2.7.2 and still getting these warnings and... the restore process fails...no restore all data only the data if is not in a compression policy date...for example if I have older than 7 days, the pg_restore only restore the last 7 days...no more. I did in the same machine, same postgres, same timescaledb version...and following the Guide. Any solution or explanation or help?

[fasblom]

Same issue

[umlumpa]

Same issue

[fraenku]

Have the same issue, i've the warning can be ignored it would be good to add it to the documentation on https://docs.timescale.com/timescaledb/latest/how-to-guides/backup-and-restore/troubleshooting/#versions-are-mismatched-when-dumping-and-restoring-a-database

[geo7]

same issue

[jlsevillano]

Following the guide, it runs OK:

-- BACKUP -- https://docs.timescale.com/self-hosted/latest/backup-and-restore/pg-dump-and-restore/

pg_dump -h <REMOTE_HOST> -p 55555 -U tsdbadmin -Fc -f tsdb.bak tsdb -- ATENTION: Do not use the pg_dump command to backup individual hypertables. Dumps created using this method lack the necessary information to correctly restore the hypertable from backup.

-- RESTORE CREATE DATABASE tsdb; \c tsdb CREATE EXTENSION IF NOT EXISTS timescaledb;

SELECT timescaledb_pre_restore();

! pg_restore -Fc -d tsdb tsdb.bak

SELECT timescaledb_post_restore();