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