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