Chapter 8. Migration from PostgreSQL 9 to PostgreSQL 13

Procedure

1. Save and push changes back to the Git repositories for all running workspaces of the CodeReady Workspaces instance.
2. Stop all workspaces in the CodeReady Workspaces instance.

3. Scale down the CodeReady Workspaces and RH-SSO deployments:

   oc scale deployment codeready --replicas=0 -n openshift-workspaces
   oc scale deployment keycloak --replicas=0 -n openshift-workspaces

4. Backup available databases:

   POSTGRES_POD=$(oc get pods -n openshift-workspaces | grep postgres | awk '{print $1}')
   CHE_POSTGRES_DB=$(oc get checluster/codeready-workspaces -n openshift-workspaces -o json  | jq '.spec.database.chePostgresDb')
   oc exec -it $POSTGRES_POD -n openshift-workspaces  -- bash  -c "pg_dump $CHE_POSTGRES_DB > /tmp/che.sql"
   oc exec -it $POSTGRES_POD -n openshift-workspaces  -- bash  -c "pg_dump keycloak > /tmp/keycloak.sql"

5. Copy the obtained backups to a local file system:

   oc cp openshift-workspaces/$POSTGRES_POD:/tmp/che.sql che.sql
   oc cp openshift-workspaces/$POSTGRES_POD:/tmp/keycloak.sql keycloak.sql

6. Scale down the PostgreSQL deployment:

   oc scale deployment postgres --replicas=0 -n openshift-workspaces

7. Delete the corresponding PVC unit to clean up old data:

   oc delete pvc postgres-data -n openshift-workspaces

   After deleting the PVC from the step above, a new PVC will automatically appear in a few seconds.

8. Set the version of the new PostgreSQL database to 13.3:

   oc patch checluster codeready-workspaces -n openshift-workspaces --type=json -p '[{"op": "replace", "path": "/spec/database/postgresVersion", "value": "13.3"}]'

9. Scale up the PostgreSQL deployments:

   oc scale deployment postgres --replicas=1 -n openshift-workspaces
   oc wait --for=condition=ready pod -l app.kubernetes.io/component=postgres -n openshift-workspaces --timeout=120s

10. Provision a database:

    POSTGRES_POD=$(oc get pods -n openshift-workspaces | grep postgres | awk '{print $1}')
    OPERATOR_POD=$(oc get pods -n openshift-workspaces | grep codeready-operator | awk '{print $1}')
    
    IDENTITY_POSTGRES_SECRET=$(oc get checluster/codeready-workspaces -n openshift-workspaces -o json | jq -r '.spec.auth.identityProviderPostgresSecret')
    IDENTITY_POSTGRES_PASSWORD=$(if [ -z "$IDENTITY_POSTGRES_SECRET" ] || [ $IDENTITY_POSTGRES_SECRET = "null" ]; then oc get checluster/codeready-workspaces  -n openshift-workspaces -o json | jq -r '.spec.auth.identityProviderPostgresPassword'; else oc get secret $IDENTITY_POSTGRES_SECRET -n openshift-workspaces -o json | jq -r '.data.password' | base64 -d; fi)
    
    oc exec -it $POSTGRES_POD -n openshift-workspaces  -- bash  -c "psql postgres -tAc \"CREATE USER keycloak WITH PASSWORD '$IDENTITY_POSTGRES_PASSWORD'\""
    oc exec -it $POSTGRES_POD -n openshift-workspaces  -- bash  -c "psql postgres -tAc \"CREATE DATABASE keycloak\""
    oc exec -it $POSTGRES_POD -n openshift-workspaces  -- bash  -c "psql postgres -tAc \"GRANT ALL PRIVILEGES ON DATABASE keycloak TO keycloak\""
    
    POSTGRES_SECRET=$(oc get checluster/codeready-workspaces -n openshift-workspaces -o json | jq -r '.spec.database.chePostgresSecret')
    CHE_USER=$(if [ -z "$POSTGRES_SECRET" ] || [ $POSTGRES_SECRET = "null" ]; then oc get checluster/codeready-workspaces  -n openshift-workspaces -o json | jq -r '.spec.database.chePostgresUser'; else oc get secret $POSTGRES_SECRET -n openshift-workspaces -o json | jq -r '.data.user' | base64 -d; fi)
    
    oc exec -it $POSTGRES_POD -n openshift-workspaces  -- bash  -c "psql postgres -tAc \"ALTER USER $CHE_USER WITH SUPERUSER\""

11. Copy the backups to the PostgreSQL Pod:

    oc cp che.sql openshift-workspaces/$POSTGRES_POD:/tmp/che.sql
    oc cp keycloak.sql openshift-workspaces/$POSTGRES_POD:/tmp/keycloak.sql

12. Restore the database:

    oc exec -it $POSTGRES_POD -n openshift-workspaces  -- bash  -c "psql keycloak < /tmp/keycloak.sql"
    oc exec -it $POSTGRES_POD -n openshift-workspaces  -- bash  -c "psql $CHE_POSTGRES_DB < /tmp/che.sql"

13. Scale up the RH-SSO and CodeReady Workspaces deployments:

    oc scale deployment keycloak --replicas=1 -n openshift-workspaces
    oc wait --for=condition=ready pod -l app.kubernetes.io/component=keycloak -n openshift-workspaces --timeout=120s
    oc scale deployment codeready --replicas=1 -n openshift-workspaces
    oc wait --for=condition=ready pod -l app.kubernetes.io/component=codeready -n openshift-workspaces --timeout=120s