[bitnami/postgresql] Upgrade postgres 11 to 12

[qdupuy]

Hello,

I am upgrading a postgres server via helm charts but when I switch to postgres version 12, I get the following error :

2021-11-04 14:25:03.639 GMT [1] DETAIL:  The data directory was initialized by PostgreSQL version 11, which is not compatible with this version 12.0.

And when I type the following command pg_upgrade -U postgres to update the schemas and the database I get this:

could not look up effective user ID 1001: user does not exist
Failure, exiting
command terminated with exit code 1

I would like to avoid creating another instance for the postgres will be in version 12 if possible

[juan131]

Hi @qdupuy

I recommend to upgrade your release updating the image to the latest 12 version and also enabling the "diagnostic mode", find more info about this mode below:

• https://github.com/bitnami/charts/tree/master/bitnami/postgresql#common-parameters

This way, you can entere into the PostgreSQL 12 container and upgrade the database schema. Once you complete the schema upgrade, you can upgrade your Helm release again but this time disabling the diagnostic mode.

[qdupuy]

I've already tried this mode and it still doesn't work

[juan131]

Could you please share (step by step) the exact command you're using? I'd like to reproduce your exact issue in my local environment. Thanks in advance.

[qdupuy]

Create a postgres 12 instance, then I take the version 13 and I launch pg_upgrade by putting the pods with "sleep infinity" so that it does not crash

[juan131]

Hi @qdupuy

Please provide as many details as possible. I know it's a pain to describe all the environment you're using but the more information I have, the more helpful I can be:

• Are you upgrading from 11 to 12? Or are you upgrading from 12 to 13? You mentioned both scenarios on different comments.
• What version of the chart did you use the 1st time you installed the chart? What values did you use to install the chart the 1st time (before upgrading PostgreSQL)?
• Did you populate your PostgreSQL database before upgrading?
• What version of the chart did you use when you upgraded your chart release? What values did you use to upgraded the chart switching to a more recent version?
• Could you share the whole command you used when running the "pg_upgrade" action?

[qdupuy]

Hello,

The scheme I want to make:

Migrate to version 12 and then to 13 starting from version 11

The last helm chart was used.

Basically, there was no values.yaml file and I put one in to configure the postgres as I wanted

Yes, the database is populated.

This is the command I typed when I wanted to do the 'pg_upgrade' command :

k -n databases exec -ti postgres-postgresql-0 -- pg_upgrade -U postgres

[juan131]

Hi @qdupuy

Here's what I did trying to reproduce the issue. I hope it's useful for you:

• Install the chart using 11.x and write some data:

$ helm install postgresql bitnami/postgresql --set image.tag=11.13.0-debian-10-r89
(...)
$ kubectl get pods
NAME                      READY   STATUS    RESTARTS   AGE
postgresql-postgresql-0   1/1     Running   0          64s
$ kubectl exec -it postgresql-postgresql-0 -- bash
I have no name!@postgresql-postgresql-0:/$ postgres --version
postgres (PostgreSQL) 11.13
I have no name!@postgresql-postgresql-0:/$ PGPASSWORD=$POSTGRES_PASSWORD psql --host 127.0.0.1 -U postgres -d postgres -p 5432
postgres=# CREATE TABLE IF NOT EXISTS app_user (
postgres(#   username varchar(45) NOT NULL,
postgres(#   password varchar(450) NOT NULL,
postgres(#   enabled integer NOT NULL DEFAULT '1',
postgres(#   PRIMARY KEY (username)
postgres(# );
CREATE TABLE
postgres=# exit

Upgrade to 12.x enabling diagnostic mode (and running the container as root) and upgrade the schema:

$ export POSTGRES_PASSWORD=$(kubectl get secret --namespace default postgresql -o jsonpath="{.data.postgresql-password}" | base64 --decode)
$ helm upgrade postgresql bitnami/postgresql \
    --set image.tag=12.8.0-debian-10-r90 \
    --set containerSecurityContext.runAsUser=0 \
    --set diagnosticMode.enabled=true \
    --set postgresqlPassword=$POSTGRESQL_PASSWORD
(...)
$ kubectl get pods
NAME                      READY   STATUS    RESTARTS   AGE
postgresql-postgresql-0   1/1     Running   0          38s
$ kubectl exec -it postgresql-postgresql-0 -- bash
root@postgresql-postgresql-0:/# postgres --version
postgres (PostgreSQL) 12.8
root@postgresql-postgresql-0:/# . /opt/bitnami/scripts/libos.sh
root@postgresql-postgresql-0:/# ensure_group_exists postgres -i 1001
root@postgresql-postgresql-0:/# ensure_user_exists postgres -i 1001 -g postgres
root@postgresql-postgresql-0:/tmp# mv /bitnami/postgresql/data /bitnami/postgresql/olddata
root@postgresql-postgresql-0:/tmp# mkdir -p /bitnami/postgresql/data mkdir -p /bitnami/postgresql/oldbin
root@postgresql-postgresql-0:/tmp# chown -R postgres:postgres /bitnami/postgresql/data /bitnami/postgresql/olddata
root@postgresql-postgresql-0:/# cd /tmp/
root@postgresql-postgresql-0:/# curl --remote-name --silent https://downloads.bitnami.com/files/stacksmith/postgresql-11.13.0-14-linux-amd64-debian-10.tar.gz
root@postgresql-postgresql-0:/# tar --extract --directory /bitnami/postgresql/oldbin/ --file postgresql-11.13.0-14-linux-amd64-debian-10.tar.gz --strip-components=4 postgresql-11.13.0-14-linux-amd64-debian-10/files/postgresql/bin
root@postgresql-postgresql-0:/tmp# gosu postgres initdb -E UTF8 -D /bitnami/postgresql/data -U postgres
(...)
root@postgresql-postgresql-0:/tmp# gosu postgres pg_upgrade -c -b /bitnami/postgresql/oldbin -B /opt/bitnami/postgresql/bin -d /bitnami/postgresql/olddata -D /bitnami/postgresql/data
(...)

[robbo10]

We are having the same issue but not using helm, how can we set "diagnosticMode" without helm as when I change the image to be 12 for example the pod just goes into a CrashLoop due to

The data directory was initialized by PostgreSQL version 11, which is not compatible with this version 12.8.

[qdupuy]

@juan131

Thank you, however, in the meantime I have mounted another instance instead of doing an upgrade.

I think it's a pity that this kind of handling is not in the documentation.

[robbo10]

@qdupuy - do you mind listing the steps which you took? :)

[qdupuy]

@robbo10

I'm in a Kubernetes environment.

However, I couldn't do what he said due to time constraints, I had to deliver to my client quickly. So I created a postgres 13 instance next to it and did a pg_dumpall to integrate it with the new instance

[juan131]

Hi everyone,

Please note that you're trying to use pg_upgrade, a tool that's not intended for ephemeral environments such as containers & kubernetes. This tool assumes you have 2 installation (one with the old version, and one with the new version) and you migrate your data from the old one to the new one. However, that's not true in this container environment, you only have 1 installation.

In my previous response, I shared some steps about how to try to reproduce having an environment similar to the one expected by pg_upgrade but please note this approach should be considered a "hacky workaround" and it's very likely to fail.

[robbo10]

@juan131 - thanks for the reply, if this were a production k8s environment with TB of data would you have any recommendation for the upgrade aside from pg_dump ? Thank you :)

[juan131]

Hi @robbo10, I'd try to follow these steps:

• Cordon the production database (or at least ensure there are no write actions).
• Backup the production database.
• Move the backup to a development environment.
• Perform the db upgrade in the dev environment.
• If everything goes well, backup the upgraded database in the dev environment.
• Upgrade the production release enabling diagnostic mode.
• Recover the upgraded database backup in the production environment.
• Upgrade the release disabling the diagnostic mode.

Does it make sense?

[github-actions]

This Issue has been automatically marked as "stale" because it has not had recent activity (for 15 days). It will be closed if no further activity occurs. Thanks for the feedback.

[github-actions]

Due to the lack of activity in the last 5 days since it was marked as "stale", we proceed to close this Issue. Do not hesitate to reopen it later if necessary.

[bitemarcz]

Hello, when I try to run the commands to do an upgrade I'm having an issue with passwords error? I really don't understand why this is coming up when my yaml file is using postgresqlPassword?

helm upgrade dai-psql-settest-ironman bitnami/postgresql --set image.tag=14.5.0-debian-11-r6 --set containerSecurityContext.runAsUser=0 --set diagnosticMode.enabled=true --set postgresqlPasword=$POSTGRESQL_PASSWORD -n daicos-settest-ironman Error: UPGRADE FAILED: execution error at (postgresql/templates/secrets.yaml:17:24): PASSWORDS ERROR: The secret "dai-psql-settest-ironman-postgresql" does not contain the key "postgres-password"

A little more contact, I'm currently working on upgrades from 10.16.1 of helm chart version to the latest helm chart version 11.8.1 in order to upgrade the postgres application to version 14.5.0. One of the applications I support requires the newest version of Postgres to be installed but running into all kinds of issues upgrading. I did try dumping the backup deploying the newer version and restoring the backup to the new instance but ran into an issue with my pods connecting to the new database now... trying this method to see if this works but already running into problems with this weird password error and makes no sense.

[felipecrs]

I did not try, but this may help:

https://github.com/tianon/docker-postgres-upgrade

[nhoangduy1912]

Hi @qdupuy

Here's what I did trying to reproduce the issue. I hope it's useful for you:

• Install the chart using 11.x and write some data:

$ helm install postgresql bitnami/postgresql --set image.tag=11.13.0-debian-10-r89
(...)
$ kubectl get pods
NAME                      READY   STATUS    RESTARTS   AGE
postgresql-postgresql-0   1/1     Running   0          64s
$ kubectl exec -it postgresql-postgresql-0 -- bash
I have no name!@postgresql-postgresql-0:/$ postgres --version
postgres (PostgreSQL) 11.13
I have no name!@postgresql-postgresql-0:/$ PGPASSWORD=$POSTGRES_PASSWORD psql --host 127.0.0.1 -U postgres -d postgres -p 5432
postgres=# CREATE TABLE IF NOT EXISTS app_user (
postgres(#   username varchar(45) NOT NULL,
postgres(#   password varchar(450) NOT NULL,
postgres(#   enabled integer NOT NULL DEFAULT '1',
postgres(#   PRIMARY KEY (username)
postgres(# );
CREATE TABLE
postgres=# exit

Upgrade to 12.x enabling diagnostic mode (and running the container as root) and upgrade the schema:

$ export POSTGRES_PASSWORD=$(kubectl get secret --namespace default postgresql -o jsonpath="{.data.postgresql-password}" | base64 --decode)
$ helm upgrade postgresql bitnami/postgresql \
    --set image.tag=12.8.0-debian-10-r90 \
    --set containerSecurityContext.runAsUser=0 \
    --set diagnosticMode.enabled=true \
    --set postgresqlPassword=$POSTGRESQL_PASSWORD
(...)
$ kubectl get pods
NAME                      READY   STATUS    RESTARTS   AGE
postgresql-postgresql-0   1/1     Running   0          38s
$ kubectl exec -it postgresql-postgresql-0 -- bash
root@postgresql-postgresql-0:/# postgres --version
postgres (PostgreSQL) 12.8
root@postgresql-postgresql-0:/# . /opt/bitnami/scripts/libos.sh
root@postgresql-postgresql-0:/# ensure_group_exists postgres -i 1001
root@postgresql-postgresql-0:/# ensure_user_exists postgres -i 1001 -g postgres
root@postgresql-postgresql-0:/tmp# mv /bitnami/postgresql/data /bitnami/postgresql/olddata
root@postgresql-postgresql-0:/tmp# mkdir -p /bitnami/postgresql/data mkdir -p /bitnami/postgresql/oldbin
root@postgresql-postgresql-0:/tmp# chown -R postgres:postgres /bitnami/postgresql/data /bitnami/postgresql/olddata
root@postgresql-postgresql-0:/# cd /tmp/
root@postgresql-postgresql-0:/# curl --remote-name --silent https://downloads.bitnami.com/files/stacksmith/postgresql-11.13.0-14-linux-amd64-debian-10.tar.gz
root@postgresql-postgresql-0:/# tar --extract --directory /bitnami/postgresql/oldbin/ --file postgresql-11.13.0-14-linux-amd64-debian-10.tar.gz --strip-components=4 postgresql-11.13.0-14-linux-amd64-debian-10/files/postgresql/bin
root@postgresql-postgresql-0:/tmp# gosu postgres initdb -E UTF8 -D /bitnami/postgresql/data -U postgres
(...)
root@postgresql-postgresql-0:/tmp# gosu postgres pg_upgrade -c -b /bitnami/postgresql/oldbin -B /opt/bitnami/postgresql/bin -d /bitnami/postgresql/olddata -D /bitnami/postgresql/data
(...)

Hi @juan131

In your steps, where can I find the files for postgresql 14 and 15? I'm trying to upgrade from 14 to 15

root@postgresql-postgresql-0:/# curl --remote-name --silent https://downloads.bitnami.com/files/stacksmith/postgresql-11.13.0-14-linux-amd64-debian-10.tar.gz
root@postgresql-postgresql-0:/# tar --extract --directory /bitnami/postgresql/oldbin/ --file postgresql-11.13.0-14-linux-amd64-debian-10.tar.gz --strip-components=4 postgresql-11.13.0-14-linux-amd64-debian-10/files/postgresql/bin

Also there's no curl command in the pod.

[javsalgar]

We removed curl for security reasons. You may need to import the tarball using kubectl cp

[github-actions]

Due to the lack of activity in the last 5 days since it was marked as "stale", we proceed to close this Issue. Do not hesitate to reopen it later if necessary.

[quorak]

I managed it to migrate from postgres 11 to 15 with the bitnami container. Sharing my painful steps here!

To operate in peace I found it quite helpful to run the postgres statefulset with a inifint sleep to have a running container.
Therefore edit the stateful set

    command:
    - /bin/sh
    - '-c'
    args:
    - sleep infinity
    readinessProbe: {} 
    livenessProbe: {} 

Lets start with the operation, connect a shell to the container and pg_update

# lets work in /tmp
cd /tmp/

# Download binaries of your old installation.
# I found this line as quite helpful to identify the url of your version
# https://github.com/bitnami/containers/blob/2ab7f4b1fc3ae7f8c0c18a38e4ec078ccd0c2d89/bitnami/postgresql/15/debian-11/Dockerfile#L35
# Following line for debian 11 with postgresql 11
# OLD SCRIPT: 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-11.20.0-11-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, "$FILE_NAME"); print $f $_ while <$s>; close $f'
# Update script
perl -e 'use IO::Socket::INET; use IO::File; my $url="downloads.bitnami.com"; my $file_path="/files/stacksmith/postgresql-11.20.0-11-linux-amd64-debian-11.tar.gz"; my $filename="postgresql-11.20.0-11-linux-amd64-debian-11.tar.gz"; my $socket = IO::Socket::INET->new(PeerHost => $url, PeerPort => 80, Proto => "tcp") or die "Cannot connect to $url: $!"; print $socket "GET $file_path HTTP/1.0\r\nHost: $url\r\n\r\n"; my $fh = IO::File->new($filename, "w") or die "Cannot open file $filename: $!"; while (my $line = <$socket>) { last if $line =~ /^\r\n$/; } while (my $line = <$socket>) { print $fh $line; } $fh->close; $socket->close;'
mkdir /bitnami/postgresql/oldbin/
tar --extract --directory /bitnami/postgresql/oldbin/ --file postgresql-11.20.0-11-linux-amd64-debian-11.tar.gz --strip-components=4 postgresql-11.20.0-linux-amd64-debian-11/files/postgresql/bin

# copy old datadir
/opt/bitnami/scripts/postgresql/entrypoint.sh mv /bitnami/postgresql/data /bitnami/postgresql/olddata

# 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

# 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

# Check consistency fist
# 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

# 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

remove the command line from the statefulset and the postgres should start as usual, with your old data.
Cleaup the olddata dir and the oldbin dir
done

[antonmatsiuk]

I've managed to run migration as @quorak has described above with several small adjustments:

1. Before migration set readinessProbe: {} and livenessProbe: {} otherwise the container will restart
2. POSTGRES_DB must be unset in the Helm Release, otherwise migration script will complain about the present database.
3. perl script to download the binary didn't work for me. I have simply downloaded it from the Bitnami repo postgresql-11.20.0-11-linux-amd64-debian-11.tar.gz and copied into the container with kubectl cp to /tmp/ folder
4. /bitnami/postgresql/olddata/postgresql.conf was missing in my case, I had to execute 2 additional commands :

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

5. "# Check consistency fist" command in the original instructions misses -c flag, be careful with that!
6. Do not forget to remove old data after the migration is done and the Helm release is upgraded: rm -rf /bitnami/postgresql/olddata/

[felipecrs]

It would be so much simpler, and less error prone, if the Helm chart had a flag to do all this.

[dopic]

Hi, I'm trying to upgrade from version 12.7 to version 13.12 using the method suggested by @quorak and @antonmatsiuk, but everytime I get this:

"Performing Consistency Checks
-----------------------------
Checking cluster versions                                   ok

The source cluster was not shut down cleanly.
Failure, exiting"

What I have done until now

• Running version 13.12 in Kubernetes
• Copied postgres bin folder from version 12.7 running in docker (mount a volume called "temp" and copy it to host)
• Copied the bin 12.7 from host to tmp folder in 13.12 running in Kubernetes (with kubectl cp)

Any suggestions?

Thank you

[dopic]

Hi, for future reference, I solved this problem with the instructions in this thread.

I don't have a large database, so It was easy to migrate the data, and, as an advantage, is a non-destructive approach.

The steps that I executed was:

• I created a new helm release with the new version of postgresql
• I ran the following command in order to migrate the data

kubectl exec -it <NEW_POSTGRES_POD_NAME> -n <NEW_POSTGRES_NAMESPACE> -- /opt/bitnami/scripts/postgresql/entrypoint.sh /bin/bash -c 'export PGPASSWORD="<OLD_POSTGRES_PASSWORD>"; echo "local all postgres trust" > /opt/bitnami/postgresql/conf/pg_hba.conf; pg_ctl reload; time pg_dumpall -h <OLD_POSTGRES_HOST> -U postgres | psql -U postgres’

• And finally, I restarted the new pod to restore the pg_hba.conf file to its original state.

Hope this helps.