upgrade 14/stable from r429 to r553 fails with PostgreSQLEnableDisableExtensionError

[vmpjdc]

Steps to reproduce

1. deploy postgresql r429 from 14/stable in [email protected] machine model
2. relate an application to it and use it (e.g. netbox)
3. upgrade postgresql to r553 from 14/stable

Expected behavior

Upgrade succeeds.

Actual behavior

Upgrade fails.

stg-netbox@is-bastion-ps6:~$ juju status postgresql
Model       Controller                      Cloud/Region           Version  SLA          Timestamp
stg-netbox  juju-controller-34-staging-ps6  prodstack6/prodstack6  3.4.5    unsupported  02:07:40Z

App         Version  Status  Scale  Charm       Channel    Rev  Exposed  Message
postgresql  14.11    active      3  postgresql  14/stable  553  no       

Unit            Workload  Agent  Machine  Public address  Ports     Message
postgresql/9    waiting   idle   34       10.142.102.31   5432/tcp  awaiting for member to start
postgresql/10   waiting   idle   32       10.142.102.240  5432/tcp  awaiting for member to start
postgresql/11*  error     idle   33       10.142.102.89   5432/tcp  hook failed: "database-relation-changed"

Machine  State    Address         Inst id                               Base          AZ                   Message
32       started  10.142.102.240  244ca796-afb2-4f0d-864a-0204e17c17f7  [email protected]  availability-zone-3  ACTIVE
33       started  10.142.102.89   2696446e-eb3b-4fa2-9e59-d9b453a30b85  [email protected]  availability-zone-3  ACTIVE
34       started  10.142.102.31   b05d1ccb-fd1e-45ee-adea-56c0956a6011  [email protected]  availability-zone-1  ACTIVE

Offer       Application  Charm       Rev  Connected  Endpoint  Interface          Role
postgresql  postgresql   postgresql  553  1/1        database  postgresql_client  provider

Versions

Operating system: Ubuntu 22.04.4 LTS

Juju CLI: 3.4.6-ubuntu-amd64

Juju agent: 3.4.5

Charm revision: 553

LXD: n/a

Log output

Juju debug log: see snippet below

unit-postgresql-11: 2025-03-21 01:26:36 INFO juju.worker.uniter unit "postgresql/11" started
unit-postgresql-11: 2025-03-21 01:26:36 INFO juju.worker.uniter hooks are retried true
unit-postgresql-11: 2025-03-21 01:26:37 INFO juju.worker.uniter.charm downloading ch:amd64/jammy/postgresql-553 from API server
unit-postgresql-11: 2025-03-21 01:27:39 INFO juju.worker.uniter found queued "upgrade-charm" hook
unit-postgresql-11: 2025-03-21 01:27:40 ERROR unit.postgresql/11.juju-log Cluster upgrade failed, ensure pre-upgrade checks are ran first.
unit-postgresql-11: 2025-03-21 01:27:41 INFO juju.worker.uniter.operation ran "upgrade-charm" hook (via hook dispatching script: dispatch)
unit-postgresql-11: 2025-03-21 01:27:41 INFO juju.worker.uniter found queued "config-changed" hook
unit-postgresql-11: 2025-03-21 01:27:56 INFO unit.postgresql/11.juju-log PostgreSQL restart required
unit-postgresql-11: 2025-03-21 01:27:57 ERROR unit.postgresql/11.juju-log failed to change plugins: 
Traceback (most recent call last):
  File "/var/lib/juju/agents/unit-postgresql-11/charm/lib/charms/postgresql_k8s/v0/postgresql.py", line 354, in enable_disable_extensions
    cursor.execute(
psycopg2.errors.UndefinedFile: could not open extension control file "/snap/charmed-postgresql/115/usr/share/postgresql/14/extension/pgaudit.control": No such file or directory


The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "/var/lib/juju/agents/unit-postgresql-11/charm/src/charm.py", line 1071, in enable_disable_extensions
    self.postgresql.enable_disable_extensions(extensions, database)
  File "/var/lib/juju/agents/unit-postgresql-11/charm/lib/charms/tempo_coordinator_k8s/v0/charm_tracing.py", line 1064, in wrapped_function
    return callable(*args, **kwargs)  # type: ignore
  File "/var/lib/juju/agents/unit-postgresql-11/charm/lib/charms/postgresql_k8s/v0/postgresql.py", line 365, in enable_disable_extensions
    raise PostgreSQLEnableDisableExtensionError() from e
charms.postgresql_k8s.v0.postgresql.PostgreSQLEnableDisableExtensionError
unit-postgresql-11: 2025-03-21 01:27:58 INFO juju.worker.uniter.operation ran "config-changed" hook (via hook dispatching script: dispatch)
unit-postgresql-11: 2025-03-21 01:28:00 INFO unit.postgresql/11.juju-log PostgreSQL restart required
unit-postgresql-11: 2025-03-21 01:28:02 INFO juju.worker.uniter.operation ran "secret-changed" hook (via hook dispatching script: dispatch)
unit-postgresql-11: 2025-03-21 01:28:05 INFO unit.postgresql/11.juju-log PostgreSQL restart required
unit-postgresql-11: 2025-03-21 01:28:06 INFO juju.worker.uniter.operation ran "secret-changed" hook (via hook dispatching script: dispatch)
unit-postgresql-11: 2025-03-21 01:28:08 INFO juju.worker.uniter.operation ran "secret-remove" hook (via hook dispatching script: dispatch)
unit-postgresql-11: 2025-03-21 01:28:09 INFO juju.worker.uniter.operation ran "restart-relation-changed" hook (via hook dispatching script: dispatch)

Additional context

Noticed the snap is downrev 2 point releases, and held:

ubuntu@juju-bd78f7-stg-netbox-33:~$ snap info charmed-postgresql | tail -n 10
channels:
  14/stable:    14.13 2024-10-09 (133) 186MB -
  14/candidate: ^                            
  14/beta:      ^                            
  14/edge:      14.17 2025-03-11 (155) 186MB -
  16/stable:    --                           
  16/candidate: --                           
  16/beta:      --                           
  16/edge:      16.8  2025-03-18 (161) 197MB -
installed:      14.11            (115) 185MB held
ubuntu@juju-bd78f7-stg-netbox-33:~$ _

[syncronize-issues-to-jira]

Thank you for reporting your feedback to us!

The internal ticket has been created: https://warthogs.atlassian.net/browse/DPE-6856.

This message was autogenerated

[dragomirp]

Hi, the expected snap for rev. 553 is rev. 143. Was the pre-upgrade-check ran before the upgrade? That step is necessary for the refresh to work as intended.

[vmpjdc]

It was, or so I thought, but I just noticed two units are in waiting with message awaiting for member to start so perhaps something went wrong there. I'll address that problem first and try again. Thanks!

[dragomirp]

Hi, Issues are probably related. The other members are likely also unable to start due to the missing plugin.

[marceloneppel]

Hi, @vmpjdc!

To restore the units, you should refresh the snap to the right revision and start the snap services again in each unit.

You can do the following commands in each unit (do them first in the primary, then in the replicas):

# List which services are active.
sudo snap services charmed-postgresql

# Refresh the snap to the right revision.
sudo snap refresh charmed-postgresql --revision 143

# Start the patroni and the other previously active services again.
sudo snap start charmed-postgresql.patroni

To retrieve the primary unit (to run the above commands on it before running on replicas), you can check the output from the following command in one of the units. The latest number in the end will be the unit which is the primary.

sudo -H -u snap_daemon charmed-postgresql.patronictl -c /var/snap/charmed-postgresql/current/etc/patroni/patroni.yaml history postgresql