Re: Primary key Index Error

On Mon, Oct 24, 2011 at 8:32 AM, manoj <manoj(at)comodo(dot)com> wrote:
> 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

do you happen to still have the database logs on the backup from
startup to end of recovery? anything interesting in there?

merlin