While recovering from a cloud failure, I found that some tables on a PostgreSQL
database are behaving strangely. These tables are indexed using a primary key, but a pg_dump
yielded duplicate fields, failing a pg_restore
on a backup server.
I have tried to REINDEX
:
REINDEX INDEX rank_details_pkey;
ERROR: could not create unique index "rank_details_pkey"
DETAIL: Table contains duplicated values.
The index is defined as:
<table info here>
Indexes:
"rank_details_pkey" PRIMARY KEY, btree (user_id)
And, oddly,
SELECT user_id, COUNT(*) FROM <table name> GROUP BY 1 HAVING COUNT(*) > 1;
user_id | count
---------+-------
(0 rows)
To conclude - I have duplicate values in my table which can not be found or cleared.
Any ideas how to fix this? This is a production server, so all fixes should be done without affecting service.