Back to Posts

PostgreSQL in Kubernetes

Posted in Cloud

Last week we manage to configure MicroK8s for having our own Kubernetes (k8s) cluster, so for learning a bit more on it we are going to learn how to install a PostgreSQL server that will be run natively in our cloud.

For installing PostgreSQL we are going to use a k8s operator, but first let’s understand what an operator is.

Operators are software extensions to k8s that make use of custom resources to manage applications and their components. K8s’ controllers concept lets you extend the cluster’s behaviour without modifying the code of k8s itself. Operators are clients of the k8s API that act as controllers for a Custom Resource.

So basically we extended the functionality of our cluster to easily deploy applications that could automatize how they are deployed and configure.

Why use a operator to deploy a database server? Because databases in a cloud are really hard, we need to provision storage, manage replication, credentials for the clients, and a dozen of complicated and delicate details, an operator will automatize all of this for us.

For this we are going to use this postgres-operator.

Adding the Operator

First we need to clone the repo:

$ git clone

Note: Currently on version 1.2.0 of the operator there is a bug that is fixed but not released, we will patch the installation before. Edit the file manifests/postgres-operator.yaml :





End Note

Now we will install the operator using the manifests:

$ cd postgres-operator
$ microk8s.kubectl create -f manifests/configmap.yaml  # configuration
$ microk8s.kubectl create -f manifests/operator-service-account-rbac.yaml  # identity
$ microk8s.kubectl create -f manifests/postgres-operator.yaml  # deployment

Now to check that the operator has started we could do:

$ microk8s.kubectl get pod -l name=postgres-operator
NAME                                 READY   STATUS    RESTARTS   AGE
postgres-operator-66cc575d9c-jfnlj   1/1     Running   0          20s

Creating a Database

Operators work with resource, when we provide to k8s a resource with the data for a register operator it will use it to install it.

For example les create a database named movies with and admin user name moviesdba and a user named moviesuser. For this we will create a file that will could name movies-db.yml :

apiVersion: ""
kind: postgresql
  name: movies-db-cluster
  namespace: default
  teamId: "movies"
    size: 1Gi
  numberOfInstances: 2
    moviesdba:  # database owner
    - superuser
    - createdb
    moviesuser: []  # roles
    movies: moviesdba  # dbname: owner
    version: "11"

Now let’s create our database using :

$ microk8s.kubectl create -f movies-db.yml created

Now we we need to wait that our database is running, we could check until we get this output :

$ microk8s.kubectl get postgresql
movies-db-cluster   movies   11        2      1Gi                                     106s   Running

And the inspect the nodes four our database with :

$ microk8s.kubectl get pods -l application=spilo -L spilo-role
movies-db-cluster-0   1/1     Running   0          6m56s   master
movies-db-cluster-1   1/1     Running   0          6m15s   replica

As we could see our database has a master and a replica running.

Accessing our database

For testing our database lest just use the postgresql client, we could install it with :

sudo apt-get install postgresql-client  

We will login in our server with our moviesdba user so we need to get it password, we could get it with:

$ microk8s.kubectl get secret moviesdba.movies-db-cluster.credentials -o 'jsonpath={.data.password}' | base64 -d 

The operator has configured our database server to only be accessed within the cluster so in order to test it we will forward the postgres port 5432 on our master to our localhost port 6432, this will run until we do ctrl+c :

$ microk8s.kubectl port-forward movies-db-cluster-0 6432:5432                        
Forwarding from -> 5432
Forwarding from [::1]:6432 -> 5432

Finally we can connect to our database with with the provide user and password using psql in another shell :

$ psql -h localhost -p 6432 -U moviesdba movies                                                            
Password for user moviesdba: 
psql (11.6 (Ubuntu 11.6-1.pgdg18.04+1), server 11.5 (Ubuntu 11.5-1.pgdg18.04+1))
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, bits: 256, compression: off)
Type "help" for help.

movies=# \list
                                  List of databases
   Name    |   Owner   | Encoding |   Collate   |    Ctype    |   Access privileges   
 movies    | moviesdba | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 postgres  | postgres  | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 template0 | postgres  | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |           |          |             |             | postgres=CTc/postgres
 template1 | postgres  | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |           |          |             |             | postgres=CTc/postgres
(4 rows)

movies=# \du
                                          List of roles
 Role name  |                         Attributes                         |       Member of        
 admin      | Create DB, Cannot login                                    | {moviesdba,moviesuser}
 moviesdba  | Superuser, Create DB                                       | {}
 moviesuser |                                                            | {}
 postgres   | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
 robot_zmon | Cannot login                                               | {}
 standby    | Replication                                                | {}
 zalandos   | Create DB, Cannot login                                    | {}

movies=# \q

We have verify that our users are setup in our database, now we are ready for more steps that I’ll try to cover in the following days creating an application that run on our k8s cluster and connect to our database.

Finally if we look to look more about how this operator works, how we could scale our replicas and repair our database we should take a look to the operator documentation.


About Juan Medina
I'm just a normal geek that code all kind of stuff, from complex corporate applications to games.

Games, music, movies and traveling are my escape pods.

Read Next

Configuring MicroK8s