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

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

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

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