Support for postgres version upgrade using Bitnami chart

[evegufy]

I'm attempting to execute a major postgres version upgrade (from 14 to 15) for the containerized image from Bitnami.

Up to now, there doesn't exist an official documentation on how to upgrade those images:
bitnami/charts#8025
bitnami/charts#14926

Therefore, I used a combination of the following two comments as guidance:
bitnami/charts#8025 (comment)
bitnami/charts#8025 (comment)

Unfortunately, in the end, I'm always facing this error in step at step 8 of the list below:
The source cluster was not shut down cleanly.

I assume because I'm not doing the following step 2 (from bitnami/charts#8025 (comment)) correctly:
"POSTGRES_DB must be unset in the Helm Release, otherwise migration script will complain about the present database."
I'd next look into bitnami/charts#8025 (comment)

Here the step that I'm executing:

1. enable diagnosticMode for postgres dependency

2. unset POSTGRES_DATABASE

unset POSTGRES_DATABASE

3. Download binaries of your old installation.

cd /tmp/

perl -e 'use IO::Socket::INET; my $s = IO::Socket::INET->new(PeerAddr=>"downloads.bitnami.com", PeerPort=>80, Proto=>"tcp"); print $s "GET /files/stacksmith/postgresql-14.2.0-0-linux-amd64-debian-11.tar.gz HTTP/1.0\r\nHost: downloads.bitnami.com\r\n\r\n"; while(<$s>) { last if $_ eq "\r\n" }; open(my $f, ">", "postgresql-14.2.0-0-linux-amd64-debian-11.tar.gz") or die "Could not open file: $!"; print $f $_ while <$s>; close $f; close $s;' 

mkdir /bitnami/postgresql/oldbin/

tar --extract --directory /bitnami/postgresql/oldbin/ --file postgresql-14.2.0-0-linux-amd64-debian-11.tar.gz --strip-components=4 postgresql-14.2.0-linux-amd64-debian-11/files/postgresql/bin

4. copy old datadir and create postgres.conf and trusted access file for old

/opt/bitnami/scripts/postgresql/entrypoint.sh mv /bitnami/postgresql/data /bitnami/postgresql/olddata
cp /bitnami/postgresql/olddata/postgresql.auto.conf /bitnami/postgresql/olddata/postgresql.conf
echo "local all postgres trust" > /bitnami/postgresql/olddata/pg_hba.conf

5. Initialize new cluster

/opt/bitnami/scripts/postgresql/entrypoint.sh /opt/bitnami/scripts/postgresql/setup.sh
/opt/bitnami/scripts/postgresql/entrypoint.sh chmod 700 /bitnami/postgresql/olddata
/opt/bitnami/scripts/postgresql/entrypoint.sh chmod 700 /bitnami/postgresql/data

6. Create postgres.conf and trusted access file

cp /bitnami/postgresql/data/postgresql.auto.conf /bitnami/postgresql/data/postgresql.conf
echo "local all postgres trust" > /bitnami/postgresql/data/pg_hba.conf

7. Avoid running into 'FATAL: lock file "postmaster.pid" already exists' at step 8

rm /bitnami/postgresql/olddata/postmaster.pid 

8. Check consistency fist

/opt/bitnami/scripts/postgresql/entrypoint.sh pg_upgrade  -b /bitnami/postgresql/oldbin -B /opt/bitnami/postgresql/bin -d /bitnami/postgresql/olddata -D /bitnami/postgresql/data

9. If it says the versions are compatible, run the same command without -c and it will copy the data

/opt/bitnami/scripts/postgresql/entrypoint.sh pg_upgrade  -b /bitnami/postgresql/oldbin -B /opt/bitnami/postgresql/bin -d /bitnami/postgresql/olddata -D /bitnami/postgresql/data -c

10. Delete pg_hba.conf and postgresql.conf so the container versions are used on reboot

rm /bitnami/postgresql/data/postgresql.conf
rm /bitnami/postgresql/data/pg_hba.conf

Can you please support me for this issue?

Figuring out how to execute a postgres version upgrade for the containerized image from Bitnami could be beneficial to multiple products, not just for portal, as multiple products are using it as dependency.

[FaGru3n]

FYI @evegufy we moved this to our general tasks for system team to find together a way for updates

[evegufy]

@FaGru3n next week we'll test a blue green deployment approach, just fyi as it may be affecting this issue.

[almadigabor]

This comment worked for me.

According to the Postgresql documentation:

Because pg_dump is used to transfer data to newer versions of PostgreSQL, the output of pg_dump can be expected to load into PostgreSQL server versions newer than pg_dump's version. pg_dump can also dump from PostgreSQL servers older than its own version. (Currently, servers back to version 8.0 are supported.) However, pg_dump cannot dump from PostgreSQL servers newer than its own major version; it will refuse to even try, rather than risk making an invalid dump. Also, it is not guaranteed that pg_dump's output can be loaded into a server of an older major version — not even if the dump was taken from a server of that version. Loading a dump file into an older server may require manual editing of the dump file to remove syntax not understood by the older server. Use of the --quote-all-identifiers option is recommended in cross-version cases, as it can prevent problems arising from varying reserved-word lists in different PostgreSQL versions.

1. I've created a chart with 2 dependencies (bitnami charts with postgres versions 14 and 15).
2. Created a table and 1 row in the pod for version 14.
3. Ran this command: kubectl exec -it portal-postgres-new-0 -n portal-postgres -- /opt/bitnami/scripts/postgresql/entrypoint.sh /bin/bash -c 'export PGPASSWORD="asd"; echo "local all postgres trust" > /opt/bitnami/postgresql/conf/pg_hba.conf; pg_ctl reload; time pg_dumpall -h 10.42.0.106 -U postgres | psql -U postgres' where 10.42.0.106 was the cluster IP of the old version database.
4. Disabled the old postgres version in the chart
5. Scaled the version 15 pod to 0 and back to 1 to restore the pg_hba.conf file to its original state.
6. Queried the new database and got my table with the inserted row.

# Chart.yaml
apiVersion: v2
name: portal-postgres
description: A Helm chart for Kubernetes
type: application

version: 0.1.0
appVersion: "1.16.0"

dependencies:
  - condition: postgresql.enabled
    name: postgresql
    repository: https://charts.bitnami.com/bitnami
    version: 11.9.13 # postgres 14
  - condition: newpostgresql.enabled
    name: postgresql
    repository: https://charts.bitnami.com/bitnami
    alias: newpostgresql
    version: 12.12.10 # postgres 15

# values.yaml
postgresql:
  enabled: false
  auth:
    database: "postgres"
    port: 5432
    postgresPassword: "asd"
newpostgresql:
  enabled: true
  fullnameOverride: portal-postgres-new
  auth:
    database: "postgres"
    port: 5432
    postgresPassword: "asd"

Let's try this tomorrow in a session.

[almadigabor]

A new follow-up issue is created to document the process so I would close this one.