Error CREATE UNIQUE INDEX when migrate to Cloud SQL for PostgreSQL using Data Migration Service

[Vivikas]

I'm using DMS and got this error in a brand-new destination database.

I restarted the replication twice and got the same error.

Could someone give me some thoughts or advice on how to fix this issue?

pg_restore: error: could not execute query: ERROR: could not create unique index "public_components_component_package_type_idx" DETAIL: Key (component, package_type)=(block-diag-ilu, pypi) is duplicated. CONTEXT: parallel worker Command was: CREATE UNIQUE INDEX public_components_component_package_type_idx ON public.public_components USING btree (component, package_type);

[ms4446]

It appears that the error you are getting is from a duplicate entry in the public_components table, which is conflicting with the unique index public_components_component_package_type_idx.

Here's how to try to resolve this error:

1. Identify and Resolve Duplicates in the Source Database

First, let's find any duplicates in your source database with the following SQL query:

SELECT component, package_type, COUNT(*)
FROM public_components
GROUP BY component, package_type
HAVING COUNT(*) > 1;

Please use this code with caution. After identifying the duplicates, you will need to either delete these duplicates or merge them into unique records, depending on what makes sense for your data and business rules.

2. Clean Up the Destination Database

Next, clean up the destination database by removing the problematic index and duplicates:

• Drop the existing index:

DROP INDEX IF EXISTS public_components_component_package_type_idx;

• Remove duplicate rows:

DELETE FROM public_components
WHERE ctid NOT IN (
    SELECT min(ctid) FROM public_components GROUP BY component, package_type
);

• (Optional) Recreate the unique index:

CREATE UNIQUE INDEX public_components_component_package_type_idx
ON public.public_components USING btree (component, package_type);

3. Restart the Migration (Optional)

If you are confident that both the source and destination databases are now clean, consider restarting the DMS replication process.

Additional Considerations

• Continuous Replication: If your source database is actively changing, ensure that any new changes are handled during the cleanup process to prevent reintroducing duplicates.
• DMS Configuration: Double-check your DMS settings, particularly any custom mappings or transformations that might affect the component and package_type columns. Ensure that data types and index definitions are consistent between the source and destination schemas.