AWX fails to upgrade DB when upgrading to 8.0.0+ -OR- How to upgrade your Docker Container based Postgres Database

While this example will show how I upgraded a Docker container based Postgres database for AWX, this process can be applied to any Docker container based Postgres database. The example is for upgrading Postgres 9.6 to 10, but it should work for any recent version of Postgres.

When upgrading Ansible AWX from version 7.0.0 to version 8.0.0, I recived the following error due to the change from Postgres 9.6 to 10:

   "stdout":"Performing Consistency Checks\n-----------------------------\nChecking cluster versions                                   ok\nChecking database user is the install user                  \ndatabase user \"awx\" is not the install user\nFailure, exiting",
   "stdout_lines":[  
      "Performing Consistency Checks",
      "-----------------------------",
      "Checking cluster versions                                   ok",
      "Checking database user is the install user                  ",
      "database user \"awx\" is not the install user",
      "Failure, exiting"

In order to solve the problem, I had to manually upgrade the Postgres database to version 10. Typically the upgrade from 9.6 to 10 is trivial using the pg_upgrade tool provided by Postgres. However, in a container based environment, the pg_upgrade tool may not be available or easily installed. Luckily, user tianon has created a postgres-upgrade Docker container to make the upgrade a simple process.

Below are the steps I used to manually upgrade Postgres to version 10:

First, create a new directory on the Docker host to store the Postgres 10 data files. If you have your Postgres 9.6 files in /mnt/pgdocker/9.6/ then I would recommend creating the /mnt/pgdocker/10/ directory. I will use those directories for this example.

Next, start the postgres-upgrade Docker container with the bash shell.

sudo docker run -it --rm \
      -v /mnt/pgdocker/9.6/data:/var/lib/postgresql/9.6/data \
      -v /mnt/pgdocker/10/data:/var/lib/postgresql/10/data \
      -e PGUSER=awx -e POSTGRES_INITDB_ARGS="-U awx" \
      tianon/postgres-upgrade:9.6-to-10  bash

Once you are at the bash prompt inside postgres-upgrade Docker container then become the postgres user.

su - postgres

Initialize the postgresql 10 database.

The upgrade of AWX failed because of issues with the awx user and character encoding. I did the initialization without the -U awx option because the next step kept failing because the "database user \"awx\" is not the install user" error. The next step also kept failing because of encoding errors, so I had to manually define UTF8. If you are not upgrading your database for AWX, you should consult the initdb documenation.

/usr/lib/postgresql/10/bin/initdb -E UTF8 --lc-collate=en_US.utf8 --lc-ctype=en_US.utf8 -D /var/lib/postgresql/10/data/

Finally I did the upgrade.

/usr/lib/postgresql/10/bin/pg_upgrade -b /usr/lib/postgresql/9.6/bin/ -B /usr/lib/postgresql/10/bin/ -d /var/lib/postgresql/9.6/data/ -D /var/lib/postgresql/10/data/

Now you should have an upgraded Postgres 10 database. You can exit the bash shell on the postgres-upgrade Docker container. Once you have verified everything is working you can also remove the/mnt/pgdocker/9.6/ directory.