Configuring an external PostgreSQL database for Zen

From foundational services version 4.6, Zen service uses CNPG PostgreSQL as a database to store Zen data. If you upgrade foundational services from version 3.19, 3.23, 4.0 or later to 4.6 or later, Zen operator migrates Zen data from CockroachDB to CNPG PostgreSQL database. For more information, see Migrating foundational services version 3.x to 4.x.

Note: Ensure that you configure the Zen with the embedded or external CNPG PostgreSQL database before you install or upgrade the foundational services. You cannot migrate data from embedded postgresql to external CNPG PostgreSQL after you install or upgrade the foundational services.

Prerequisites

From foundational services version 4.6, Zen supports PostgreSQL 14 as the external database. Complete the following prerequisites to configure Zen with the CNPG PostgreSQL database:

Set up the database server if you use new external database serve. For more information, see Setting up an external PostgreSQL database server.

You can skip this step if you already completed the setup for the external database server with all the certificates.
You need to generate the following key files in the database server to configure Zen to use CNPG PostgreSQL as a database:
- client_zen.crt
- client_zen.key
- client_zen_key.pem
- client_zen.pem
- root.pem

To generate the key files, see Generating key files for Zen.

Supported database

Zen supports an embedded and external CNPG PostgreSQL database. Zen is configured with the embedded PostgreSQL database as default. To configure Zen with an external PostgreSQL database, see Generating key files for Zen.

Generating key files for Zen

To configure Zen with an external PostgreSQL database, generate the key files in the database server with the following steps:

Login to an external database server and copy the key files to your target cluster.
```
scp client_zen.crt client_zen.key client_zen_key.pem client_zen.pem root.pem root@Y<your_ocp_IPaddress>:/root
```
Replace <your_ocp_IPaddress> with the Red Hat OpenShift Container Platform IP address.

Export the following environment variables.

export PGREQUIRESSL=require 
export PGSSLCERT=client_zen.crt 
export PGSSLKEY=client_zen.key

Download and enable PostgreSQL 14 on your target cluster.

sudo dnf install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-8-x86_64/pgdg-redhat-repo-latest.noarch.rpm 
sudo dnf -qy module disable postgresql 
sudo dnf install -y postgresql14-server

Test the PostgreSQL server connection.

psql --host=<yourFyreClusterName>1.fyre.ibm.com --port=5432 --dbname=zen --username=zen_user -c "SELECT VERSION()" -c "SHOW ssl" -c "SHOW max_connections" -c "\list" -c "\dn" -c "\du"

Replace <yourFyreClusterName> with your Fyre cluster name to specify the host name of the PostgreSQL server.

Create the ibm-zen-metastore-edb-secret secret.

oc create secret generic ibm-zen-metastore-edb-secret \
    --type=kubernetes.io/tls \
    --from-file=ca.crt=root.pem \
    --from-file=tls.crt=client_zen.pem \
    --from-file=tls.key=client_zen_key.pem \
    -n <Zen namespace>

Replace <Zen namespace> with the namespace where you deployed the Platform UI (IBM Zen Service).

You can update ca.crt, tls.crt, and tls.key parameters with the appropriate values.

Verify that the certificates are stored in the secret.

oc extract secret/ibm-zen-metastore-edb-secret -n <Zen namespace> --to=- --keys=ca.crt | openssl x509 -noout -subject -issuer -startdate -enddate

oc extract secret/ibm-zen-metastore-edb-secret -n <Zen namespace> --to=- --keys=tls.crt | openssl x509 -noout -subject -issuer -startdate -enddate

Replace <Zen namespace> with the namespace where you deployed the Platform UI (IBM Zen Service).

Create the ibm-zen-metastore-edb-cm configmap.

apiVersion: v1
kind: ConfigMap
metadata:
  name: ibm-zen-metastore-edb-cm
  namespace: <Zen namespace>
data:
  DATABASE_CA_CERT: ca.crt
  DATABASE_CLIENT_CERT: tls.crt
  DATABASE_CLIENT_KEY: tls.key
  DATABASE_NAME: zen
  DATABASE_PORT: "5432"
  DATABASE_R_ENDPOINT: sertexternaledb16x1.fyre.ibm.com
  DATABASE_RW_ENDPOINT: sertexternaledb16x1.fyre.ibm.com
  DATABASE_USER: zen_user
  IS_EMBEDDED: 'false'

Replace <Zen namespace> with the namespace where you deployed the Platform UI (IBM Zen Service).

You can update the DATABASE_R_ENDPOINT, DATABASE_RW_ENDPOINT, DATABASE_USER, and DATABASE_NAME parameters with the appropriate values.

Apply the ibm-zen-metastore-edb-cm configmap.

oc apply -f ibm-zen-metastore-edb-cm.yaml

Add name: ibm-platformui-operator operands in the spec section of the operand-request.yaml file to enable the Zen operator. The following is a sample OperandRequest yaml file:

apiVersion: operator.ibm.com/v1alpha1
kind: OperandRequest
metadata:
  name: common-service
  namespace: $<your foundational service namespace>
spec:
  requests:
    - operands:
        - name: ibm-im-operator
        - name: ibm-events-operator
        - name: ibm-platformui-operator
        - name: cloud-native-postgresql
      registry: common-service
      registryNamespace: $<your foundational service namespace>

Replace <your foundational service namespace> with the namespace where you deployed the foundational services.

Create the Zen custom resource with the following definitions:

apiVersion: zen.cpd.ibm.com/v1
kind: ZenService
metadata:
  name: lite-zen
  namespace: $<your foundational service namespace>
spec:
  blockStorageClass: rook-ceph-block
  fileStorageClass: rook-cephfs
  iamIntegration: true

Replace <your foundational service namespace> with the namespace where you deployed the foundational services.

Verify that the zenservice instance is running and the Progress field is 100%. Ensure that the zen-metastore-edb cluster is not created.
```
oc get zenservice -A -w -o yaml | grep Progress:
```