1--- 2stage: Configure 3group: Configure 4info: To determine the technical writer assigned to the Stage/Group associated with this page, see https://about.gitlab.com/handbook/engineering/ux/technical-writing/#assignments 5--- 6 7# Upgrading PostgreSQL for Auto DevOps **(FREE)** 8 9Auto DevOps provides an [in-cluster PostgreSQL database](customize.md#postgresql-database-support) 10for your application. 11 12The version of the chart used to provision PostgreSQL: 13 14- Is 0.7.1 in GitLab 12.8 and earlier. 15- Can be set to from 0.7.1 to 8.2.1 in GitLab 12.9 and later. 16 17GitLab encourages users to migrate their database to the newer PostgreSQL chart. 18 19This guide provides instructions on how to migrate your PostgreSQL database, which 20involves: 21 221. Taking a database dump of your data. 231. Installing a new PostgreSQL database using the newer version 8.2.1 of the chart 24 and removing the old PostgreSQL installation. 251. Restoring the database dump into the new PostgreSQL. 26 27## Prerequisites 28 291. Install 30 [`kubectl`](https://kubernetes.io/docs/tasks/tools/). 311. Ensure that you can access your Kubernetes cluster using `kubectl`. 32 This varies based on Kubernetes providers. 331. Prepare for downtime. The steps below include taking the application offline 34 so that the in-cluster database does not get modified after the database dump is created. 351. Ensure you have not set `POSTGRES_ENABLED` to `false`, as this setting deletes 36 any existing channel 1 database. For more information, see 37 [Detected an existing PostgreSQL database](troubleshooting.md#detected-an-existing-postgresql-database). 38 39NOTE: 40If you have configured Auto DevOps to have staging, 41consider trying out the backup and restore steps on staging first, or 42trying this out on a review app. 43 44## Take your application offline 45 46If required, take your application offline to prevent the database from 47being modified after the database dump is created. 48 491. Get the Kubernetes namespace for the environment. It typically looks like `<project-name>-<project-id>-<environment>`. 50 In our example, the namespace is called `minimal-ruby-app-4349298-production`. 51 52 ```shell 53 $ kubectl get ns 54 55 NAME STATUS AGE 56 minimal-ruby-app-4349298-production Active 7d14h 57 ``` 58 591. For ease of use, export the namespace name: 60 61 ```shell 62 export APP_NAMESPACE=minimal-ruby-app-4349298-production 63 ``` 64 651. Get the deployment name for your application with the following command. In our example, the deployment name is `production`. 66 67 ```shell 68 $ kubectl get deployment --namespace "$APP_NAMESPACE" 69 NAME READY UP-TO-DATE AVAILABLE AGE 70 production 2/2 2 2 7d21h 71 production-postgres 1/1 1 1 7d21h 72 ``` 73 741. To prevent the database from being modified, set replicas to 0 for the deployment with the following command. 75 We use the deployment name from the previous step (`deployments/<DEPLOYMENT_NAME>`). 76 77 ```shell 78 $ kubectl scale --replicas=0 deployments/production --namespace "$APP_NAMESPACE" 79 deployment.extensions/production scaled 80 ``` 81 821. You must also set replicas to zero for workers if you have any. 83 84## Backup 85 861. Get the service name for PostgreSQL. The name of the service should end with `-postgres`. In our example the service name is `production-postgres`. 87 88 ```shell 89 $ kubectl get svc --namespace "$APP_NAMESPACE" 90 NAME TYPE CLUSTER-IP EXTERNAL-IP PORT(S) AGE 91 production-auto-deploy ClusterIP 10.30.13.90 <none> 5000/TCP 7d14h 92 production-postgres ClusterIP 10.30.4.57 <none> 5432/TCP 7d14h 93 ``` 94 951. Get the pod name for PostgreSQL with the following command. In our example, the pod name is `production-postgres-5db86568d7-qxlxv`. 96 97 ```shell 98 $ kubectl get pod --namespace "$APP_NAMESPACE" -l app=production-postgres 99 NAME READY STATUS RESTARTS AGE 100 production-postgres-5db86568d7-qxlxv 1/1 Running 0 7d14h 101 ``` 102 1031. Connect to the pod with: 104 105 ```shell 106 kubectl exec -it production-postgres-5db86568d7-qxlxv --namespace "$APP_NAMESPACE" -- bash 107 ``` 108 1091. Once, connected, create a dump file with the following command. 110 111 - `SERVICE_NAME` is the service name obtained in a previous step. 112 - `USERNAME` is the username you have configured for PostgreSQL. The default is `user`. 113 - `DATABASE_NAME` is usually the environment name. 114 115 - When prompted for the database password, the default is `testing-password`. 116 117 ```shell 118 ## Format is: 119 # pg_dump -h SERVICE_NAME -U USERNAME DATABASE_NAME > /tmp/backup.sql 120 121 pg_dump -h production-postgres -U user production > /tmp/backup.sql 122 ``` 123 1241. Once the backup dump is complete, exit the Kubernetes exec process with `Control-D` or `exit`. 125 1261. Download the dump file with the following command: 127 128 ```shell 129 kubectl cp --namespace "$APP_NAMESPACE" production-postgres-5db86568d7-qxlxv:/tmp/backup.sql backup.sql 130 ``` 131 132## Retain persistent volumes 133 134By default the [persistent 135volumes](https://kubernetes.io/docs/concepts/storage/persistent-volumes/) 136used to store the underlying data for PostgreSQL is marked as `Delete` 137when the pods and pod claims that use the volume is deleted. 138 139This is significant as, when you opt into the newer 8.2.1 PostgreSQL, the older 0.7.1 PostgreSQL is 140deleted causing the persistent volumes to be deleted as well. 141 142You can verify this by using the following command: 143 144```shell 145$ kubectl get pv 146NAME CAPACITY ACCESS MODES RECLAIM POLICY STATUS CLAIM STORAGECLASS REASON AGE 147pvc-0da80c08-5239-11ea-9c8d-42010a8e0096 8Gi RWO Delete Bound minimal-ruby-app-4349298-staging/staging-postgres standard 7d22h 148pvc-9085e3d3-5239-11ea-9c8d-42010a8e0096 8Gi RWO Delete Bound minimal-ruby-app-4349298-production/production-postgres standard 7d22h 149``` 150 151To retain the persistent volume, even when the older 0.7.1 PostgreSQL is 152deleted, we can change the retention policy to `Retain`. In this example, we find 153the persistent volume names by looking at the claims names. As we are 154interested in keeping the volumes for the staging and production of the 155`minimal-ruby-app-4349298` application, the volume names here are 156`pvc-0da80c08-5239-11ea-9c8d-42010a8e0096` and `pvc-9085e3d3-5239-11ea-9c8d-42010a8e0096`: 157 158```shell 159$ kubectl patch pv pvc-0da80c08-5239-11ea-9c8d-42010a8e0096 -p '{"spec":{"persistentVolumeReclaimPolicy":"Retain"}}' 160persistentvolume/pvc-0da80c08-5239-11ea-9c8d-42010a8e0096 patched 161$ kubectl patch pv pvc-9085e3d3-5239-11ea-9c8d-42010a8e0096 -p '{"spec":{"persistentVolumeReclaimPolicy":"Retain"}}' 162persistentvolume/pvc-9085e3d3-5239-11ea-9c8d-42010a8e0096 patched 163$ kubectl get pv 164NAME CAPACITY ACCESS MODES RECLAIM POLICY STATUS CLAIM STORAGECLASS REASON AGE 165pvc-0da80c08-5239-11ea-9c8d-42010a8e0096 8Gi RWO Retain Bound minimal-ruby-app-4349298-staging/staging-postgres standard 7d22h 166pvc-9085e3d3-5239-11ea-9c8d-42010a8e0096 8Gi RWO Retain Bound minimal-ruby-app-4349298-production/production-postgres standard 7d22h 167``` 168 169## Install new PostgreSQL 170 171WARNING: 172Using the newer version of PostgreSQL deletes 173the older 0.7.1 PostgreSQL. To prevent the underlying data from being 174deleted, you can choose to retain the [persistent volume](#retain-persistent-volumes). 175 176NOTE: 177You can also 178[scope](../../ci/environments/index.md#scope-environments-with-specs) the 179`AUTO_DEVOPS_POSTGRES_CHANNEL`, `AUTO_DEVOPS_POSTGRES_DELETE_V1` and 180`POSTGRES_VERSION` variables to specific environments, for example, `staging`. 181 1821. Set `AUTO_DEVOPS_POSTGRES_CHANNEL` to `2`. This opts into using the 183 newer 8.2.1-based PostgreSQL, and removes the older 0.7.1-based 184 PostgreSQL. 1851. Set `AUTO_DEVOPS_POSTGRES_DELETE_V1` to a non-empty value. This flag is a 186 safeguard to prevent accidental deletion of databases. 187 <!-- DO NOT REPLACE when upgrading GitLab's supported version. This is NOT related to GitLab's PostgreSQL version support, but the one deployed by Auto DevOps. --> 1881. If you have a `POSTGRES_VERSION` set, make sure it is set to `9.6.16` *or 189higher*. This is the 190 minimum PostgreSQL version supported by Auto DevOps. See also the list of 191 [tags available](https://hub.docker.com/r/bitnami/postgresql/tags). 1921. Set `PRODUCTION_REPLICAS` to `0`. For other environments, use 193 `REPLICAS` with an [environment scope](../../ci/environments/index.md#scope-environments-with-specs). 1941. If you have set the `DB_INITIALIZE` or `DB_MIGRATE` variables, either 195 remove the variables, or rename the variables temporarily to 196 `XDB_INITIALIZE` or the `XDB_MIGRATE` to effectively disable them. 1971. Run a new CI pipeline for the branch. In this case, we run a new CI 198 pipeline for `main`. 1991. After the pipeline is successful, your application is upgraded 200 with the new PostgreSQL installed. Zero replicas exist at this time, so 201 no traffic is served for your application (to prevent 202 new data from coming in). 203 204## Restore 205 2061. Get the pod name for the new PostgreSQL, in our example, the pod name is 207 `production-postgresql-0`: 208 209 ```shell 210 $ kubectl get pod --namespace "$APP_NAMESPACE" -l app=postgresql 211 NAME READY STATUS RESTARTS AGE 212 production-postgresql-0 1/1 Running 0 19m 213 ```` 214 2151. Copy the dump file from the backup steps to the pod: 216 217 ```shell 218 kubectl cp --namespace "$APP_NAMESPACE" backup.sql production-postgresql-0:/tmp/backup.sql 219 ``` 220 2211. Connect to the pod: 222 223 ```shell 224 kubectl exec -it production-postgresql-0 --namespace "$APP_NAMESPACE" -- bash 225 ``` 226 2271. Once connected to the pod, run the following command to restore the database. 228 229 - When asked for the database password, the default is `testing-password`. 230 - `USERNAME` is the username you have configured for PostgreSQL. The default is `user`. 231 - `DATABASE_NAME` is usually the environment name. 232 233 ```shell 234 ## Format is: 235 # psql -U USERNAME -d DATABASE_NAME < /tmp/backup.sql 236 237 psql -U user -d production < /tmp/backup.sql 238 ``` 239 2401. You can now check that your data restored correctly after the restore 241 is complete. You can perform spot checks of your data by using the 242 `psql`. 243 244## Reinstate your application 245 246Once you are satisfied the database has been restored, run the following 247steps to reinstate your application: 248 2491. Restore the `DB_INITIALIZE` and `DB_MIGRATE` variables, if previously 250 removed or disabled. 2511. Restore the `PRODUCTION_REPLICAS` or `REPLICAS` variable to its original value. 2521. Run a new CI pipeline for the branch. In this case, we run a new CI 253 pipeline for `main`. After the pipeline is successful, your 254 application should be serving traffic as before. 255