Charmed PostgreSQL How-To - Configure the PostgreSQL database

How to configure the PostgreSQL database

Warning: The feature is currently available in the channel 14/edge only (revision 338+) and will be released to the channel 14/stable soon.

To configure the PostgreSQL database you need to:

To configure a new value for a PostgreSQL parameter you can simply change the value of a config option:

juju config postgresql `<parameter_group_in_lowercase>_<postgresql_parameter_in_lowercase>`=<value>

As an example:

juju config postgresql response_lc_monetary=en_GB.utf8

Note: the parameter groups for each PostgreSQL parameter are used to better organise them and are listed at Supported database configurations/parameters.

After changing the value of one or more of the parameters, the PostgreSQL charm will apply the new configuration to the database.

If some parameters need a database restart (i.e. shared_buffers), the charm will automatically restart the database to apply the new configuration.

In that situation, you can check the restart operation status of each unit like in the following example (from juju status):

Model  Controller  Cloud/Region        Version  SLA          Timestamp
dev    lxd         lxd/localhost       3.1.6    unsupported  19:44:02Z

App             Version  Status   Scale  Charm           Channel  Rev  Address         Exposed  Message
postgresql      14.9     waiting      3  postgresql                 0  10.152.183.142  no       installing agent

Unit               Workload     Agent      Address      Ports  Message
postgresql/0       maintenance  executing  10.1.123.45         Executing restart operation
postgresql/1*      active       executing  10.1.123.46         
postgresql/2       waiting      executing  10.1.123.48         Awaiting restart operation

If any parameter is configured with an invalid value, the charm will get into an error state,

Also, in the Juju debug logs you can see why the configuration change failed:

1 validation error for CharmConfig durability_synchronous_commit
  Value not one of 'on', 'remote_apply' or 'remote_write'

To resolve that, call one of the following commands:

juju config postgresql --reset <config_option_name> # reset to the default value of the config option
juju config postgresql <config_option_name>=<valid_value> # set a valid value for the config option

Then call:

juju resolve postgresql/0

Not all PostgreSQL parameters are available to configure through the charm to avoid combinations of settings that can impact the database usage or performance. The list of supported parameters is available at Supported database configurations/parameters.

Values for each config option related to a PostgreSQL parameter can be checked on PostgreSQL charm config options.