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:
- Mount a new /backup/ volume to backup the data on the old databasesever
- Backup the existing database with
pg_dump
- Undeploy your old PostgreSQL Server
- Create a new deployment for the new empty Server and mount the /backup/ volume
- 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.