Using pg_dump and pg_restore with/without -Fc

TimescaleDB and PostgreSQL
1

I have tried to backup and restore a database according to the docs at https://docs.timescale.com/self-hosted/latest/backup-and-restore/pg-dump-and-restore/

This did not work, I kept getting pg_restore: error: did not find magic string in file header as an error.

PostgreSQL version is 12.9 and TimescaleDB version is 2.5.1

I then tried to use pg_dump without the -Fc option, which creates an ASCII backup. I could restore this like so: psql --set ON_ERROR_STOP=on -U myuser -f /dbdump-asc.sql

Cursory tests of our application show that the data from the hypertable appears to be there. But my concern is now that perhaps the ASCII backup isn’t suitable for the binary hypertable chunks. Is there a way to verify that those were indeed restored correctly?

2

Hi @ulim, that is an interesting question. You can find all metadata information using the timescaledb_information.hypertables and join info with timescaledb_information.chunks to precisely see if everything is there.

Another option is just validate using a query in both sides and compare the results. I’m not that familiar with the level of compatibilities of ASCII mode using binary.

I’d also recommend you to take a look at our new migration tool: hypershift!