Charmed PostgreSQL K8s

  • By Canonical Data Platform
Channel Revision Published Runs on
latest/stable 20 20 Sep 2022
Ubuntu 20.04
14/stable 73 18 Apr 2023
Ubuntu 22.04
14/candidate 73 18 Apr 2023
14/beta 73 18 Apr 2023
14/edge 92 Yesterday
juju deploy postgresql-k8s --channel 14/stable
Show information


Get a Charmed PostgreSQL up and running

This is part of the Charmed PostgreSQL Tutorial. Please refer to this page for more information and the overview of the content.


To deploy Charmed PostgreSQL K8s, all you need to do is run the following command, which will fetch the charm from Charmhub and deploy it to your model:

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

Note: --trust is required because the charm and Patroni need to create some K8s resources.

Juju will now fetch Charmed PostgreSQL K8s and begin deploying it to the local MicroK8s. This process can take several minutes depending on how provisioned (RAM, CPU, etc) your machine is. You can track the progress by running:

juju status --watch 1s

This command is useful for checking the status of Charmed PostgreSQL K8s and gathering information about the machines hosting Charmed PostgreSQL K8s. Some of the helpful information it displays include IP addresses, ports, state, etc. The command updates the status of Charmed PostgreSQL K8s every second and as the application starts you can watch the status and messages of Charmed PostgreSQL K8s change. Wait until the application is ready - when it is ready, juju status will show:

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

App             Version  Status  Scale  Charm           Channel    Rev  Address         Exposed  Message
postgresql-k8s           active      1  postgresql-k8s  14/stable  56  no

Unit               Workload  Agent  Address       Ports  Message
postgresql-k8s/0*  active    idle

To exit the screen with juju status --watch 1s, enter Ctrl+c. If you want to further inspect juju logs, can watch for logs with juju debug-log. More info on logging at juju logs.

Access PostgreSQL

! Disclaimer: this part of the tutorial accesses PostgreSQL via the operator user. Do not directly interface with this user in a production environment. In a production environment always create a separate user using Data Integrator and connect to PostgreSQL with that user instead. Later in the section covering Relations we will cover how to access PostgreSQL without the operator user.

The first action most users take after installing PostgreSQL is accessing PostgreSQL. The easiest way to do this is via the PostgreSQL interactive terminal psql. Connecting to the database requires that you know the values for host, username and password. To retrieve the necessary fields please run Charmed PostgreSQL K8s action get-password:

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

Running the command should output:

  UnitId: postgresql-k8s/0
  id: "2"
    password: SYhCduijXTAfg9mU
  status: completed
    completed: 2023-03-20 11:01:26 +0000 UTC
    enqueued: 2023-03-20 11:01:24 +0000 UTC
    started: 2023-03-20 11:01:25 +0000 UTC

Note: to request a password for a different user, use an option username:

juju run-action postgresql-k8s/leader get-password username=replication --wait

The host’s IP address can be found with juju status (the unit hosting the PostgreSQL application):

Unit               Workload  Agent  Address       Ports  Message
postgresql-k8s/0*  active    idle

To access the units hosting Charmed PostgreSQL K8s use:

juju ssh --container postgresql postgresql-k8s/leader bash

Note: if at any point you’d like to leave the unit hosting Charmed PostgreSQL K8s, enter Ctrl+d or type exit*.

The psql tool is already installed here. To show list of all available databases use:

psql --host= --username=operator --password --list

Note: when requested, enter the <password> for charm user operator from the output above.

The example of the output:

                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges
 postgres  | operator | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 template0 | operator | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/operator          +
           |          |          |             |             | operator=CTc/operator
 template1 | operator | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/operator          +
           |          |          |             |             | operator=CTc/operator
(3 rows)

You can now interact with PostgreSQL directly using any PostgreSQL SQL Queries. For example entering SELECT version(); should output something like:

> root@postgresql-k8s-0:/# psql --host= --username=operator --password postgres
psql (14.5 (Ubuntu 14.5-0ubuntu0.22.04.1))
Type "help" for help.

postgres=# SELECT version();
 PostgreSQL 14.5 (Ubuntu 14.5-0ubuntu0.22.04.1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 11.2.0-19ubuntu1) 11.2.0, 64-bit
(1 row)

Note: if at any point you’d like to leave the PostgreSQL client, enter Ctrl+d or type exit.

Feel free to test out any other PostgreSQL queries. When you’re ready to leave the PostgreSQL shell you can just type exit. Once you’ve typed exit you will be back in the host of Charmed PostgreSQL K8s (postgresql-k8s/0). Exit this host by once again typing exit. Now you will be in your original shell where you first started the tutorial; here you can interact with Juju and MicroK8s.

Help us improve this documentation

Most of this documentation can be collaboratively discussed and changed on the respective topic in the doc category of the Charmhub forum. See the documentation guidelines if you’d like to contribute.

Last updated a month ago. Help improve this document in the forum.