Upgrading Postgres

Package Security Manager versions 6.6.1 and older utilize Postgres version 9.6 for new installations. However, as of November 2021 Postgres 9.6 has reached its end of life (EOL). Package Security Manager version 6.6.2 and later utilize Postgres version 14.9 for new installations. If you have upgraded to Package Security Manager 6.6.2 or later, Anaconda recommends upgrading your version of Postgres manually. Package Security Manager is tested and known to be compatible and stable with this version of Postgres.

Log in to your instance of Package Security Manager as a user with root access.
Open your Package Security Manager installer directory by running the following command. This directory contains both the docker-compose.yml and .env files.
You can find your installer directory by running the ls -la command to view the contents of your current working directory.
# Replace <INSTALLER_DIRECTORY> with your base installer directory cd <INSTALLER_DIRECTORY>
Verify you are running Postgres 9.6 by running the following command:
docker ps
Stop all services by running the following command:
docker compose down
Restart the Postgres container by running the following command:
docker compose up -d postgres

Export your Postgres database by running the following command:

docker exec -i postgres pg_dumpall -U postgres > dump.sql

Using your preferred file editor, open your .env file and update or add the following lines:
POSTGRES_VERSION=14.9 POSTGRES_MAJOR=14
Save your work and close the file.
Restart Postgres by running the following command:
docker-compose up -d postgres
Import all data by running the following command:
cat dump.sql | docker exec -i postgres psql -U postgres
A log of commands executed is displayed.

Update the password for Keycloak users by running the following command:

docker exec -it -ePASSWORD=$(cat .env | grep KEYCLOAK_DB_PASSWORD | cut -d '=' -f2 | tr -d '\n' | tr -d "'") postgres sh -c 'psql -U postgres -c "ALTER USER keycloak WITH PASSWORD '\''$PASSWORD'\''";'

Update the password for Postgres users by running the following command:

docker exec -it -ePASSWORD=$(cat .env | grep POSTGRES_URI | grep -oP '(?<=://postgres:).*(?=@postgres)' | tr -d '\n') postgres sh -c 'psql -U postgres -c "ALTER USER postgres WITH PASSWORD '\''$PASSWORD'\''";'

Restart the containers by running the following command:
docker compose up -d
Allow the containers to come back online then verify you are using Postgres version 14.9 by running the following command:
docker ps