Re: Help - corruption issue?

> On Tuesday, April 26, 2011, Tomas Vondra <tv(at)fuzzy(dot)cz> wrote:
>> Dne 25.4.2011 18:16, Phoenix Kiula napsal(a):
>>> Sorry, spoke too soon.
>>>
>>> I can COPY individual chunks to files. Did that by year, and at least
>>> the dumping worked.
>>>
>>> Now I need to pull the data in at the destination server.
>>>
>>> If I COPY each individual file back into the table, it works. Slowly,
>>> but seems to work. I tried to combine all the files into one go, then
>>> truncate the table, and pull it all in in one go (130 million rows or
>>> so) but this time it gave the same error. However, it pointed out a
>>> specific row where the problem was:
>>>
>>> COPY links, line 15272357:
>>> "16426447     9s2q7   9s2q7   N       http://www.amazon.com/gp/search?camp=1789&amp;creative=9325&amp;ie=UTF8&amp;i..."
>>> server closed the connection unexpectedly
>>>       This probably means the server terminated abnormally
>>>       before or while processing the request.
>>> The connection to the server was lost. Attempting reset: Failed.
>>>
>>> Is this any use at all?  Would appreciate any pointers!
>>
>> So the dump worked fina and it fails when loading it back into the DB?
>> Have you checked the output file (just see the tail). Can you post the
>> part that causes issues? Just the line 16426447 and few lines around.
>>
>> regards
>> Tomas

From the old server:
Yearly COPY files worked. Pg_dumpall was giving problems.

In the new server:
COPY FROM worked. All files appear to have been copied. Then I create
the primary key index, and another index. Many records are there, but
many are not there! There's no error, just that some records/rows just
didn't make it.

I did the COPY FROM in a transaction block. If there had been an
error, then "commit" would have rolledback, right? It didn't. It
committed. No errors. Just that some data has not come in.

How can I get more info on why?

Tomas, the line where it crashed, here are the 10 or so lines around it:

> head -15272350 /backup/links/links_all.txt | tail -20
16426422 9s2pi 9s2pi N http://www.amazon.com/gp/search?camp=1789&amp;creative=9325&amp;ie=UTF8&amp;index=digital-music&amp;keywords=Cannibal+Corpse+-+Split+Wide+Open&amp;linkCode=ur2&amp;tag=dmp3-20 0 121.214.194.133 7a69d5842739e20b56c0103d1a6ec172e58f9e07 \N Y 2009-01-10
20:59:31.135881 2009-01-10 20:59:31.135881 \N \N
16426423 9s2pj 9s2pj N http://www.amazon.com/gp/search?camp=1789&amp;creative=9325&amp;ie=UTF8&amp;index=digital-music&amp;keywords=Juana+Fe+-+la+murga+final&amp;linkCode=ur2&amp;tag=dmp3-20 0 201.215.6.104 5e2ae1f363c7854c13a101a60b32a9a1ade26767 \N Y 2009-01-10
20:59:31.593474 2009-01-10 20:59:31.593474 Y \N \N
15897862 9gqva 9gqva N http://www.amazon.com/gp/search?camp=1789&amp;creative=9325&amp;ie=UTF8&amp;index=digital-music&amp;keywords=Boyz+II+Men+-+Ill+Make+Love+To+You&amp;linkCode=ur2&amp;tag=dmp3-20 0 76.10.185.87 3c840fa5428c0464556dccb7d1013a6ec53d1743 N Y 2009-01-04
19:40:50.734967 2009-01-10 20:59:32.286937 N \N \N
15130149 90ahx 90ahx N http://www.amazon.com/gp/search?camp=1789&amp;creative=9325&amp;ie=UTF8&amp;index=digital-music&amp;keywords=The+Killers+-+All+The+Pretty+Faces&amp;linkCode=ur2&amp;tag=dmp3-20 0 65.25.74.141 5eb2a1bb48d4926d8eaf946fb544ce11c50a9e5b N Y 2008-12-22
14:54:20.813923 2009-01-10 20:59:33.896232 N \N \N
16426425 9s2pl 9s2pl N http://www.amazon.com/gp/search?camp=1789&amp;creative=9325&amp;ie=UTF8&amp;index=digital-music&amp;keywords=Freddy+Quinn+-+Junge%2C+Komm+Bald+Wieder&amp;linkCode=ur2&amp;tag=dmp3-20 0 123.100.137.226 fb7af64a4b886f074a6443b8d43f571c3083f51c \N Y 2009-01-10
20:59:33.986764 2009-01-10 20:59:33.986764 Y \N \N
16391756 9rbyk 9rbyk N http://www.amazon.com/gp/search?camp=1789&amp;creative=9325&amp;ie=UTF8&amp;index=digital-music&amp;keywords=Closure+In+Moscow+-+Ofelia...+Ofelia&amp;linkCode=ur2&amp;tag=dmp3-20 0 71.233.18.39 a4f95f246b89523785b736530fb4b3a335195c4b N Y 2009-01-10
13:20:54.86346 2009-01-10 20:59:34.641193 N \N \N
16229928 9nv3c 9nv3c N http://www.amazon.com/gp/search?camp=1789&amp;creative=9325&amp;ie=UTF8&amp;index=digital-music&amp;keywords=Ministry+of+Sound+-+Freestylers+%2F+Push+Up&amp;linkCode=ur2&amp;tag=dmp3-20 0 24.60.222.70 b455933eb976b39313f5da56afcd9db29d3f7bde N Y 2009-01-08
19:35:19.842463 2009-01-10 20:59:35.343552 N \N \N
16426427 9s2pn 9s2pn N http://www.annehelmond.nl/2007/11/26/celebrating-two-thousand-delicious-bookmarks/ 195.190.28.97 22a06537e25985273297471dbeb3fb6ae217cb90 \N Y 2009-01-10
20:59:36.125122 2009-01-10 20:59:36.125122 Y \N \N
16426428 9s2po 9s2po N http://twinkle.tapulous.com/index.php?hash=9c01cb7b216a7f8b66056d20dd218f67f52f433e 66.135.60.238 d60e7f2801c05422b4ef17a1ca63df13772c4692 \N Y 2009-01-10
20:59:36.249249 2009-01-10 20:59:36.249249 Y \N \N
16426426 9s2pm 9s2pm N http://www.bikinibeat.org/bikini-barista-alisha-erickson-of-java-girls/11322/ 0 67.205.21.208 40970475a84e9879a2659aedf821156e2aac7323 N Y 2009-01-10
20:59:34.190555 2009-01-10 20:59:36.538822 N \N \N
16426429 9s2pp 9s2pp N http://www.amazon.com/gp/search?camp=1789&amp;creative=9325&amp;ie=UTF8&amp;index=digital-music&amp;keywords=Chico+Trujillo+-+Cabildo&amp;linkCode=ur2&amp;tag=dmp3-20 0 201.215.6.104 820aa985ca7c1e98b9763914155b9f0cd583fc60 \N Y 2009-01-10
20:59:36.556744 2009-01-10 20:59:36.556744 Y \N \N
16426237 9s2kd 9s2kd N http://www.amazon.com/gp/search?camp=1789&amp;creative=9325&amp;ie=UTF8&amp;index=digital-music&amp;keywords=%E0%B8%81%E0%B8%A5%E0%B9%89%E0%B8%A7%E0%B8%A2+%E0%B9%81%E0%B8%AA%E0%B8%95%E0%B8%A1%E0%B8%9B%E0%B9%8C+-+%E0%B8%A2%E0%B8%B1%E0%B8%87%E0%B8%A3%E0%B8%B1%E0%B8%81%E0%B8%81%E0%B8%B1%E0%B8%99%E0%B8%AD%E0%B8%A2%E0%B8%B9%E0%B9%88%E0%B9%84%E0%B8%AB%E0%B8%A1&amp;linkCode=ur2&amp;tag=dmp3-20 0 125.26.153.157 dfd14418cb8ad8afc5843e7873ee271dcd05289b 2009-01-10
20:56:36.271531 2009-01-10 20:59:37.163608 N \N \N
16426431 9s2pr 9s2pr N http://www.amazon.com/gp/search?camp=1789&amp;creative=9325&amp;ie=UTF8&amp;index=digital-music&amp;keywords=+-+Amplify+SD&amp;linkCode=ur2&amp;tag=dmp3-20 0 41.235.241.185 9a7f63d3cc8455d8a89cf8b707e38eef10245a66 \N Y 2009-01-10
20:59:37.498966 2009-01-10 20:59:37.498966 Y \N \N
16426432 9s2ps 9s2ps N http://www.zoliblog.com/2008/08/06/what-are-a-million-users-worth-zoho-thinks-a-lot/ 207.58.136.202 aa7bfcc1bf1b2ca19c14b262f3bd7272eed09e87 \N Y 2009-01-10
20:59:37.779863 2009-01-10 20:59:37.779863 Y \N \N
16306150 9phwm 9phwm N http://www.amazon.com/gp/search?camp=1789&amp;creative=9325&amp;ie=UTF8&amp;index=digital-music&amp;keywords=Takeharu+Ishimoto+-+Holding+My+Thoughts+In+My+Heart&amp;linkCode=ur2&amp;tag=dmp3-20 0 118.137.44.94 445e020999b8ddfaf72cb16bded949c9cab0fc8f N Y 2009-01-09
15:26:04.80344 2009-01-10 20:59:41.717183 N \N \N
16426435 9s2pv 9s2pv N http://www.amazon.com/gp/search?camp=1789&amp;creative=9325&amp;ie=UTF8&amp;index=digital-music&amp;keywords=chico+trujillo+-+como+quisiera&amp;linkCode=ur2&amp;tag=dmp3-20 0 201.215.6.104 1e1d275525cd2f5215e19db22af08f4edbf3bae5 \N Y 2009-01-10
20:59:41.844667 2009-01-10 20:59:41.844667 \N \N
16426436 9s2pw 9s2pw N http://twinkle.tapulous.com/index.php?hash=e4a3bee3941130cae759dd51659d58848644ea07 66.135.60.241 334722bd7db9c30762f9d8d0c19bccbf55e16249 \N Y 2009-01-10
20:59:42.86758 2009-01-10 20:59:42.86758 Y \N \N
16426437 9s2px 9s2px N http://www.amazon.com/gp/search?camp=1789&amp;creative=9325&amp;ie=UTF8&amp;index=digital-music&amp;keywords=David+Friedman%2FPeabo+Bryson%2FRegina+Belle+-+The+Battle&amp;linkCode=ur2&amp;tag=dmp3-20 0 124.171.4.232 37985292fd5c6a46de49bea712f780e54b0c747c \N Y 2009-01-10
20:59:43.617785 2009-01-10 20:59:43.617785 Y \N \N
16426438 9s2py 9s2py N http://www.manuscrypts.com/?p=132 0 74.220.219.59 64246d90b7e3dd259f8b315211eeb44dcf6f661c \N Y 2009-01-10
20:59:43.92993 2009-01-10 20:59:43.92993 Y \N \N
16426439 9s2pz 9s2pz N http://www.amazon.com/gp/search?camp=1789&amp;creative=9325&amp;ie=UTF8&amp;index=digital-music&amp;keywords=New+Riders+of+the+Purple+Sage+-+Panama+Red&amp;linkCode=ur2&amp;tag=dmp3-20 0 76.20.192.237 5bfee6de3bc012098df107e6967201eb7338949c \N Y 2009-01-10
20:59:44.341971 2009-01-10 20:59:44.341971 Y \N \N