short lines in big dump prevent backup

From: Christian Fowler <spider(at)steelsun(dot)com>
To: pgsql-admin(at)postgresql(dot)org
Subject: short lines in big dump prevent backup
Date: 2004-09-27 14:43:54
Message-ID: Pine.LNX.4.44.0409271024120.20435-100000@leda.steelsun.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-admin


I'm running a large database ( data dir is ~9gigs ) on 7.4.5 that I have
been dumping nightly with:
/usr/bin/pg_dump -U postgres mydb > /var/lib/pgsql/backups/$filename
and then I bzip2 it.

Last night, things crashed, and I am attempting to restore from a backup.

When restoring as postgres user with: bzcat dump.bz2|psql mydb
I get an error. In one table that has about 5.4 million rows, the dump has
several incomplete rows in the large block of copy data. It seems to
attempt to fill the copy with data from the *next* line.

ERROR: invalid input syntax for type timestamp: "4"
CONTEXT: COPY loc_data, line 169371, column mod_date: "4"

going to 169371 lines after the start of the copy, indeed there is a short
row (by two), and two fields in on the *next* line is indeed a "4"

I can fix this either by hand, or regenerating. However, once the DB is
mission critical, having an unusable dump is my biggest concern. Is there
something I should be doing?

(system is Fedora Core 1 with 7.4.5-PGDG rpm's)

[ \ /
[ >X< spider(at)steelsun(dot)com | http://www.steelsun.com/
[ / \

Browse pgsql-admin by date

  From Date Subject
Next Message Gonzales,S 2004-09-27 14:50:42 securing template1
Previous Message Tsirkin Evgeny 2004-09-27 09:15:45 Re: how to read /write xml data and insert into the postgres