Chapter 9. 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

   Copy to Clipboard Toggle word wrap

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"

   Copy to Clipboard Toggle word wrap

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

   Copy to Clipboard Toggle word wrap

6. Scale down the PostgreSQL deployment:

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

   Copy to Clipboard Toggle word wrap

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

   oc delete pvc postgres-data -n openshift-workspaces

   Copy to Clipboard Toggle word wrap

   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"}]'

   Copy to Clipboard Toggle word wrap

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

   Copy to Clipboard Toggle word wrap

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\""

    Copy to Clipboard Toggle word wrap

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

    Copy to Clipboard Toggle word wrap

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"

    Copy to Clipboard Toggle word wrap

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

    Copy to Clipboard Toggle word wrap