Add Database Diagnostics monitoring page

What does this MR do and why?

Add Database Diagnostic monitoring page

Adds a new admin page for database diagnostics. It will show collation mismatches and any corrupted indexes related to collation.

References

• Related to #555916 (closed)

Screenshots or screen recordings

• No any mismatch of corrupted index

  

• Collation Mismatch

  

• Index Corruption

  

How to set up and validate locally

1. As an admin, visit http://gdk.test:3000/admin/database_diagnostics
2. By default, there shouldn't be any mismatches or corrupted indexes

Testing Collation Mismatch Detection

To test the collation mismatch detection functionality:

1. Backup Current State

-- Record the current state for later restoration
SELECT collname, collprovider, collversion 
FROM pg_collation 
WHERE collname = 'en_US.UTF-8' AND collprovider = 'c';

2. Create a Collation Mismatch

UPDATE pg_catalog.pg_collation SET collversion = '2.28' WHERE collname = 'en_US.UTF-8' AND collprovider = 'c';

3. Verify the Mismatch

SELECT
  collname AS collation_name,
  collprovider AS provider,
  collversion AS stored_version,
  pg_collation_actual_version(oid) AS actual_version
FROM
  pg_collation
WHERE
  collprovider IN ('c', 'd')
  AND (collversion IS DISTINCT FROM pg_collation_actual_version(oid));

4. Run the Collation Checker

• Visit http://gdk.test:3000/admin/database_diagnostics
• Run the collation check
• View results

5. Revert the Change

-- Restore the original state (assuming it was NULL)
UPDATE pg_catalog.pg_collation 
SET collversion = NULL 
WHERE collname = 'en_US.UTF-8' AND collprovider = 'c';

6. Verify the Change

• Visit http://gdk.test:3000/admin/database_diagnostics
• Run the collation check
• View results

Creating and Repairing Index Corruption

To test the index corruption detection and repair functionality:

1. Create index corruption

-- Drop existing indexes
DROP INDEX index_merge_request_diff_commit_users_on_name_and_email;
DROP INDEX index_merge_request_diff_commit_users_on_org_id_name_email;

-- Insert duplicates
INSERT INTO merge_request_diff_commit_users (name, email, organization_id)
VALUES ('Test Corruption User', 'test.corruption@example.com', 1);
INSERT INTO merge_request_diff_commit_users (name, email, organization_id)
VALUES ('Test Corruption User', 'test.corruption@example.com', 1);

-- Verify duplicates exist
SELECT name, email, COUNT(*)
FROM merge_request_diff_commit_users
WHERE name = 'Test Corruption User' AND email = 'test.corruption@example.com'
GROUP BY name, email;

-- Recreate indexes CONCURRENTLY (creates corruption)
CREATE UNIQUE INDEX CONCURRENTLY index_merge_request_diff_commit_users_on_name_and_email
ON merge_request_diff_commit_users (name, email);
CREATE UNIQUE INDEX CONCURRENTLY index_merge_request_diff_commit_users_on_org_id_name_email
ON merge_request_diff_commit_users (organization_id, name, email);

2. Verify corruption detection

• Visit http://gdk.test:3000/admin/database_diagnostics
• Run the collation check
• Verify corrupted indexes are shown with duplicate entries

3. Test repair functionality

# In Rails console
Gitlab::Database::RepairIndex.run(database_name: 'main')

4. Verify repair success

• Visit http://gdk.test:3000/admin/database_diagnostics

• Run the collation check again in admin page to refresh

• Confirm no corrupted indexes are shown

• Verify in database that indexes are valid:

  \d merge_request_diff_commit_users

5. Clean up

DELETE FROM merge_request_diff_commit_users 
WHERE name = 'Test Corruption User' AND email = 'test.corruption@example.com';

MR acceptance checklist

Evaluate this MR against the MR acceptance checklist. It helps you analyze changes to reduce risks in quality, performance, reliability, security, and maintainability.