ansible / awx

AWX provides a web-based user interface, REST API, and task engine built on top of Ansible. It is one of the upstream projects for Red Hat Ansible Automation Platform.

Other

14.08k stars 3.43k forks source link

AWX fails to upgrade DB when upgrading to 8.0.0+ #5214

Open ybizeul opened 5 years ago

ybizeul commented 5 years ago

ISSUE TYPE

Bug Report

SUMMARY

AWX fails to upgrade DB when upgrading to 8.0.0

ENVIRONMENT

AWX version: 8.0.0
AWX install method: docker
Ansible version: 2.8.5
Operating System: CentOS 7.7
Web Browser: Safari

STEPS TO REPRODUCE

I have a pre-existing version of AWX, installed around 1.x, and upgraded to each major release from 2.x to 7.x

Now when upgrading to 8.0.0, the following error in thrown :

{  
   "changed":true,
   "cmd":"docker run --rm  -v /var/lib/pgdocker/pgdata:/var/lib/postgresql/9.6/data  -v /var/lib/pgdocker/10/data:/var/lib/postgresql/10/data  -e PGUSER=awx -e POSTGRES_INITDB_ARGS=\"-U awx\"  tianon/postgres-upgrade:9.6-to-10 --username=awx\n",
   "delta":"0:00:18.502387",
   "end":"2019-11-02 23:15:58.742763",
   "msg":"non-zero return code",
   "rc":1,
   "start":"2019-11-02 23:15:40.240376",
   "stderr":"Unable to find image 'tianon/postgres-upgrade:9.6-to-10' locally\nTrying to pull repository docker.io/tianon/postgres-upgrade ... \n9.6-to-10: Pulling from docker.io/tianon/postgres-upgrade\n80369df48736: Already exists\nb18dd0a6efec: Already exists\n5c20c5b8227d: Already exists\nc5a7f905c8ec: Already exists\n5a3f55930dd8: Already exists\nffc097878b09: Already exists\n3106d02490d4: Already exists\n88d1fc513b8f: Already exists\nf19250dffc5e: Pulling fs layer\n756351b7a443: Pulling fs layer\n0d24b08575ba: Pulling fs layer\n31babd3be108: Pulling fs layer\n16f2724dc303: Pulling fs layer\n69802daaa561: Pulling fs layer\n438bfdc24a14: Pulling fs layer\nfa98d6d574b2: Pulling fs layer\n5ad633827764: Pulling fs layer\n6a047a7ec007: Pulling fs layer\n31babd3be108: Waiting\n16f2724dc303: Waiting\n69802daaa561: Waiting\n438bfdc24a14: Waiting\nfa98d6d574b2: Waiting\n5ad633827764: Waiting\n6a047a7ec007: Waiting\n0d24b08575ba: Verifying Checksum\n0d24b08575ba: Download complete\n756351b7a443: Verifying Checksum\n756351b7a443: Download complete\n31babd3be108: Verifying Checksum\n31babd3be108: Download complete\n16f2724dc303: Verifying Checksum\n16f2724dc303: Download complete\nf19250dffc5e: Verifying Checksum\nf19250dffc5e: Download complete\n69802daaa561: Verifying Checksum\n69802daaa561: Download complete\n438bfdc24a14: Verifying Checksum\n438bfdc24a14: Download complete\nfa98d6d574b2: Verifying Checksum\nfa98d6d574b2: Download complete\n6a047a7ec007: Verifying Checksum\n6a047a7ec007: Download complete\n5ad633827764: Verifying Checksum\n5ad633827764: Download complete\nf19250dffc5e: Pull complete\n756351b7a443: Pull complete\n0d24b08575ba: Pull complete\n31babd3be108: Pull complete\n16f2724dc303: Pull complete\n69802daaa561: Pull complete\n438bfdc24a14: Pull complete\nfa98d6d574b2: Pull complete\n5ad633827764: Pull complete\n6a047a7ec007: Pull complete\nDigest: sha256:15e1a8cd6426d8f312f29c973d48ff98b246a1392756495f819bbcc9e84080c6\nStatus: Downloaded newer image for docker.io/tianon/postgres-upgrade:9.6-to-10",
   "stderr_lines":[  
      "Unable to find image 'tianon/postgres-upgrade:9.6-to-10' locally",
      "Trying to pull repository docker.io/tianon/postgres-upgrade ... ",
      "9.6-to-10: Pulling from docker.io/tianon/postgres-upgrade",
     [...]
      "Digest: sha256:15e1a8cd6426d8f312f29c973d48ff98b246a1392756495f819bbcc9e84080c6",
      "Status: Downloaded newer image for docker.io/tianon/postgres-upgrade: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"
   ]
}

EXPECTED RESULTS

Database should upgrade successfully

ACTUAL RESULTS

Database upgrade fails

Commifreak commented 5 years ago

What is the „install user“ on your machine? Root?

It seems, that database user has to equal the system user, which installs awx.

This requirement seem to come from the pgsql image and not from the awx installer.

Correct me if Iam wrong

ybizeul commented 5 years ago

It has always been installed as container, so in the end it has to be an awx issue in the packaging. Unfortunately I'm no expert in PostgreSQL, but I was able to check that in version 9, user awx is one of the superuser of the database. I'm almost sure that initdb during the upgrade is performed with awx so I'm not sure what to do now.

Commifreak commented 5 years ago

The Errors are from the running postgre container not from AWX. At least as far as I can see.

The main issue is the upgrade from PGSql 9.6 to 10 which needs an awx-db upgrade as well and the consistency check of pgsql raises this error.

https://stackoverflow.com/questions/41854387/database-user-postgres-is-not-the-install-user

The best is to wait until the awx team provides a fix as the run command comes from the awx playbook.

shanemcd commented 5 years ago

Did you specify a different user for pg_username between versions? I dont believe the default value has ever changed. It is possible this is a bug that we haven't seen before, but this is the first time we are hearing about it.

thibveni commented 5 years ago

i have exactly the same issue, using awx in docker container mode since more than 1 year, classic installation on centos 7.

ybizeul commented 5 years ago

When I open the database from a 9.6 container and execute a few commands I feel might be useful, here is what I get :

postgres@4c20c2119589:~$ psql --user awx
psql (9.6.15)
Type "help" for help.

awx=# \l
                                 List of databases
   Name    |  Owner   | Encoding |  Collate   |   Ctype    |   Access privileges   
-----------+----------+----------+------------+------------+-----------------------
 awx       | awx      | UTF8     | en_US.utf8 | en_US.utf8 | 
 postgres  | postgres | UTF8     | en_US.utf8 | en_US.utf8 | 
 template0 | postgres | UTF8     | en_US.utf8 | en_US.utf8 | =c/postgres          +
           |          |          |            |            | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.utf8 | en_US.utf8 | =c/postgres          +
           |          |          |            |            | postgres=CTc/postgres
(4 rows)

awx=# \du
                                   List of roles
 Role name |                         Attributes                         | Member of 
-----------+------------------------------------------------------------+-----------
 awx       | Superuser                                                  | {}
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}

awx=# \dt
                                List of relations
 Schema |                          Name                          | Type  | Owner 
--------+--------------------------------------------------------+-------+-------
 public | auth_group                                             | table | awx
 public | auth_group_permissions                                 | table | awx
 public | auth_permission                                        | table | awx
 public | auth_user                                              | table | awx
[...]

That's the very same database that wouldn't upgrade with the method packaged in AWX.

thibveni commented 5 years ago

i still have the same issue when trying to upgrade to 9.0.1 release

Here is my error: "stdout_lines": [ "The files belonging to this database system will be owned by user \"postgres\".", "This user must also own the server process.", "", "The database cluster will be initialized with locale \"en_US.utf8\".", "The default database encoding has accordingly been set to \"UTF8\".", "The default text search configuration will be set to \"english\".", "", "Data page checksums are disabled.", "", "fixing permissions on existing directory /var/lib/postgresql/10/data ... ok", "creating subdirectories ... ok", "selecting default max_connections ... 100", "selecting default shared_buffers ... 128MB", "selecting default timezone ... Etc/UTC", "selecting dynamic shared memory implementation ... posix", "creating configuration files ... ok", "running bootstrap script ... ok", "performing post-bootstrap initialization ... ok", "syncing data to disk ... ok", "", "Success. You can now start the database server using:", "", " pg_ctl -D /var/lib/postgresql/10/data -l logfile start", "", "Performing Consistency Checks", "-----------------------------", "Checking cluster versions ok", "Checking database user is the install user ", "database user \"awx\" is not the install user", "Failure, exiting"

ybizeul commented 5 years ago

i still have the same issue when trying to upgrade to 9.0.1 release

Just to be thorough, you're not upgrading to 9.x but to 10.x right ?

thibveni commented 5 years ago

i still have the same issue when trying to upgrade to 9.0.1 release

Just to be thorough, you're not upgrading to 9.x but to 10.x right ?

i'm talking about 9.0.1 release from https://github.com/ansible/awx/releases still having the issue after doing a git pull from this release

ybizeul commented 5 years ago

What is your current version ?

thibveni commented 5 years ago

version

AWX 7.0.0

ybizeul commented 5 years ago

Ok Makes sense then. I suppose you'd have the same issue going to just 8.x

Anyways, it doesn't seem that one is getting any attention, I guess AWX 7 is just fine !

ybizeul commented 4 years ago

That's the heart of the issue. AWX 9+ has a mechanism in place to upgrade to PostgreSQL 10, but it doesn't work. That's what we're trying to get a fix for in this thread...

Commifreak commented 4 years ago

I just upgraded from 7.0.0 to 9.0.1 today without any problems. Just FYI. The upgrade step completes successfully.

pg_username and pg_database in inventory are set to awx

thibveni commented 4 years ago

i still have the error :/ database user "awx" is not the install user

"pg_username and pg_database in inventory are set to awx" , same as you...

thibveni commented 4 years ago

ok so i've succesfully updated awx, here are my changes

` docker exec -i -t awx_postgres /bin/bash

psql awx awx

update pg_authid set rolname = 'XXX' where oid = 10; UPDATE 1 update pg_authid set rolname = 'postgres' where oid = 16385; UPDATE 1 update pg_authid set rolname = 'awx' where oid = 10; UPDATE 1

CADbMkNMMwPGkuktp0AYzK0YJXmDDRtuUpD8F4x0Y387nWPP%3Dyg%40mail.gmail.com)

ALTER DATABASE awx OWNER TO awx;

REASSIGN OWNED BY postgres TO awx;

ALTER USER awx WITH PASSWORD 'PasswordFromKeepass';

\du List of roles Role name | Attributes | Member of -----------+------------------------------------------------------------+----------- awx | Superuser, Create role, Create DB, Replication, Bypass RLS | {} postgres | Superuser

troubleshoot

docker logs -f awx_task `

i had to reassign password and then to reboot my server to make it success

I now hope there will no side effets from what i've done, i also hope next upgrade will works fine, and maybe all these will help others in the same situation

thibveni commented 4 years ago

i rolled back with my virtual machine snapshot because i had some unexplainable issues :(

nicolaibaralmueller commented 4 years ago

Adding driveletter to the mount point solved my issue. This should be added to the AWX code as default.

docker run --rm -v /opt/pgdocker/pgdata:/var/lib/postgresql/9.6/data:Z -v /opt/pgdocker/pgdatanew:/var/lib/postgresql/10/data:Z -e PGUSER=awx -e POSTGRES_INITDB_ARGS="-U awx tianon/postgres-upgrade:9.6-to-10 --username=awx

kevincoakley commented 4 years ago

I had to manually upgrade the database in order to upgrade from 7.0.0 to 8.0.0+.

Here are the manual steps I ran:

Start the postgres-upgrade docker container with a bash shell.

sudo docker run -it --rm \
      -v /mnt/tower/pgdocker/pgdata:/var/lib/postgresql/9.6/data \
      -v /mnt/tower/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

Become the postgres user.

su - postgres

Initialize the postgresql 10 database. 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.

/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/

Before running the ansible install.yml, I modified installer/roles/local_docker/tasks/main.yml and disabled - name: Upgrade Postgres and - name: Remove old data directory, just in case.

I have since upgrade awx to 9.1.0 and have not had any errors. I renamed the old pgdata directory in postgres_data_dir to prevent future upgrades from trying to migrate from 9.6 to 10. I have been running awx since June 2018, and I believe, continuously on the same database. I assume this might be something that only affects older installations.

ybizeul commented 4 years ago

@kevincoakley Great work and clear explanations.

I was able to pass 8.0.0 as well

Be careful to remove old pg data directory once the upgrade is confirmed and working or it'll try again with 9.1.1

Gaby033 commented 4 years ago

Hello, thank you @kevincoakley for your step by step instructions. Unfortunately, I'm facing the same issue. I'm currently on AWX 6.1.0.0, i'm trying to upgrade to the latest available which is 9.2.0 currently.

Here are my steps :

git checkout tags/9.2.0
mkdir /root/upgradepostgres

Edit /root/awx/installer/inventory : postgres_data_dir=/root/upgradepostgres

Edit /root/awx/installer/roles/local_docker/tasks/main.yml :

name: Stop AWX before upgrading postgres
name: Upgrade Postgres
name: Remove old data directory For the 3 of them, I have modified when: false

docker run -it --rm \
       -v /root/pgdocker/pgdata:/var/lib/postgresql/9.6/data \
       -v /root/upgradepostgres:/var/lib/postgresql/10/data \
       -e PGUSER=awx -e POSTGRES_INITDB_ARGS="-U awx" \
       tianon/postgres-upgrade:9.6-to-10  bash
chown -R postgres:postgres /var/lib/postgresql/10/
su - postgres
/usr/lib/postgresql/10/bin/initdb -E UTF8 --lc-collate=en_US.utf8 --lc-ctype=en_US.utf8 -D /var/lib/postgresql/10/data/
/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/
exit
exit
ansible-playbook -i inventory install.yml

I don't have any error during the upgrade process, but I need to use the default login/password in order to log in (which is admin/passsword) and then everything is empty, no inventory, etc.

I don't know why I'm losing all my data after the upgrade. Any ideas ?

kevincoakley commented 4 years ago

@Gaby033 it is difficult to troubleshoot without access to the system. Instead of creating /root/upgradepostgres, I would just back up /root/pgdocker/pgdata and restore the directory if something goes wrong. The upgrade process shouldn't overwrite the files in /root/pgdocker/pgdata, the upgraded database files should be saved to /root/pgdocker/pgdata/10. Can you run the psql command and query the database after you run the pg_upgrade command? My guess is there is a path issue.

Gaby033 commented 4 years ago

Thank you for your fast answer @kevincoakley.

If I understood correctly, you wanted me to try the command "psql" right after the upgrade command (and before the exit). Here is the result :

psql: could not connect to server: No such file or directory
        Is the server running locally and accepting
        connections on Unix domain socket "/var/run/postgresql/.s.PGSQL.5432"?

I modified the docker command as you requested :

docker run -it --rm \
       -v /root/pgdocker/pgdata:/var/lib/postgresql/9.6/data \
       -v /root/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

kevincoakley commented 4 years ago

@Gaby033 Sorry, I forgot the postgres-upgrade:9.6-to-10 container doesn't have the postgresql server running, so the psql command won't connect unless you start the postgresql server.

I am not sure what issue you are running into, but it sounds like there is a path issue. I would review the volumes path for the postgres container in the docker-compose.yml file defined by docker_compose_dir and make sure the volumes path points to the directory that contains the upgrade postgres database files.

Mine looks like:

  postgres:
    image: postgres:10
    container_name: awx_postgres
    restart: unless-stopped
    volumes:
      - /mnt/tower/pgdocker/10/data/:/var/lib/postgresql/data/pgdata:Z
    environment:
      POSTGRES_USER: awx
      POSTGRES_PASSWORD: awxpass
      POSTGRES_DB: awx
      PGDATA: /var/lib/postgresql/data/pgdata
      http_proxy:
      https_proxy:
      no_proxy:

Gaby033 commented 4 years ago

I have checked the file : ~/.awx/awxcompose/docker-compose.yml

  postgres:
    image: postgres:10
    container_name: awx_postgres
    restart: unless-stopped
    volumes:
      - /root/upgradepostgres/10/data/:/var/lib/postgresql/data/pgdata:Z
    environment:
      POSTGRES_USER: awx
      POSTGRES_PASSWORD: awxpass
      POSTGRES_DB: awx
      PGDATA: /var/lib/postgresql/data/pgdata
      http_proxy:
      https_proxy:
      no_proxy:

It seems to be correct regarding the path. So the data loss is due to something else I think.

Gaby033 commented 4 years ago

Hello @kevincoakley, would you have other leads in order to resolve this matter please ? If you got some commands you want me to run, or if you need me to provide some logs, don't hesitate :)

Maybe the problem is because I'm upgrading from AWX 6.1.0 to 9.2.0 directly ?

Thank you !

wilf1rst commented 4 years ago

Hello All,

I was able to update from 6.0.1 to 9.2.0.

I perform the upgrade with the following steps to ensure I correct all problems as they come: 6.0.1 => 7.0.0 => 8.0.0 => 9.0.0 => 9.0.1 => 9.1.0 => 9.2.0

Here's what I encounter:

I need to update my system to the latest release (using yum update -y as it's a CentOS 7.x server).
I need to update all my packages installed throught pip (ansible and most if not all of it's dependencies as it was more up to date than the CentOS repositories) using: pip install --upgrade pip then pip list --outdated --format=freeze | grep -v '^\-e' | cut -d = -f 1 | xargs -n1 pip install -U
I need to ensure my path was correctly set in the migration containers in the steps @kevincoakley write in #577771419, Connect to your container and run ls /var/lib/postgresql/{9.6,10}/data and correct the path in your docker run command if you have not the following output:

root@a49a17f51ba9:/var/lib/postgresql# ls /var/lib/postgresql/{9.6,10}/data
/var/lib/postgresql/10/data:
base    pg_commit_ts  pg_hba.conf    pg_logical    pg_notify    pg_serial     pg_stat      pg_subtrans  pg_twophase  pg_wal   postgresql.auto.conf  postmaster.opts
global  pg_dynshmem   pg_ident.conf  pg_multixact  pg_replslot  pg_snapshots  pg_stat_tmp  pg_tblspc    PG_VERSION   pg_xact  postgresql.conf

/var/lib/postgresql/9.6/data:
base    pg_clog       pg_dynshmem  pg_ident.conf  pg_multixact  pg_replslot  pg_snapshots  pg_stat_tmp  pg_tblspc    PG_VERSION  postgresql.auto.conf  postmaster.opts
global  pg_commit_ts  pg_hba.conf  pg_logical     pg_notify pg_serial    pg_stat       pg_subtrans  pg_twophase  pg_xlog     postgresql.conf       postmaster.pid

As I see the following error in my logs: psycopg2.OperationalError: FATAL: no pg_hba.conf entry for host "172.18.0.3", user "awx", database "awx", SSL off I have to add the following line at the end of my new pga_hba.conf as the default posgres conf is more restrictive than the old one: host all all all md5

And that's all I've now a working AWX 9.2.0 version.

Hope it helps.

Edit: By the way if your update your system and pip packages and then ensure the paths are corrects during the pgupgrade and correctly set your pga_hba.conf right I think you could perform an upgrade from 6.0.1 to 9.2.0

Gaby033 commented 4 years ago

Hello @wilf1rst, I have tried the commands you gave.

I was using Centos : 7.6.1810 went to : 7.7.1908 During the pip list --outdated --format=freeze | grep -v '^\-e' | cut -d = -f 1 | xargs -n1 pip install -U I had some errors with incompatibility between some packets. The ls commands give the 2 correct paths just like you. However, I didn't get the error you got, so I didn't modified the pg_hba.conf file. The problem was the same, after the upgrade of postgres, awx was empty.

So I tried the other method : upgrade from 6.1 to 7.0, but I remembered I already encountered some problems while doing so. I, of course, used the postgres database 9.6 and got the error : "A server error has occurred" while accessing the web url. I searched and found this topic : https://github.com/ansible/awx/issues/4852 however no real answer was given. And I don't know where are the log file in the awx_task and awx_web container. I looked in /var/log and /home/awx/supervisord.log, so I can't tell what's the reason of the error.

wilf1rst commented 4 years ago

Despite the fact you didn't have the same error than me could you try to add the line in pga_hba.conf ? The empty AWX is because you can't join the database due to restriction I think...

Please restart your container afterward using the docker-compose file in docker-compose folder: docker-compose stop then docker-compose up -d

Edit: Could you post the whole error log you have when you try to upgrade the postgres db ?

Gaby033 commented 4 years ago

I have added the line inside : /root/pgdocker/10/data/pg_hba.conf. Then

cd  ~/.awx/awxcompose/
docker-compose stop
docker-compose up -d

Went to the url, got the "awx upgrade" and then still the awx with the "demo project" and "demo inventory".

Inside the docker, /var/log/postgresql/ is empty. Where should I find the log ?

wilf1rst commented 4 years ago

It really seems to me that your postgres container point on the wrong path on your filesystem.

Did you modify the installer/inventory.yml postgres_data_dir variable to point to your upgraded postgres folder ?

As for log can you post the result of the following command you run previously inside the tianon/postgres-upgrade container as you said you remember having an error:

/usr/lib/postgresql/10/bin/initdb -E UTF8 --lc-collate=en_US.utf8 --lc-ctype=en_US.utf8 -D /var/lib/postgresql/10/data/
/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/

If you can re-do the whole posgres upgrade process and post the result here.

Gaby033 commented 4 years ago

@wilf1rst Yes, I modified installer/inventory.yml setting : postgres_data_dir="/root/pgdocker/pgdata/10"

Here is the output of the commands :

postgres@f3a7b6a2d498:~$ /usr/lib/postgresql/10/bin/initdb -E UTF8 --lc-collate=en_US.utf8 --lc-ctype=en_US.utf8 -D /var/lib/postgresql/10/data/
The files belonging to this database system will be owned by user "postgres".
This user must also own the server process.

The database cluster will be initialized with locales
  COLLATE:  en_US.utf8
  CTYPE:    en_US.utf8
  MESSAGES: C
  MONETARY: C
  NUMERIC:  C
  TIME:     C
The default text search configuration will be set to "english".

Data page checksums are disabled.

fixing permissions on existing directory /var/lib/postgresql/10/data ... ok
creating subdirectories ... ok
selecting default max_connections ... 100
selecting default shared_buffers ... 128MB
selecting default timezone ... Etc/UTC
selecting dynamic shared memory implementation ... posix
creating configuration files ... ok
running bootstrap script ... ok
performing post-bootstrap initialization ... ok
syncing data to disk ... ok

WARNING: enabling "trust" authentication for local connections
You can change this by editing pg_hba.conf or using the option -A, or
--auth-local and --auth-host, the next time you run initdb.

Success. You can now start the database server using:

    /usr/lib/postgresql/10/bin/pg_ctl -D /var/lib/postgresql/10/data/ -l logfile start

postgres@f3a7b6a2d498:~$ /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/
Performing Consistency Checks
-----------------------------
Checking cluster versions                                   ok
Checking database user is the install user                  ok
Checking database connection settings                       ok
Checking for prepared transactions                          ok
Checking for reg* data types in user tables                 ok
Checking for contrib/isn with bigint-passing mismatch       ok
Checking for invalid "unknown" user columns                 ok
Creating dump of global objects                             ok
Creating dump of database schemas
                                                            ok
Checking for presence of required libraries                 ok
Checking database user is the install user                  ok
Checking for prepared transactions                          ok

If pg_upgrade fails after this point, you must re-initdb the
new cluster before continuing.

Performing Upgrade
------------------
Analyzing all rows in the new cluster                       ok
Freezing all rows in the new cluster                        ok
Deleting files from new pg_xact                             ok
Copying old pg_clog to new server                           ok
Setting next transaction ID and epoch for new cluster       ok
Deleting files from new pg_multixact/offsets                ok
Copying old pg_multixact/offsets to new server              ok
Deleting files from new pg_multixact/members                ok
Copying old pg_multixact/members to new server              ok
Setting next multixact ID and offset for new cluster        ok
Resetting WAL archives                                      ok
Setting frozenxid and minmxid counters in new cluster       ok
Restoring global objects in the new cluster                 ok
Restoring database schemas in the new cluster
                                                            ok
Copying user relation files
                                                            ok
Setting next OID for new cluster                            ok
Sync data directory to disk                                 ok
Creating script to analyze new cluster                      ok
Creating script to delete old cluster                       ok
Checking for hash indexes                                   ok

Upgrade Complete
----------------
Optimizer statistics are not transferred by pg_upgrade so,
once you start the new server, consider running:
    ./analyze_new_cluster.sh

Running this script will delete the old cluster's data files:
    ./delete_old_cluster.sh

wilf1rst commented 4 years ago

@Gaby033

Ok great so the upgrade seems to be ok.

So there's maybe a PATH issue with your new container. Let's isolate the problem and ensure all paths are correct:

Could you post the result of:

Volume source: docker container inspect -f '{{ (index .Mounts 0).Source }}' awx_postgres

example on my side:

[user@awx ~]# docker container inspect -f '{{ (index .Mounts 0).Source }}' awx_postgres 
/var/lib/pgdocker-update/10/data

Volume destination: docker container inspect -f '{{ (index .Mounts 0).Destination }}' awx_postgres

example on my side:

[user@awx ~]# docker container inspect -f '{{ (index .Mounts 0).Destination }}' awx_postgres 
/var/lib/postgresql/data/pgdata

Could you also post the result of:

The database folder on the host: ls $(docker container inspect -f '{{ (index .Mounts 0).Source }}' awx_postgres)

Example on my side:

[user@awx ~]# ls $(docker container inspect -f '{{ (index .Mounts 0).Source }}' awx_postgres )
base    pg_commit_ts  pg_hba.conf    pg_logical    pg_notify    pg_serial     pg_stat      pg_subtrans  pg_twophase  pg_wal   postgresql.auto.conf  postmaster.opts
global  pg_dynshmem   pg_ident.conf  pg_multixact  pg_replslot  pg_snapshots  pg_stat_tmp  pg_tblspc    PG_VERSION   pg_xact  postgresql.conf       postmaster.pid

The database folder inside your awx_postgres container: DEST=$(docker container inspect -f '{{ (index .Mounts 0).Destination }}' awx_postgres ); docker exec -it awx_postgres ls ${DEST}

Example on my side:

[user@awx ~]# DEST=$(docker container inspect -f '{{ (index .Mounts 0).Destination }}' awx_postgres ); docker exec -it awx_postgres ls ${DEST}
base          pg_ident.conf  pg_serial     pg_tblspc    postgresql.auto.conf
global        pg_logical     pg_snapshots  pg_twophase  postgresql.conf
pg_commit_ts  pg_multixact   pg_stat       PG_VERSION   postmaster.opts
pg_dynshmem   pg_notify      pg_stat_tmp   pg_wal   postmaster.pid
pg_hba.conf   pg_replslot    pg_subtrans   pg_xact

Also let's ensure your postgres db is well populated. Could you post the result of:

docker container exec -it awx_postgres psql -U awx -d awx -c '\l';

Example on my side:

[user@awx ~]# docker container exec -it awx_postgres psql -U awx -d awx -c '\l';
                                 List of databases
   Name    |  Owner   | Encoding |  Collate   |   Ctype    |   Access privileges   
-----------+----------+----------+------------+------------+-----------------------
 awx       | awx      | UTF8     | en_US.utf8 | en_US.utf8 | 
 postgres  | postgres | UTF8     | en_US.utf8 | en_US.utf8 | 
 template0 | postgres | UTF8     | en_US.utf8 | en_US.utf8 | =c/postgres          +
           |          |          |            |            | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.utf8 | en_US.utf8 | postgres=CTc/postgres+
           |          |          |            |            | =c/postgres
(4 rows)

docker container exec -it awx_postgres psql -U awx -d awx -c '\dt';

Example on my side:

[user@awx ~]# docker container exec -it awx_postgres psql -U awx -d awx -c '\dt';
                                 List of relations
 Schema |                           Name                            | Type  | Owner 
--------+-----------------------------------------------------------+-------+-------
 public | auth_group                                                | table | awx
 public | auth_group_permissions                                    | table | awx
 public | auth_permission                                           | table | awx
 public | auth_user                                                 | table | awx
 public | auth_user_groups                                          | table | awx
 public | auth_user_user_permissions                                | table | awx
[...]

Let's pick a table and see if the data are still here, for example we can use the table that store the job activity: docker container exec -it awx_postgres psql -U awx -d awx -c 'SELECT * from main_activitystream_job LIMIT 5

Example on my side:

[user@awx ~]# docker container exec -it awx_postgres psql -U awx -d awx -c 'SELECT * FROM main_activitystream_job LIMIT 5';
  id  | activitystream_id | job_id 
------+-------------------+--------
 1982 |              3579 |   5825
 1983 |              3580 |   5827
 1987 |              3584 |   5840
 1991 |              3588 |   5852
 1992 |              3589 |   5854
(5 rows)

Gaby033 commented 4 years ago

Thank you for your fast answer @wilf1rst

The 1st time I run this command I got :

docker container inspect -f '{{ (index .Mounts 0).Source }}' awx_postgres
/root/upgradepostgres/10/data

So I did :

rm -rf  ~/.awx/awxcompose/
ansible-playbook -i inventory install.yml

and then I got : /root/pgdocker/pgdata/10/10/data

The double 10/10 seems weird so : rm -rf /root/pgdocker/10 and restarted the upgrade process, but no changes, I still end up with :

ls /root/pgdocker/pgdata/10/
10
ls /root/pgdocker/pgdata/10/10/
data

For reminder, here are the commands I use :

docker run -it --rm \
       -v /root/pgdocker/pgdata:/var/lib/postgresql/9.6/data \
       -v /root/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

chown -R postgres:postgres /var/lib/postgresql/10/
su - postgres
/usr/lib/postgresql/10/bin/initdb -E UTF8 --lc-collate=en_US.utf8 --lc-ctype=en_US.utf8 -D /var/lib/postgresql/10/data/
/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/

I didn't try your other commands yet, because I think that with the first command we got an idea of the issue. I don't know how to solve the wrong path however.

Gaby033 commented 4 years ago

If it can be of any help :

The 2 following commands give same output as you :

# docker container inspect -f '{{ (index .Mounts 0).Destination }}' awx_postgres
/var/lib/postgresql/data/pgdata

# ls $(docker container inspect -f '{{ (index .Mounts 0).Source }}' awx_postgres)
base    pg_commit_ts  pg_hba.conf    pg_logical    pg_notify    pg_serial     pg_stat      pg_subtrans  pg_twophase  pg_wal   postgresql.auto.conf  postmaster.opts
global  pg_dynshmem   pg_ident.conf  pg_multixact  pg_replslot  pg_snapshots  pg_stat_tmp  pg_tblspc    PG_VERSION   pg_xact  postgresql.conf       postmaster.pid

This one seems really wrong :

# DEST=$(docker container inspect -f '{{ (index .Mounts 0).Destination }}' awx_postgres ); docker exec -it awx_postgres ls ${DEST}
pgdata

A little bit different, owner : awx vs postgres for you.

# docker container exec -it awx_postgres psql -U awx -d awx -c '\l';
                             List of databases
   Name    | Owner | Encoding |  Collate   |   Ctype    | Access privileges
-----------+-------+----------+------------+------------+-------------------
 awx       | awx   | UTF8     | en_US.utf8 | en_US.utf8 |
 postgres  | awx   | UTF8     | en_US.utf8 | en_US.utf8 |
 template0 | awx   | UTF8     | en_US.utf8 | en_US.utf8 | =c/awx           +
           |       |          |            |            | awx=CTc/awx
 template1 | awx   | UTF8     | en_US.utf8 | en_US.utf8 | =c/awx           +
           |       |          |            |            | awx=CTc/awx
(4 rows)

# docker container exec -it awx_postgres psql -U awx -d awx -c '\dt';
                                 List of relations
 Schema |                           Name                            | Type  | Owner
--------+-----------------------------------------------------------+-------+-------
 public | auth_group                                                | table | awx
 public | auth_group_permissions                                    | table | awx
 public | auth_permission                                           | table | awx
 public | auth_user                                                 | table | awx
 public | auth_user_groups                                          | table | awx
 public | auth_user_user_permissions                                | table | awx

# docker container exec -it awx_postgres psql -U awx -d awx -c 'SELECT * FROM main_activitystream_job LIMIT 5';
 id | activitystream_id | job_id
----+-------------------+--------
(0 rows)

wilf1rst commented 4 years ago

Ok so it's indeed a PATH problem with one of your command.

To get rid of the clusterfuck let's just recreate the postgres upgrade from scratch:

/root/pgdocker/pgdata is you actual postgres 9.6 folder it contain all your postgres data files
Remove your /root/pgdocker/pgdata/10/ folder

Run the following command that @kevincoakley describe to upgrade postgres 9.6 to 10 let's just change the base folder for postgres 10 to /root/pgdocker/10 (edit: it's not mandatory to change the folder as I misread the doc and my first thought was that the postgreql '10' folder is in the postgres 9.6 'pgdata' folder.... By the way I use two completely differents path for postgres 9.6 and 10 volumes to avoid confusion on my host ;)):

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

Then

chown -R postgres:postgres /var/lib/postgresql/10/
su - postgres
/usr/lib/postgresql/10/bin/initdb -E UTF8 --lc-collate=en_US.utf8 --lc-ctype=en_US.utf8 -D /var/lib/postgresql/10/data/
/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/

If you get this then the upgrade is complete:


Upgrade Complete
----------------
Optimizer statistics are not transferred by pg_upgrade so,
once you start the new server, consider running:
./analyze_new_cluster.sh

Running this script will delete the old cluster's data files: ./delete_old_cluster.sh

Ensure you have all your files correctly set in your container`ls /var/lib/postgresql/{9.6,10}/data` should return:
```postgres@87ad388c67d7:~$ ls /var/lib/postgresql/{9.6,10}/data
/var/lib/postgresql/10/data:
PG_VERSION  global    pg_dynshmem  pg_ident.conf  pg_multixact  pg_replslot  pg_snapshots  pg_stat_tmp  pg_tblspc    pg_wal   postgresql.auto.conf  postmaster.opts
base        pg_commit_ts  pg_hba.conf  pg_logical     pg_notify     pg_serial    pg_stat       pg_subtrans  pg_twophase  pg_xact  postgresql.conf

/var/lib/postgresql/9.6/data:
PG_VERSION  global   pg_commit_ts  pg_hba.conf    pg_logical    pg_notify    pg_serial     pg_stat  pg_subtrans  pg_twophase  postgresql.auto.conf  postmaster.opts
base        pg_clog  pg_dynshmem   pg_ident.conf  pg_multixact  pg_replslot  pg_snapshots  pg_stat_tmp  pg_tblspc    pg_xlog      postgresql.conf

then exit the upgrade container

Ensure your postgres data files are where they should be with: ls /root/pgdocker/{10,pgdata}, it should return:


/root/pgdocker/10:
PG_VERSION  global    pg_dynshmem  pg_ident.conf  pg_multixact  pg_replslot  pg_snapshots  pg_stat_tmp  pg_tblspc    pg_wal   postgresql.auto.conf  postmaster.opts
base        pg_commit_ts  pg_hba.conf  pg_logical     pg_notify     pg_serial    pg_stat       pg_subtrans  pg_twophase  pg_xact  postgresql.conf

/root/pgdocker/pgdata: PG_VERSION global pg_commit_ts pg_hba.conf pg_logical pg_notify pg_serial pg_stat pg_subtrans pg_twophase postgresql.auto.conf postmaster.opts base pg_clog pg_dynshmem pg_ident.conf pg_multixact pg_replslot pg_snapshots pg_stat_tmp pg_tblspc pg_xlog postgresql.conf

5. update you inventory.yaml variable `postgres_data_dir="/root/pgdocker/10"` it's your new postgresql base folder.

6. Ensure the following variables are set in your inventory:

pg_username=awx pg_password=awxpass pg_database=awx pg_port=5432


7. Launch the playbook to update your AWX instance

8. Check the source and destination

docker container inspect -f '{{ (index .Mounts 0).Source }}' awx_postgres

It should return: `/root/pgdocker/10`

docker container inspect -f '{{ (index .Mounts 0).Destination }}' awx_postgres


It should return: `/var/lib/postgresql/data/pgdata`

9. If all is ok you can now check the database with: `docker container exec -it awx_postgres psql -U awx -d awx -c 'SELECT * FROM main_activitystream_job LIMIT 5';`, you will see jobs (if you had run jobs previously in you old AWX instance...).

Gaby033 commented 4 years ago

Again, thank you very much @wilf1rst for your time.

I have also deleted this : rm -rf /root/.awx/*

As said in one of my previous post, I have modified this :

Edit /root/awx/installer/roles/local_docker/tasks/main.yml :
    name: Stop AWX before upgrading postgres
    name: Upgrade Postgres
    name: Remove old data directory
    For the 3 of them, I have modified when: false

Step 3) was ok :

# ls /var/lib/postgresql/{9.6,10}/data
/var/lib/postgresql/10/data:
PG_VERSION  global        pg_dynshmem  pg_ident.conf  pg_multixact  pg_replslot  pg_snapshots  pg_stat_tmp  pg_tblspc    pg_wal   postgresql.auto.conf  postmaster.opts
base        pg_commit_ts  pg_hba.conf  pg_logical     pg_notify     pg_serial    pg_stat       pg_subtrans  pg_twophase  pg_xact  postgresql.conf

/var/lib/postgresql/9.6/data:
PG_VERSION  global   pg_commit_ts  pg_hba.conf    pg_logical    pg_notify    pg_serial     pg_stat      pg_subtrans  pg_twophase  postgresql.auto.conf  postmaster.opts
base        pg_clog  pg_dynshmem   pg_ident.conf  pg_multixact  pg_replslot  pg_snapshots  pg_stat_tmp  pg_tblspc    pg_xlog      postgresql.conf

Step 4) also :

# ls /root/pgdocker/{10,pgdata}
/root/pgdocker/10:
10    global        pg_dynshmem  pg_ident.conf  pg_multixact  pg_replslot  pg_snapshots  pg_stat_tmp  pg_tblspc    PG_VERSION  pg_xact               postgresql.conf
base  pg_commit_ts  pg_hba.conf  pg_logical     pg_notify     pg_serial    pg_stat       pg_subtrans  pg_twophase  pg_wal      postgresql.auto.conf  postmaster.opts

/root/pgdocker/pgdata:
base    pg_clog       pg_dynshmem  pg_ident.conf  pg_multixact  pg_replslot  pg_snapshots  pg_stat_tmp  pg_tblspc    PG_VERSION  postgresql.auto.conf  postmaster.opts
global  pg_commit_ts  pg_hba.conf  pg_logical     pg_notify     pg_serial    pg_stat       pg_subtrans  pg_twophase  pg_xlog     postgresql.conf

# docker container inspect -f '{{ (index .Mounts 0).Source }}' awx_postgres
/root/pgdocker/10/10/data

Still wrong :/

# docker container inspect -f '{{ (index .Mounts 0).Destination }}' awx_postgres
/var/lib/postgresql/data/pgdata

This one is ok, just like before.

/root/.awx/awxcompose/docker-compose.yml :

  postgres:
    image: postgres:10
    container_name: awx_postgres
    restart: unless-stopped
    volumes:
      - /root/pgdocker/10/10/data/:/var/lib/postgresql/data/pgdata:Z
    environment:
      POSTGRES_USER: awx
      POSTGRES_PASSWORD: awxpass
      POSTGRES_DB: awx
      PGDATA: /var/lib/postgresql/data/pgdata
      http_proxy:
      https_proxy:
      no_proxy:

The volume isn't correct, I don't know why. I did the step 5) correctly.

wilf1rst commented 4 years ago

At this point I don't know why there's still a problem with your PATH.

But as you validate that the postgres 10 data is in the folder /root/pgdocker/10 on your host you can:

Stop the Database: docker-compose stop postgres
change the postgres volume definition in your docker-compose.yml to:

  postgres:
    image: postgres:10
    container_name: awx_postgres
    restart: unless-stopped
    volumes:
      - /root/pgdocker/10/:/var/lib/postgresql/data/pgdata:Z
    environment:
      POSTGRES_USER: awx
      POSTGRES_PASSWORD: awxpass
      POSTGRES_DB: awx
      PGDATA: /var/lib/postgresql/data/pgdata
      http_proxy:
      https_proxy:
      no_proxy:

And restart postgres with: docker-compose up -d postgres
Then check your log with docker-compose logs -f postgres

Gaby033 commented 4 years ago

Thank you @wilf1rst, I did that, and obtained the error : FATAL: no pg_hba.conf entry for host "172.18.0.6", user "awx", database "awx", SSL off So I stopped the docker postgres, and added at the end of file /root/pgdocker/10/pg_hba.conf what you gave me : host all all all md5 I relaunch the postgres, and got :

awx_postgres | 2020-03-12 11:16:05.085 UTC [1] LOG:  database system is ready to accept connections
awx_postgres | 2020-03-12 11:16:17.474 UTC [32] ERROR:  column main_instancegroup.credential_id does not exist at character 167
awx_postgres | 2020-03-12 11:16:17.474 UTC [32] STATEMENT:  SELECT "main_instancegroup"."id", "main_instancegroup"."name", "main_instancegroup"."created", "main_instancegroup"."modified", "main_instancegroup"."controller_id", "main_instancegroup"."credential_id", "main_instancegroup"."pod_spec_override", "main_instancegroup"."policy_instance_percentage", "main_instancegroup"."policy_instance_minimum", "main_instancegroup"."policy_instance_list" FROM "main_instancegroup"
awx_postgres | 2020-03-12 11:16:37.637 UTC [35] ERROR:  column main_unifiedjob.dependencies_processed does not exist at character 629
awx_postgres | 2020-03-12 11:16:37.637 UTC [35] STATEMENT:  SELECT "main_unifiedjob"."id", "main_unifiedjob"."polymorphic_ctype_id", "main_unifiedjob"."modified", "main_unifiedjob"."description", "main_unifiedjob"."created_by_id", "main_unifiedjob"."modified_by_id", "main_unifiedjob"."name", "main_unifiedjob"."old_pk", "main_unifiedjob"."emitted_events", "main_unifiedjob"."unified_job_template_id", "main_unifiedjob"."created", "main_unifiedjob"."launch_type", "main_unifiedjob"."schedule_id", "main_unifiedjob"."execution_node", "main_unifiedjob"."controller_node", "main_unifiedjob"."cancel_flag", "main_unifiedjob"."status", "main_unifiedjob"."failed", "main_unifiedjob"."started", "main_unifiedjob"."dependencies_processed", "main_unifiedjob"."finished", "main_unifiedjob"."canceled_on", "main_unifiedjob"."elapsed", "main_unifiedjob"."job_args", "main_unifiedjob"."job_cwd", "main_unifiedjob"."job_env", "main_unifiedjob"."job_explanation", "main_unifiedjob"."start_args", "main_unifiedjob"."result_traceback", "main_unifiedjob"."celery_task_id", "main_unifiedjob"."instance_group_id" FROM "main_unifiedjob" WHERE (("main_unifiedjob"."status" = 'running' OR ("main_unifiedjob"."modified" <= '2020-03-12T11:15:37.633834+00:00'::timestamptz AND "main_unifiedjob"."status" = 'waiting')) AND ("main_unifiedjob"."execution_node" = 'awx' OR "main_unifiedjob"."controller_node" = 'awx') AND NOT ("main_unifiedjob"."polymorphic_ctype_id" = 48 AND "main_unifiedjob"."polymorphic_ctype_id" IS NOT NULL)) ORDER BY "main_unifiedjob"."id" ASC
awx_postgres | 2020-03-12 11:16:37.661 UTC [36] ERROR:  column main_instancegroup.credential_id does not exist at character 241
awx_postgres | 2020-03-12 11:16:37.661 UTC [36] STATEMENT:  DECLARE "_django_curs_140126291347008_303" NO SCROLL CURSOR WITH HOLD FOR SELECT "main_instancegroup"."id", "main_instancegroup"."name", "main_instancegroup"."created", "main_instancegroup"."modified", "main_instancegroup"."controller_id", "main_instancegroup"."credential_id", "main_instancegroup"."pod_spec_override", "main_instancegroup"."policy_instance_percentage", "main_instancegroup"."policy_instance_minimum", "main_instancegroup"."policy_instance_list" FROM "main_instancegroup" WHERE "main_instancegroup"."credential_id" IS NOT NULL
awx_postgres | 2020-03-12 11:16:37.662 UTC [36] ERROR:  cursor "_django_curs_140126291347008_303" does not exist
awx_postgres | 2020-03-12 11:16:37.662 UTC [36] STATEMENT:  CLOSE "_django_curs_140126291347008_303"
awx_postgres | 2020-03-12 11:16:37.698 UTC [38] ERROR:  column main_instancegroup.credential_id does not exist at character 167
awx_postgres | 2020-03-12 11:16:37.698 UTC [38] STATEMENT:  SELECT "main_instancegroup"."id", "main_instancegroup"."name", "main_instancegroup"."created", "main_instancegroup"."modified", "main_instancegroup"."controller_id", "main_instancegroup"."credential_id", "main_instancegroup"."pod_spec_override", "main_instancegroup"."policy_instance_percentage", "main_instancegroup"."policy_instance_minimum", "main_instancegroup"."policy_instance_list" FROM "main_instancegroup"
awx_postgres | 2020-03-12 11:16:57.735 UTC [39] ERROR:  column main_instancegroup.credential_id does not exist at character 167
awx_postgres | 2020-03-12 11:16:57.735 UTC [39] STATEMENT:  SELECT "main_instancegroup"."id", "main_instancegroup"."name", "main_instancegroup"."created", "main_instancegroup"."modified", "main_instancegroup"."controller_id", "main_instancegroup"."credential_id", "main_instancegroup"."pod_spec_override", "main_instancegroup"."policy_instance_percentage", "main_instancegroup"."policy_instance_minimum", "main_instancegroup"."policy_instance_list" FROM "main_instancegroup"
awx_postgres | 2020-03-12 11:17:17.789 UTC [42] ERROR:  column main_instancegroup.credential_id does not exist at character 167
awx_postgres | 2020-03-12 11:17:17.789 UTC [42] STATEMENT:  SELECT "main_instancegroup"."id", "main_instancegroup"."name", "main_instancegroup"."created", "main_instancegroup"."modified", "main_instancegroup"."controller_id", "main_instancegroup"."credential_id", "main_instancegroup"."pod_spec_override", "main_instancegroup"."policy_instance_percentage", "main_instancegroup"."policy_instance_minimum", "main_instancegroup"."policy_instance_list" FROM "main_instancegroup"

The web url is saying "upgrading AWX" but I think it's stuck.

Gaby033 commented 4 years ago

Hello,

It's been a while, but I still have the issue. I'm unable to upgrade my AWX 6.1. I've even tried to upgrade to the latest : 13.0.0 but I have the same error.

I can provide more logs/conf if required. If someone could help me please.

isuftin commented 4 years ago

Seeing this same issue on a fresh new Bento CentOS 7 instance in VirtualBox using 13.0.0 AWX installation:

Vagrantfile:


Vagrant.require_version ">= 1.7.0"

AWX_TOWER_IP = '192.168.50.4'
UBUNTU_IP = '192.168.50.5'
CENTOS_IP = '192.168.50.6'
AWX_TOWER_RAM = '4096'
ANSIBLE_COMPATIBILITY_MODE = '2.0'
ANSIBLE_VERBOSITY = 'v'
CENTOS_BOX = 'bento/centos-7'
UBUNTU_BOX = 'bento/ubuntu-18.04'

Vagrant.configure(2) do |config|

  config.ssh.insert_key = false

  config.vm.box_download_insecure = true

  config.vm.define "awx" do |v|
    # https://www.mediaglasses.blog/2017/09/24/ansible-awx/
    v.vm.box = CENTOS_BOX
    v.vm.network "private_network", ip: AWX_TOWER_IP
    v.vm.network "forwarded_port", guest: 80, host: 8080
    v.vm.hostname = "awx.local"
    v.vm.provider "virtualbox" do |vb|
      vb.memory = AWX_TOWER_RAM
      vb.customize ["modifyvm", :id, "--ioapic", "on"]
    end
  end

  config.vm.define "centos" do |v|
    v.vm.box = CENTOS_BOX
    v.vm.provision :shell, inline: "yum install libselinux-python -y"
    v.vm.network "private_network", ip: CENTOS_IP
  end
end

awx_playbook.yml:

---

# https://www.mediaglasses.blog/2017/09/24/ansible-awx/

- name: Deploy AWX
  hosts: all
  become: true
  become_user: root

  tasks:

    - name: sort out the yum repos
      yum:
        name: ['epel-release']
        state: "latest"

    - name: add the docker ce yum repo
      yum_repository:
        name: "docker-ce"
        description: "Docker CE YUM repo"
        gpgcheck: "yes"
        enabled: "yes"
        baseurl: "https://download.docker.com/linux/centos/7/$basearch/stable"
        gpgkey: "https://download.docker.com/linux/centos/gpg"

    - name: install the prerequisites using yum
      yum:
        name: ['python-setuptools', 'yum-utils', 'libselinux-python', 'python-wheel', 'python-pip', 'git', 'docker-ce']
        state: "latest"

    - name: start and enable docker
      systemd:
        name: "docker"
        enabled: "yes"
        state: "started"

    - name: install the python packages using pip
      pip:
        name: ['pip', 'ansible', 'boto', 'boto3', 'docker', 'docker_compose']
        state: "latest"
        extra_args: "--trusted-host=python.org --trusted-host=pypi.org --trusted-host=pypi.python.org --trusted-host=files.pythonhosted.org"

    - name: check out the awx repo
      git:
        repo: "https://github.com/ansible/awx.git"
        dest: "~/awx"
        clone: "yes"
        update: "yes"
        version: 13.0.0

    - name: install awx
      command: "ansible-playbook --connection=awx -i /vagrant/awx_inventory install.yml"
      args:
        chdir: "~/awx/installer"

awx_inventory:

[awx]
localhost ansible_connection=local ansible_host=localhost ansible_python_interpreter=/usr/bin/python

[all:vars]
dockerhub_base=ansible
awx_task_hostname=awx
awx_web_hostname=awxweb
postgres_data_dir="~/.awx/pgdocker"
host_port=80
host_port_ssl=443
docker_compose_dir="~/.awx/awxcompose"
pg_username=awx
pg_password=awxpass
pg_database=awx
pg_port=5432
admin_user=admin
admin_password=password
create_preload_data=True
secret_key=awxsecret

$ vagrant up awx

$ vagrant ssh awx

[vagrant@awx ~]$ sudo yum install -y epel-release
[vagrant@awx ~]$ sudo yum install -y python2-pip
[vagrant@awx ~]$ sudo pip install -U pip ansible --trusted-host=python.org --trusted-host=pypi.org --trusted-host=pypi.python.org --trusted-host=files.pythonhosted.org
[vagrant@awx ~]$ ansible-playbook --connection=awx -i /vagrant/awx_inventory  /vagrant/awx_playbook.yml

danilomendes commented 4 years ago

Today I had some of those issues blocking my upgrade (in my case it was from 3.0 to 13). It was failing me at the postgres upgrade. Thanks to some clues I've seen here I found the main issue: when I installed awx for the first time, the postgres folder pattern seems to be different from what it should be now.

In fact I had something like this: {{ postgres_data_dir }}/pgdata/

And the postgres upgrade task was trying to upgrade from here: {{ postgres_data_dir }}/9.6/ to there: {{ postgres_data_dir }}/10/

So I've created {{ postgres_data_dir }}/9.6/ and placed a copy of my {{ postgres_data_dir }}/pgdata/ as data, as it was looking for. Then the upgrade went fine.

So far so good.

kevincoakley commented 3 years ago

I had this same issue when upgrading from 16.0.0 to 17.0.0.

My instructions in https://github.com/ansible/awx/issues/5214#issuecomment-577771419 also worked, I just had to change the version number from 9.6 & 10 to 10 & 12. I also had to copy the pg_hba.conf file from 10 to 12.