How to restore from Postgres DB using generated backup on Tigris?

, ,
1

Hi, I am currently using the automatic generated backup on Tigris, using this fly postgres backup enable · Fly Docs. I downloaded the .tar file, and tried to restore the DB locally for testing purpose. I used the code:
pg_restore -c -U postgres -d postgres -v "db.tar" -W.

However, it gives me this error:
pg_restore: error: could not find header for file "toc.dat" in tar archive

I searched on the internet and found the issue was the version of the pg_dump and the pg_restore is different on here database restore - pg_restore: [tar archiver] could not find header for file "toc.dat" in tar archive :- [PostgreSQL-11] pg admin 4 - Stack Overflow.

I tried searching the version of pg_dump or even the tools to backup our data in Fly Postgres, but didn’t find any info? Can someone please help. Thanks a lot :pray:

2

Hi… I believe the Fly Postgres image tag includes the version hint that you need…

$ fly status -a db-app-name
flyio/postgres-flex:16.4
                    ^^

In general, Postgres is very picky about major-version mismatches, e.g., 16.x versus 14.x, :dragon:. (Which is one of the few things that I dislike about that DBMS, actually.)

Hope this helps a little!

3

Thanks for your answer, but I tried opening the db.tar from Tigris, and I think it really has not toc.dat, I also can’t find the file restore.sql . I found another way to backup my DB which is to use fly proxy and pg_dump it from the proxy port. Now, I just need to run the .sql file usingpsql to my fly io machine, and it should be done.