upgrading_postgresql.md 10.9 KB
Newer Older
1
---
2 3
stage: Configure
group: Configure
4
info: 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

9
Auto DevOps provides an [in-cluster PostgreSQL database](customize.md#postgresql-database-support)
10 11 12 13 14 15 16
for your application.

The version of the chart used to provision PostgreSQL:

- Is 0.7.1 in GitLab 12.8 and earlier.
- Can be set to from 0.7.1 to 8.2.1 in GitLab 12.9 and later.

Justin Adkins's avatar
Justin Adkins committed
17
GitLab encourages users to migrate their database to the newer PostgreSQL chart.
18 19 20 21 22 23 24 25 26 27 28 29

This guide provides instructions on how to migrate your PostgreSQL database, which
involves:

1. Taking a database dump of your data.
1. Installing a new PostgreSQL database using the newer version 8.2.1 of the chart
   and removing the old PostgreSQL installation.
1. Restoring the database dump into the new PostgreSQL.

## Prerequisites

1. Install
30
   [`kubectl`](https://kubernetes.io/docs/tasks/tools/).
31 32 33 34
1. Ensure that you can access your Kubernetes cluster using `kubectl`.
   This varies based on Kubernetes providers.
1. Prepare for downtime. The steps below include taking the application offline
   so that the in-cluster database does not get modified after the database dump is created.
35 36
1. Ensure you have not set `POSTGRES_ENABLED` to `false`, as this setting deletes
   any existing channel 1 database. For more information, see
37
   [Detected an existing PostgreSQL database](troubleshooting.md#detected-an-existing-postgresql-database).
38

39
NOTE:
40
If you have configured Auto DevOps to have staging,
41 42 43 44 45 46 47 48 49 50 51
consider trying out the backup and restore steps on staging first, or
trying this out on a review app.

## Take your application offline

If required, take your application offline to prevent the database from
being modified after the database dump is created.

1. Get the Kubernetes namespace for the environment. It typically looks like `<project-name>-<project-id>-<environment>`.
   In our example, the namespace is called `minimal-ruby-app-4349298-production`.

52
    ```shell
53 54 55 56 57 58 59 60
    $ kubectl get ns

    NAME                                                  STATUS   AGE
    minimal-ruby-app-4349298-production                   Active   7d14h
    ```

1. For ease of use, export the namespace name:

61
   ```shell
62 63 64 65 66
   export APP_NAMESPACE=minimal-ruby-app-4349298-production
   ```

1. Get the deployment name for your application with the following command. In our example, the deployment name is `production`.

67
    ```shell
68 69 70 71 72 73 74 75 76
    $ kubectl get deployment --namespace "$APP_NAMESPACE"
    NAME                  READY   UP-TO-DATE   AVAILABLE   AGE
    production            2/2     2            2           7d21h
    production-postgres   1/1     1            1           7d21h
    ```

1. To prevent the database from being modified, set replicas to 0 for the deployment with the following command.
   We use the deployment name from the previous step (`deployments/<DEPLOYMENT_NAME>`).

77
    ```shell
78 79 80 81
    $ kubectl scale --replicas=0 deployments/production --namespace "$APP_NAMESPACE"
    deployment.extensions/production scaled
    ```

82
1. You must also set replicas to zero for workers if you have any.
83 84 85 86 87

## Backup

1. Get the service name for PostgreSQL. The name of the service should end with `-postgres`. In our example the service name is `production-postgres`.

88
    ```shell
89 90 91 92 93 94 95 96
    $ kubectl get svc --namespace "$APP_NAMESPACE"
    NAME                     TYPE        CLUSTER-IP    EXTERNAL-IP   PORT(S)    AGE
    production-auto-deploy   ClusterIP   10.30.13.90   <none>        5000/TCP   7d14h
    production-postgres      ClusterIP   10.30.4.57    <none>        5432/TCP   7d14h
    ```

1. Get the pod name for PostgreSQL with the following command. In our example, the pod name is `production-postgres-5db86568d7-qxlxv`.

97
    ```shell
98 99 100 101 102 103 104
    $ kubectl get pod --namespace "$APP_NAMESPACE" -l app=production-postgres
    NAME                                   READY   STATUS    RESTARTS   AGE
    production-postgres-5db86568d7-qxlxv   1/1     Running   0          7d14h
    ```

1. Connect to the pod with:

105
    ```shell
106
    kubectl exec -it production-postgres-5db86568d7-qxlxv --namespace "$APP_NAMESPACE" -- bash
107 108 109 110 111 112 113 114
    ```

1. Once, connected, create a dump file with the following command.

   - `SERVICE_NAME` is the service name obtained in a previous step.
   - `USERNAME` is the username you have configured for PostgreSQL. The default is `user`.
   - `DATABASE_NAME` is usually the environment name.

115
   - When prompted for the database password, the default is `testing-password`.
116

117
    ```shell
118 119 120 121 122 123 124 125 126 127
    ## Format is:
    # pg_dump -h SERVICE_NAME -U USERNAME DATABASE_NAME > /tmp/backup.sql

    pg_dump -h production-postgres -U user production > /tmp/backup.sql
    ```

1. Once the backup dump is complete, exit the Kubernetes exec process with `Control-D` or `exit`.

1. Download the dump file with the following command:

128
    ```shell
129 130 131 132 133 134 135 136 137 138
    kubectl cp --namespace "$APP_NAMESPACE" production-postgres-5db86568d7-qxlxv:/tmp/backup.sql backup.sql
    ```

## Retain persistent volumes

By default the [persistent
volumes](https://kubernetes.io/docs/concepts/storage/persistent-volumes/)
used to store the underlying data for PostgreSQL is marked as `Delete`
when the pods and pod claims that use the volume is deleted.

139
This is significant as, when you opt into the newer 8.2.1 PostgreSQL, the older 0.7.1 PostgreSQL is
140 141 142 143
deleted causing the persistent volumes to be deleted as well.

You can verify this by using the following command:

144
```shell
145 146 147 148 149 150 151 152 153 154 155 156 157
$ kubectl get pv
NAME                                       CAPACITY   ACCESS MODES   RECLAIM POLICY   STATUS   CLAIM                                                     STORAGECLASS   REASON   AGE
pvc-0da80c08-5239-11ea-9c8d-42010a8e0096   8Gi        RWO            Delete           Bound    minimal-ruby-app-4349298-staging/staging-postgres         standard                7d22h
pvc-9085e3d3-5239-11ea-9c8d-42010a8e0096   8Gi        RWO            Delete           Bound    minimal-ruby-app-4349298-production/production-postgres   standard                7d22h
```

To retain the persistent volume, even when the older 0.7.1 PostgreSQL is
deleted, we can change the retention policy to `Retain`. In this example, we find
the persistent volume names by looking at the claims names. As we are
interested in keeping the volumes for the staging and production of the
`minimal-ruby-app-4349298` application, the volume names here are
`pvc-0da80c08-5239-11ea-9c8d-42010a8e0096` and `pvc-9085e3d3-5239-11ea-9c8d-42010a8e0096`:

158
```shell
159 160 161 162 163 164 165 166 167 168 169 170
$ kubectl patch pv  pvc-0da80c08-5239-11ea-9c8d-42010a8e0096 -p '{"spec":{"persistentVolumeReclaimPolicy":"Retain"}}'
persistentvolume/pvc-0da80c08-5239-11ea-9c8d-42010a8e0096 patched
$ kubectl patch pv  pvc-9085e3d3-5239-11ea-9c8d-42010a8e0096 -p '{"spec":{"persistentVolumeReclaimPolicy":"Retain"}}'
persistentvolume/pvc-9085e3d3-5239-11ea-9c8d-42010a8e0096 patched
$ kubectl get pv
NAME                                       CAPACITY   ACCESS MODES   RECLAIM POLICY   STATUS   CLAIM                                                     STORAGECLASS   REASON   AGE
pvc-0da80c08-5239-11ea-9c8d-42010a8e0096   8Gi        RWO            Retain           Bound    minimal-ruby-app-4349298-staging/staging-postgres         standard                7d22h
pvc-9085e3d3-5239-11ea-9c8d-42010a8e0096   8Gi        RWO            Retain           Bound    minimal-ruby-app-4349298-production/production-postgres   standard                7d22h
```

## Install new PostgreSQL

171
WARNING:
172
Using the newer version of PostgreSQL deletes
173
the older 0.7.1 PostgreSQL. To prevent the underlying data from being
174
deleted, you can choose to retain the [persistent volume](#retain-persistent-volumes).
175

176
NOTE:
177
You can also
Fiona Neill's avatar
Fiona Neill committed
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 182 183 184

1. Set `AUTO_DEVOPS_POSTGRES_CHANNEL` to `2`. This opts into using the
   newer 8.2.1-based PostgreSQL, and removes the older 0.7.1-based
   PostgreSQL.
185 186
1. Set `AUTO_DEVOPS_POSTGRES_DELETE_V1` to a non-empty value. This flag is a
   safeguard to prevent accidental deletion of databases.
187 188 189 190 191
   <!-- 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. -->
1. If you have a `POSTGRES_VERSION` set, make sure it is set to `9.6.16` *or
higher*. This is the
   minimum PostgreSQL version supported by Auto DevOps. See also the list of
   [tags available](https://hub.docker.com/r/bitnami/postgresql/tags).
192
1. Set `PRODUCTION_REPLICAS` to `0`. For other environments, use
Fiona Neill's avatar
Fiona Neill committed
193
   `REPLICAS` with an [environment scope](../../ci/environments/index.md#scope-environments-with-specs).
194 195 196 197
1. If you have set the `DB_INITIALIZE` or `DB_MIGRATE` variables, either
   remove the variables, or rename the variables temporarily to
   `XDB_INITIALIZE` or the `XDB_MIGRATE` to effectively disable them.
1. Run a new CI pipeline for the branch. In this case, we run a new CI
198
   pipeline for `main`.
199 200 201
1. After the pipeline is successful, your application is upgraded
   with the new PostgreSQL installed. Zero replicas exist at this time, so
   no traffic is served for your application (to prevent
202 203 204 205 206 207 208
   new data from coming in).

## Restore

1. Get the pod name for the new PostgreSQL, in our example, the pod name is
   `production-postgresql-0`:

209
    ```shell
210 211 212 213 214 215 216
    $ kubectl get pod --namespace "$APP_NAMESPACE" -l app=postgresql
    NAME                      READY   STATUS    RESTARTS   AGE
    production-postgresql-0   1/1     Running   0          19m
    ````

1. Copy the dump file from the backup steps to the pod:

217
   ```shell
218 219 220 221 222
   kubectl cp --namespace "$APP_NAMESPACE" backup.sql production-postgresql-0:/tmp/backup.sql
   ```

1. Connect to the pod:

223
   ```shell
224
   kubectl exec -it production-postgresql-0 --namespace "$APP_NAMESPACE" -- bash
225 226 227 228
   ```

1. Once connected to the pod, run the following command to restore the database.

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 232
   - `DATABASE_NAME` is usually the environment name.

233
   ```shell
234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252
   ## Format is:
   # psql -U USERNAME -d DATABASE_NAME < /tmp/backup.sql

   psql -U user -d production < /tmp/backup.sql
   ```

1. You can now check that your data restored correctly after the restore
   is complete. You can perform spot checks of your data by using the
   `psql`.

## Reinstate your application

Once you are satisfied the database has been restored, run the following
steps to reinstate your application:

1. Restore the `DB_INITIALIZE` and `DB_MIGRATE` variables, if previously
   removed or disabled.
1. Restore the `PRODUCTION_REPLICAS` or `REPLICAS` variable to its original value.
1. 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.