The charm is not handling requirement for pg_trgm extension

[mastier]

The neither charm beta (rev2) or the current built from source is not working

It requires postgresql-k8s 16/ (no stable branch only beta and edge)

edge postgresql is not starting cluster, I tried adding extra unit but doest not help

$ juju status
Model                Controller           Cloud/Region  Version  SLA          Timestamp
ubuntu-security-api  localhost-localhost  ck8s/default  3.6.13   unsupported  14:59:19+01:00

App                  Version  Status   Scale  Charm                Channel  Rev  Address         Exposed  Message
postgresql-k8s       16.11    waiting      3  postgresql-k8s       16/edge  748  10.152.183.239  no       installing agent
ubuntu-security-api           blocked      1  ubuntu-security-api             0  10.152.183.55   no       missing integrations: postgresql

Unit                    Workload  Agent      Address     Ports  Message
postgresql-k8s/0*       waiting   executing  10.1.0.54          awaiting for cluster to start
postgresql-k8s/1        unknown   idle       10.1.0.3           
postgresql-k8s/2        unknown   idle       10.1.0.11          
ubuntu-security-api/0*  blocked   idle       10.1.0.186         missing integrations: postgresql

when using postgresql-k8s 16 beta charm is failing

$ juju status --relations
Model                Controller           Cloud/Region  Version  SLA          Timestamp
ubuntu-security-api  localhost-localhost  ck8s/default  3.6.13   unsupported  15:30:58+01:00

App                  Version  Status   Scale  Charm                Channel  Rev  Address         Exposed  Message
postgresql-k8s       16.8     active       1  postgresql-k8s       16/beta  525  10.152.183.71   no       
ubuntu-security-api           blocked      1  ubuntu-security-api             0  10.152.183.150  no       database migration command ['bash', '-eo', 'pipefail', 'migrate.sh'] failed, will retry in next update-status

Unit                    Workload  Agent  Address     Ports  Message
postgresql-k8s/0*       active    idle   10.1.0.169         Primary
ubuntu-security-api/0*  blocked   idle   10.1.0.22          database migration command ['bash', '-eo', 'pipefail', 'migrate.sh'] failed, will retry in next update-status

Integration provider                Requirer                            Interface          Type     Message
postgresql-k8s:database             ubuntu-security-api:postgresql      postgresql_client  regular  
postgresql-k8s:database-peers       postgresql-k8s:database-peers       postgresql_peers   peer     
postgresql-k8s:restart              postgresql-k8s:restart              rolling_op         peer     
postgresql-k8s:upgrade              postgresql-k8s:upgrade              upgrade            peer     
ubuntu-security-api:secret-storage  ubuntu-security-api:secret-storage  secret-storage     peer   

The debug-log attached

ubuntu-security-api_debug-log.log

[mastier]

Ok, so the issue seems to be the database missing an pg_trgm extension

So I went to the database and added the extensions
First got operator password from juju secrets

$ juju secrets  
$ juju show-secret d67j3u70tl6s49mqrolg --reveal |grep operator
    operator-password: ZpObawIuwuF2503X

The got to the database

kubectl exec -it -n ubuntu-security-api postgresql-k8s-0 -c postgresql -- bash


root@postgresql-k8s-0:/# psql -h 127.0.0.1 -U operator postgres
Password for user operator: 
psql (16.8 (Ubuntu 16.8-0ubuntu0.24.04.1))
Type "help" for help.

postgres=# \l
                                                           List of databases
        Name         |  Owner   | Encoding | Locale Provider | Collate |  Ctype  | ICU Locale | ICU Rules |     Access privileges      
---------------------+----------+----------+-----------------+---------+---------+------------+-----------+----------------------------
 postgres            | operator | UTF8     | libc            | C       | C.UTF-8 |            |           | operator=CTc/operator     +
                     |          |          |                 |         |         |            |           | backup=CTc/operator       +
                     |          |          |                 |         |         |            |           | replication=CTc/operator  +
                     |          |          |                 |         |         |            |           | rewind=CTc/operator       +
                     |          |          |                 |         |         |            |           | monitoring=CTc/operator   +
                     |          |          |                 |         |         |            |           | admin=c/operator
 template0           | operator | UTF8     | libc            | C       | C.UTF-8 |            |           | =c/operator               +
                     |          |          |                 |         |         |            |           | operator=CTc/operator
 template1           | operator | UTF8     | libc            | C       | C.UTF-8 |            |           | =c/operator               +
                     |          |          |                 |         |         |            |           | operator=CTc/operator
 ubuntu-security-api | operator | UTF8     | libc            | C       | C.UTF-8 |            |           | operator=CTc/operator     +
                     |          |          |                 |         |         |            |           | relation_id_4=CTc/operator+
                     |          |          |                 |         |         |            |           | admin=CTc/operator        +
                     |          |          |                 |         |         |            |           | backup=CTc/operator       +
                     |          |          |                 |         |         |            |           | replication=CTc/operator  +
                     |          |          |                 |         |         |            |           | rewind=CTc/operator       +
                     |          |          |                 |         |         |            |           | monitoring=CTc/operator
(4 rows)

postgres=# \c ubuntu-security-api
You are now connected to database "ubuntu-security-api" as user "operator".
ubuntu-security-api=# SELECT * 
FROM pg_extension;
  oid  | extname | extowner | extnamespace | extrelocatable | extversion | extconfig | extcondition 
-------+---------+----------+--------------+----------------+------------+-----------+--------------
 14266 | plpgsql |       10 |           11 | f              | 1.0        |           | 
 16403 | pgaudit |       10 |         2200 | t              | 16.0       |           | 
(2 rows)

ubuntu-security-api=# create extension pg_trgm with schema pg_catalog;
CREATE EXTENSION

After that the migration worked

unit-ubuntu-security-api-0: 16:06:24 INFO unit.ubuntu-security-api/0.juju-log database migration command ['bash', '-eo', 'pipefail', 'migrate.sh'] completed, stdout: , stderr:  * Tip: There are .env or .flaskenv files present. Do "pip install python-dotenv" to use them.
/lib/python3.10/site-packages/talisker/util.py:29: UserWarning: pkg_resources is deprecated as an API. See https://setuptools.pypa.io/en/latest/pkg_resources.html. The pkg_resources package is slated for removal as early as 2025-11-30. Refrain from using this package or pin to Setuptools<81.
  import pkg_resources
INFO  [alembic.runtime.migration] Context impl PostgresqlImpl.
INFO  [alembic.runtime.migration] Will assume transactional DDL.
INFO  [alembic.runtime.migration] Running upgrade  -> e8760725610a, firstmigration
INFO  [alembic.runtime.migration] Running upgrade e8760725610a -> 566647193540, “release_update”
INFO  [alembic.runtime.migration] Running upgrade 566647193540 -> c2bc5f0b027d, Add release support expiry dates
INFO  [alembic.runtime.migration] Running upgrade c2bc5f0b027d -> fc282ca1dc4d, cve_migration
INFO  [alembic.runtime.migration] Running upgrade fc282ca1dc4d -> 8e9386e7a94b, add component column to status table
INFO  [alembic.runtime.migration] Running upgrade 8e9386e7a94b -> 765ed540939b, insert-releases
INFO  [alembic.runtime.migration] Running upgrade 765ed540939b -> 10c24cb270ff, add_cve_patch_column
INFO  [alembic.runtime.migration] Running upgrade 10c24cb270ff -> 570b8ff188c4, update_cve_component_field
INFO  [alembic.runtime.migration] Running upgrade 570b8ff188c4 -> 88f1398da6c1, add-cve-pocket-column
INFO  [alembic.runtime.migration] Running upgrade 88f1398da6c1 -> 0fe2f3a871da, update-release-12-04
INFO  [alembic.runtime.migration] Running upgrade 0fe2f3a871da -> 3e0a3f15abb9, add-cve-mitigation-column
INFO  [alembic.runtime.migration] Running upgrade 3e0a3f15abb9 -> 8008e46e6ea8, add_is_hidden_column_for_notices
INFO  [alembic.runtime.migration] Running upgrade 8008e46e6ea8 -> 5c1128073317, add_impact
INFO  [alembic.runtime.migration] Running upgrade 5c1128073317 -> 1e0a9c001320, empty message
INFO  [alembic.runtime.migration] Running upgrade 1e0a9c001320 -> 0320de7481ac, empty message
INFO  [alembic.runtime.migration] Running upgrade 0320de7481ac -> d1b71925b9e3, empty message
INFO  [alembic.runtime.migration] Running upgrade d1b71925b9e3 -> f18a0bd8a809, empty message
INFO  [alembic.runtime.migration] Running upgrade f18a0bd8a809 -> c27fec1bfee2, empty message
INFO  [alembic.runtime.migration] Running upgrade c27fec1bfee2 -> 5df43dc932dd, empty message
INFO  [alembic.runtime.migration] Running upgrade 5df43dc932dd -> 645c9424286e, empty message
INFO  [alembic.runtime.migration] Running upgrade 645c9424286e -> 5ad36b3ca4ba, add indexes for CVE search
INFO  [alembic.runtime.migration] Running upgrade 5ad36b3ca4ba -> fc4b8f31d182, Add indexes for notice search optimization

[mastier]

I see that postgresql-k8s charm can enable that extension

The charm would need enable that config plugin-pg-trgm-enableoption or be put in blocked state with message asking to configure postgresal to enable that extension