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.
  
  
  
  

Small bash script to create or reset nginx reverse proxy protected password

Create a file /etc/nginx/listofuser

say it contains username

user1
user2
user3

Then, create a hidden file /etc/nginx/.users


Create a bash script and put it at cron, say daily so once run, it will reset the proxy password to daily and email the result to users on the list with CC on admin.


##############################################################################
#!/bin/bash


for USERNAME in `cat /etc/nginx/listofusers`
do
#Random Pass generation using openssl
RANPASS=`openssl rand -base64 32 | sha256sum | base64 | head -c 8 ; echo`

#htpasswd command
/usr/bin/htpasswd -b /etc/nginx/.users $USERNAME $RANPASS

#email the results to users
echo -e "USER: $USERNAME \nPASSWORD: $RANPASS \n \nPlease login at https://myproxy.example.com \n \nNote: Please expect email notification that it has been changed at the end of the day" | mail -s "password changed at myproxy on `date`" -r noreply@example.com $USERNAME@example.com,admin@example.com

done
###############################################################################

or if you want to load it at command line only and output the user and pass and check the file  /etc/nginx/.users, see below


#################################################################
#!/bin/bash
for USERNAME in `cat /etc/nginx/listofusers`
do

#Random pass generation using openssl (found it by googling)
RANPASS=`openssl rand -base64 32 | sha256sum | base64 | head -c 8 ; echo`

#htpasswd command
/usr/bin/htpasswd -b /etc/nginx/.users $USERNAME $RANPASS

#display the output after execution
echo "USER: $USERNAME"
echo "PASSWORD: $RANPASS"

done
#################################################################

[root@localhost ~]# /usr/local/bin/test.sh
Updating password for user user1
USER: user1
PASSWORD: ZTkxMTQ0
Updating password for user user2
USER: user2
PASSWORD: ZDc4MTMz
Updating password for user user3
USER: user3
PASSWORD: NTIwOGUx

Below are encrypted password file per htpasswd default encryption (CRYPT)

[root@localhost ~]# cat /etc/nginx/.users
user1:$apr1$HJ7/Q/qG$UTO3KujLj3rkANcNpHked1
user2:$apr1$uk59ZMD4$c4zjvMXarVK9zI7nKthz60
user3:$apr1$iIOsYmbh$GdKdkMjwfi4VEHfwTnER60

Below is the portion where you will set on your nginx config under location parameter

    auth_basic "Protected";
    auth_basic_user_file /etc/nginx/.users;