Resolving Index Corruption and Primary Key Duplication in PostgreSQL

Resolving Index Corruption and Primary Key Duplication in PostgreSQL

Overview

This KB provides guidance for identifying and resolving Primary Key (PK) duplication errors that typically occur due to data corruption or constraint issues.

Step 1: Verify the Error from Logs

     Corruption errors may surface in different scenarios. Check the appropriate log file based on the failure:
Info
      a. Upgrade failure → Review updatemgrlogXX.txt for corruption errors.
      b. Backup failure → Review /pgsql/data/pg_log for corruption errors.
      c. Daily pgbackup failure → Review logs/sps/pgsql/dailypgbackupX.txt for corruption errors.

Look for errors similar to the one below, where the index name is mentioned (e.g., convertodesc_pk):

Warning
ERROR: duplicate key value violates unique constraint "<table_name>_pk"

(or)

ERROR: could not create unique index "<table_name>_pk"

Step 2: Pre-Resolution Action

If the above PK duplication / index corruption error is confirmed:

  1. Stop the application server.

  2. Take a snapshot of the PostgreSQL folder (pgsql) before attempting any corruption resolution.

    • This ensures you have a rollback point in case the cleanup process causes unexpected issues.

Alert

If the error is different from the above PK duplication issue → do not proceed with cleanup. Instead:

  • Collect the complete pgsql folder in debugging mode.

  • Move the ticket to G3-Sunil Team for detailed corruption analysis and resolution.

Step 3: Resolve Primary Key Duplication

Notes
 The query must be executed during downtime to resolve corruption for the tables listed below. If corruption is detected in any other table, collect the complete pgsql folder and move the ticket to the G3-Sunil Team.

Table : arc_notification, notificationtodesc, arc_workorderhistorydiff, workorderhistorydiff, workordertodescription, requestnotificationtodesc, workorderstates, workorder, conversationdescription, requestresolution, workordernotes, adsadsyncobjects, adsadsyncobjattributes, spaceparent, dcparameters, UserDepartment

Before running the cleanup query, first identify the table name and primary key (PK) column(s) associated with the index mentioned in the error.
Run the following query (replace <index_name> with the index name from the error message):
Idea
SELECT i.relname AS index_name, t.relname AS table_name, string_agg(a.attname, ', ') AS pk_columns FROM pg_class t JOIN pg_index ix ON t.oid = ix.indrelid JOIN pg_class i ON i.oid = ix.indexrelid JOIN pg_attribute a ON a.attrelid = t.oid AND a.attnum = ANY(ix.indkey) WHERE i.relname = '<index_name>' GROUP BY i.relname, t.relname;

Once the table name and PK column(s) are identified, use the template below to remove duplicate rows.

Execute this query inside a transaction during downtime:
Idea
BEGIN;
SET LOCAL enable_indexscan = off;
SET LOCAL enable_bitmapscan = off;
SET LOCAL enable_indexonlyscan = off;
DELETE FROM <table_name> a USING <table_name> b WHERE a.ctid < b.ctid AND a.<pk_column1> = b.<pk_column1> [AND a.<pk_column2> = b.<pk_column2> ...];
COMMIT;
Info
What to Replace:
<table_name> → Replace with the table name from the error message.
Example: requestnotificationtodesc or adsadsyncobjattributes.
<pk_column1>, <pk_column2>, … → Replace with the primary key column(s) of that table.

For single-column PK tables → use just that column.
For composite PK tables → include all PK columns with AND conditions.

Example 1: Single Primary Key Column
Warning
Error:

ERROR: duplicate key value violates unique constraint "requestnotificationtodesc_pk"

PK column = notificationid

DELETE FROM requestnotificationtodesc a USING requestnotificationtodesc b WHERE a.ctid < b.ctid AND a.notificationid = b.notificationid;


Example 2: Composite Primary Key
Warning

Error:


ERROR: duplicate key value violates unique constraint "adsadsyncobjattributes_pk"

PK columns = object_id, attrib_ldap_name

DELETE FROM adsadsyncobjattributes a USING adsadsyncobjattributes b WHERE a.ctid < b.ctid AND a.object_id = b.object_id AND a.attrib_ldap_name = b.attrib_ldap_name;

Alert

If an error message appears while executing the cleanup query, rollback the transaction immediately to prevent partial changes.

  • Check whether the error is due to a syntax mistake. If so, correct the query and re-execute it.

  • If the query still fails for reasons other than syntax, collect the complete pgsql folder and move the ticket to the G3-Sunil Team.


BEGIN;
-- run the duplicate cleanup query here

-- if error message occurs during execution
ROLLBACK;

Step 4: Post-Resolution Validation

Run the following maintenance queries on the database: (Note : Don't run below query within transaction)



IdeaREINDEX DATABASE SERVICEDESK;
VACUUM ANALYZE;
UPDATE GLOBALCONFIG SET PARAMVALUE = 'false' WHERE CATEGORY = 'BACKUPSCHEDULE' AND PARAMETER = 'IS_DB_CORRUPTED';

Confirm that above commands executed successfully.

  • If errors occur and the error mention index corruption, re-check the inconsistent table as per Steps 3–4, delete duplicate records, and re-run REINDEX and VACUUM.

  • If any other type of error occurs, collect the complete pgsql folder in debugging mode and move the ticket to G3-Sunil Team for corruption analysis and resolution.

Step 5: Final Validation Backup

After completing the cleanup and validation, verify that the database can be backed up successfully using either of the following methods. If the command completes without errors, the corruption has been resolved successfully. If the dump fails, collect the complete pgsql folder and move the ticket to G3-Sunil Team.

  Option 1: pg_dump Command

  1. Run the following command from the pgsql/bin/ directory to validate the backup.

    On Linux/Unix

    ./pg_dump -d servicedesk -U sdpadmin -h 127.0.0.1 -p <port_number> > /dev/null

    On Windows (Command Prompt)

    pg_dump -d servicedesk -U sdpadmin -h 127.0.0.1 -p <port_number> > nul

    • Replace <port_number> with the actual PostgreSQL port in your environment

Option 2: Manual Backup

  1. If preferred, you can run the manual backup script instead. Note that this method usually takes longer than pg_dump.

    • Execute the backup script located in the bin/ directory:

      • On Windows: backUpData.bat

      • On Linux/Unix: backUpData.sh

Notes

Note

  1. If the PostgreSQL version is 15+, please make sure to mention G3-sunil team in the ticket stating that corruption was encountered in this version, since we are actively monitoring these cases.

  2. The provided queries are safe cleanup queries that retain one valid row while removing duplicate

  3. Always execute the fix in a transaction block (BEGIN … COMMIT) to avoid accidental data loss.

  4. Ensure you are running the query on the correct table as indicated in the error message.


For more information, please refer to KB: PostgreSQL Corruption Guidelines while handling Customer Tickets.

      New to M365 Manager Plus?
      New to M365 Manager Plus?
        New to RecoveryManager Plus?
        New to RecoveryManager Plus?
          New to Exchange Reporter Plus?
          New to Exchange Reporter Plus?
            New to SharePoint Manager Plus?
            New to SharePoint Manager Plus?
                See all integrations
                New to ADManager Plus?
                See all integrations
                New to ADManager Plus?

                  New to ADSelfService Plus?

                  Start your free trial
                  Start your free trial

                    • Related Articles

                    • 8121 Index fix jar (contact module corruption)

                      This is compatible with 8121 At times , the contact search is not working and that leads to mail fetching issues also. This fix might work in certain instances. Please check it. However the permanent solution is available from 11.0 version. It is ...

                    • Troubleshooting PostgreSQL Upgrade & Startup Issues

                      When troubleshooting PostgreSQL issues, we are currently identifying the generic trace, but we missed identifying the absolute cause of that issue and following the workaround, provided. Most of those issues have their own workaround in our KBs with ...

                    • SQL Master Key Password FAQs

                      How to create the master key password. The user with dbcreator permissions can follow these steps to create the master key password: Open MSSQL Server Management Studio, right-click the database and choose New Query In the workspace, run the ...

                    • Steps to fix DocumentIndexing or Search is not working properly or startout.log file is growing too much issue

                      This KB is applicable only for 8121 Builds We came across several cases, where the search is not working properly due to the index file corruption or Indexing thread gets locked in 8.1 series builds. This also leads Mail fetching issue.For which we ...

                    • Login | ERROR: Wrong key or corrupt data in AAAPASSWORD table

                      Wrong key or corrupt data issue: User is facing issues in local authentication or failing in AD user import or receiving an internal error while editing or deleting the user profile from UI. First, go to the logs and check if the below error traces ...

                      Related Products