Re: Primary key Index Error

On 10/24/2011 06:38 PM, Merlin Moncure wrote:
> On Mon, Oct 24, 2011 at 3:22 AM, Manoj K P<manoj(at)comodo(dot)com> 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*