On Mon, Oct 24, 2011 at 8:32 AM, manoj <ma...@comodo.com> wrote: > On 10/24/2011 06:38 PM, Merlin Moncure wrote: > > On Mon, Oct 24, 2011 at 3:22 AM, Manoj K P <ma...@comodo.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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general