Re: Help - corruption issue? - Mailing list pgsql-general

> On Tuesday, April 26, 2011, Tomas Vondra <tv@fuzzy.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&creative=9325&ie=UTF8&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&creative=9325&ie=UTF8&index=digital-music&keywords=Cannibal+Corpse+-+Split+Wide+Open&linkCode=ur2&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&creative=9325&ie=UTF8&index=digital-music&keywords=Juana+Fe+-+la+murga+final&linkCode=ur2&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&creative=9325&ie=UTF8&index=digital-music&keywords=Boyz+II+Men+-+Ill+Make+Love+To+You&linkCode=ur2&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&creative=9325&ie=UTF8&index=digital-music&keywords=The+Killers+-+All+The+Pretty+Faces&linkCode=ur2&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&creative=9325&ie=UTF8&index=digital-music&keywords=Freddy+Quinn+-+Junge%2C+Komm+Bald+Wieder&linkCode=ur2&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&creative=9325&ie=UTF8&index=digital-music&keywords=Closure+In+Moscow+-+Ofelia...+Ofelia&linkCode=ur2&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&creative=9325&ie=UTF8&index=digital-music&keywords=Ministry+of+Sound+-+Freestylers+%2F+Push+Up&linkCode=ur2&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&creative=9325&ie=UTF8&index=digital-music&keywords=Chico+Trujillo+-+Cabildo&linkCode=ur2&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&creative=9325&ie=UTF8&index=digital-music&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&linkCode=ur2&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&creative=9325&ie=UTF8&index=digital-music&keywords=+-+Amplify+SD&linkCode=ur2&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&creative=9325&ie=UTF8&index=digital-music&keywords=Takeharu+Ishimoto+-+Holding+My+Thoughts+In+My+Heart&linkCode=ur2&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&creative=9325&ie=UTF8&index=digital-music&keywords=chico+trujillo+-+como+quisiera&linkCode=ur2&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&creative=9325&ie=UTF8&index=digital-music&keywords=David+Friedman%2FPeabo+Bryson%2FRegina+Belle+-+The+Battle&linkCode=ur2&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&creative=9325&ie=UTF8&index=digital-music&keywords=New+Riders+of+the+Purple+Sage+-+Panama+Red&linkCode=ur2&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