How to upgrade postgresql inside a Kubernetes pod?

Question

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?

Answer 1

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

Answer 2

Thanks to @justadev for the answer. Some additions:

psql -U postgres -d keycloak -W -f dumpall.sql

I had to add the -d keycloak database flag because while the psql log was OK during the import, the data was missing in the database afterwards. You need to explicitly indicate the database for psql.

So, check the psql flags here: https://www.postgresql.org/docs/current/app-psql.html

By the way, I managed to upgrade from Postgres 11 to Posgres 14.5 this way.

Also, I want to add this: tar may be absent on a pod, this means that kubectl cp will not work.

Here is the workaround:

1. Copy data from a pod to a local machine:

kubectl exec -n ${namespace} ${postgresql_pod} -- cat db_dump.sql  > db_dump.sql

2. Copy data from a local machine to a pod:

cat db_dump.sql | kubectl exec -i -n ${namespace} ${postgresql_pod} "--" sh -c "cat > db_dump.sql"

Answer 3

Using this How to upgrade postgresql database from 10 to 12 without losing data for openproject 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:

3. 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

4. Start the postgresql service

     service postgresql start

5. Log in as postgres user

     su postgres

6. Check your new postgres version

     psql -c "SELECT version();"

7. Run the generated new cluster script

     ./analyze_new_cluster.sh

8. 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

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

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