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@xxxxxxxxxxx -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
