Skip site navigation (1) Skip section navigation (2)

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 (view raw or flat)
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/
[ / \


pgsql-admin by date

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

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group