Обсуждение: table corrupted

Hi<br /><br />Can someone help me how to repair the problem below, I'm using Postgres 8.2.5:<br />- after appeared the
errosbelow in selects, vacuum and dump in one table:<br />2009-10-16 16:07:06 BRT 192.168.0.87 ERROR:  could not access
statusof transaction 29024764<br /> 2009-10-16 16:07:06 BRT 192.168.0.87 DETAIL:  Could not open file "pg_clog/001B":
Nosuch file or directory.<br /> 2009-10-16 16:07:06 BRT 192.168.0.87 STATEMENT:  select ...<br />2009-10-16 16:11:47
BRT192.168.0.29 ERROR:  invalid page header in block 462821 of relation "..."<br />2009-10-16 16:11:47 BRT 192.168.0.29
STATEMENT: select ....<br /> I created the file pg_clog/001B with 256kB of /dev/zero <br />That resolve the problem
withvacuum but began other error in selects and dump to the same table ended all connections and stay up after showing
theerror:<br /> 2009-10-19 13:50:03 BRT  LOG:  server process (PID 1544) was terminated by signal 11<br />2009-10-19
13:50:03BRT  LOG:  terminating any other active server processes<br />2009-10-19 13:50:03 BRT 192.168.0.253 WARNING: 
terminatingconnection because of crash of another server process<br /> 2009-10-19 13:50:03 BRT 192.168.0.253 DETAIL: 
Thepostmaster has commanded this server process to roll back the current transaction and exit, because another server
processexited abnormally and possibly corrupted shared memory.<br /> 2009-10-19 13:50:03 BRT 192.168.0.253 HINT:  In a
momentyou should be able to reconnect to the database and repeat your command.<br />Was habilited the zero_damage_pages
optionthen executed selects, vacuums, and dumps but not changed...<br />Was identified 2 register that if refered cause
error.<br/><div class="gmail_quote">The BD was restored in backup server with 8.2.7 and executed vacuums ok but select
andreindex crashed...<br /></div><div class="gmail_quote"><font color="#888888"><font color="#000000">Instaled 8.3.8
versionand used pg_dump but error</font></font></div><div class="gmail_quote">And then the select below show the
problemwhere the codentrega from where clause differ from select answer:<br /></div><font color="#888888">LOGIST=#
selectcodentrega from entregas where codentrega='9879622';<br /> codentrega<br />------------<br /> z879622<br />(1
registro)<br/></font><br />Any idea?<br /> 

On Thu, 2009-10-22 at 14:28 -0200, João Eugenio Marynowski wrote:
> Hi
>
Repair? Not likely. Get past? Maybe.

set zero_damaged_pages to on;
vacuum verbose;

I would do a hardware check too.

Joshua D. Drake


> Can someone help me how to repair the problem below, I'm using
> Postgres 8.2.5:
> - after appeared the erros below in selects, vacuum and dump in one
> table:
> 2009-10-16 16:07:06 BRT 192.168.0.87 ERROR:  could not access status
> of transaction 29024764
> 2009-10-16 16:07:06 BRT 192.168.0.87 DETAIL:  Could not open file
> "pg_clog/001B": No such file or directory.
> 2009-10-16 16:07:06 BRT 192.168.0.87 STATEMENT:  select ...
> 2009-10-16 16:11:47 BRT 192.168.0.29 ERROR:  invalid page header in
> block 462821 of relation "..."
> 2009-10-16 16:11:47 BRT 192.168.0.29 STATEMENT:  select ....
> I created the file pg_clog/001B with 256kB of /dev/zero
> That resolve the problem with vacuum but began other error in selects
> and dump to the same table ended all connections and stay up after
> showing the error:
> 2009-10-19 13:50:03 BRT  LOG:  server process (PID 1544) was
> terminated by signal 11
> 2009-10-19 13:50:03 BRT  LOG:  terminating any other active server
> processes
> 2009-10-19 13:50:03 BRT 192.168.0.253 WARNING:  terminating connection
> because of crash of another server process
> 2009-10-19 13:50:03 BRT 192.168.0.253 DETAIL:  The postmaster has
> commanded this server process to roll back the current transaction and
> exit, because another server process exited abnormally and possibly
> corrupted shared memory.
> 2009-10-19 13:50:03 BRT 192.168.0.253 HINT:  In a moment you should be
> able to reconnect to the database and repeat your command.
> Was habilited the zero_damage_pages option then executed selects,
> vacuums, and dumps but not changed...
> Was identified 2 register that if refered cause error.
> The BD was restored in backup server with 8.2.7 and executed vacuums
> ok but select and reindex crashed...
>
> Instaled 8.3.8 version and used pg_dump but error
> And then the select below show the problem where the codentrega from
> where clause differ from select answer:
>
> LOGIST=# select codentrega from entregas where codentrega='9879622';
>  codentrega
> ------------
>  z879622
> (1 registro)
>
> Any idea?


--
PostgreSQL.org Major Contributor
Command Prompt, Inc: http://www.commandprompt.com/ - 503.667.4564
Consulting, Training, Support, Custom Development, Engineering
If the world pushes look it in the eye and GRR. Then push back harder. - Salamander

On Thu, Oct 22, 2009 at 10:34 PM, João Eugenio Marynowski
<joaoem@gmail.com> wrote:
> The hardware is ok because we have migrated to other machine and the errors
> is the same
> zero_damaged_pages is on and the vaccum verbose don't show any error...

You need to dump (or otherwise extract) your data and load it into a
new cluster.  The one you have sounds like it's in bad shape.

...Robert

I don't be able to select the data from table with pg_dump or select, both occur error...<br />how i can identified the
registerof a table independent the contends of the register, only control of the postgres?<br />I eliminate the index
pkbut now I don't be able to create begin because show the error:<br /> ERROR:  index row requires 21776744 bytes,
maximumsize is 8191<br />How increase the index row?<br /><br clear="all" />--<br />João Eugenio Marynowski<br />(41)
9624-3933<br/><br /><br /><div class="gmail_quote">2009/10/23 Robert Haas <span dir="ltr"><<a
href="mailto:robertmhaas@gmail.com">robertmhaas@gmail.com</a>></span><br/><blockquote class="gmail_quote"
style="border-left:1px solid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;"><div class="im">On Thu,
Oct22, 2009 at 10:34 PM, João Eugenio Marynowski<br /> <<a href="mailto:joaoem@gmail.com">joaoem@gmail.com</a>>
wrote:<br/> > The hardware is ok because we have migrated to other machine and the errors<br /> > is the same<br
/>> zero_damaged_pages is on and the vaccum verbose don't show any error...<br /><br /></div>You need to dump (or
otherwiseextract) your data and load it into a<br /> new cluster.  The one you have sounds like it's in bad shape.<br
/><fontcolor="#888888"><br /> ...Robert<br /></font></blockquote></div><br /> 

On Thu, Oct 22, 2009 at 11:00 PM, João Eugenio Marynowski
<joaoem@gmail.com> wrote:
> I don't be able to select the data from table with pg_dump or select, both
> occur error...
> how i can identified the register of a table independent the contends of the
> register, only control of the postgres?
> I eliminate the index pk but now I don't be able to create begin because
> show the error:
> ERROR:  index row requires 21776744 bytes, maximum size is 8191
> How increase the index row?

Well, that's not really the problem.  Your data is corrupted -
increasing the index row size is not going to fix it.

I'm not really knowledgeable enough about the guts of the database to
know whether there are lower-level tools that could be used to rescue
your data.  I wonder if you'd have any luck selecting data a few rows
at a time (LIMIT 100, say, without ORDER BY).  That might at least
enable you to get some of the data out of there, if there are some
pages that are undamaged.  But I'm grasping at straws here.

...Robert

On Thu, 2009-10-22 at 14:28 -0200, João Eugenio Marynowski wrote:
> Hi
> 
Repair? Not likely. Get past? Maybe.

set zero_damaged_pages to on;
vacuum verbose;

I would do a hardware check too.

Joshua D. Drake


> Can someone help me how to repair the problem below, I'm using
> Postgres 8.2.5:
> - after appeared the erros below in selects, vacuum and dump in one
> table:
> 2009-10-16 16:07:06 BRT 192.168.0.87 ERROR:  could not access status
> of transaction 29024764
> 2009-10-16 16:07:06 BRT 192.168.0.87 DETAIL:  Could not open file
> "pg_clog/001B": No such file or directory.
> 2009-10-16 16:07:06 BRT 192.168.0.87 STATEMENT:  select ...
> 2009-10-16 16:11:47 BRT 192.168.0.29 ERROR:  invalid page header in
> block 462821 of relation "..."
> 2009-10-16 16:11:47 BRT 192.168.0.29 STATEMENT:  select ....
> I created the file pg_clog/001B with 256kB of /dev/zero 
> That resolve the problem with vacuum but began other error in selects
> and dump to the same table ended all connections and stay up after
> showing the error:
> 2009-10-19 13:50:03 BRT  LOG:  server process (PID 1544) was
> terminated by signal 11
> 2009-10-19 13:50:03 BRT  LOG:  terminating any other active server
> processes
> 2009-10-19 13:50:03 BRT 192.168.0.253 WARNING:  terminating connection
> because of crash of another server process
> 2009-10-19 13:50:03 BRT 192.168.0.253 DETAIL:  The postmaster has
> commanded this server process to roll back the current transaction and
> exit, because another server process exited abnormally and possibly
> corrupted shared memory.
> 2009-10-19 13:50:03 BRT 192.168.0.253 HINT:  In a moment you should be
> able to reconnect to the database and repeat your command.
> Was habilited the zero_damage_pages option then executed selects,
> vacuums, and dumps but not changed...
> Was identified 2 register that if refered cause error.
> The BD was restored in backup server with 8.2.7 and executed vacuums
> ok but select and reindex crashed...
> 
> Instaled 8.3.8 version and used pg_dump but error
> And then the select below show the problem where the codentrega from
> where clause differ from select answer:
> 
> LOGIST=# select codentrega from entregas where codentrega='9879622';
>  codentrega
> ------------
>  z879622
> (1 registro)
> 
> Any idea?


-- 
PostgreSQL.org Major Contributor
Command Prompt, Inc: http://www.commandprompt.com/ - 503.667.4564
Consulting, Training, Support, Custom Development, Engineering
If the world pushes look it in the eye and GRR. Then push back harder. - Salamander

João Eugenio Marynowski <joaoem@gmail.com> writes:
> 2009-10-16 16:11:47 BRT 192.168.0.29 ERROR:  invalid page header in
> block 462821 of relation "..."

It could be that the following will prove helpful: http://archives.postgresql.org/pgsql-general/2007-07/msg00506.php
http://pgsql.tapoueh.org/site/html/news/20080410.badblock.html

Regards,
--
dim

<div class="gmail_quote">2009/10/23 Robert Haas <span dir="ltr"><<a
href="mailto:robertmhaas@gmail.com">robertmhaas@gmail.com</a>></span><br/><blockquote class="gmail_quote"
style="border-left:1px solid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;"><div
class="im"></div>Well,that's not really the problem.  Your data is corrupted -<br /> increasing the index row size is
notgoing to fix it.<br /><br /> I'm not really knowledgeable enough about the guts of the database to<br /> know
whetherthere are lower-level tools that could be used to rescue<br /> your data.  I wonder if you'd have any luck
selectingdata a few rows<br /> at a time (LIMIT 100, say, without ORDER BY).  That might at least<br /> enable you to
getsome of the data out of there, if there are some<br /> pages that are undamaged.  But I'm grasping at straws
here.<br/><font color="#888888"><br /> ...Robert<br /></font></blockquote></div>I ask about the index row size because
Ican't re-index the database and I've a server for tests and in this I removed the pk and can't recreate the index
becauseit showing error about size row limit indices.<br />And, only occurs erros when you run a query involving the
recordsdamaged. I'm trying to identify them (less of 1% of the total registers).<br /><br /><br /> 

<br /><br /><div class="gmail_quote">2009/10/23 João Eugenio Marynowski <span dir="ltr"><<a
href="mailto:joaoem@gmail.com"target="_blank">joaoem@gmail.com</a>></span><br /><blockquote class="gmail_quote"
style="border-left:1px solid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;"><div
class="gmail_quote"></div>Ithought this would solve my problems but not yet ...<br />the block that I found is not
presentingerror...<br /> find_bad_block <br />----------------<br /> (514708,16)<br />(1 registro)<br /> blockId /
blocks_per_chunk= 514708 / 131072 = 3,9269104<br /> base/21058/21271.3 <br />chunk_block_id = block_id %
blocks_per_chunk= 514708 % 131072 = 121492<br />is attached the result of pg_filedump -if -R 121492
/var/lib/postgresql/8.2/main/base/21058/21271.3<br/>Even then I filled the block with zero<br /> But when I run again
thefind_bad_block it show one block before, ctid 514707, and then, 514706 and I stop...<br />Any suggestion and
explication?<br/><br clear="all" />--<br /><font color="#888888">João Eugenio<br /></font></blockquote></div><br />I
fondmanually a record with error (462813,13) and zeroed it and the BD work ok only with 18 register lost that will be
recoveryfrom old backup.<br />The function find_bad_block ( <a
href="http://archives.postgresql.org/pgsql-general/2007-07/msg00506.php">http://archives.postgresql.org/pgsql-general/2007-07/msg00506.php</a>
)<br/> don't work for me, anybody know why?<br /><br />Thanks for now!<br /><br clear="all" />--<br />João Eugenio
Marynowski<br/><br /><br /> 

On Mon, 2009-10-26 at 09:14 -0400, Jonah H. Harris wrote:
> On Thu, Oct 22, 2009 at 7:16 PM, Joshua D. Drake
> <jd@commandprompt.com> wrote:
>         On Thu, 2009-10-22 at 14:28 -0200, João Eugenio Marynowski
>         wrote:
>         > Hi
>         >
>         Repair? Not likely. Get past? Maybe.
>
> I don't know how valuable your data is, but I've performed data
> recovery on tens of PG databases suffering from both hardware and
> software corruption on versions 7.0 through 8.3.  My rate is $300-600
> USD/hour depending on the database/table size and the extent of the
> corruption.
>
> If you're just trying to save what's not corrupted, there's quite a
> few examples online.

Jonah,

This reply is wholly inappropriate for a Pg list. We are here to help
people. If you have a consultancy, please feel free to list that but any
discussion of rates is just plain rude. Please use better discretion in
the future.

Joshua D. Drake



--
PostgreSQL.org Major Contributor
Command Prompt, Inc: http://www.commandprompt.com/ - 503.667.4564
Consulting, Training, Support, Custom Development, Engineering
If the world pushes look it in the eye and GRR. Then push back harder. - Salamander

>>>"Jonah H. Harris" <jonah.harris@gmail.com> wrote:
> Joshua D. Drake <jd@commandprompt.com>wrote:
> 
>> This reply is wholly inappropriate for a Pg list. We are here to
>> help people. If you have a consultancy, please feel free to list
>> that but any discussion of rates is just plain rude. Please use
>> better discretion in the future.
>>
> 
> Perhaps.  Though, I only posted because you made it sound somewhat
> impossible and because I only know of a few ppl in the PG community
> that offer it and/or have done is successfully.  Maybe letting
> people know there are options, other than being screwed, is wrong...
> my bad :-)
That really sounded disingenuous.
It would probably be appropriate to point out that there are numerous
sources of professional support for PostgreSQL.
http://www.postgresql.org/support/professional_support
As someone who ran a consulting business for 25 years and has been on
both sides of the calls for assistance on recovery from database
problems (and currently not in that business, so I have no ax to grind
here), that post was inappropriate for the list.  I don't think anyone
would fault you (or anyone else) for making a polite offer of
assistance off-list, as long as you quietly bow out if they're not
interested; but anybody who is a professional in this business should
know better than to post that to the list.  There are several reasons,
and they should be obvious.
-Kevin

On Mon, 2009-10-26 at 09:14 -0400, Jonah H. Harris wrote:
> On Thu, Oct 22, 2009 at 7:16 PM, Joshua D. Drake
> <jd@commandprompt.com> wrote:
>         On Thu, 2009-10-22 at 14:28 -0200, João Eugenio Marynowski
>         wrote:
>         > Hi
>         >
>         Repair? Not likely. Get past? Maybe.
> 
> I don't know how valuable your data is, but I've performed data
> recovery on tens of PG databases suffering from both hardware and
> software corruption on versions 7.0 through 8.3.  My rate is $300-600
> USD/hour depending on the database/table size and the extent of the
> corruption.
> 
> If you're just trying to save what's not corrupted, there's quite a
> few examples online.

Jonah,

This reply is wholly inappropriate for a Pg list. We are here to help
people. If you have a consultancy, please feel free to list that but any
discussion of rates is just plain rude. Please use better discretion in
the future.

Joshua D. Drake



-- 
PostgreSQL.org Major Contributor
Command Prompt, Inc: http://www.commandprompt.com/ - 503.667.4564
Consulting, Training, Support, Custom Development, Engineering
If the world pushes look it in the eye and GRR. Then push back harder. - Salamander