Upgrading PostgreSQL for a podified Infrastructure management installation

If you are upgrading to Infrastructure Automation 4.1.2 from a podified installation of 3.6.x, you can follow the Online (CLI) or Offline instructions and skip these steps to upgrade to PostgreSQL 13.

If you are upgrading to Infrastructure Automation 4.1.2 from a podified installation of 3.5.x or older, you must follow these instructions to complete the required upgrade to PostgreSQL 13.

The process for upgrading PostgreSQL requires you to complete a database backup, upgrade your installation with the PostgreSQL image (PostgreSQL 13), and then restore your database by using the updated PostgreSQL.

Important: PostgreSQL 10 is not supported by IBM Cloud Pak for Watson AIOps Infrastructure Automation. The following procedure uses PostgreSQL 10 as an example for upgrading PostgreSQL, specifically to PostgreSQL 13.

Prerequisites

You must have a physical volume claim (PVC) attached to your existing PostgreSQL pod with sufficient space for upgrading. Run the following command in the PostgreSQL pod to ensure that the data directory is large enough to hold both the database backup file and the imported database:
```
psql -d vmdb_production -c "SELECT pg_size_pretty(pg_database_size('vmdb_production'));"
```
Consider allocating an extra 50% of space based on your current sizing since this command is only an estimate of size on disk. For instance, for 1 GB of space, increase your data directory to 1.5 GB. For 2 GB, increase to 3 GB.
Increase the idle timeout value. The default timeout for oc logs/exec/rsh/ commands is 15 minutes. If you do not increase this time, you might encounter issues with completing the migration steps as the debug pod can time out and force a restart of the process. For more information about changing this timeout setting, see How to change idle-timeout for oc logs/exec/rsh commands .

Procedure

Connect to your Red Hat OpenShift Container Platform cluster and run the following command to postpone the orchestrator operations so that you can back up the database without client connections:
```
oc patch deployment orchestrator -p '{"spec":{"template":{"spec":{"containers":[{"name":"orchestrator","command":["sleep", "1d"]}]}}}}' -n <namespace>
```
Where <namespace> is your project (namespace) where IBM Cloud Pak for Watson AIOps is installed.

Remote shell into the database pod and run the database backup:

oc rsh deploy/postgresql
pg_dump -Fc -d vmdb_production > /path_to_large_data_volume
exit
oc cp --retries=-1 <postgresql pod>:/path_to_large_data_volume <your_backup_file>

Store the backup on a volume outside of the PostgreSQL data directory, such as on a different physical volume.

Edit the Infrastructure management installation operand to update the PostgreSQL version (postgresqlVersion:) from 10 to 13.

You need to modify the ibm-management-im-install operand instance to change the postgresqlVersion key that has the value 10 to 13.

Then, save your changes. This change causes the existing PostgreSQL pod to stop and the new upgrade PostgreSQL (PostgreSQL 13) pod to start.

The PostgreSQL pod restarts with PostgreSQL 13 and fails as the data directory is still configured for PostgreSQL 10. For more information about how the initial instance of the operand was created, see Deploying Infrastructure management as a containerized deployment (podified).

Alternative method: You can use a command line to change the PostgreSQL version:
1. Run the following command:
```
oc get iminstalls.infra.management.ibm.com -n <namespace>
```
  Where <namespace> is the namespace where Infrastructure Automation is installed.
  
  Record the install name value. You need to use this value in the following step.
2. Then, run the following command:
```
oc edit iminstalls.infra.management.ibm.com/<install name> -n <namespace>
```
  Where <install name> is the name of the install that you recorded in the previous step.
3. Find and modify the postgresqlVersion key that has the value 10 to `13 and save your changes.
Postpone Infrastructure management operations so that you can shut down the existing PostgreSQL pod and remove the existing PVC before a new PVC is created with the upgraded PostgreSQL. Run the following command:
```
oc patch deployment ibm-infra-management-application -p '{"spec":{"template":{"spec":{"containers":[{"name":"ibm-infra-management-application","command":["sleep", "1d"]}]}}}}' -n <namespace>
```
Where <namespace> is your project (namespace) where IBM Cloud Pak for Watson AIOps is installed.
Store the database backup file outside of the physical volume that you are using for the PostgreSQL pod (outside of the data directory) to avoid any issues with deleting the existing PVC.

Delete the existing PVC:

oc delete pvc/postgresql deploy/postgresql

Remove the configuration for postponing Infrastructure management operations:
```
oc delete deploy/ibm-infra-management-application
```
When this process completes, the PVC is re-created and the PostgreSQL pod is started.

Remote shell into the orchestrator container and clear the database:

oc rsh deploy/orchestrator
cd /var/www/miq/vmdb
source ./container_env
DISABLE_DATABASE_ENVIRONMENT_CHECK=1 rake db:drop db:create
exit

Copy the database backup into the PostgreSQL pod:

oc cp --retries=-1 <your_backup_file> <postgresql pod>:/var/lib/pgsql

Restore the database backup:
1. Remote shell into the database pod:
```
oc rsh deploy/postgresql
```
2. Change directory to the directory where you copied the backup file.
```
cd /var/lib/pgsql
```
3. Run the restore in the background and capture the output in nohup.out. Ensure that the command begins with nohup. The last '&' is required to run the process in the background.
```
nohup pg_restore -v -d vmdb_production <your_backup_file>&
```
4. Review the nohup.out file and look for unignored errors. Use cat or less or more to search for errors.
```
cat nohup.out
```
  When the process finishes, the error or warning count is reported if the process was successful. The following error is expected when you upgrade from PostgreSQL 10 to 13 and can be ignored:
```
pg_restore: error: could not execute query: ERROR:  schema "public" already exists
```
  Review any other errors.
5. Run ps to verify pg_restore is no longer running:
```
ps -ef
```
6. Remove the backup file from this pod if everything restored correctly. Keep the backup file stored elsewhere until you test and verify your upgraded application.
```
rm -f <your_backup_file>
exit
```

Remote shell into the debug orchestrator pod to migrate the database:

oc rsh deploy/orchestrator
cd /var/www/miq/vmdb
source ./container_env
rake db:migrate
exit

Delete the orchestrator deployment to remove the command override that was previously added:
```
oc delete deployment orchestrator -n <namespace>
```
Where <namespace> is your project (namespace) where IBM Cloud Pak for Watson AIOps is installed.

The orchestrator is now redeployed and running with the upgraded PostgreSQL (PostgreSQL 13).