Thursday, April 27, 2017

could not create unique index "pg_statistic_relid_att_inh_index" on postgresql 9.3 - FIXED

I have a newly restored database via symantec backup but got a problem.

Scenario:

  • Postgresql version: 9.3 - Centos
  • Able to access the database, via command line and pgadmin4 only, pgadmin 3 does not work.
  • When creating any table, I cannot create, it will not proceed.
  • When doing command reindex system DBNAME, i am stuck with the error below.

    DBNAME=# reindex system DBNAME;
    NOTICE: table "pg_catalog.pg_class" was reindexed
    ERROR: could not create unique index "pg_statistic_relid_att_inh_index"
    DETAIL: Key (starelid, staattnum, stainherit)=(2610, 15, f) is duplicated.

    LOG:
    Apr 26 14:50:44 stationname postgres[5452]: [10-2] 2017-04-26 14:50:44 PHT postgres DBNAME 127.0.0.1 DETAIL: Key (starelid, staattnum, stainherit)=(2610, 15, f) is duplicated.


    Got a tip from one blog I found to check if there were really a duplicate by sql command below: (link of blog: http://www.pateldenish.com/2016/09/recovering-corrupted-postgres-database.html)

    select starelid, staattnum, stainherit from pg_catalog.pg_statistic where starelid=2610 order by 2;


    DBNAME=# select starelid, staattnum, stainherit from pg_catalog.pg_statistic where starelid=2610 order by 2;
    starelid | staattnum | stainherit
    ----------+-----------+------------
    2610 | 1 | f
    2610 | 2 | f
    2610 | 3 | f
    2610 | 4 | f
    2610 | 5 | f
    2610 | 6 | f
    2610 | 7 | f
    2610 | 8 | f
    2610 | 9 | f
    2610 | 10 | f
    2610 | 11 | f
    2610 | 12 | f
    2610 | 13 | f
    2610 | 14 | f
    2610 | 15 | f
    2610 | 16 | f
    2610 | 17 | f
    2610 | 18 | f
    (18 rows)


    but there wee no duplicates found.

    So since I dont have a choice because I cannot seem to create any additional table, then I may need to restore another one from backup, and since I still have a running backup, so before doing another restore, I tried deleting the reported duplicate by command below:

    DBNAME=# delete from pg_catalog.pg_statistic where starelid=2610 and staattnum=15;
    DELETE 1

    DBNAME=# reindex database DBNAME;
    NOTICE: table "pg_catalog.pg_class" was reindexed
    ERROR: could not create unique index "pg_statistic_relid_att_inh_index"
    DETAIL: Key (starelid, staattnum, stainherit)=(2610, 13, f) is duplicated.

    DBNAME=# delete from pg_catalog.pg_statistic where starelid=2610 and staattnum=13;
    DELETE 1


    After the last deletion of duplicate, I able to reindex system and reindex database and seems those indexes were recreated and my issue on creating any table was fixed.



Posted by at