From release 3.44.0 onwards, Nexus Repository 3 instances that use Postgres will require the pg_trgm (trigram) module to be installed with their Postgres instance. This requirement has been introduced to prepare for upcoming slow search performance improvements. This module is used to create an extension in the Nexus Repository 3 database that enables the creation of a more performant search index.
This module may not be installed with Postgres by default on all linux distributions. If this is the case, the upgrade will log an exception like the following:
2022-12-28 12:03:49,020-0500 INFO [FelixStartLevel] *SYSTEM org.sonatype.nexus.datastore.mybatis.MyBatisDataStore - nexus - Creating schema for SearchTableDAO 2022-12-28 12:03:49,064-0500 WARN [FelixStartLevel] *SYSTEM Sisu - Problem adding: org.eclipse.sisu.inject.LazyBeanEntry@2c00368 to: org.sonatype.nexus.datastore.mybatis.MyBatisDataStore$$EnhancerByGuice$$366511306@400e761a via: org.sonatype.nexus.datastore.internal.DataStoreManagerImpl$DataAccessMediator@4debec0a org.sonatype.nexus.datastore.api.DataAccessException: ### Error updating database. Cause: org.postgresql.util.PSQLException: ERROR: could not open extension control file "/usr/share/pgsql/extension/pg_trgm.control": No such file or directory Where: SQL statement "CREATE EXTENSION IF NOT EXISTS pg_trgm"
The consequences of not having this module available will vary depending on your particular upgrade/installation situation.
- If you are attempting a fresh installation using a new, empty Postgres database, the creation of both the search table and its index will fail, resulting in broken search functionality.
- If you are upgrading your existing installation, the search table will already exist, but the index creation will fail, resulting in no performance improvement - but search functionality will continue to operate as it did before.
In this situation, it is necessary to install the postgresql-contrib package available from your linux distribution.
sudo dnf install postgresql-contrib
Once installed, execute the following Postgres command:
create extension pg_trgm;
Note the documentation mentions having the correct privileges for the database.
This module is considered“trusted”, that is, it can be installed by non-superusers who have
CREATEprivilege on the current database.
Simply ensure the module is installed and that correct privileges are assigned to the database user, then restart your upgrade/installation.
Following successful upgrade/installation, the available extensions can be listed by the following Postgres commands/statements:
select * from pg_extension;
and should result in output like the following
List of installed extensions
Name | Version | Schema | Description
pg_trgm | 1.5 | public | text similarity measurement and index searching based on trigrams
plpgsql | 1.0 | pg_catalog | PL/pgSQL procedural language
(the first line above indicates the pg_trgm extension has been created for the current database)
Use the following commands to describe the search table and list its indexes
The final entry in the list of indexes should be
"trgm_idx_search_components_paths" gist (paths gist_trgm_ops)
which confirms that the new index creation was successful.