Broken primary key after backup restore.

From: Michael Chau <michael(dot)chau(at)gameyourgame(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Broken primary key after backup restore.
Date: 2015-09-17 23:31:14
Message-ID: CALE++3R7_bb-21Pd4U9F=rH5EMrWXVaZp2uviZdN8EFSQE-oOw@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hi,

In Production, I have a DB2 which is replicated partially using Londiste
from DB1. I make file-system backups nightly on both DBs.

Last Monday, when I restored the backup made from DB2 to a test server,
Postgres(9.3.5) started up fine. But, I found out that the primary key of
one of the tables is broken

# select * from <mytable> order by id desc;
ERROR: could not find left sibling of block 17392 in index "mytable_pkey"

I am able to select without using the id index. On Prod DB1 , DB2 and on
another test server restored from backup made from DB1, there is no
problem, as I am able to select the table with and without index.

The table has 5 million rows. And I run Vacuum Analyze once a week.

1) To fix the above error, I tried to run vacuum full on the table and run
'reindex table <mytable>;. But it didn't help as the reindexing has taken
very very long time and not sure if it has finished or just timed out.

There is also a suggestion to recreate the primary key constraint
concurrently which I will look into later.

2) However, my main concern right now is whether there is any corruption in
the Prods table as it does look fine. Is there any way to check? And also
should we trust a file-system backup in this case?

Thanks

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Adrian Klaver 2015-09-17 23:53:05 Re: Broken primary key after backup restore.
Previous Message Florin Andrei 2015-09-17 20:04:06 Re: BDR: cannot drop database even after parting the node