Here's an example of using the PostgreSQL Operator to deploy a high availability PostgreSQL cluster in Kubernetes. In this example, I'll be using the PostgreSQL Operator from Zalando [https://github.com/zalando/postgres-operator] to deploy a PostgreSQL cluster with two nodes.
The Postgres Operator can be installed by using the provided Helm chart which saves you the manual steps.
# add repo for postgres-operator
helm repo add postgres-operator-charts https://opensource.zalando.com/postgres-operator/charts/postgres-operator
# install the postgres-operator
helm install postgres-operator postgres-operator-charts/postgres-operator
Starting the operator may take a few seconds. Check if the operator pod is running before applying a Postgres cluster manifest.
kubectl get pod -l app.kubernetes.io/name=postgres-operator
dmi@dmi-laptop:~/pg_ha_k8s$ kubectl get pod -l app.kubernetes.io/name=postgres-operator
NAME READY STATUS RESTARTS AGE
postgres-operator-6c5657ccd6-zx6dq 1/1 Running 0 61s
dmi@dmi-laptop:~/pg_ha_k8s$
If the operator pod is running it listens to new events regarding postgresql resources. Now, it's time to submit your first Postgres cluster manifest.
Clone the repository zalando/postgres-operator and change to the directory
git clone https://github.com/zalando/postgres-operator.git
cd postgres-operator
cat manifests/minimal-postgres-manifest.yaml
apiVersion: "acid.zalan.do/v1"
kind: postgresql
metadata:
name: acid-minimal-cluster
spec:
teamId: "acid"
volume:
size: 1Gi
numberOfInstances: 2
users:
zalando: # database owner
- superuser
- createdb
foo_user: [] # role for application foo
databases:
foo: zalando # dbname: owner
preparedDatabases:
bar: {}
postgresql:
version: "15"
# create a Postgres cluster
kubectl create -f manifests/minimal-postgres-manifest.yaml
After the cluster manifest is submitted and passed the validation the operator will create Service and Endpoint resources and a StatefulSet which spins up new Pod(s) given the number of instances specified in the manifest. All resources are named like the cluster. The database pods can be identified by their number suffix, starting from -0. They run the Spilo container image by Zalando. As for the services and endpoints, there will be one for the master pod and another one for all the replicas (-repl suffix). Check if all components are coming up. Use the label application=spilo to filter and list the label spilo-role to see who is currently the master.
# check the deployed cluster
kubectl get postgresql
kubectl get postgresql
NAME TEAM VERSION PODS VOLUME CPU-REQUEST MEMORY-REQUEST AGE STATUS
acid-minimal-cluster acid 15 2 1Gi 78s Creating
Wait a few minutes...
kubectl get postgresql
NAME TEAM VERSION PODS VOLUME CPU-REQUEST MEMORY-REQUEST AGE STATUS
acid-minimal-cluster acid 15 2 1Gi 4m12s Running
# check created database pods
kubectl get pods -l application=spilo -L spilo-role
kubectl get pods -l application=spilo -L spilo-role
NAME READY STATUS RESTARTS AGE SPILO-ROLE
acid-minimal-cluster-0 1/1 Running 0 5m21s master
acid-minimal-cluster-1 1/1 Running 0 3m20s replica
# check created service resources
kubectl get svc -l application=spilo -L spilo-role
kubectl get svc -l application=spilo -L spilo-role
NAME TYPE CLUSTER-IP EXTERNAL-IP PORT(S) AGE SPILO-ROLE
acid-minimal-cluster ClusterIP 10.100.2.8 <none> 5432/TCP 6m9s master
acid-minimal-cluster-config ClusterIP None <none> <none> 4m3s
acid-minimal-cluster-repl ClusterIP 10.110.12.149 <none> 5432/TCP 6m9s replica
With a port-forward on one of the database pods (e.g. the master) you can connect to the PostgreSQL database from your machine. Use labels to filter for the master pod of our test cluster.
# get name of master pod of acid-minimal-cluster
export PGMASTER=$(kubectl get pods -o jsonpath={.items..metadata.name} -l application=spilo,cluster-name=acid-minimal-cluster,spilo-role=master -n default)
# set up port forward
kubectl port-forward $PGMASTER 6432:5432 -n default
Open another CLI and connect to the database using e.g. the psql client. When connecting with a manifest role like foo_user user, read its password from the K8s secret which was generated when creating acid-minimal-cluster. As non-encrypted connections are rejected by default set SSL mode to require:
export PGPASSWORD=$(kubectl get secret postgres.acid-minimal-cluster.credentials.postgresql.acid.zalan.do -o 'jsonpath={.data.password}' | base64 -d)
export PGSSLMODE=require
psql -U postgres -h localhost -p 6432
psql (12.12 (Ubuntu 12.12-0ubuntu0.20.04.1), server 15.1 (Ubuntu 15.1-1.pgdg22.04+1))
WARNING: psql major version 12, server major version 15.
Some psql features might not work.
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, bits: 256, compression: off)
Type "help" for help.
postgres=# select version();
version
-----------------------------------------------------------------------------------------------------------------------------------
PostgreSQL 15.1 (Ubuntu 15.1-1.pgdg22.04+1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 11.3.0-1ubuntu1~22.04) 11.3.0, 64-bit
(1 row)
postgres=#
postgres=# create database my_test_db;
CREATE DATABASE
postgres=#
postgres=# \c my_test_db
You are now connected to database "my_test_db" as user "postgres".
my_test_db=# create table my_table_1(a varchar(100), b timestamp);
CREATE TABLE
my_test_db=# create table my_table_2(a varchar(100), b timestamp);
CREATE TABLE
my_test_db=# create table my_table_3(a varchar(100), b timestamp);
CREATE TABLE
my_test_db=# insert into my_table_1 values('One One One', '2001-01-01 01:01:01');
INSERT 0 1
my_test_db=# insert into my_table_1 values('One One One', '2001-01-01 01:01:02');
INSERT 0 1
my_test_db=# insert into my_table_1 values('One One One', '2001-01-01 01:01:03');
INSERT 0 1
my_test_db=# insert into my_table_1 values('One One One', '2001-01-01 01:01:04');
INSERT 0 1
my_test_db=# insert into my_table_2 values('Two Two Two', '2002-02-02 01:01:04');
INSERT 0 1
my_test_db=#
my_test_db=# \d
List of relations
Schema | Name | Type | Owner
--------+-------------------------+-------+----------
public | my_table_1 | table | postgres
public | my_table_2 | table | postgres
public | my_table_3 | table | postgres
public | pg_stat_kcache | view | postgres
public | pg_stat_kcache_detail | view | postgres
public | pg_stat_statements | view | postgres
public | pg_stat_statements_info | view | postgres
(7 rows)
my_test_db=#
Let's delete the pod in the K8S cluster that is master, and verify that the failover procedure is working.
How to deploy a high availability (HA) Postgres cluster in Kubernetes?
kubectl get pods
NAME READY STATUS RESTARTS AGE
acid-minimal-cluster-0 1/1 Running 0 22m
acid-minimal-cluster-1 1/1 Running 0 20m
postgres-operator-6c5657ccd6-zx6dq 1/1 Running 0 27m
kubectl get pods
NAME READY STATUS RESTARTS AGE
acid-minimal-cluster-0 1/1 Running 0 2m45s
acid-minimal-cluster-1 1/1 Running 0 30m
postgres-operator-6c5657ccd6-zx6dq 1/1 Running 0 37m
Let's examine log files of the pods after the failover: the pod acid-minimal-cluster-0 became secondary and the acid-minimal-cluster-1 became the leader.
kubectl logs acid-minimal-cluster-0
...
2023-04-12 13:07:36,610 INFO: no action. I am (acid-minimal-cluster-0), a secondary, and following a leader (acid-minimal-cluster-1)
2023-04-12 13:07:46,608 INFO: no action. I am (acid-minimal-cluster-0), a secondary, and following a leader (acid-minimal-cluster-1)
2023-04-12 13:07:56,611 INFO: no action. I am (acid-minimal-cluster-0), a secondary, and following a leader (acid-minimal-cluster-1)
2023-04-12 13:08:06,612 INFO: no action. I am (acid-minimal-cluster-0), a secondary, and following a leader (acid-minimal-cluster-1)
kubectl logs acid-minimal-cluster-1
...
2023-04-12 13:08:34.726 33 LOG {ticks: 0, maint: 0, retry: 0}
2023-04-12 13:08:36,604 INFO: no action. I am (acid-minimal-cluster-1), the leader with the lock
2023-04-12 13:08:46,604 INFO: no action. I am (acid-minimal-cluster-1), the leader with the lock
2023-04-12 13:08:56,604 INFO: no action. I am (acid-minimal-cluster-1), the leader with the lock
2023-04-12 13:09:04.731 33 LOG {ticks: 0, maint: 0, retry: 0}
2023-04-12 13:09:06,608 INFO: no action. I am (acid-minimal-cluster-1), the leader with the lock
2023-04-12 13:09:16,608 INFO: no action. I am (acid-minimal-cluster-1), the leader with the lock
2023-04-12 13:09:26,619 INFO: no action. I am (acid-minimal-cluster-1), the leader with the lock
Let's connect to the PostgreSQL after failover and ensure that the data populated before is persistent.
psql -U postgres -h localhost -p 6432
postgres=# \c my_test_db
psql (12.12 (Ubuntu 12.12-0ubuntu0.20.04.1), server 15.1 (Ubuntu 15.1-1.pgdg22.04+1))
WARNING: psql major version 12, server major version 15.
Some psql features might not work.
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, bits: 256, compression: off)
You are now connected to database "my_test_db" as user "postgres".
my_test_db=#
my_test_db=# \d
List of relations
Schema | Name | Type | Owner
--------+-------------------------+-------+----------
public | my_table_1 | table | postgres
public | my_table_2 | table | postgres
public | my_table_3 | table | postgres
public | pg_stat_kcache | view | postgres
public | pg_stat_kcache_detail | view | postgres
public | pg_stat_statements | view | postgres
public | pg_stat_statements_info | view | postgres
(7 rows)
my_test_db=# select * from my_table_1;
a | b
-------------+---------------------
One One One | 2001-01-01 01:01:01
One One One | 2001-01-01 01:01:02
One One One | 2001-01-01 01:01:03
One One One | 2001-01-01 01:01:04
(4 rows)
my_test_db=#
We can enter a shell into one of the Pods and check the cluster status there:
kubectl exec -it acid-minimal-cluster-0 -- /bin/bash
____ _ _
/ ___| _ __ (_) | ___
\___ \| '_ \| | |/ _ \
___) | |_) | | | (_) |
|____/| .__/|_|_|\___/
|_|
This container is managed by runit, when stopping/starting services use sv
Examples:
sv stop cron
sv restart patroni
Current status: (sv status /etc/service/*)
run: /etc/service/patroni: (pid 24) 992s
run: /etc/service/pgqd: (pid 25) 992s
root@acid-minimal-cluster-0:/home/postgres#
root@acid-minimal-cluster-0:/home/postgres# patronictl list
+ Cluster: acid-minimal-cluster (7221139180597178431) ----+----+-----------+
| Member | Host | Role | State | TL | Lag in MB |
+------------------------+------------+---------+---------+----+-----------+
| acid-minimal-cluster-0 | 172.17.0.4 | Replica | running | 2 | 0 |
| acid-minimal-cluster-1 | 172.17.0.5 | Leader | running | 2 | |
+------------------------+------------+---------+---------+----+-----------+
root@acid-minimal-cluster-0:/home/postgres#
root@acid-minimal-cluster-0:/home/postgres# su - postgres
postgres@acid-minimal-cluster-0:~$
postgres@acid-minimal-cluster-0:~$ psql -d postgres
psql (15.1 (Ubuntu 15.1-1.pgdg22.04+1))
Type "help" for help.
postgres=#
postgres=# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | ICU Locale | Locale Provider | Access privileges
------------+-----------+----------+-------------+-------------+------------+-----------------+-----------------------
bar | bar_owner | UTF8 | en_US.utf-8 | en_US.utf-8 | | libc |
foo | zalando | UTF8 | en_US.utf-8 | en_US.utf-8 | | libc |
my_test_db | postgres | UTF8 | en_US.utf-8 | en_US.utf-8 | | libc |
postgres | postgres | UTF8 | en_US.utf-8 | en_US.utf-8 | | libc |
template0 | postgres | UTF8 | en_US.utf-8 | en_US.utf-8 | | libc | =c/postgres +
| | | | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | en_US.utf-8 | en_US.utf-8 | | libc | =c/postgres +
| | | | | | | postgres=CTc/postgres
(6 rows)
postgres=# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | ICU Locale | Locale Provider | Access privileges
------------+-----------+----------+-------------+-------------+------------+-----------------+-----------------------
bar | bar_owner | UTF8 | en_US.utf-8 | en_US.utf-8 | | libc |
foo | zalando | UTF8 | en_US.utf-8 | en_US.utf-8 | | libc |
my_test_db | postgres | UTF8 | en_US.utf-8 | en_US.utf-8 | | libc |
postgres | postgres | UTF8 | en_US.utf-8 | en_US.utf-8 | | libc |
template0 | postgres | UTF8 | en_US.utf-8 | en_US.utf-8 | | libc | =c/postgres +
| | | | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | en_US.utf-8 | en_US.utf-8 | | libc | =c/postgres +
| | | | | | | postgres=CTc/postgres
(6 rows)
postgres=# \c my_test_db
You are now connected to database "my_test_db" as user "postgres".
my_test_db=# \d
List of relations
Schema | Name | Type | Owner
--------+-------------------------+-------+----------
public | my_table_1 | table | postgres
public | my_table_2 | table | postgres
public | my_table_3 | table | postgres
public | pg_stat_kcache | view | postgres
public | pg_stat_kcache_detail | view | postgres
public | pg_stat_statements | view | postgres
public | pg_stat_statements_info | view | postgres
(7 rows)
my_test_db=# select * from my_table_1;
a | b
-------------+---------------------
One One One | 2001-01-01 01:01:01
One One One | 2001-01-01 01:01:02
One One One | 2001-01-01 01:01:03
One One One | 2001-01-01 01:01:04
(4 rows)
my_test_db=#
Top comments (0)