Here's what I did:
After doing single table restore to a text file of the rawfeed table (the one the triggered the error),
I was able to get the id of the last row that was successfully exported. As mentioned earlier I was
able to import all the data up to that point without error.
To see how many rows were loaded in my development database (where the partial dataset was loaded) I ran:
select count(*) from rawfeed;
>> 287,478
The same table in the production database had 749,723 rows.
To get the id of the first 'bad' row, (the next one after the last successfully imported row), I ran this in production:
select id from rawfeed offset 287477 limit 10;
I verified that the first id returned matched the id of that last row successfully loaded on the dev server.
Then I took the id of the next row in the list (37126091) and did:
select * into rawfeed_backup where id = 37126091;
delete from rawfeed where id = 37126091;
After doing this I was able to do another dump of the entire database with the -Fc option and was able to restore that on our dev server successfully. BTW -- this includes dumping/restoring the rawfeed_backup table containing the one 'bad row'.
Then I noticed was that our original dump file from 1 week ago was 7GB, and the one today was 14GB.
We've had a lot of db activity, but I doubt our database has doubled in size in just one week.
Now I'm thinking that the 7GB dump file was somehow truncated or aborted before it was finished. (I ran the pg_dump as a background job, and didn't capture the output... so there may have been an error I didn't see).
Does this file truncation theory sound consistent with the symptoms I reported? Or does the 'single corrupted row of data' theory ring truer?
Mason
> ***(Single step mode: verify
> command)*******************************************
> COPY blocked_info (id, created_at, reason_code, note, do_count_links) FROM
> stdin;
> ***(press return to proceed or enter x and return to
> cancel)********************
> ERROR: invalid input syntax for integer: "2006-10-09 22:55:58"
> CONTEXT: COPY blocked_info, line 1, column id: "2006-10-09 22:55:58"
>
> The part of the script that is responsible for this error is:
>
> COPY blocked_info (id, created_at, reason_code, note, do_count_links) FROM
> stdin;
> 1 2006-10-09 22:55:58 0 \N \N
> 2 2006-10-09 22:55:58 0 \N \N
> 3 2006-10-09 22:55:58 0 \N \N
> 4 2006-10-09 22:55:58 0 \N \N
That's completely bizarre ... it seems like it's just lost the first
field of the COPY data, which is not a failure I've ever heard of
before. I have no theory about that at the moment.
I'm mystified too. Once I catch my breath, I circle back to take a closer look at this. But for now, I can backup and restore the database. If I find something more, I'll be sure to send a follow-up message.
Mason
