Troubleshooting Tip: FortiSIEM upgrade fails at migrate-database stage due to duplicate entries

Description

This article describes how to troubleshoot and resolve a database schema upgrade failure that may occur during a FortiSIEM upgrade from versions 7.1.x to 7.2.x.

During the upgrade, the process may fail at the migrate-database stage with an error similar to the following:

[1;31m[11:54:41] migrate-database : DATABASE | Upgrade to new schema version script ...| localhost | FAILED | 32.54s[0m
<truncated>
ERROR: could not create unique index "ph_cust_property_device_property_cons"
DETAIL: Key (obj_id, property_def_id)=(1685056, 4212744350) is duplicated.
CONTEXT: SQL statement "ALTER TABLE ph_cust_property
ADD CONSTRAINT ph_cust_property_device_property_cons
UNIQUE (obj_id, property_def_id)"

This issue occurs when duplicate records exist in the ph_cust_property table, preventing the upgrade script from creating the required unique constraint.

Scope

 FortiSIEM v7.x+.

Solution

1. Access the Phoenix database.
   Log in to the FortiSIEM Supervisor as root and connect to the PostgreSQL database:
   
   

   psql -U phoenix -d phoenixdb

   
   

2. Check for duplicate entries.
   Run the following query to identify duplicate entries in the ph_cust_property table:
   
   

   SELECT obj_id, property_def_id, COUNT(*) AS duplicate_count FROM ph_cust_property GROUP BY obj_id, property_def_id HAVING COUNT(*) > 1 ORDER BY duplicate_count DESC;

   
   If the query returns any rows, duplicate records are present and must be removed before the upgrade can continue.
   
   

3. Remove duplicate records.
   Execute the following query to remove duplicate entries while retaining a single copy of each record:
   
   

   DELETE FROM ph_cust_property a USING ph_cust_property b WHERE a.ctid < b.ctid AND a.obj_id = b.obj_id AND a.property_def_id = b.property_def_id;

   
   

4. Verify cleanup.
   Re-run the duplicate check query from Step 2 and confirm that no rows are returned.
   
   

5. Re-run the upgrade.
   After all duplicate records have been removed, restart the FortiSIEM upgrade process.

The database schema upgrade should now complete successfully, allowing the upgrade to proceed beyond the migrate-database stage.