ERROR: "Checking for invalid indexes" fails PG15 pre-upgrade check in YugabyteDB Anywhere

Problem

During a YSQL major-version upgrade (PostgreSQL 11 → PostgreSQL 15), the YBA upgrade task stops in the pre-upgrade validation phase. The PGUpgradeTServerCheck runs a series of consistency checks, and the check named "Checking for invalid indexes" fails with a fatal status.

Error / alert observed:

{"name":"Checking for invalid indexes",
 "details":"In database: <database_name>
   demo.idx_demo_id (oid=23336)
   ...
   Your installation contains invalid indexes that must be fixed before upgrading.
   Invalid indexes are typically created when CREATE INDEX CONCURRENTLY fails or
   is interrupted. ...",
 "status":"fatal"}
...
"title":"Performing Consistency Checks on Old Live Server","overallStatus":"Failure, exiting"

Impact: The upgrade halts before any data is migrated. The universe stays on the old version and remains fully available, but the scheduled/automated upgrade cannot proceed until the invalid indexes are removed.

Cause

The pg_upgrade refuses to migrate a database that contains invalid indexes. An index is marked invalid (pg_index.indisvalid = false) or not-ready (indisready = false) when a CREATE INDEX CONCURRENTLY (or REINDEX CONCURRENTLY) operation fails or is interrupted for example, the session is cancelled, the node restarts, or a conflicting DDL aborts the build. The leftover index object exists in the catalog but is not usable by the planner.

The PG15 upgrade precheck (Performing Consistency Checks on Old Live Server) reports these so they are cleaned up first, because migrating a half-built index would produce an inconsistent catalog on the new version. This is the same guard PostgreSQL's own pg_upgrade applies.

Note: Invalid indexes take no part in query execution the planner ignores them. Dropping an invalid index does not remove any working index; a valid index of the same logical definition (if one exists) is unaffected.

Diagnosis

Run the following before applying any fix to get the authoritative list of invalid indexes, per database. The upgrade log also writes the same list to a file. For example in logs following line appears:

A list of invalid indexes and DROP commands is printed above and in the file: /mnt/d0/pg_data/pg_upgrade_output.d/20260613T000138.993/invalid_indexes.txt","status":"fatal"}

Step 1: Connect to each database.

Connect to every user database. Do not connect only to the database named in the error message.

Run this query in each database:

-- Run in each database (\c <database_name> first)
SELECT i.indexrelid::regclass AS invalid_index
FROM pg_index i
JOIN pg_class c ON c.oid = i.indrelid
WHERE (NOT i.indisvalid AND c.relkind != 'p')
   OR NOT i.indisready;

Step 2: Check the output.

This output shows the problem. The data in this example is redacted.

                  invalid_index
--------------------------------------------------
 <schema>.<index_name_1>
 <schema>.<index_name_2>
 <schema>.<index_name_3>
 <schema>.<index_name_4>
(4 rows)

Step 3: Check each index in the list.

For each index, decide if you need a valid replacement after you drop it. Check if the query pattern still needs that index.

Get the index definition first. Use this query:

-- Inspect the definition so you can recreate it if it is needed
SELECT indexdef FROM pg_indexes
WHERE schemaname = '<schema>' AND indexname = '<index_name>';

Resolution

Note: Dropping an invalid index is safe, it is not used by queries. This is not a data-loss operation (no table rows are affected). Recreating a valid index afterward is optional and depends on whether the workload needs it.

1. Drop each invalid index. Connect to the reported database and drop the indexes by their fully-qualified names from the diagnosis output.

   \c feeds-lt
   DROP INDEX metrics.idx_integrator_reports_ea_product_id;
   DROP INDEX metrics.idx_integrator_reports_service_report_id;
   DROP INDEX metrics.idx_report_usages_integrator_report_id;
   DROP INDEX metrics.idx_service_reports_status;

   Reason: Removing the half-built index objects clears the catalog state that the pre-check blocks on.

    

2. Recreate any index that the workload still needs using CONCURRENTLY so the rebuild does not lock the table for writes. 
   
   Concurrent index creation

   Index creation in YugabyteDB can happen CONCURRENTLY or NONCONCURRENTLY. The default mode is CONCURRENTLY, wherever possible (see CONCURRENTLY for restrictions).

   Reason: The original failure left no usable index if that index served real queries, rebuild it. Skip this step for indexes that are obsolete.

   CREATE INDEX CONCURRENTLY idx_service_reports_status
     ON metrics.service_reports (status);

   Value rationale: Use CONCURRENTLY for a live database so application writes are not blocked during the build. Do not run it inside the upgrade window, build and confirm it is valid first.

    

3. Confirm no invalid indexes remain in any database (re-run the diagnosis query per database). The precheck scans every database, so one leftover invalid index in an unrelated database will still block the upgrade.

CDC note: Dropping and recreating the invalid indexes above does not affect CDC (Debezium / CDCSDK logical replication) streams or PITR schedules. Invalid indexes are already skipped by CDC and backup/snapshot processing, so this step is safe to run with CDC or PITR active. The CDC-specific warning below applies only if the upgrade previously rolled back and you also need to run the sys-catalog retention maintenance in Permanent Fix.

Verification

Re-run the diagnosis query in every user database:

SELECT i.indexrelid::regclass
FROM pg_index i
JOIN pg_class c ON c.oid = i.indrelid
WHERE (NOT i.indisvalid AND c.relkind != 'p')
   OR NOT i.indisready;

Expected output (healthy state):

 indexrelid
------------
(0 rows)

Then re-run the YBA upgrade. The "Checking for invalid indexes" check should now return ok.

Permanent Fix

The resolution above clears the current blocker. To prevent recurrence:

• Investigate why the original CREATE INDEX CONCURRENTLY failed (cancelled session, node restart, conflicting DDL). Interrupted concurrent index builds are the usual source.
• After any failed/cancelled CREATE INDEX CONCURRENTLY, immediately drop the leftover invalid index rather than leaving it — it will silently accumulate and only surface at the next upgrade.
• Add an invalid-index check to pre-upgrade readiness run-books so it is caught during planning, not during the upgrade window.

If the upgrade previously failed and was rolled back, also run the sys-catalog flush/compact maintenance before retrying, see the related KB below. A rollback can leave stale PG15 catalog rows that fail the next attempt on unrelated checks.

YSQL Major Upgrade failure: Mandatory procedure to follow before attempting a failed upgrade again