Upgrade PostgreSQL from 13 to 14 in a container

upgrade-postgresql-13-to-14-container.md

See the working PostgreSQL deployment SDL manifest for Akash Network in the comments section.

Upgrade PostgreSQL from 13 to 14 in a container

1. Start postgresql:14 container with sleep infinity entrypoint

with the data mounted over /var/lib/postgresql/data directory.

Make sure to either use PGDATA=/var/lib/postgresql/data/<some-other-dir> or rmdir /var/lib/postgresql/data/lost+found when having /var/lib/postgresql/data a mountpoint over a device (say Ceph's RBD), due to initdb not willing to work with the mountpoints (but can be tricked to by removing lost+found directory! :-)).
More here https://www.postgresql.org/message-id/flat/21269e094e35a1b32c426408cc0a4408%40nixaid.com#e4510c5b090c2737e68cfdcf8223020a

Example command for K8s:

kubectl -n $ns patch statefulset db --type=json -p='[{"op":"replace", "path":"/spec/template/spec/containers/0/image","value":"postgres:14"},{"op":"replace", "path":"/spec/template/spec/containers/0/command","value":["sleep", "infinity"]}]'

2. Check the PG version you are about to upgrade is of v13

root@db-0:~# gosu postgres sh -c "cd $PGDATA && cat PG_VERSION"
13

3. Install PostgreSQL 13

It will be used for upgrading from 13 to 14:

sed -i 's/$/ 13/' /etc/apt/sources.list.d/pgdg.list
apt update && apt install -y --no-install-recommends postgresql-13

4. Migrate the data

Export the following environment variables:

export PGBINOLD=/usr/lib/postgresql/13/bin
export PGBINNEW=/usr/lib/postgresql/14/bin
export PGDATAOLD=$PGDATA/../data.old
export PGDATANEW=$PGDATA

$PGDATAOLD should be a different directory from the $PGDATA as initdb expects the latter to be empty.

WARNING: Make sure the $PGDATAOLD has enough disk space before proceeding.

Move the current data to a different directory:

mkdir $PGDATAOLD
chown postgres $PGDATAOLD
chmod 700 $PGDATAOLD
gosu postgres sh -c "cd $PGDATA && mv -vi * .??* $PGDATAOLD"

Initialize PostgreSQL 14 data:

# PostgreSQL 14 defaults to "scram-sha-256" which breaks the authentication
# If you were upgrading from the previous versions:
# - FATAL:  password authentication failed for user "postgres"
# Hence, let's preserve the "md5" unless the user was setting a different
# auth method before the postgresql 14.
# "scram-sha-256" is better, but requires you resetting each password:
# https://www.cybertec-postgresql.com/en/from-md5-to-scram-sha-256-in-postgresql/

export POSTGRES_HOST_AUTH_METHOD=${POSTGRES_HOST_AUTH_METHOD:-md5}
timeout 5s /usr/local/bin/docker-entrypoint.sh postgres

Upgrade PostgreSQL 13 to 14:

gosu postgres bash -c "cd /var/lib/postgresql && /usr/lib/postgresql/14/bin/pg_upgrade"

if pg_upgrade fails, then try to chmod 700 "$PGDATAOLD" "$PGDATANEW"; chown -R postgres "$PGDATAOLD" "$PGDATANEW" and repeat the upgrade command again.

Delete old PostgreSQL 13 data:

It is good to ensure you have a backup first in case if the upgrade to 14 fails.

rm -rf $PGDATAOLD

Exit the container:

exit

5. Start postgresql:14 with default entrypoint

and with the data mounted over /var/lib/postgresql/data directory as in the 1st step.

Example command for K8s:

kubectl -n $ns patch statefulset db --type=json -p='[{"op":"replace", "path":"/spec/template/spec/containers/0/image","value":"postgres:14"},{"op":"remove", "path":"/spec/template/spec/containers/0/command"}]'

Working PostgreSQL deployment SDL manifest for Akash Network

TL;DR you have to set PGDATA=<mountpoint>/<something> environment variable, where the mountpoint cannot be /var/lib/postgresql due to 1 & 2.
There is also a historical reason for why PGDATA cannot be a mountpoint directly - mainly for preventing an issue where postgresql can overwrite the good pg_control file if the postgresql was started with the unmounted PGDATA mountpoint device and had the other DB in that path (when someone either initdb before mounting the device over the PGDATA or the postgresql starting script runs initdb when finds PGDATA is empty) and have then stopped the DB with the PGDATA backing device mounted over back again.
IMHO, this is very rare, however, the postgresql devs claim there are horror stories about such cases in the PG community mailing list archives, dating from before they installed the don't-use-a-mount-point defenses in initdb.
That defense works this simple fail-safe way, where one would be mounting an upper path (i.e. $PGDATA/../.) to make initdb fail to initialize and postgresql fail to start - both due to a missing PGDATA unless the upper directory is mounted with the PGDATA present in it (be it empty or not).

---
version: "2.0"

services:
  db:
    image: postgres:14
    # It is recommended to set this environment variable:
    #   - PGDATA=/var/lib/postgresql/data/<some-dir>
    # with the storage mountpoint /var/lib/postgresql/data.
    #
    # You can probably also get around by having the mountpoint over `/srv/pg` with `PGDATA=/srv/pg/data` as well,
    # or any other path for the mountpoint than `/var/lib/postgresql` and `PGDATA=<mountpoint>/<something>`.
    #
    # If you wish to use /var/lib/postgresql/data (PGDATA) as a mountpoint,
    # then make sure to remove $PGDATA/lost+found directory for the "initdb" not
    # to detect it as a mountpoint as it exits otherwise.
    #
    # The reason behind is:
    # https://www.postgresql.org/message-id/flat/21269e094e35a1b32c426408cc0a4408%40nixaid.com#e4510c5b090c2737e68cfdcf8223020a
    #
    # WARNING: Do NOT mount /var/lib/postgresql directly, otherwise
    # /var/lib/postgresql/data (PGDATA) will be lost on the container restart!
    # - https://github.com/docker-library/postgres/blob/3c20b7bdb915ecb1648fb468ab53080c58bb1716/Dockerfile-debian.template#L184
    # - https://github.com/docker-library/postgres/issues/404#issuecomment-773755801
    #
    ## Workaround for /var/lib/postgresql/data mountpoint
    ## with the PGDATA set to the same path (default).
    #command:
    #  - "sh"
    #  - "-c"
    #args:
    #  - "rmdir -v /var/lib/postgresql/data/lost+found >/dev/null 2>&1;
    #     exec /usr/local/bin/docker-entrypoint.sh postgres"
    env:
      - POSTGRES_PASSWORD=adminpass
      #- PGDATA=/var/lib/postgresql/data/pgdata1
    params:
      storage:
        pgdata:
          mount: /var/lib/postgresql/data
    expose:
      # postgresql port
      - port: 5432
        as: 5432
        to:
          - global: true

profiles:
  compute:
    db:
      resources:
        cpu:
          units: 1.0
        memory:
          size: 1Gi
        storage:
          - size: 1Gi
          - name: pgdata
            size: 10Gi
            attributes:
              persistent: true
              class: beta3
  placement:
    akash:
      attributes:
        host: akash
      signedBy:
        anyOf:
          - "akash1365yvmc4s7awdyj3n2sav7xfx76adc6dnmlx63"
      pricing:
        db:
          denom: uakt
          amount: 10000

deployment:
  db:
    akash:
      profile: db
      count: 1