Re: table corrupted

Hi

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?

Hi everybody,
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?

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

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...

2009/10/22 Joshua D. Drake <jd(at)commandprompt(dot)com>

> 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(at)gmail(dot)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...
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?

--
João Eugenio Marynowski
(41) 9624-3933

2009/10/23 Robert Haas <robertmhaas(at)gmail(dot)com>

> On Thu, Oct 22, 2009 at 10:34 PM, João Eugenio Marynowski
> <joaoem(at)gmail(dot)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(at)gmail(dot)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

João Eugenio Marynowski <joaoem(at)gmail(dot)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

2009/10/23 Robert Haas <robertmhaas(at)gmail(dot)com>

> 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
>
I ask about the index row size because I can'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 because it showing error about size row limit indices.
And, only occurs erros when you run a query involving the records damaged.
I'm trying to identify them (less of 1% of the total registers).

2009/10/23 Dimitri Fontaine <dfontaine(at)hi-media(dot)com>

> João Eugenio Marynowski <joaoem(at)gmail(dot)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
>
I thought this would solve my problems but not yet ...
the block that I found is not presenting error...
find_bad_block
----------------
(514708,16)
(1 registro)
blockId / blocks_per_chunk = 514708 / 131072 = 3,9269104
base/21058/21271.3
chunk_block_id = block_id % blocks_per_chunk = 514708 % 131072 = 121492
is attached the result of pg_filedump -if -R 121492
/var/lib/postgresql/8.2/main/base/21058/21271.3
Even then I filled the block with zero
But when I run again the find_bad_block it show one block before, ctid
514707, and then, 514706 and I stop...
Any suggestion and explication?

--
João Eugenio

2009/10/23 João Eugenio Marynowski <joaoem(at)gmail(dot)com>

> I thought this would solve my problems but not yet ...
> the block that I found is not presenting error...
> find_bad_block
> ----------------
> (514708,16)
> (1 registro)
> blockId / blocks_per_chunk = 514708 / 131072 = 3,9269104
> base/21058/21271.3
> chunk_block_id = block_id % blocks_per_chunk = 514708 % 131072 = 121492
> is attached the result of pg_filedump -if -R 121492
> /var/lib/postgresql/8.2/main/base/21058/21271.3
> Even then I filled the block with zero
> But when I run again the find_bad_block it show one block before, ctid
> 514707, and then, 514706 and I stop...
> Any suggestion and explication?
>
> --
> João Eugenio
>

I fond manually a record with error (462813,13) and zeroed it and the BD
work ok only with 18 register lost that will be recovery from old backup.
The function find_bad_block (
http://archives.postgresql.org/pgsql-general/2007-07/msg00506.php )
don't work for me, anybody know why?

Thanks for now!

--
João Eugenio Marynowski

On Thu, Oct 22, 2009 at 7:16 PM, Joshua D. Drake <jd(at)commandprompt(dot)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 H. Harris

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(at)commandprompt(dot)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

On Mon, Oct 26, 2009 at 12:55 PM, Joshua D. Drake <jd(at)commandprompt(dot)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 :-)

--
Jonah H. Harris, Senior DBA
myYearbook.com

>>>"Jonah H. Harris" <jonah(dot)harris(at)gmail(dot)com> wrote:
> Joshua D. Drake <jd(at)commandprompt(dot)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