[YSQL] YSQL Major Version Upgrade fails for indexes created on very old versions

Solution:

This issue is caused by the absence of index information in the indexes proto field for base tables created before this feature was introduced. During a YSQL major version upgrade, the pg_upgrade process checks for the existence of each index and creates a new one if it doesn't exist. However, when the index already exists in the old version, the master node detects it and skips creation. When the IsCreateTableDone polling checks the base table's indexes field, it finds it empty and never returns "done," causing the create table request to time out and fail the entire upgrade.

Workaround:

To work around this issue, you can create a script to manually populate the indexes proto field for all base tables before performing a YSQL major version upgrade. This can be done using the YSQL protocol or the YSQL CLI. Here's an example using the YSQL CLI:

#!/bin/bash

# Replace <your_database> with the name of your database
# Replace <your_user> with the name of a superuser account
# Replace <your_password> with the password for the superuser account

yqlsh -d <your_database> -U <your_user> -p <your_password> << EOF

-- Fetch a list of all tables in the database
CREATE TEMP TABLE tables_list AS
SELECT table_schema, table_name
FROM information_schema.tables
WHERE table_schema = 'public';

-- Iterate through each table and update the indexes proto field
DO $
DECLARE
  table_name text;
  table_schema text;
BEGIN
  FOR table_record IN SELECT table_schema, table_name FROM tables_list LOOP
    EXECUTE 'ALTER TABLE ' || table_schema || '.' || table_name || ' REFRESH PROTO';
  END LOOP;
END $;

EOF

This script uses the REFRESH PROTO command to update the indexes proto field for all tables in the database. Run this script on the source cluster before performing a YSQL major version upgrade to ensure all base tables have the correct indexes proto field.

Alternative Solution:

Another solution is to perform a YSQL minor version upgrade instead of a major version upgrade. This will avoid the issue of checking for indexes in the indexes proto field, as minor version upgrades do not change the schema of existing tables. Once you have reached the desired minor version, you can then perform major version upgrades at your convenience.

Additional Information:

For more information on YSQL index backfill support and the pg_upgrade process, you can refer to the YugabyteDB documentation.