Postgres Instances Major Version Upgrade

Postgres Major Version Upgrade

Postgres Operator provides the ability to perform a Postgres Major Version Upgrade by using the PostgresVersionUpgrade CR. The upgrade process is performed by using pg_upgrade utility with a hard link option that doesn't consume additional space in the environment.

Example of postgres-version-upgrade.yaml file:

```
apiVersion: sql.tanzu.vmware.com/v1
kind: PostgresVersionUpgrade
metadata:
  name: <postgres-upgrade-name>
spec:
  omitBackup: false
  postgresInstance:
    name: <postgres-source-name>
  postgresVersion:
    name: <postgres-version-upgrade>
```

Where:

spec.postgresInstance.name field is the PostgresSQL source instance name.
spec.postgresVersion.name field is the desired upgrade version.
spec.omitBackup field provides the ability to omit the creation of full backup as part of the major version upgrade process. The default value is set to false.

Warning: Setting the value of the `omitBackup` field to true is equal to bypassing the creation of a full backup during the upgrade process. When using this option, the user accepts the entire risk of losing the ability to perform a rollback operation if the Major Version Upgrade process is unsuccessful.

Prerequisites

The following conditions need to be met before performing the Postgres Major Version Upgrade:

pgaudit or timetravel extension is not enabled on the Postgres instance. Use View installed extensions to see all the enabled extensions. If pgaudit or timetravel extension is enabled, the extension should be dropped before proceeding with upgrade process.
Postgres instance that is target for upgrade should be in a healthy state. If the cluster is having issues, they should be addressed before the process is initiated.
Postgres Major Upgrade target version should be one of the supported versions of the Postgres Operator.
Prepare the application for downtime. During the process of Major Version Upgrade, the database will not be accessible.
When backup location is configured and spec.omitBackup field is not set to true an automatic on-demand full backup creation will be performed as part of the major version upgrade process. This will provide rollback capabilities in the worst-case scenario where the environment can't be brought back without initiating a restore process of the backup. For details see Restoring VMware Postgres Operator.
When there is a scheduled backup configuration in the target upgrade environment and a full scheduled backup is in progress during a major version upgrade process, the full backup that is part of the upgrade process will run after the scheduled full backup finishes.
When the upgraded Postgres instance is in HA configuration as part of the Major Version Upgrade process, the environment will be scaled down automatically to a Single Instance configuration, then initiate the upgrade process and scale back up to HA configuration on the new Postgres version.

Warning: Note that once a Major Version Upgrade process is concluded, it becomes irreversible, and you can't downgrade to the source version of the Postgres instance.

Procedure

Configure the PostgresVersionUpgrade CR to point to the source Postgres instance by using the spec.postgresInstance.name field and set the desired upgrade version by setting the spec.postgresVersion.name.

To list all available Postgres version run the following command:

kubectl get postgresversion

Example output:

NAME          DB VERSION
postgres-11   11.21
postgres-12   12.16
postgres-13   13.12
postgres-14   14.9
postgres-15   15.4

Example of postgres-version-upgrade.yaml file:

apiVersion: sql.tanzu.vmware.com/v1
kind: PostgresVersionUpgrade
metadata:
  name: my-postgres-upgrade
spec:
  postgresInstance:
    name: postgres-sample
  postgresVersion:
    name: postgres-15

Deploy the configured postgres-version-upgrade.yaml file. For example:
```
kubectl apply -f postgres-version-upgrade.yaml -n <NAMESPACE>
```
where NAMESPACE is the namespace of the target Postgres instance for upgrade.

Monitoring

To monitor the current phase of the upgrade process, you can run the following command: kubectl describe postgresversionupgrade <NAME> -n <NAMESPACE> where NAME is the name of the PostgresVersionUpgrade kind and NAMESPACE is the namespace of the target Postgres instance for upgrade.

In the Status.Phase field, you can see the following phases while the upgrade process is running:

Preparation - during this phase a full on-demand backup is performed, and environment is prepared for the upgrade process.
Running - the Major Version Upgrade process is running the pg_upgrade utility against the persistent volume of the database. This includes the pre-upgrade checks of the pg_upgrade utility and the upgrade itself is performed in this phase.
WaitingForStartup - the upgrade process was performed, and the Postgres instance is prepared to be available. During this phase, the Major Version Upgrade CR ownership is transferred to the Postgres CR. This enables automatic deletion of Major Version Upgrade CR once Postgres CR is deleted.
Succeeded - the process completed successfully and the database is accessible in the upgraded Postgres version.

When automated on-demand full backup is part of the Major Version Upgrade process(spec.omitBackup is not set to true), the backup process can be verified by using:

```
kubectl get postgresbackup -L sql.tanzu.vmware.com/created-from-upgrade -l postgres-instance=<POSTGRES-NAME> -l sql.tanzu.vmware.com/created-from-upgrade=<UID> -n <NAMESPACE> -w 
```
where `POSTGRES-NAME` is the name of the Postgres resource, `UID` is the Metadata.UID field value in the PostgresVersionUpgrade resource and `NAMESPACE` is the namespace of the target Postgres instance for upgrade.

For detailed monitoring of the process, you can see the Major Version Upgrade job logs. The job name of the process will be generated with the same name as the Major Version Upgrade CR. Jobs can be listed with the following command:

```
kubectl get jobs
```

Example output:

```
NAME                  COMPLETIONS   DURATION   AGE
my-postgres-upgrade   1/1           29s        3h
```

To see the job logs, you can run the following command:

```
kubectl logs -f job/<JOBNAME>
```
where `JOBNAME` is the name of the PostgresVersionUpgrade resource.

To verify the Postgres database version, you can execute the following command:

```
kubectl get postgres <NAME> -n <NAMESPACE> 
```
where `NAME` is the name of the Postgres instance and `NAMESPACE` is the namespace where the Postgres instance is deployed.

Example output:

```
NAME              STATUS    DB VERSION   BACKUP LOCATION      AGE
postgres-sample   Running   15.4         my-backup-location   19h
```

To see the Operator logs, you can run the following command:

```
kubectl logs -l app=postgres-operator -n <OPERATOR-NAMESPACE> --tail=-1
```
where `OPERATOR-NAMESPACE` is the namespace where the Postgres Operator is deployed.

Disaster Recovery (if applicable)

Warning: The Disaster Recovery instance will not apply any new WAL files generated from the upgraded primary Postgres instance until reconfiguration of the version is performed. During the process of reconfiguring the Disaster Recovery instance to the new Postgres version, the instance will not be available to serve the purpose of Disaster Recovery.

When the Postgres Major Version Upgrade process is completed, the Disaster Recovery instance remains available in read only state in the Postgres version before the upgrade process was initiated.

To sync the DR with the newly upgraded Postgres version, you have to recreate the DR by executing the following steps:

Conduct a full backup on primary instance after the successful run of the Postgres Major Version Upgrade process. For more details, you can see Perform an On-Demand Backup.
When the full backup is successfully created and available, delete the Disaster Recovery Postgres instance by executing the following command:
```
kubectl delete postgres <NAME-OF-DR-INSTANCE> -n <NAMESPACE>
```
where NAME-OF-DR-INSTANCE is the Disaster Recovery Postgres instance name and NAMESPACE is the namespace where the DR instance was deployed.
Apply the Disaster Recovery CR yaml file after changing the postgresVersion field value to the newly upgraded major version of the Postgres instance and verify that the production stanza is correctly set. For more details, you can see Configuring Disaster Recovery.
```
postgresVersion:
  name: <new-postgres-version>
highAvailability:
  enabled: false
  readReplicas: 0
deploymentOptions:
  continuousRestoreTarget: true
  sourceStanzaName: <source-instance-stanza-name>
```
where postgresVersion.name is the new version of the Postgres instance after a successful Postgres Major Version Upgrade and deploymentOptions.sourceStanzaName is the stanza name of the primary Postgres instance.

Then apply the updated yaml file:
```
kubectl apply -f <FILENAME> -n <NAMESPACE>
```
where FILENAME is the changed Disaster Recovery CR yaml file that was used to create the DR instance and NAMESPACE is the namespace where the DR instance will be deployed.

Note: If you perform a fail-over to the Disaster Recovery site and then fail back to the primary site, make sure that you have updated the stanza name to reflect the new stanza of the Disaster Recovery. For additional details, you can see Fail back to the primary site.