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?

Legumin

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

Warplane

This is what I'm trying all day. Strange is, when new dump is created using pg_dump -Z1 my_new_database > backup everytime backup is corrupted, but on postgres DB as default db created pg_dump -Z1 postgres > backup it works fine. How is it possible? – Legumin

Why did you come to conclusion, that result of pg_dump -Z1 my_new_database > backup is corrupted? What exactly happening? – Warplane

I'm not able to unpack it in any way. Everything return CRC error. You can try yourself. This is test backup: backup.gz (0.5kB) and this is plain SQL of new backup backup.sql. I wonder if anyone can do it. – Legumin

Cannot repeat your error. For me everything is working fine for any DB. Try to use gzip instead pg_dump -Z1, like this: pg_dump my_new_database | gzip - > backup.gz – Warplane

With gzip pipe it's alright. But I desperately need old backup. Have you tried my example above? If not, could you please? – Legumin

Yes, I've tried. Its undoubtedly broken. If it is the same with your 16Gb gz-file, than it is bad situation. GZip do not provide any recovery features. Though, you can try to read this. Or use for example gzrecover, but it is only allow you to skip over corrupted data, not to recover it. Is there no possibility to make this backup again as a PLAIN SQL? – Warplane

Thank you very much. I've already tried gzrecover - didn't work. Unfortunately there is no way to make backup again. However, I'll report it as bug. – Legumin

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

Sink

I wrote solution as an edit at the end of my question: Windows replaced newline \n with \r\n. You have to replace it back. I advise not to use forwarding to file in Windows but rather use output file as an parameter of pg_dump (or any other script you are using). – Legumin

@Legumin I've just added this answer as possible solution for similar problem, but another root cause – Sink

Answer 3

Your dump is plain SQL, it's not a tar format, like you try to use in pg_restore. Use --format=custom or -Fc when you want a compressed format and use this setting in pg_restore as well. Check the manual.

Stelle

Unfortunately, it is not plain SQL. Try yourself pg_dump -Z 1..., manual is not clear to me if I only use -Z parameter. Also filesize of 200GB db would be much bigger than 16GB in case of plain SQL. – Legumin

Answer 4

This is an old thread though I had the exact same issue and managed to fix the somewhat corrupted dump with fixgz:

Short answer: run fixgz http://www.gzip.org/fixgz.zip on compressed dump.

fixgz.exe bad.gz fixed.gz

Long answer: So if you used pg_dump with --compresss or -Z without specifying custom format option (-Fc) what you actually get is a compressed file in ASCII mode instead of BINARY mode.

Quoting from http://www.gzip.org/#faq1

If you have transferred a file in ASCII mode and you no longer have access to the original, you can try the program fixgz to remove the extra CR (carriage return) bytes inserted by the transfer. A Windows 9x/NT/2000/ME/XP binary is here. But there is absolutely no guarantee that this will actually fix your file. Conclusion: never transfer binary files in ASCII mode.

Backus

Answer 5

I got this problem when restoring using PGAdmin III. The problem doesn't occur with PGAdmin 4.

Rationality

Welcome to the SO club. Would you please clarify your advice? Do you suggest to use PGAdmin 4 instead of PGAdmin 3? – Xenon