This topic tells you how to troubleshoot the Metadata Store database index issue after upgrading from v1.5 to a later version of Supply Chain Security Tools (SCST) - Store.
The index corruption happened because of a base OS upgrade in the PostgreSQL database images to address some of the CVE which includes a breaking change in glibc locale
library.
Postgres-bionic-13:1.22.0
uses ubuntu:18.04
, which includes ubuntu/glibc 2.27-3ubuntu1.6
.Postgres-bionic-13:1.23.0
uses ubuntu:22.04
and jammy-20221101
, which includes ubuntu/glibc 2.35-0ubuntu3.1
.glibc v2.28
includes a major update for the locale data, which might corrupt indices after upgrading. For more information, see the PostgreSQL wiki.
The following sections tell you how to detect and solve a database index corruption issue reported in Metadata Store App Container logs.
The Metadata Store cannot reconcile because the Metadata Store pod reports a potential database index corruption issue.
kubectl logs metadata-store-app-pod_name -n metadata-store
{“level”:“error”,“ts”:“2023-08-15T16:38:31.528115988Z”,“logger”:“MetadataStore”,“msg”:“unable to check index corruption since user is not a superuser to perform \“CREATE EXTENSION amcheck\“. Please create this extension and check for index corruption using following sql command \“SELECT bt_index_check(oid) FROM pg_class WHERE relname in (SELECT indexrelid::regclass::text FROM (SELECT indexrelid, indrelid, indcollation[i] coll FROM pg_index, generate_subscripts(indcollation, 1) g(i)) s JOIN pg_collation c ON coll=c.oid WHERE collprovider IN (‘d’, ‘c’) AND collname NOT IN (‘C’, ‘POSIX’));\“”,“hostname”:“metadata-store-app-77c9fb59c8-qplxt”}
{“level”:“error”,“ts”:“2023-08-15T16:38:31.528139637Z”,“logger”:“MetadataStore”,“msg”:“Found corrupted database indexes but unable to fix them”,“hostname”:“metadata-store-app-77c9fb59c8-qplxt”,“error”:“unable to check index corruption since user is not a superuser to perform \“CREATE EXTENSION amcheck\“. Please create this extension and check for index corruption using following sql command \“SELECT bt_index_check(oid) FROM pg_class WHERE relname in (SELECT indexrelid::regclass::text FROM (SELECT indexrelid, indrelid, indcollation[i] coll FROM pg_index, generate_subscripts(indcollation, 1) g(i)) s JOIN pg_collation c ON coll=c.oid WHERE collprovider IN (‘d’, ‘c’) AND collname NOT IN (‘C’, ‘POSIX’));\“”}
You can solve this issue by upgrading Tanzu Application Platform with updated schema values to connect to the PostgreSQL database directly and then fix the index manually.
The rest of this topic describes alternative solutions:
auto_correct_db_indexes
property to be true
in tap-values.yaml
To edit the auto_correct_db_indexes
property:
Change the property auto_correct_db_indexes
value to true
in tap-values.yaml
.
Update Tanzu Application Platform by running:
tanzu package installed update tap -p tap.tanzu.vmware.com -v {{ vars.tap_version }} \
--values-file tap-values.yaml -n tap-install
Examine the Metadata Store API logs and verify that there are no errors.
To connect to the Metadata Store database with the same account that the Metadata Store API uses:
Run this SQL to re-index individual tables:
REINDEX TABLE "artifact_groups";
REINDEX TABLE "artifact_group_images";
REINDEX TABLE "images";
REINDEX TABLE "sources";
REINDEX TABLE "source_images";
REINDEX TABLE "packages";
REINDEX TABLE "image_packages";
REINDEX TABLE "vulnerabilities";
REINDEX TABLE "package_vulnerabilities";
REINDEX TABLE "source_packages";
REINDEX TABLE "artifact_group_sources";
REINDEX TABLE "method_types";
REINDEX TABLE "ratings";
REINDEX TABLE "package_managers";
REINDEX TABLE "artifact_group_labels";
REINDEX TABLE "analysis_instances";
REINDEX TABLE "vulnerability_analyses";
REINDEX TABLE "reports";
REINDEX TABLE "report_ratings";
REINDEX TABLE "report_packages_vulnerabilities";
REINDEX TABLE "index_migration_statuses";
Run the following SQL:
INSERT INTO index_migration_statuses (created_at, updated_at, "version", status) VALUES(now(), \
now(), version(), true)
Examine the Metadata Store API logs and verify that there are no errors.
To connect to the Metadata Store database with a superuser account and manually fix the index:
Install the extension on your PostgreSQL database.
CREATE EXTENSION amcheck
Run the following SQL:
SELECT indexrelid::regclass::text FROM (SELECT indexrelid, indrelid, indcollation[i] coll \
FROM pg_index, generate_subscripts(indcollation, 1) g(i)) s JOIN pg_collation c ON coll=c.oid
WHERE collprovider IN ('d', 'c') AND collname NOT IN ('C', 'POSIX');
Run the following SQL when you see an error:
SELECT indexrelid::regclass::text FROM (SELECT indexrelid, indrelid, indcollation[i] coll \
FROM pg_index, generate_subscripts(indcollation, 1) g(i)) s JOIN pg_collation c ON coll=c.oid
WHERE collprovider IN ('d', 'c') AND collname NOT IN ('C', 'POSIX');
Run the following SQL if the previous step caused an error:
REINDEX database "metadata-store"
Run the following SQL:
INSERT INTO index_migration_statuses (created_at, updated_at, "version", status) VALUES(now(), \
now(), version(), true)
Examine the Metadata Store API logs and verify that there are no errors.