Skip to main content

Your submission was sent successfully! Close

Thank you for signing up for our newsletter!
In these regular emails you will find the latest updates from Canonical and upcoming events where you can meet our team.Close

Thank you for contacting us. A member of our team will be in touch shortly. Close

Charmed PostgreSQL K8s Tutorial > 6. Integrate with other applications

Integrate with other applications

Integrations, known as “relations” in Juju 2.9, are the easiest way to create a user for PostgreSQL in Charmed PostgreSQL K8s.

Integrations automatically create a username, password, and database for the desired user/application. As mentioned earlier in 3. Access PostgreSQL , it is a better practice to connect to PostgreSQL via a specific user rather than the admin user.

In this section, you will integrate your Charmed PostgreSQL K8s with another application.

Summary


Deploy data-integrator

In this tutorial, we will relate to the data-integrator charm. This is a bare-bones charm that allows for central management of database users, providing support for different kinds of data platforms (e.g. PostgreSQL, MySQL, MongoDB, Kafka, etc) with a consistent, opinionated and robust user experience.

To deploy the data-integrator charm, run the following command:

juju deploy data-integrator --channel edge --config database-name=test-database

Expected output:

Located charm "data-integrator" in charm-hub, revision 6
Deploying "data-integrator" from charm-hub charm "data-integrator", revision 6 in channel edge on jammy

juju status will show a blocked state for the newly deployed charm. This is expected, since the applications have not yet been related (integrated).

Example juju status output:

Model     Controller  Cloud/Region        Version  SLA          Timestamp
tutorial  charm-dev   microk8s/localhost  2.9.42   unsupported  12:11:53+01:00

App              Version  Status   Scale  Charm            Channel    Rev  Address         Exposed  Message
data-integrator           waiting      1  data-integrator  edge       6    10.152.183.66   no       installing agent
postgresql-k8s            active       2  postgresql-k8s   14/stable  56   10.152.183.167  no

Unit                Workload    Agent  Address       Ports  Message
data-integrator/0*  blocked     idle   10.1.188.211         Please relate the data-integrator with the desired product
postgresql-k8s/0*   active      idle   10.1.188.206
postgresql-k8s/1    active      idle   10.1.188.209

Integrate with PostgreSQL

Now that the data-integrator charm has been set up, we can relate it to PostgreSQL. This will automatically create a username, password, and database for data-integrator.

Integrate the two applications with the following command:

juju integrate data-integrator postgresql-k8s

Wait for juju status --watch 1s to show all applications/units as active:

Model     Controller  Cloud/Region        Version  SLA          Timestamp
tutorial  charm-dev   microk8s/localhost  2.9.42   unsupported  12:12:12+01:00

App              Version  Status   Scale  Charm            Channel    Rev  Address         Exposed  Message
data-integrator           waiting      1  data-integrator  edge        6   10.152.183.66   no       installing agent
postgresql-k8s            active       2  postgresql-k8s   14/stable  56   10.152.183.167  no

Unit                Workload    Agent  Address       Ports  Message
data-integrator/0*  active      idle   10.1.188.211
postgresql-k8s/0*   active      idle   10.1.188.206
postgresql-k8s/1    active      idle   10.1.188.209

To retrieve information such as the username, password, and database, run the command

juju run data-integrator/leader get-credentials

This should output something like:

unit-data-integrator-0:
  UnitId: data-integrator/0
  id: "12"
  results:
    ok: "True"
    postgresql:
      database: test-database
      endpoints: postgresql-k8s-primary.tutorial.svc.cluster.local:5432
      password: WHnROd8wqzQKzd4F
      read-only-endpoints: postgresql-k8s-replicas.tutorial.svc.cluster.local:5432
      username: relation_id_3
      version: "14.5"
  status: completed
  timing:
    completed: 2023-03-20 11:12:26 +0000 UTC
    enqueued: 2023-03-20 11:12:25 +0000 UTC
    started: 2023-03-20 11:12:26 +0000 UTC

Note that your hostnames, usernames, and passwords will likely be different.

Use endpoints, username, password from above to connect newly created database test-database on the PostgreSQL server:

> psql --host=10.1.188.206 --username=relation_id_3 --password test-database
Password:
...
test-database=> \l
...
 test-database | operator | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =Tc/operator              +
               |          |          |             |             | operator=CTc/operator     +
               |          |          |             |             | relation_id_3=CTc/operator
...

The newly created database test-database is also available on all other PostgreSQL cluster members:

> psql --host=10.89.49.209 --username=relation-3 --password --list
...
 test-database | operator | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =Tc/operator              +
               |          |          |             |             | operator=CTc/operator     +
               |          |          |             |             | relation_id_3=CTc/operator
...

When you relate two applications, Charmed PostgreSQL K8s automatically sets up a new user and database for you. Note the database name we specified when we first deployed the data-integrator charm: --config database-name=test-database.

Remove the user

Removing the integration automatically removes the user that was created when the integration was created. Enter the following to remove the integration:

juju remove-relation postgresql-k8s data-integrator

Now try again to connect to the same PostgreSQL you just used in the previous section:

> psql --host=10.1.188.206 --username=relation_id_3 --password --list

This will output an error message like the one shown below:

psql: error: connection to server at "10.1.188.206", port 5432 failed: FATAL:  password authentication failed for user "relation_id_3"

This is because the user no longer exists, as expected. Remember, juju remove-relation postgresql-k8s data-integrator also removes the user.

Data remains on the server at this stage.

If you want to create a user again, integrate the applications again:

juju integrate data-integrator postgresql-k8s

Re-integrating generates a new user and password:

juju run data-integrator/leader get-credentials

You can then connect to the database with these new credentials. From here you will see all of your data is still present in the database.

Next step: 7. Enable TLS

Last updated 6 months ago. Help improve this document in the forum.