Possible backup corruption using pg_dump only with compress parameter?

Question

I used this command to backup 200GB database (postgres 9.1, win7 x64):

pg_dump -Z 1 db_name > backup

It created 16GB file, which is fine I think because previous backups which works (and were packed by ext. tools) had similar size. Now, when I'm trying to restore into PG9.2 using pg_restore, I'm getting the error:

input file does not appear to be a valid archive

With pg_restore -Ft:

[tar archiver] corrupt tar header found in ▼ (expected 13500752, com
puted 78268) file position 512

Gzip also shows it's corrupted. When I open the backup file in Total Commander, the inner file has only 1.8GB. When I was looking for a solution, dump should be done with -Cf parameter probably.

Which format has the file right now? Is it only tar or gzip (winrar shows gzip)? Is there any way to restore this properly or is it corrupted somehow (no error when dumped)? Could it be due to file size limitations of tar or gzip?

Answer 1

What you have as output in "backup" is just zipped plain sql. You could check it by prompting:

gzip -l backup

Unfortunately pg_retore do not provide possibility to restore PLAIN SQL, so you just need to decompress the file and use psql -f <FILE> command:

zcat backup > backup.sql
psql -f backup.sql

It is not possible to make dump with pg_dump -Fc from postgres 9.1 as proposed by "Frank Heikens", because dump formats are not compatible between primary versions, like 9.0 -> 9.1 -> 9.2 and "pg_restore" will give you an error on 9.2

Answer 2

Mostly this error mean that your restore action used invalid format

From manual of pg_dump ( pg_dump --help )

-F, --format=c|d|t|p         output file format (custom, directory, tar,  
                             plain text (default))

This mean that if you create dump with pg_dump without option --format / -F that your dump will be created in plain text format

NOTE: Plain text format cannot be restored with pg_restore tool. Use psql < dump.sql instead.

Examples:

# plain text export/import
pg_dump -Fp -d postgres://<db_user>:<db_password>@<db_host>:<db_port>/<db_name> > dump.sql
psql -d postgres://<target_db_user>:<target_db_password>@<target_db_host>:<target_db_port>/<target_db_name> -f dump.sql

# custom format 
pg_dump -Fc -d postgres://<db_user>:<db_password>@<db_host>:<db_port>/<db_name> > dump.sql.custom
pg_restore -Ft postgres://<target_db_user>:<target_db_password>@<target_db_host>:<target_db_port>/<target_db_name> dump.sql.custom

# tar format 
pg_dump -Ft -d postgres://<db_user>:<db_password>@<db_host>:<db_port>/<db_name> > dump.sql.tar
pg_restore -Ft postgres://<target_db_user>:<target_db_password>@<target_db_host>:<target_db_port>/<target_db_name> dump.sql.tar

Error from subject also can occur when restoring format not match backup.
For example created dump will be in custom format but for restore specified tar