Unsightly warnings during pg_dump

[victor-sudakov]

Relevant system information:

• OS: Debian Buster
• Postgres version: Debian 12.6-1.pgdg100+1
• Timescaledb 2.2.0
• Installation method: apt install from pgdg repo

Describe the bug

There are unsightly warnings from pg_dump while doing the pg_dump of a Timescaledb-enabled database:

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.
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 <hypertable>) 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
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.
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 <hypertable>) 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

To Reproduce

pg_dump -Fc -U postgres -f zabbix.dump -d zabbix

Expected Bbehavior

It is not clear whether these warnings mean that:

• the timescaledb database itself is corrupt ?
• the dump will come out corrupt?
• everything is fine and the warnings can be safely ignored?

Additional Considerations

I've read https://docs.timescale.com/latest/using-timescaledb/backup, it says that restoring data from a backup currently requires some additional procedures. It would be fine if the warnings mentioned those special procedures. But in their current state, the warnings are useless (?) and alarming. The message about circular foreign-key constraints is especially alarming. The phrase Data for hypertables are stored in the chunks of a hypertable so COPY TO of a hypertable will not copy any data also (falsely?) hints that the dump may be inconsistent.

[erimatnor]

@victor-sudakov Thank you for reporting this issue. It is a partial duplicate of #1581

Short answer is that these warnings are not a problem for your data dump. pg_dump blindly tries to COPY data from all tables, including the "root" hypertable that contains no data. This leads to the warning you are seeing since the extension cannot distinguish between pg_dump and a user trying to COPY data out of a hypertable. We're considering removing these messages in the future.

[PaweltK]

I have the same problem.

• Debian GNU/Linux 10 (buster)
• (PostgreSQL) 11.9 (Debian 11.9-1.pgdg100+1)
• timescaledb 1.7.4

command:
pg_dump -Fc -f /home/backup/pgsql/zabbix-20210608.pgdump zabbix

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 <hypertable>) 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
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.
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 <hypertable>) 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
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.
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 <hypertable>) 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
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.
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 <hypertable>) TO ..." to copy all data in hypertable, or copy each chunk individually.

the backup is probably not correct.
The backup is 1000MB and the database is 257GB
I don't think compression works that well.

 # ls -lah /home/backup/pgsql/zabbix-20210608.pgdump
-rw-r--r-- 1 postgres postgres 1000M Jun  8 17:53 /home/backup/pgsql/zabbix-20210608.pgdump

postgres=# \l+ zabbix
                                               List of databases
  Name  | Owner  | Encoding |   Collate   |    Ctype    | Access privileges |  Size  | Tablespace | Description
--------+--------+----------+-------------+-------------+-------------------+--------+------------+-------------
 zabbix | zabbix | UTF8     | en_US.UTF-8 | en_US.UTF-8 |                   | 247 GB | pg_default |
(1 row)

[victor-sudakov]

the backup is probably not correct.
The backup is 1000MB and the database is 257GB
I don't think compression works that well.

What happens if you try to pg_restore the dump? Just don't forget about the additional procedures described in https://docs.timescale.com/latest/using-timescaledb/backup

[NunoFilipeSantos]

@victor-sudakov can you please try with our latest TimescaleDB 2.4.2 (PG 12 or PG13) and get back to us?

[victor-sudakov]

@victor-sudakov can you please try with our latest TimescaleDB 2.4.2 (PG 12 or PG13) and get back to us?

Will be able to do that after the weekend.

[NunoFilipeSantos]

That's great! What were your results?

[NunoFilipeSantos]

Hi @victor-sudakov ! 👋 If you can reproduce this on the latest TimescaleDB version, please feel free to reopen the issue.

[victor-sudakov]

I'm currently on timescaledb-2-postgresql-12/buster,now 2.5.0~debian10 amd64. The warnings are still there. Maybe I'm doing something wrong?

# pg_dump -U postgres -Fc -f zabbix.pgdump zabbix
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.
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 <hypertable>) 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
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.
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 <hypertable>) 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
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.
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 <hypertable>) 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
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.
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 <hypertable>) TO ..." to copy all data in hypertable, or copy each chunk individually.

[mkindahl]

@victor-sudakov The warnings are expected and does not affect your backup.

The main reason they are there is because the COPY command on a hypertable does not copy the rows of the hypertable and we have added a warning to make sure that the user understands that the reason there are no rows are because they are in the chunks. Eliminating the warnings would be good, but that would require us to be able to detect that the COPY command is running as part of a pg_dump.

Have you checked if pg_restore restore the backup correctly?

[NunoFilipeSantos]

@victor-sudakov It seems to work as expected. If you can check 'pg_restore' that would be great. Feel free to reopen the issue.

[victor-sudakov]

@victor-sudakov It seems to work as expected. If you can check 'pg_restore' that would be great. Feel free to reopen the issue.

I switched to wal-g for TimescaleDB backup, but I'll make a lab this week and report.

I clearly remember there being special recommendations/steps for logical backup and restore of a TimescaleDB database, but now cannot find them anymore. It was in the link I gave above. You cannot just run pg_dump | pg_restore with TimescaleDB, can you?

[ravz]

hello, any update on this? what is the recommended way to do backups for timescaledb please?

[fabriziomello]

@ravz you can execute pg_dump/pg_restore in a tsdb database. You just pay attention in instructions during the restore: https://docs.timescale.com/timescaledb/latest/how-to-guides/backup-and-restore/pg-dump-and-restore/