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 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)
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.