PostgreSQL: Major Upgrade in Kubernetes

In the following I show an example how you can upgrade an old PostgreSQL server to a new Major version running in a Kubernetes cluster. In this example I upgrade directly from 9.6.1 to 17.4. My deployment runs on Kubernetes and I have external data volumes bound to my servers based on a ceph system. The migration concept in short is the following:

1. Mount a new /backup/ volume to backup the data on the old databasesever
2. Backup the existing database with pg_dump
3. Undeploy your old PostgreSQL Server
4. Create a new deployment for the new empty Server and mount the /backup/ volume
5. Restore the backup with pg_restore

1) Mount a Backup Volume

First create a new Data Volume on your old server. A deployment on Kubernetes may look like this

# Deployment old PostgreSQL Server
apiVersion: apps/v1
kind: Deployment
metadata:
  name: postgres
  namespace: my-dbserver
  labels: 
    app: postgres
spec:
  replicas: 1
  selector: 
    matchLabels:
      app: postgres
  strategy:
    type: Recreate
  template:
    metadata:
      labels:
        app: postgres
    spec:
      containers:
      - env:
        - name: POSTGRES_DB
          value: mydb
        - name: POSTGRES_PASSWORD
          value: abc
        - name: POSTGRES_USER
          value: abc
        image: postgres:9.6.1
        name: postgres
        ports:
          - containerPort: 5432        
        volumeMounts:
        - mountPath: /var/lib/postgresql/data
          name: dbdata
          subPath: postgres
        # backup store
        - mountPath: /backup
          name: dbbackup
      restartPolicy: Always
      volumes:
      - name: dbdata_old
        persistentVolumeClaim:
          claimName: dbdata_old
      - name: dbbackup
        persistentVolumeClaim:
          claimName: dbbackup

Note that I bind a second data volume named ‘dbbackp’ monted on /backup/

2. Backup your Database

Next we can backup the database from the old Server. Open a shell on your old server and run:

$ pg_dump -v -p 5432 -U abc -d mydb -F c -f /backup/mydb.dump

This command will create a backup from the database ‘mydb’ into the file /backup/mydb.dump’. If the database is very large this can take a long time. In my case 12 hours.

3. Undeploy the old PostgreSQL Server

After the backup is finished, undeploy your old server. This is important to be able to mount the backup volume on the new one.

$ kubectl delete -f 01-postgresql_9_6.yaml 

4. Deploy a new empty PostgreSQL Server

Next deploy your new server with a new empty database, and again mount the same backup directory from before where you have created the dump file:

# Deployment new PostgreSQL Server
apiVersion: apps/v1
kind: Deployment
metadata:
  name: postgres
  namespace: my-dbserver
  labels: 
    app: postgres
spec:
  replicas: 1
  selector: 
    matchLabels:
      app: postgres
  strategy:
    type: Recreate
  template:
    metadata:
      labels:
        app: postgres
    spec:
      containers:
      - env:
        - name: POSTGRES_DB
          value: mydb
        - name: POSTGRES_PASSWORD
          value: abc
        - name: POSTGRES_USER
          value: abc
        image: postgres:17.4
        name: postgres
        ports:
          - containerPort: 5432        
        volumeMounts:
        - mountPath: /var/lib/postgresql/data
          name: dbdata_new
          subPath: postgres
        - mountPath: /backup
          name: dbbackup
      restartPolicy: Always
      volumes:
      - name: dbdata_new
        persistentVolumeClaim:
          claimName: dbdata_new
      - name: dbbackup
        persistentVolumeClaim:
          claimName: dbbackup

Note: I have named the data volumes ‘dbdata_old‘ and ‘dbdata_new‘. So each Postgre Server has its own data volume. This is good if something went wrong with the restore. Only the volume ‘dbbackup‘ is the same here. Make sure that you mount the dbbackup volume only to one of the two servers once a time. So it is important to undeploy the old postgre server before you can deploy the new one.

$ kubectl apply  -f 02-postgresql_17_4.yaml 

5. Restore the Data into the new Server

Open a shell on your new (empty) server and run

pg_restore -v -p 5432 -U abc -d mydb /backup/mydb.dump

Test you result.

This is a save procedure for a migration. You have made a backup and if something went totally wrong you can simply redeploy your old server.