Re: Broken primary key after backup restore.

On 09/17/2015 04:31 PM, Michael Chau wrote:
> Hi,
>
> In Production, I have a DB2 which is replicated partially using Londiste
> from DB1.

Well I think the above needs more explanation to help understand how the
DB2 backup got into this state and possibly prevent it in the future.


I make file-system backups nightly on both DBs.

How is that done exactly?

>
> 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.

Did you restore to the DB2 derived test server in the same way as you
did the other servers?

>
> 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
>
>
>
>
>
>


--
Adrian Klaver
adrian.klaver@aklaver.com