> ## Documentation Index
> Fetch the complete documentation index at: https://anaconda.com/docs/llms.txt
> Use this file to discover all available pages before exploring further.

# Upgrading Postgres

Package Security Manager versions `6.6.1` and older utilize Postgres version `9.6` for new installations. However, [as of November 2021](https://endoflife.date/postgresql) 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.

Danger

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

1. Log in to 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.

   > [!TIP]
   >
   > 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.

   ```sh theme={null}
   # Replace <INSTALLER_DIRECTORY> with your base installer directory
   cd <INSTALLER_DIRECTORY>
   ```

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

   ```sh theme={null}
   docker ps
   ```

   > [!NOTE]
   >
   > Take note of the Postgres container ID and save it for later use.

4. Stop all services by running the following command:

   ```sh theme={null}
   docker compose down
   ```

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

   ```sh theme={null}
   docker compose up --detach postgres
   ```

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

   ```sh theme={null}
   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:

   ```sh theme={null}
   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:

   ```sh theme={null}
   # 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:

    ```sh theme={null}
    rm -rf /opt/anaconda/repo/state/postgresql/data
    ```

    > [!NOTE]
    >
    > 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:

    ```sh theme={null}
    image: ${DOCKER_REGISTRY}postgres:9.6
    ```

13. Update the Postgres image reference:

    ```sh theme={null}
    image: ${DOCKER_REGISTRY}postgres:${POSTGRES_MAJOR}
    ```

14. Restart Postgres by running the following commands:

    ```sh theme={null}
    docker-compose up -d postgres
    docker ps
    ```

15. Import all data by running the following command:

    ```sh theme={null}
    cat dump.sql |  docker exec -i postgres psql -U postgres
    ```

    > [!NOTE]
    >
    > 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:

    ```sh theme={null}
    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:

    ```sh theme={null}
    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:

    ```sh theme={null}
    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:

    ```sh theme={null}
    docker ps
    ```