PostgreSQL DB with crunchy operator

Install the DB cluster

After installing the Crunchy Postgres Operator, create a postgres Cluster:

kubectl apply -k k8s/database

The Deployment is created via kustomize in a new created namespace databases and a 3 instance cluster hippo is created incl Backup to Disk.

Add role for monitorings

kubectl exec -it hippo-instance1-45mt-0 -- patronictl list

kubectl exec -it [MASTER_INSTANCE] -- psql

Here create the monitoring user:

CREATE ROLE ccp_monitoring WITH LOGIN;
ALTER ROLE ccp_monitoring WITH PASSWORD '[DECODED PASSWORD OF SECRET hippo-monitoring]';
ALTER ROLE ccp_monitoring WITH SUPERUSER;

Now install the monitoring for postgres DBs in this namespace:

kubectl apply -k k8s/monitoring‚

Create a small table for testing

For testing purpose, create a small table:

create table test (id int, name char(32));

insert into test (id, name)
values
(1,'Manni'),
(2,'Henry'),
(3,'Florian'),
(4, 'Dirk'),
(5,'Markus'),
(6,'Björn');

Create a backup of the DB

Backups can be done on demand:

kubectl annotate -n databases postgrescluster hippo \
  postgres-operator.crunchydata.com/pgbackrest-backup="$( date '+%F_%H:%M:%S' )"

If you need to rerun the backup at the same day, you need to use the --overwrite option

kubectl annotate -n databases postgrescluster hippo --overwrite \
  postgres-operator.crunchydata.com/pgbackrest-backup="$( date '+%F_%H:%M:%S' )"

Check the backups:

kubectl exec -it [LEADER_INSTANCE_POD] -- pgbackrest info

Upgrade postgres to higher version

Create a PG_UPDATE_DEPLOYMENT_NAME yaml manifest pg_update.yaml and apply it in the same namespace where the database is running:

apiVersion: postgres-operator.crunchydata.com/v1beta1
kind: PGUpgrade
metadata:
  name: [PG_UPDATE_DEPLOYMENT_NAME]
spec:
  image: registry.developers.crunchydata.com/crunchydata/crunchy-upgrade:ubi8-5.5.0-0
  postgresClusterName: [PG_CLUSTER_NAME]
  fromPostgresVersion: [FRON_VERSION]
  toPostgresVersion: [TO_VERSION]

After this manifest is deployed you can check with

kubectl -n [NAMESPACE] get pgupgrades

Now annotate the instance to fulfil the update

kubectl -n pgo annotate postgrescluster [POSTGRES_CLUISTER_NAME] postgres-operator.crunchydata.com/allow-upgrade="[PG_UPDATE_DEPLOYMENT_NAME]

Now shutdown the database by changing the notation for shutdown in the postgres-cluster yaml manifest

...
  spec:
    shutdown: true
...

Now the instance will be shutted down and the database will be upgraded. For this a couple of jobs run - you see completed pods at the end.