Trax .NET
View as MarkdownEdit on GitHub

How-to guide

Install pg_trgm before migration 063

Source 063_log_text_search_trigram.sql, 066_metadata_failure_search.sql

063_log_text_search_trigram.sql starts with CREATE EXTENSION IF NOT EXISTS pg_trgm. Since Postgres 13 pg_trgm is a trusted extension, so a role with CREATE on the database installs it without being a superuser. When the role Trax migrates with cannot, the migration fails and the host does not start. What 063 adds is described in Schema migrations.

1. Check whether it is installed

SELECT e.extname, n.nspname AS schema
FROM pg_extension e JOIN pg_namespace n ON n.oid = e.extnamespace
WHERE e.extname = 'pg_trgm';

A row means it is installed, in whichever schema it shows, and there is nothing more to do: the migrator adds that schema to the search path of the session that runs the scripts. Go to step 3 only to build the indexes ahead of time. No row, and SELECT * FROM pg_available_extensions WHERE name = 'pg_trgm'; also returns none, means the server lacks Postgres's contrib modules; install them through your distribution or managed service first.

2. Install it as a role that can

Connect to the Trax database as a role allowed to create the extension, and run:

CREATE EXTENSION IF NOT EXISTS pg_trgm;

It goes into the first schema on that session's search path, normally public.

3. Optionally, build the indexes ahead of the upgrade

On a large trax.log the index builds are the cost of this upgrade (about 23 s and 11 s at three million short entries), and startup waits for them. The migration skips an index that already exists under its name, so the indexes can be built beforehand, on the running version, as the role Trax migrates with, in a session whose search path includes the extension's schema:

CREATE INDEX CONCURRENTLY IF NOT EXISTS ix_log_message_trgm
    ON trax.log USING gin (lower(message) gin_trgm_ops);
 
CREATE INDEX CONCURRENTLY IF NOT EXISTS ix_log_category_trgm
    ON trax.log USING gin (lower(category) gin_trgm_ops);

Migration 066_metadata_failure_search.sql uses the same extension for its trigram index on trax.metadata and needs nothing more once 063 has run. Its two indexes can be built ahead the same way, from the statements in that script.

Related