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.

Upgrading Postgres will bring down your Package Security Manager instance for a short time.

1. Log into your instance of Package Security Manager as a user with root access.

2. 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.

   ---

   Type ate-installer and then press tab to autocomplete.

   # Replace <INSTALLER_DIRECTORY> with your base installer directory
   cd <INSTALLER_DIRECTORY>
   

3. Verify you are running Postgres 9.6 by running the following command:

   docker ps
   

   Take note of the Postgres container ID and save it for later use.

4. Stop all services by running the following command:

   docker compose down
   

5. Restart the Postgres container by running the following command:

   docker compose up --detach postgres
   

6. Export your Postgres database by running the following command:

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

7. Using your preferred file editor, open your .env file and update or add the following lines:

   POSTGRES_VERSION=14.9
   POSTGRES_MAJOR=14
   

8. Save your work and close the file.

9. Stop and remove the old Postgres container by running the following command:

   # Replace <CONTAINER_ID> with your running postgres container ID
   docker stop <CONTAINER_ID>
   docker rm postgres
   docker ps
   

10. Delete the old Postgres data directory by running the following command:

    rm -rf /opt/anaconda/repo/state/postgresql/data
    

    Removing the old 9.6 data directory avoids compatibility issues when starting Postgres 14.

11. Using your preferred file editor, open your docker-compose.yml file.

12. Find the Postgres image reference:

    image: ${DOCKER_REGISTRY}postgres:9.6
    

13. Update the Postgres image reference:

    image: ${DOCKER_REGISTRY}postgres:${POSTGRES_MAJOR}
    

14. Restart Postgres by running the following commands:

    docker-compose up -d postgres
    docker ps
    

15. Import all data by running the following command:

    cat dump.sql |  docker exec -i postgres psql -U postgres
    

    This imports the data from the backup into the new Postgres database. A log of commands executed is displayed.

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

    PASSWORD=$(grep KC_DB_PASSWORD docker-compose.yml | cut -d '=' -f2 | tr -d '\n') docker exec -it postgres psql -U postgres -c "ALTER USER keycloak WITH PASSWORD '${PASSWORD}';"
    

17. Update the password for Postgres superuser by running the following command:

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

18. Restart the containers by running the following command:

    docker compose up --detach
    

19. Allow the containers to come back online then verify you are using Postgres version 14.9 by running the following command:

    docker ps