Postgresql version upgrade script for dcape

pg-upgrade_v1.1.sh

#!/bin/sh

# Postgresql version upgrade script for dcape
# Uses tianon/postgres-upgrade docker image
# See also:
# * https://github.com/tianon/docker-postgres-upgrade
# * https://dopos.github.io/dcape/

# Restart after upgrade
# * dcape-app-pg-backup

set -euo pipefail

# Postgresql versions
OLD=13
NEW=15
NEW_IMAGE="postgres:15.2"

# Upgrading image
UPGRADE_IMAGE=tianon/postgres-upgrade:${OLD}-to-${NEW}

# ------------------------------------------------------------------------------
# Vars from dcape's .env

# Filter and load vars from .env
TEMP_VARS=.env.tmp$$
grep -E "^(PG_ENCODING|TZ|DRONE_VAR_PATH|DCAPE_TAG|PG_IMAGE)=" .env > $TEMP_VARS
. $TEMP_VARS
rm $TEMP_VARS

ROOT=$DRONE_VAR_PATH
# Postgresql Database encoding
PG_ENCODING=en_US.utf8

PG_CONTAINER=${DCAPE_TAG}_db_1

# ------------------------------------------------------------------------------
# code

pause() {
  read -n1 -s -p "$@ [Press Enter to confirm or s to skip]" s
  [[ -z $s ]] || s=" Skipped"
  echo $s >&2
  echo $s
}

echo "Running PG upgrade script"
echo "Dcape ROOT: $ROOT"
echo "Upgrading PG from $OLD ($PG_IMAGE) to $NEW ($NEW_IMAGE)"
echo "------------------------------------------"

[[ -z $(pause "1. pull docker image") ]]     && docker pull $UPGRADE_IMAGE && docker pull $NEW_IMAGE
[[ -z $(pause "2. stop dcape") ]]            && make down || echo "WARNING! There are unstopped containers. Restart them if needed"
[[ -z $(pause "3. archive database") ]]      && time tar -czvf $ROOT/db-backup.tar.gz -C $ROOT/db/data .

#CMD=--check
CMD=

[[ -z $(pause "4. run pg_upgrade") ]]        && time docker run --rm \
    -v $ROOT/db/data:/var/lib/postgresql/$OLD/data \
    -v $ROOT/db-${NEW}/data:/var/lib/postgresql/$NEW/data \
    -e TZ="$TZ" \
    -e LANG=$PG_ENCODING \
    $UPGRADE_IMAGE \
    pg_upgrade $CMD

[[ -z $(pause "5. patch pg config") ]]       && sudo sed -i "s/#shared_preload_libraries = ''/shared_preload_libraries = 'pg_stat_statements'/"  $ROOT/db-${NEW}/data/postgresql.conf
[[ -z $(pause "6. patch pg_hba") ]]          && sudo echo "host all all all md5" >> $ROOT/db-${NEW}/data/pg_hba.conf
[[ -z $(pause "7. rotate pg data dirs") ]]   && sudo mv $ROOT/db $ROOT/db-${OLD} && sudo mv $ROOT/db-${NEW} $ROOT/db
[[ -z $(pause "8. patch .env") ]]            && sudo sed -i "s/PG_IMAGE=$PG_IMAGE/PG_IMAGE=$NEW_IMAGE/" .env
[[ -z $(pause "9. start pg container") ]]    && make dc CMD="up -d db"
[[ -z $(pause "10. update DB stats") ]]      && docker exec -it $PG_CONTAINER gosu postgres /usr/lib/postgresql/15/bin/vacuumdb --all --analyze-in-stages

SQL=$(cat <<EOF
-- https://stackoverflow.com/a/61915608

SELECT format(
          'ALTER EXTENSION %I UPDATE;',
          extname
       )
FROM pg_extension \gexec

EOF
)

[[ -z $(pause "11. update DB extensions") ]] && echo $SQL | docker exec -i $PG_CONTAINER psql -U postgres
[[ -z $(pause "12. start dcape services") ]] && make up
echo "All done."