K
Q

How to upgrade postgresql inside a Kubernetes pod?

11/15/2020

I have a kubernetes cluster running an app. Part of the cluster is a postgresql pod, currently running version 10.4. Unfortunately, I discovered that I need to upgrade the postgresql version.

The postgres yaml is as follow:

apiVersion: apps/v1
kind: Deployment
metadata:
  name: postgres
spec:
  replicas: 1
  selector:
    matchLabels:
      app: postgres
  template:
    metadata:
      labels:
        app: postgres
    spec:
      containers:
        - name: postgres
          image: postgres:10.4
          imagePullPolicy: "IfNotPresent"
          ports:
            - containerPort: 5432
          envFrom:
            - configMapRef:
                name: postgres-config
          volumeMounts:
            - mountPath: /var/lib/postgresql/data
              name: postgredb
      volumes:
        - name: postgredb
          persistentVolumeClaim:
            claimName: postgres-pv-claim

The postgresql database already has some data in it. I need to find a way to upgrade the cluster while in production.

If I simply try to change the image to 12.0 and run kubectl apply I get an error:

2020-11-15 22:48:08.332 UTC [1] DETAIL:  The data directory was initialized by PostgreSQL version 10, which is not compatible with this version 12.5 (Debian 12.5-1.pgdg100+1).

So I understand that I need to manually upgrade the postgres database inside the cluster, and only then I will be able to fix the yaml. Is that correct?

-- justadev
kubernetes
postgresql

Similar Questions

How to manage multiple kubernetes clusters via Terraform?
Is there any way to turn off eviction in kubelet?
Synchronizing deploys to separate servers for full-stack applications
Ingress TLS routes with cert-manager not applied
Blue Green Deployment with Helm Charts

2 Answers

11/16/2020

Using this https://stackoverflow.com/questions/60409585/how-to-upgrade-postgresql-database-from-10-to-12-without-losing-data-for-openpro as the basis for my post. I'm converting it to a container-with-volume friendly approach. I am assuming you're using the official Postgresql image on Docker Hub.

  1. Backup the data - Out of scope for this answer. There are other people better suited to answering that question.

  2. Upgrade postgres from inside the pod and migrate the data Get a shell in your postgres pod

# insert your pod and namespace here
kubectl exec -it postgresl-shsdjkfshd -n default /bin/sh

Run the following inside the container

apt update
apt-get install postgresql-12 postgresql-server-dev-12
service postgresql stop
# Migrate the data
su postgres
/usr/lib/postgresql/12/bin/pg_upgrade \
     --old-datadir=/var/lib/postgresql/10/main \
     --new-datadir=/var/lib/postgresql/12/main \
     --old-bindir=/usr/lib/postgresql/10/bin \
     --new-bindir=/usr/lib/postgresql/12/bin \
     --old-options '-c config_file=/etc/postgresql/10/main/postgresql.conf' \
     --new-options '-c config_file=/etc/postgresql/12/main/postgresql.conf'
exit # exits the postgres user

The next bit is verbatim taken from the linked post:

  1. Swap the ports the old and new postgres versions.
     vim /etc/postgresql/12/main/postgresql.conf
     #change port to 5432
     vim /etc/postgresql/10/main/postgresql.conf
     #change port to 5433
  1. Start the postgresql service
     service postgresql start
  1. Log in as postgres user
     su postgres
  1. Check your new postgres version
     psql -c "SELECT version();"
  1. Run the generated new cluster script
     ./analyze_new_cluster.sh
  1. Return as a normal(default user) user and cleanup up the old version's mess
     apt-get remove postgresql-10 postgresql-server-dev-10
     #uninstalls postgres packages
     rm -rf /etc/postgresql/10/
     #removes the old postgresql directory
     su postgres
     #login as postgres user
     ./delete_old_cluster.sh
     #delete the old cluster data

  1. Now change the deployment YAML image reference to the Postgres 12 and kubectl apply

  2. Check the logs to see if it started up correctly.

-- Justin Tamblyn
Source: StackOverflow
11/16/2020

I tried @Justin method, but I encountered an issue that I couldn't stop current running postgres process inside the pod (for some reason inside the container there is no access to postgresql service. You can see more about that issue here)

Since I couldn't upgrade the postgresql specifically inside the pod, what I did at the end is creating a parallel postgres pod in Kubernetes which holds the new version. Then I dumped database from old server, copied it to the new server, and used it to initialize the database there.

Here are the steps one by one:

1) Create a parallel postgres service with the new version

2) In old version pod:

pg_dumpall -U postgresadmin -h localhost -p 5432 > dumpall.sql

3) In the host:

kubectl cp postgres-old-pod:/dumpall.sql dumpall.sql
kubectl cp dumpall.sql postgres2-new-pod:/dumpall.sql

4) ssh to new-pod

5) extra step that I needed, becuase for some reason new pod didn't had 'postgres' user created: get into postgres client using your credentials:

psql postgresql://postgresadmin:pass1234@127.0.0.1:5432/postgresdb?sslmode=disable
postgresdb=# CREATE ROLE postgres LOGIN SUPERUSER PASSWORD 'somepassword123';

then exit postgres and exit to normal user

6) Finally update the database:

psql -U postgres -W -f dumpall.sql
-- justadev
Source: StackOverflow