Duplicate key in oc_storages

Hi all,

I try to migrate OwnCloud 8.0.3 to NextCloud 12.0.5 but the migration fails as soon as the database is restored on a dev machine, with the following error:

pg_restore: [archive program (db)] COPY failed for table "oc_storages": ERROR: value of a duplicate key breaks unique constraint "storages_id_index"
DETAIL: The key "(id) = (local :: / var / www / owncloud_data /)" already exists.

Indeed, a simple query on the oc_sorages database shows that there is a duplicate.

ocl=# select  * from oc_storages where id ~* 'owncloud_data';
               id               | numeric_id | available | last_checked 
 local::/var/www/owncloud_data/ |        491 |         1 |             
 local::/var/www/owncloud_data/ |        838 |         1 |             
(2 rows)

But at the same time, PostgreSQL managed to create an index for this table based on the id (storages_id_index). How is it possible that PostgreSQL accepts this duplicate in this table?

 ocl=# SELECT indexname, indexdef FROM pg_indexes WHERE tablename = 'oc_storages';
     indexname     |                                      indexdef                                       
 oc_storages_pkey  | CREATE UNIQUE INDEX oc_storages_pkey ON public.oc_storages USING btree (numeric_id)
 storages_id_index | CREATE UNIQUE INDEX storages_id_index ON public.oc_storages USING btree (id)
(2 rows)

what to do to get out of this impasse: delete one of the two values? which ?

Thanks in advance.


According to Laurenz Albe, there are usually two explanation for this, what he explains on StackOverFlow:

- Hardware problems leading to data corruption. Then remove conflicting rows manually, export the database and import it into a newly created cluster to get rid of potential lurking data corruption.

- You upgraded the C library on the operating system and the collations changed, corrupting the index. Then remove conflicting rows manually and REINDEX the indexes with string columns.

I will try to reindex, but I need firt to remove one of the two duplicate lines. which of the two?

numeric_id = 491
numeric_id = 838

i suppose 838 was the last created… but i dot not know if this id is used in another OwnCloud table.