Re: Primary key Index Error

[manoj <manoj@xxxxxxxxxx>]

On 10/24/2011 06:38 PM, Merlin Moncure wrote:

On Mon, Oct 24, 2011 at 3:22 AM, Manoj K P <manoj@xxxxxxxxxx> wrote:

Postgres recovered from data folder , after that some queries shows error


select * from table2 order by app_id  ; - its work ( 50000000 data)
select * from table2 order by app_id   desc ; - its work

Here app_id contains  binary index


select * from table2 order by id  ; - its work ( 50000000 data)
select * from table2 order by id   desc ; - shows following error

manoj[local] postgres ERROR:  index "tbl2_id_pkey" contains unexpected zero
page at block 311121
manoj [local] postgres HINT:  Please REINDEX it.

index "tbl2_id_pkey" is primary key

Reindex not pratical this table (250 GB data)

How i can solve this ?

It looks like you have data corruption.  Your first priority should be
to do the minimum necessary to get a usable database dump and then to
get back online.  I would consider your database off line until this
is done. Essentially, the only practical ways to repair the error you
see would be to reindex or restore from backup.  Did you have any
unexpected power events?  Server crashes?

merlin

  Actually this is backup server ,no power failure & Server crashes happens  in between , The data folder is base backup(pg_start_backup) on the server. pg_dump not practical in this case  because of data size.
  After recovering from base backup run  WAL recovery file successfully and its updates current date data. 
  When i am running query on big table    (more than 5 Million  data)  shows error other wise its work fine.

  select id , name  from table2 order by id  ; It uses the index 

  select id , name  from  table2 order by id desc ; - shows the error 

Error happens only using (DESC clause against primary key) , all other case its work fine

 REINDEX will take ages

--
Manoj K P
Postgres DBA
Comodo India

<<attachment: smime.p7s>>