0) Introduction

In the context of the IBM Cloud Pak offerings it is an logical move to also consider the move of the database in a container and host it together in the environment where the IBM Cloud Pak is hosted. Depending on the used technologies. e.g. containers in combination with virtualization there can be some overheads.

1) How to deploy the Crunchy Data postgres-operator

I tried to stick with the defaults for the postgres-operator installation to keep the installation simple, however adjustments are possible.

1.1) Preparation

To getting started I created the pgo namespace. I also tried the namespace were the ICP4BA was installed, but this did not work out of the box. Neither did the creation of the PostgreSQL cluster in the ICP4BA namespace while the pgo tools were running in the pgo namespace. However the execution in the pgo namespace was no issue as the pods are running in the same OpenShift cluster, thus easy access is possible also from ICP4BA.

export PGO_OPERATOR_NAMESPACE=pgo
oc create namespace "$PGO_OPERATOR_NAMESPACE"

As there was not the latest version of the Crunchy Data postgres-operator available in the OpenShift OperatorHub I downloaded directly from the github page via the following command (a newer version might have been released in the meantime and the command can be adjusted for the requested version):

git clone -b v4.6.2 https://github.com/CrunchyData/postgres-operator.git
cd postgres-operator

In OpenShift to run PostgreSQL in the restricted Security Context Constraint, it is recommended to edit conf/postgres-operator/pgo.yaml and set DisableFSGroup to true.

1.2) Adjusting the Configmap

Adjustments can be made via the conf/postgres-operator/pgo.yaml file. It is important to check all the available options. The documentation at this point in time can be found here:
https://access.crunchydata.com/documentation/postgres-operator/4.6.2/configuration/pgo-yaml-configuration/

1.2.1) Storage

The storage configuration is an essential part. Depending on the chosen configuration, later changes (for non-dynamic) can get tricky. The PV sizes can be such an important parameter. Depending on the expected load it also can make sense to separate the WAL log files to a separate disk/storage.

1.2.2) Create PostgreSQL cluster

After the pgo tool is installed, it is possible to create a PostgreSQL cluster with the following command. As there had been some issues with other constellations I created the cluster directly in the pgo namespace. You can see some similar entries as shown here:

[root@i28bastion openshift]# oc get pods
NAME READY STATUS RESTARTS AGE
backrest-backup-dbbaw-s2rnj 0/1 Completed 0 4d1h
dbbaw-796474d4c-vp45r 2/2 Running 0 4d1h
dbbaw-backrest-shared-repo-7cf67c6dcd-6chqt 1/1 Running 0 4d1h
pgo-client-7dd45f8546-6pffp 1/1 Running 0 4d1h
pgo-deploy-dczcn 0/1 Completed 0 4d1h
postgres-operator-5f68bdf8fc-4tzvd 4/4 Running 0 4d1h

2) How to configure the Patroni

The postgres-operator will install the Patroni tool which will be used for High Availability (HA) configuration. This implies that some of the PostgreSQL config files can not be directly edited as Patroni can adjust these and will overwrite any changes not reflected there. Thus changes need to be applied to Patroni directly. Now comes the tricky part which was not too obvious from the documentation I could find so far. Some parameters for the  PostgreSQL server must be changed via the configmap, while there is a tool normally mentioned which will do this, however this was not the case for my scenario. The following will show the config maps available in the pgo namespace after creating the dbbaw PostgreSQL cluster:

[root@i28bastion ~]# oc get configmaps
NAME                DATA   AGE
dbbaw-config        0      2d18h
dbbaw-leader        0      2d18h
dbbaw-pgha-config   3      2d18h
pgo-config          2      2d18h
pgo-deployer-cm     1      2d18h

Thus of interest for the discussed example are the configmaps dbbaw-config and dbbaw-pgha-config. The following PostgreSQL parameters are part of the special handling requirenments via the configmap DCS entries (white entries need to be the same on master and replica; grey entries can be different, however this does not make sense in context of failover):

3) Accessing the database

The database can be internally accessed via the service name. An example is given for an UMS configuration:

 dc_ums_datasource: # credentials are read from ums_configuration.db_secret_name
   dc_ums_oauth_type: postgresql # derby (for test), db2, oracle
   dc_ums_oauth_host: dbbaw.pgo.svc.cluster.local 
   dc_ums_oauth_port: 5432
   dc_ums_oauth_name: f9028ums
   dc_ums_oauth_schema: OAuthDBSchema
   dc_ums_oauth_ssl: true
   dc_ums_oauth_ssl_secret_name:
   dc_ums_oauth_driverfiles: postgresql-42.2.18.jar
   dc_ums_oauth_alternate_hosts:
   dc_ums_oauth_alternate_ports:

To access the database from outside the OpenShift environment you can enable port-forwarding by the following commands (you need to be logged in to the OpenShift cluster, the oc command can be installed on any platform, e.g. your Eclipse workstation, if you want to directly connect to the database via the Java jdbc driver ):

oc login -u <myuser> -p <mypassword> https://console-openshift-console.apps.ocp4-cl-i28.ibm.com:6443
oc port-forward dbbaw-796474d4c-vp45r 5432:5432

<Now you can connect to localhost 5432> 

4) Experimental: Migration from a non-container PostgreSQL database

Be aware that the following is experimental stuff used on a test system only. Any appliance do on your own risk.

To import a non-container PostgreSQL database backup it is required that the importing user has the permissions to create databases:

alter user <username> grant createdb ;

4.1 Backup from non-container and Restore to container PostgreSQL database server

A backup of the non-container database can be done with the pg_dumpall command:
pg_dumpall > my_backup_of_all_databases_20210406.bak

To restore the export, the following command can be used (make sure the user has the corresponding permissions as mentioned at the beginning of section 4)

psql -h localhost -U bawadmin < my_backup_of_all_databases_20210406.bak

5) More things to come ...

R) References:

https://www.youtube.com/watch?v=LwIOoU96iQw&t=2266s