Skip to content

Instantly share code, notes, and snippets.

@hugo4715
Forked from wvengen/upgrade-pg.py
Created March 12, 2024 12:31
Show Gist options
  • Save hugo4715/0fd87713797e774048054d7f84d6be67 to your computer and use it in GitHub Desktop.
Save hugo4715/0fd87713797e774048054d7f84d6be67 to your computer and use it in GitHub Desktop.
Major upgrade PostgreSQL on cloudnative-pg
#!/usr/bin/env python3
#
# Helper script to do major version upgrades of a cloudnative-pg based
# PostgreSQL cluster.
#
# Usage: python3 upgrade-pg.py <cluster-name> <new-pg-version>
#
# Example: python3 upgrade-pg.py my-backend-db 16.0
#
# Make sure to include major and minor version in the pg version.
# Error handling is quite basic, when you're asked to check if the
# new database is ok, do so by checking if the relevant data is there.
# When in doubt, don't continue, as you could suffer data loss (especially
# if you have no backups configured).
#
# This script does the following:
# 1. Create a temporary cluster with the new PostgreSQL version
# with data from the source cluster.
# 2. After confirmation, delete the original cluster (so we can replace it).
# 3. Create a new source cluster from the temporary cluster
# 4. After confirmation, delete the temporary cluster.
#
# Backups are disabled for the new cluster, as the new PostgreSQL version
# probably needs a new storage location, because the file formats may not
# be compatible.
#
# You need to make sure any subsequent infrastructure changes use the upgraded
# PostgreSQL version, and configure backup. Then take a new base backup.
#
# Note that the final cluster manifest needs some cleaning up yet.
#
# _NOTE:_ tested only a little, take care!
#
#
# Changelog:
# 20230918 initial version
#
import json
from subprocess import run
from random import randint
from time import sleep
def kubectl_get(kind, name):
'''Return Kubernetes manifest for cluster'''
r = run(['kubectl', 'get', kind, name, '-o', 'json'], check=True, capture_output=True)
return json.loads(r.stdout)
def kubectl_create(manifest):
'''Create Kubernetes resource from manifest'''
run(['kubectl', 'create', '-f', '-'], check=True, input=manifest, text=True, capture_output=True)
def kubectl_delete(kind, name):
'''Delete Kubernets resource'''
run(['kubectl', 'delete', kind, name], check=True)
def kubectl_wait_cluster_ready(name, reverse=False):
'''Wait until the cloudnative-pg cluster is ready'''
ready_instances = 0
while (not reverse and ready_instances == 0) or (reverse and ready_instances > 0):
sleep(5)
r = run(['kubectl', 'get', 'cluster', name, '-o', 'go-template={{ .status.readyInstances }}'], check=True, capture_output=True)
s = r.stdout.decode().strip()
if s and s != '<no value>': ready_instances = int(s)
class ClusterTemplate:
def __init__(self, manifest):
self.manifest = manifest
@property
def name(self):
return self.manifest.get('metadata', {}).get('name')
@name.setter
def name(self, value):
self.manifest.get('metadata', {})['name'] = value
@property
def spec(self):
return self.manifest.get('spec', {})
@property
def database(self):
return self.spec['bootstrap']['initdb']['database']
@property
def owner(self):
return self.spec['bootstrap']['initdb']['owner']
@property
def secret(self):
return self.spec['bootstrap']['initdb']['secret']['name']
@property
def imageName(self):
return self.spec.get('imageName')
@imageName.setter
def imageName(self, value):
self.spec['imageName'] = value
@property
def imageVersion(self):
return self.imageName.split(':', 1)[-1]
@imageVersion.setter
def imageVersion(self, value):
name, version = self.imageName.split(':', 1)
self.imageName = name + ':' + value
def delete_backup(self):
if 'backup' in self.spec:
self.spec.pop('backup')
def import_from(self, name, database, user, secret):
cluster_name = database + '-' + str(randint(0, 999999))
# add external cluster for specified database
if not self.spec.get('externalClusters'): self.spec['externalClusters'] = []
self.spec['externalClusters'].append({
'name': cluster_name,
'connectionParameters': {
'host': name + '-r',
'user': user,
'dbname': database
},
'password': {
'name': secret,
'key': 'password'
}
})
# indicate to import from specified database
self.spec['bootstrap']['initdb']['import'] = {
'type': 'microservice',
'databases': [database],
'source': { 'externalCluster': cluster_name }
}
if __name__ == '__main__':
import re
import sys
name, new_version = sys.argv[1:3]
new_name = name + '-' + re.sub(r'\.\d+$', '', new_version)
print('Retrieving existing cluster template')
template = ClusterTemplate(kubectl_get('cluster', name))
template.name = new_name
template.imageVersion = new_version
template.delete_backup()
template.import_from(name, template.database, template.owner, template.secret)
print('Creating temporary upgraded database')
kubectl_create(json.dumps(template.manifest))
kubectl_wait_cluster_ready(new_name)
s = input('Temporary upgraded database ready. Is it in order, continue and delete the source database? [y/n]')
if s.lower() != 'y': sys.exit()
print('Deleting source database (as to replace it)')
kubectl_delete('cluster', name)
kubectl_wait_cluster_ready(name, reverse=True)
print('Re-creating source database')
template.name = name
template.import_from(new_name, template.database, template.owner, template.secret)
kubectl_create(json.dumps(template.manifest))
kubectl_wait_cluster_ready(name)
s = input('Final upgraded database ready. Is it in order, continue and delete the temporary database? [y/n]')
if s.lower() != 'y': sys.exit()
print('Deleting temporary upgraded database')
kubectl_delete('cluster', new_name)
print('Done!')
print('Remember to update your deployment configuration:')
print('- set PostgreSQL version to ' + new_version)
print('- use a new backup destination (als WALs will not be compatible)')
print('- create a new base backup')
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment