Chapter 9. Migration from PostgreSQL 9 to PostgreSQL 13 By the 11th of November, 2021, the PostgreSQL version 9.6 came out of support, and CodeReady Workspaces team recommends that all users undergo migrating to version 13. Follow the procedure below to migrate to a newer version of PostgreSQL successfully without any data loss. Prerequisites The oc tool is available. An instance of CodeReady Workspaces running in OpenShift. Procedure Save and push changes back to the Git repositories for all running workspaces of the CodeReady Workspaces instance. Stop all workspaces in the CodeReady Workspaces instance. Scale down the CodeReady Workspaces and RH-SSO deployments: Backup available databases: Copy the obtained backups to a local file system: Scale down the PostgreSQL deployment: Delete the corresponding PVC unit to clean up old data: After deleting the PVC from the step above, a new PVC will automatically appear in a few seconds. Set the version of the new PostgreSQL database to 13.3: Scale up the PostgreSQL deployments: Provision a database: Copy the backups to the PostgreSQL Pod: Restore the database: Scale up the RH-SSO and CodeReady Workspaces deployments:

Commands:
scale deployment codeready --replicas=0 -n openshift-workspaces scale deployment keycloak --replicas=0 -n openshift-workspaces
POSTGRES_POD=USD(oc get pods -n openshift-workspaces | grep postgres | awk '{print USD1}') CHE_POSTGRES_DB=USD(oc get checluster/codeready-workspaces -n openshift-workspaces -o json | jq '.spec.database.chePostgresDb') exec -it USDPOSTGRES_POD -n openshift-workspaces -- bash -c "pg_dump USDCHE_POSTGRES_DB > /tmp/che.sql" exec -it USDPOSTGRES_POD -n openshift-workspaces -- bash -c "pg_dump keycloak > /tmp/keycloak.sql"
cp openshift-workspaces/USDPOSTGRES_POD:/tmp/che.sql che.sql cp openshift-workspaces/USDPOSTGRES_POD:/tmp/keycloak.sql keycloak.sql
scale deployment postgres --replicas=0 -n openshift-workspaces
delete pvc postgres-data -n openshift-workspaces
patch checluster codeready-workspaces -n openshift-workspaces --type=json -p '[{"op": "replace", "path": "/spec/database/postgresVersion", "value": "13.3"}]'
scale deployment postgres --replicas=1 -n openshift-workspaces wait --for=condition=ready pod -l app.kubernetes.io/component=postgres -n openshift-workspaces --timeout=120s
POSTGRES_POD=USD(oc get pods -n openshift-workspaces | grep postgres | awk '{print USD1}') OPERATOR_POD=USD(oc get pods -n openshift-workspaces | grep codeready-operator | awk '{print USD1}') IDENTITY_POSTGRES_SECRET=USD(oc get checluster/codeready-workspaces -n openshift-workspaces -o json | jq -r '.spec.auth.identityProviderPostgresSecret') IDENTITY_POSTGRES_PASSWORD=USD(if [ -z "USDIDENTITY_POSTGRES_SECRET" ] || [ USDIDENTITY_POSTGRES_SECRET = "null" ]; then oc get checluster/codeready-workspaces -n openshift-workspaces -o json | jq -r '.spec.auth.identityProviderPostgresPassword'; else oc get secret USDIDENTITY_POSTGRES_SECRET -n openshift-workspaces -o json | jq -r '.data.password' | base64 -d; fi) exec -it USDPOSTGRES_POD -n openshift-workspaces -- bash -c "psql postgres -tAc \"CREATE USER keycloak WITH PASSWORD 'USDIDENTITY_POSTGRES_PASSWORD'\"" exec -it USDPOSTGRES_POD -n openshift-workspaces -- bash -c "psql postgres -tAc \"CREATE DATABASE keycloak\"" exec -it USDPOSTGRES_POD -n openshift-workspaces -- bash -c "psql postgres -tAc \"GRANT ALL PRIVILEGES ON DATABASE keycloak TO keycloak\"" POSTGRES_SECRET=USD(oc get checluster/codeready-workspaces -n openshift-workspaces -o json | jq -r '.spec.database.chePostgresSecret') CHE_USER=USD(if [ -z "USDPOSTGRES_SECRET" ] || [ USDPOSTGRES_SECRET = "null" ]; then oc get checluster/codeready-workspaces -n openshift-workspaces -o json | jq -r '.spec.database.chePostgresUser'; else oc get secret USDPOSTGRES_SECRET -n openshift-workspaces -o json | jq -r '.data.user' | base64 -d; fi) exec -it USDPOSTGRES_POD -n openshift-workspaces -- bash -c "psql postgres -tAc \"ALTER USER USDCHE_USER WITH SUPERUSER\""
cp che.sql openshift-workspaces/USDPOSTGRES_POD:/tmp/che.sql cp keycloak.sql openshift-workspaces/USDPOSTGRES_POD:/tmp/keycloak.sql
exec -it USDPOSTGRES_POD -n openshift-workspaces -- bash -c "psql keycloak < /tmp/keycloak.sql" exec -it USDPOSTGRES_POD -n openshift-workspaces -- bash -c "psql USDCHE_POSTGRES_DB < /tmp/che.sql"
scale deployment keycloak --replicas=1 -n openshift-workspaces wait --for=condition=ready pod -l app.kubernetes.io/component=keycloak -n openshift-workspaces --timeout=120s scale deployment codeready --replicas=1 -n openshift-workspaces wait --for=condition=ready pod -l app.kubernetes.io/component=codeready -n openshift-workspaces --timeout=120s