Re: duplicate key value violates unique constraint"pg_statistic_relid_att_inh_index" while performing full vacuum on thedatabase

Raghavendra Rao J S V wrote:
> While performing vacuum full, I have  received the below highlighted error. Please guide me how to resolve this
issue.
> 
> 
> /opt/postgres/9.2/bin/psql -p 5433 --username=cmuser  cpcm -c "VACUUM FULL ANALYZE;"
> 
> 
> ERROR:  duplicate key value violates unique constraint "pg_statistic_relid_att_inh_index"
> DETAIL:  Key (starelid, staattnum, stainherit)=(18915, 6, f) already exists.
> 
> ' pg_statistic ' is a meta data table. Is it ok if I remove one duplicated record from ' pg_statistic' table?. 

That is data corruption.

Do you have any idea how you got there?
Any crashes, any unsafe settings? Unreliable hardware?

Fortunately it is only the pg_statistic table.

You can stop the server, start it with

   pg_ctl start -o -O

Then connect as superuser and run

   TRUNCATE pg_statistic;
   ANALYZE;

That should take care of the problem.

It would be a good idea to pg_dumpall the cluster, remove it,
recreate it and load the dump so that you are sure to have
no data corruption.

Yours,
Laurenz Albe