Integrate your charm with PostgreSQL

From Zero to Hero: Write your first Kubernetes charm > Integrate your charm with PostgreSQL

See previous: Expose the version of the application behind your charm

This document is part of a series, and we recommend you follow it in sequence. However, you can also jump straight in by checking out the code from the previous branches:

git clone https://github.com/canonical/juju-sdk-tutorial-k8s.git
cd juju-sdk-tutorial-k8s
git checkout 03_set_workload_version
git checkout -b 04_integrate_with_psql 

A charm often requires or supports relations to other charms. For example, to make our application fully functional we need to connect it to the PostgreSQL database. In this chapter of the tutorial we will update our charm so that it can be integrated with the existing PostgreSQL charm.

Contents:

  1. Fetch the required database interface charm libraries
  2. Define the charm relation interface
  3. Import the database interface libraries and define database event handlers
  4. Validate your charm
  5. Review the final code

Fetch the required database interface charm libraries

Navigate to your charm directory and fetch the data_interfaces charm library from Charmhub:

ubuntu@charm-dev:~/fastapi-demo$ charmcraft fetch-lib charms.data_platform_libs.v0.data_interfaces

Your charm directory should now contain the structure below:

lib
└── charms
    └── data_platform_libs
        └── v0
            └── data_interfaces.py

Well done, you’ve got everything you need to set up a database relation!

Define the charm relation interface

Now, time to define the charm relation interface.

First, find out the name of the interface that PostgreSQL offers for other charms to connect to it. According to the documentation of the PostgreSQL charm, the interface is called postgresql_client.

Next, open the metadata.yaml file of your charm and, before the containers section, define a relation endpoint using a requires block, as below. This endpoint says that our charm is requesting a relation called database over an interface called postgresql_client with a maximum number of supported connections of 1. (Note: Here, database is a custom relation name, though in general we recommend sticking to default recommended names for each charm.)

requires:
  database:
    interface: postgresql_client
    limit: 1

That will tell juju that our charm can be integrated with charms that provide the same postgresql_client interface, for example, the official PostgreSQL charm.

Read more: File ‘metadata.yaml’

Import the database interface libraries and define database event handlers

We now need to implement the logic that wires our application to a database. When a relation between our application and the data platform is formed, the provider side (i.e., the data platform) will create a database for us and it will provide us with all the information we need to connect to it over the relation – e.g., username, password, host, port, etc. On our side, we nevertheless still need to set the relevant environment variables to point to the database and restart the service.

To do so, we need to update our charm “src/charm.py” to do all of the following:

  • Import the DataRequires class from the interface library; this class represents the relation data exchanged in the client-server communication.
  • Define the event handlers that will be called during the relation lifecycle.
  • Bind the event handlers to the observed relation events.

Import the database interface libraries

First, at the top of the file, import the database interfaces library:

# Import the 'data_interfaces' library.
# The import statement omits the top-level 'lib' directory 
# because 'charmcraft pack' copies its contents to the project root.
from charms.data_platform_libs.v0.data_interfaces import DatabaseCreatedEvent
from charms.data_platform_libs.v0.data_interfaces import DatabaseRequires

Read more: Library > Location

Add relation event observers

Next, in the __init__ method, define a new instance of the ‘DatabaseRequires’ class. This is required to set the right permissions scope for the PostgreSQL charm. It will create a new user with a password and a database with the required name (below, names_db), and limit the user permissions to only this particular database (that is, below, names_db).

# The 'relation_name' comes from the 'metadata.yaml file'.
# The 'database_name' is the name of the database that our application requires. 
# See the application documentation in the GitHub repository.
self.database = DatabaseRequires(self, relation_name="database", database_name="names_db")

Now, add event observers for all the database events:

# See https://charmhub.io/data-platform-libs/libraries/data_interfaces
self.framework.observe(self.database.on.database_created, self._on_database_created)
self.framework.observe(self.database.on.endpoints_changed, self._on_database_created)

Finally, set up an event observer for the standard Juju OLM relation-broken event, as below. This will handle the situation where the charm user decides to remove the relation to the database.

self.framework.observe(self.on.database_relation_broken, self._on_database_relation_removed)

Read more: Event <relation name>-relation-broken

Fetch the database authentication data

Next, we’ll add an exception class that we can use to signal that we can’t get anything from the database yet:

class DatabaseNotReady(Exception):
    """Signals that the database cannot yet be used."""

Now we need to extract the database authentication data and endpoints information. We can do that by adding a fetch_postgres_relation_data method to our charm class. Inside this method, we first retrieve relation data from the PostgreSQL using the fetch_relation_data method of the database object. We then log the retrieved data for debugging purposes. Next we process any non-empty data to extract endpoint information, the username, and the password and return this process data as a dictionary. Finally, we ensure that, if no data is retrieved, we raise our exception to let callers know that the database is not yet ready.

def fetch_postgres_relation_data(self) -> dict:
    """Fetch postgres relation data.

    This function retrieves relation data from a postgres database using
    the `fetch_relation_data` method of the `database` object. The retrieved data is
    then logged for debugging purposes, and any non-empty data is processed to extract
    endpoint information, username, and password. This processed data is then returned as
    a dictionary. If no data is retrieved, the unit is set to waiting status and
    the program exits with a zero status code."""
    relations = self.database.fetch_relation_data()
    logger.debug("Got following database data: %s", relations)
    for data in relations.values():
        if not data:
            continue
        logger.info("New PSQL database endpoint is %s", data["endpoints"])
        host, port = data["endpoints"].split(":")
        db_data = {
            "db_host": host,
            "db_port": port,
            "db_username": data["username"],
            "db_password": data["password"],
        }
        return db_data
    raise DatabaseNotReady()

Share the authentication information with your application

Our application consumes database authentication information in the form of environment variables. Let’s update the Pebble service definition with an environment key and let’s set this key to a dynamic value – the class property self.app_environment. Your _pebble_layer property should look as below:

@property
def _pebble_layer(self):
    """Return a dictionary representing a Pebble layer."""
    command = " ".join(
        [
            "uvicorn",
            "api_demo_server.app:app",
            "--host=0.0.0.0",
            f"--port={self.config['server-port']}",
        ]
    )
    pebble_layer = {
        "summary": "FastAPI demo service",
        "description": "pebble config layer for FastAPI demo server",
        "services": {
            self.pebble_service_name: {
                "override": "replace",
                "summary": "fastapi demo",
                "command": command,
                "startup": "enabled",
                "environment": self.app_environment,
            }
        },
    }
    return ops.pebble.Layer(pebble_layer)

Now, let’s define this property such that, every time it is called, it dynamically fetches database authentication data and also prepares the output in a form that our application can consume, as below:

@property
def app_environment(self):
    """This property method creates a dictionary containing environment variables
    for the application. It retrieves the database authentication data by calling
    the `fetch_postgres_relation_data` method and uses it to populate the dictionary.
    If any of the values are not present, it will be set to None.
    The method returns this dictionary as output.
    """
    db_data = self.fetch_postgres_relation_data()
    env = {
        "DEMO_SERVER_DB_HOST": db_data.get("db_host", None),
        "DEMO_SERVER_DB_PORT": db_data.get("db_port", None),
        "DEMO_SERVER_DB_USER": db_data.get("db_username", None),
        "DEMO_SERVER_DB_PASSWORD": db_data.get("db_password", None),
    }
    return env

We also need to make sure that we handle the database not being ready yet in our update method, setting the status to WaitingStatus:

    def _update_layer_and_restart(self, event) -> None:
        """Define and start a workload using the Pebble API.

        You'll need to specify the right entrypoint and environment
        configuration for your specific workload. Tip: you can see the
        standard entrypoint of an existing container using docker inspect

        Learn more about Pebble layers at https://github.com/canonical/pebble
        """
        # Learn more about statuses in the SDK docs:
        # https://juju.is/docs/sdk/constructs#heading--statuses
        self.unit.status = ops.MaintenanceStatus("Assembling pod spec")
        try:
            new_layer = self._pebble_layer.to_dict()
        except DatabaseNotReady:
            self.unit.status = ops.WaitingStatus("Waiting for database relation")
            return
        try:
            # Get the current pebble layer config
            services = self.container.get_plan().to_dict().get("services", {})
            if services != new_layer["services"]:
                # Changes were made, add the new layer
                self.container.add_layer("fastapi_demo", self._pebble_layer, combine=True)
                logger.info("Added updated layer 'fastapi_demo' to Pebble plan")

                self.container.restart(self.pebble_service_name)
                logger.info(f"Restarted '{self.pebble_service_name}' service")

            # add workload version in juju status
            self.unit.set_workload_version(self.version)
            self.unit.status = ops.ActiveStatus()
        except ops.pebble.ConnectionError:
            self.unit.status = ops.WaitingStatus("Waiting for Pebble in workload container")

Finally, let’s define the methods that are called on database events:

def _on_database_created(self, event: DatabaseCreatedEvent) -> None:
    """Event is fired when postgres database is created."""
    self._update_layer_and_restart(None)

def _on_database_relation_removed(self, event) -> None:
    """Event is fired when relation with postgres is broken."""
    self.unit.status = ops.WaitingStatus("Waiting for database relation")

The diagram below illustrates the workflow for the case where the database integration exists and for the case where it does not:

Untitled Diagram.drawio(1)

Validate your charm

Time to check the results!

First, repack and refresh your charm:

charmcraft pack
juju refresh \
  --path="./demo-api-charm_ubuntu-22.04-amd64.charm" \
  demo-api-charm --force-units --resource \
  demo-server-image=ghcr.io/canonical/api_demo_server:1.0.0

Next, deploy the postgresql-k8s charm:

juju deploy postgresql-k8s --channel=14/stable --trust

Now, integrate our charm with the newly deployed postgresql-k8s charm:

juju integrate postgresql-k8s demo-api-charm

Read more: Integration, juju integrate

Finally, run:

juju status --relations --watch 1s

You should see both applications get to the active status, and also that the postgresql-k8s charm has a relation to the demo-api-charm over the postgresql_client interface, as below:

Model        Controller           Cloud/Region        Version  SLA          Timestamp
charm-model  tutorial-controller  microk8s/localhost  3.0.0    unsupported  13:50:39+01:00

App             Version  Status  Scale  Charm           Channel  Rev  Address         Exposed  Message
demo-api-charm  0.0.9    active      1  demo-api-charm             1  10.152.183.233  no       
postgresql-k8s           active      1  postgresql-k8s  14/stable      29  10.152.183.195  no       Primary

Unit               Workload  Agent  Address      Ports  Message
demo-api-charm/0*  active    idle   10.1.157.90         
postgresql-k8s/0*  active    idle   10.1.157.92         Primary

Relation provider              Requirer                       Interface          Type     Message
postgresql-k8s:database        demo-api-charm:database        postgresql_client  regular  
postgresql-k8s:database-peers  postgresql-k8s:database-peers  postgresql_peers   peer     
postgresql-k8s:restart         postgresql-k8s:restart         rolling_op         peer 

The relation appears to be up and running, but we should also test that it’s working as intended. First, let’s try to write something to the database by posting some name to the database via API using curl as below – where 10.1.157.90 is a pod IP and 8000 is our app port. You can repeat the command for multiple names.

curl -X 'POST' \
  'http://10.1.157.90:8000/addname/' \
  -H 'accept: application/json' \
  -H 'Content-Type: application/x-www-form-urlencoded' \
  -d 'name=maksim'

If you changed the server-port config value in the previous section, don’t forget to change it back to 8000 before doing this!

Second, let’s try to read something from the database by running:

curl 10.1.157.90:8000/names

This should produce something similar to the output below (of course, with the names that you decided to use):

{"names":{"1":"maksim","2":"simon"}}

Congratulations, your integration with PostgreSQL is functional!

Review the final code

For the full code see: 04_integrate_with_psql

For a comparative view of the code before and after this doc see: Comparison

See next: Preserve your charm’s data

Contributors: @beliaev-maksim , @florezal

There is one potential issue with the ops version:

unit-demo-api-charm-0: 14:02:35 ERROR juju.worker.uniter.operation hook "install" (via hook dispatching script: dispatch) failed: exit status 1
unit-demo-api-charm-0: 14:02:35 INFO juju.worker.uniter awaiting error resolution for "install" hook
unit-demo-api-charm-0: 14:03:18 INFO juju.worker.uniter awaiting error resolution for "install" hook
unit-demo-api-charm-0: 14:03:18 WARNING unit.demo-api-charm/0.install Traceback (most recent call last):
unit-demo-api-charm-0: 14:03:18 WARNING unit.demo-api-charm/0.install   File "/var/lib/juju/agents/unit-demo-api-charm-0/charm/./src/charm.py", line 9, in <module>
unit-demo-api-charm-0: 14:03:18 WARNING unit.demo-api-charm/0.install     from charms.data_platform_libs.v0.data_interfaces import DatabaseCreatedEvent
unit-demo-api-charm-0: 14:03:18 WARNING unit.demo-api-charm/0.install   File "/var/lib/juju/agents/unit-demo-api-charm-0/charm/lib/charms/data_platform_libs/v0/data_interfaces.py", line 295, in <module>
unit-demo-api-charm-0: 14:03:18 WARNING unit.demo-api-charm/0.install     from ops.framework import EventSource, Object, _Metaclass
unit-demo-api-charm-0: 14:03:18 WARNING unit.demo-api-charm/0.install ImportError: cannot import name '_Metaclass' from 'ops.framework' (/var/lib/juju/agents/unit-demo-api-charm-0/charm/venv/ops/framework.py)

The root cause is the ops >= 1.5.0 in the requirements.txt file. By default, charm will use the latest version 2.0+ which has significant changes compared to version 1.5.0. One solution is to use ops == 1.5.0.

thanks, for all the comments. We have planned v2 version of the tutorial this cycle

Hey, the following links lead to invalid URLs (404)

try now, I have fixed

psql was released to stable and channel got updated

1 Like

Also, a more complete example as how to get relational data would be to add some more keys/values coming from the relation:

def fetch_postgres_relation_data(self) -> dict:
        """
        Get relational data.
        """
        data = self.database.fetch_relation_data()
        logger.debug("Got following database data: %s", data)
        for key, val in data.items():
            if not val:
                continue
            logger.info("New PSQL database endpoint is %s", val["endpoints"])
            logger.info("data: ", data)
            host, port = val["endpoints"].split(":")
            db_data = {
                "db_host": host,
                "db_port": port,
                "db_username": val["username"],
                "db_password": val["password"],
                "db_name": val["database"],
                "pgsql_version": val["version"]
            }
            return db_data
        self.unit.status = ops.WaitingStatus("Waiting for database relation")
        raise SystemExit(0)

I’ve been trying to figure out how to access also the psql via the client for the new

postgresql 14.7 active 1 postgresql 14/stable 288 no Primary

I’m logging in to the instance:

juju ssh postgresql/0

Installed the psql client via apt.

Retrieved the password:

juju run-action postgresql/leader get-password --wait

Then tried to connect to the instance:

psql --host=10.89.49.129 --username=operator --password

This only leaves me in a blocked terminal after I’ve entered the password.

I’m on Ubuntu jammy.

Hi, Erik, can you provide more details about your setup? I tried this locally in an LXD container and was able to connect both from within the container and from the host using:

psql --host=10.4.210.100 --username=operator --password postgres

Is there a firewall involved? That psql hangs makes me think there’s some kind of connectivity issue.

I couldn’t reproduce it last night. I managed to get it all going. Thanx for looking in to it though.

Update is that I have not been able to reproduce my error. Possibly something local to my client might have caused the above.

So, I’m about to figure out how to replace me old postgresql for Nextcloud.

My current deploy looks like this, with the “old” reactive postgresql charm on revision 246.

The relation/integration that is used for the old versions of the charm is: postgresql:db <-> nextcloud:db on the interface: postgresql

In Nextcloud metadata.yaml

    requires:
      db:
        interface: pgsql

The new charm needs to move to a new relation to work with postgresql:

  database:
    interface: postgresql_client
    limit: 1

I have a tricky situation now, since I can’t seem to re-deploy the old charm (revision 246) charm, since its no longer available in the store. Since I would like to reproduce the deployment, to test my migration process. This means I would need access to the old version of the charm postgresql.

Where can I find it?

My strategy for migrating from old to new isn’t 100% cleared out, but I guess this is what I need to find out. I have found this draft material which I will use: Charmed PostgreSQL How-to - Migrate from Legacy (overview) @tmihoc

sorry, all this discussion is out of scope of this tutorial

please ask you question on: Mattermost

1 Like

For the history:

I have a tricky situation now, since I can’t seem to re-deploy the old charm (revision 246) charm, since its no longer available in the store.

It is available in the store, but you have faced a bug Bug #2020899 “cannot resolve charm or bundle “mongodb”: resolvin...” : Bugs : MongoDB Charm (the legacy charm should be installable again from latest/stable channel soon). The possible workaround there is to define “series” on deployment.

The manual How-to Migrate is on the way, the link point to the proper placeholder.

1 Like

I was reading through this and noticed that the link “The location of a charm library inside a charm” is to an archived page and inaccessible on the production docs.

The link should now be pointing to a subsection inside of the general “Library” explanation, which is here: Library

Another thing I noticed was that the postgresql-k8s charm itself required --trust to be deployed. I found this out by looking at the charm documentation and saw that the machine charm can be run without --trust but the k8s charm requires some changes to service accounts (maybe? or at least something with privileges).

So the line:

juju deploy postgresql-k8s --channel=14/stable

Should become

juju deploy postgresql-k8s --channel=14/stable --trust

@tmihoc can you please have a look

I recently tested these instructions. From what I remember, the code as given works – so, without trust. I’ll check again though.

Fixed, thanks!