duplicate key value violates unique constraint "pg_statistic_relid_att_inh_index" while performing full vacuum on the database
Hi,
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?.
--
Regards,
Raghavendra Rao J S V
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