[prev in list] [next in list] [prev in thread] [next in thread] 

List:       postgresql-admin
Subject:    [ADMIN] short lines in big dump prevent backup
From:       Christian Fowler <spider () steelsun ! com>
Date:       2004-09-27 14:43:54
Message-ID: Pine.LNX.4.44.0409271024120.20435-100000 () leda ! steelsun ! com
[Download RAW message or body]


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@steelsun.com   |   http://www.steelsun.com/
[ / \


---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
[prev in list] [next in list] [prev in thread] [next in thread]