How to verify that an archive produced by pg_dump archive is OK and valid?

Question

I am working on backup/restore data from postgresql for what I use pg_dump.exe and pg_restore.exe.

In order to restore backup file I have to delete actual tables in database what can be "suicidal job" if something with dumped file isn't OK.

I can check dumped file for integrity with (say) '7z t mydump.gz' what I do like first test.

But I would like to know, because this archive is original PG archive did PostgreSQL contain any technique to check this '.gz' archive which I can take before deleting actual tables?

And if do, how to do a proper check?

EDIT: This is my actual command for dumping:

"C:\Program Files (x86)\PostgreSQL\9.1\bin\pg_dump.exe" --host localhost --port 5432 --username "postgres" --no-password --verbose -F t --file "C:\Users\User 1\Desktop\mydatabase.gz" "mydatabase"

Answer 1

You appear to be trying to verify the validity and correctness of a PostgreSQL dump you've just made.

Your key misunderstanding is that you do not have to restore the dump into the same database you created it from. You can restore to another database on the same cluster, or for extra paranoia a database on another cluster (server). Verify that the dump restored without error and that the data is as you expect it to be.

For extra paranoia, stop the PostgreSQL server and copy the files in the data directory. That way you have a file-level backup too. Note that file-level copies of PostgreSQL data directories can only be read by the same major (8.1/8.2/...) version of PostgreSQL built with the same option on the same platform - so, if the datadir is from 9.2.x on Windows x64, it can only be read by another Windows x64 host with 9.2.x installed.

If you're worried about your original database then you probably don't have backups. This is a critical problem. You need to go and urgently read the documentation chapter on backup and restore and get a good automated backup scheme in place. Have a look at barman.

Update after question edit:

-F t is an odd choice; plain SQL dumps or -F c usually makes more sense.

The file you've produced is not a .gz (gzip compressed) file, anyway, it's a .tar archive, not compressed. It can be extracted into a directory full of SQL files.

To test it, use pg_restore to restore it to a new empty database created with createdb or the CREATE DATABASE command.