search

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
13.81k stars 3.39k forks source link

Migrating data from old docker-installation to AWX (19.2.1 & 19.2.2) fails during migration #10579

Open cazzen2k4 opened 3 years ago

cazzen2k4 commented 3 years ago

Summary

I have migrated the postgresql-data from an older AWX docker-installation to a kubernetes-installation. Upgrade to version 19.2.0 (operator 0.10.0) works like a charm. Upgrading to either 19.2.1 or 19.2.2 fails during the data migration. The awx-operator logs prints no errors but browsing to the awx web-ui shows "awx is upgrading" in a forever-loop.

Running the "awx-manage migrate" manually from the awx-web container fails with:

Operations to perform:
  Apply all migrations: auth, conf, contenttypes, main, oauth2_provider, sessions, sites, social_django, sso, taggit
Running migrations:
  Applying main.0144_event_partitions...Traceback (most recent call last):
  File "/usr/bin/awx-manage", line 8, in <module>
    sys.exit(manage())
  File "/var/lib/awx/venv/awx/lib64/python3.8/site-packages/awx/__init__.py", line 171, in manage
    execute_from_command_line(sys.argv)
  File "/var/lib/awx/venv/awx/lib64/python3.8/site-packages/django/core/management/__init__.py", line 381, in execute_from_command_line
    utility.execute()
  File "/var/lib/awx/venv/awx/lib64/python3.8/site-packages/django/core/management/__init__.py", line 375, in execute
    self.fetch_command(subcommand).run_from_argv(self.argv)
  File "/var/lib/awx/venv/awx/lib64/python3.8/site-packages/django/core/management/base.py", line 323, in run_from_argv
    self.execute(*args, **cmd_options)
  File "/var/lib/awx/venv/awx/lib64/python3.8/site-packages/django/core/management/base.py", line 364, in execute
    output = self.handle(*args, **options)
  File "/var/lib/awx/venv/awx/lib64/python3.8/site-packages/django/core/management/base.py", line 83, in wrapped
    res = handle_func(*args, **kwargs)
  File "/var/lib/awx/venv/awx/lib64/python3.8/site-packages/django/core/management/commands/migrate.py", line 232, in handle
    post_migrate_state = executor.migrate(
  File "/var/lib/awx/venv/awx/lib64/python3.8/site-packages/django/db/migrations/executor.py", line 117, in migrate
    state = self._migrate_all_forwards(state, plan, full_plan, fake=fake, fake_initial=fake_initial)
  File "/var/lib/awx/venv/awx/lib64/python3.8/site-packages/django/db/migrations/executor.py", line 147, in _migrate_all_forwards
    state = self.apply_migration(state, migration, fake=fake, fake_initial=fake_initial)
  File "/var/lib/awx/venv/awx/lib64/python3.8/site-packages/django/db/migrations/executor.py", line 245, in apply_migration
    state = migration.apply(state, schema_editor)
  File "/var/lib/awx/venv/awx/lib64/python3.8/site-packages/django/db/migrations/migration.py", line 124, in apply
    operation.database_forwards(self.app_label, schema_editor, old_state, project_state)
  File "/var/lib/awx/venv/awx/lib64/python3.8/site-packages/django/db/migrations/operations/models.py", line 527, in database_forwards
    alter_together(
  File "/var/lib/awx/venv/awx/lib64/python3.8/site-packages/django/db/backends/base/schema.py", line 379, in alter_index_together
    self._delete_composed_index(model, fields, {'index': True}, self.sql_delete_index)
  File "/var/lib/awx/venv/awx/lib64/python3.8/site-packages/django/db/backends/base/schema.py", line 394, in _delete_composed_index
    raise ValueError("Found wrong number (%s) of constraints for %s(%s)" % (
ValueError: Found wrong number (0) of constraints for main_inventoryupdateevent(inventory_update_id, start_line)

Additional information from postgresql of table main_inventoryupdateevent:

                                                               Table "public.main_inventoryupdateevent"
       Column        |           Type           | Collation | Nullable |                        Default                        | Storage  | Stats target | Description 
---------------------+--------------------------+-----------+----------+-------------------------------------------------------+----------+--------------+-------------
 id                  | bigint                   |           | not null | nextval('main_inventoryupdateevent_id_seq'::regclass) | plain    |              | 
 created             | timestamp with time zone |           | not null |                                                       | plain    |              | 
 modified            | timestamp with time zone |           | not null |                                                       | plain    |              | 
 event_data          | text                     |           | not null |                                                       | extended |              | 
 uuid                | character varying(1024)  |           | not null |                                                       | extended |              | 
 counter             | integer                  |           | not null |                                                       | plain    |              | 
 stdout              | text                     |           | not null |                                                       | extended |              | 
 verbosity           | integer                  |           | not null |                                                       | plain    |              | 
 start_line          | integer                  |           | not null |                                                       | plain    |              | 
 end_line            | integer                  |           | not null |                                                       | plain    |              | 
 inventory_update_id | integer                  |           | not null |                                                       | plain    |              | 
Indexes:
    "main_inventoryupdateevent_pkey1" PRIMARY KEY, btree (id)
    "main_inventoryupdateevent_inventory_update_id_idx" btree (inventory_update_id)
Check constraints:
    "main_inventoryupdateevent_counter_check" CHECK (counter >= 0)
    "main_inventoryupdateevent_end_line_check" CHECK (end_line >= 0)
    "main_inventoryupdateevent_start_line_check" CHECK (start_line >= 0)
    "main_inventoryupdateevent_verbosity_check" CHECK (verbosity >= 0)
Access method: heap

AWX version

19.2.2

Installation method

kubernetes

Modifications

no

Ansible version

default

Operating system

Centos 8

Web browser

No response

Steps to reproduce

Either by migrating the database from an older docker-installation (e.g. awx version 17) directly into (19.2.1/19.2.2) or by migrating the data into 19.2.0 and then upgrade awx to (19.2.1/19.2.2) using the awx-operator (0.11.0 / 0.12.0).

Expected results

A working awx-installation

Actual results

awx-task & awx-web stuck during data-migration phase.

Additional information

No response

sdhughes1 commented 2 years ago

I was able to work around this issue in my installation by manually adding 3 missing indexes to main_inventoryupdateevent before migrating all the way to 19.2.0+

I have no idea why they were missing. I figured this out by manually comparing a vanilla 19.0.0 installation to my migrated 19.0.0 database

CREATE INDEX main_inventoryupdateevent_inventory_update_id_start_line_idx ON main_inventoryupdateevent (inventory_update_id, start_line);
CREATE INDEX main_inventoryupdateevent_inventory_update_id_end_line_idx ON main_inventoryupdateevent(inventory_update_id, end_line);
CREATE INDEX main_inventoryupdateevent_inventory_update_id_uuid_idx ON main_inventoryupdateevent(inventory_update_id, uuid);

CREATE INDEX main_projectupdateevent_project_update_id_end_line_idx ON main_projectupdateevent (project_update_id, end_line);
CREATE INDEX main_projectupdateevent_project_update_id_event_idx ON main_projectupdateevent (project_update_id, event);
CREATE INDEX main_projectupdateevent_project_update_id_start_line_idx ON main_projectupdateevent (project_update_id, start_line);
CREATE INDEX main_projectupdateevent_project_update_id_uuid_idx ON main_projectupdateevent (project_update_id, uuid);

CREATE INDEX main_systemjobevent_system_job_id_end_line_idx ON main_systemjobevent (system_job_id, end_line);
CREATE INDEX main_systemjobevent_system_job_id_start_line_idx ON main_systemjobevent (system_job_id, start_line);
CREATE INDEX main_systemjobevent_system_job_id_uuid_idx ON main_systemjobevent (system_job_id, uuid);
mnhan3 commented 2 years ago

Is there a way to dump all the sql for main.0144_event_partitions? I've a awx 19.2.0 instance and can't get it to upgrade to any version above 19.2.0. I've tried 19.2.1, 19.2.2 and 19.3.0, all failed to migrate the 19.2.0 db. Its stuck on main.0144_event_partitions and it errors out on main_adhoccommandevent.

sdhughes1 commented 2 years ago

Is there a way to dump all the sql for main.0144_event_partitions? I've a awx 19.2.0 instance and can't get it to upgrade to any version above 19.2.0. I've tried 19.2.1, 19.2.2 and 19.3.0, all failed to migrate the 19.2.0 db. Its stuck on main.0144_event_partitions and it errors out on main_adhoccommandevent.

Some of the SQL is literal in the source code, other is abstracted with Django: https://github.com/ansible/awx/blob/devel/awx/main/migrations/0144_event_partitions.py

I just edited my comment above to include all the edits I made to my indexes before running the migration. To figure out what I needed, I compared my database tables to a vanilla/stock AWX 19.0.0 installation via simple Postgres commands. You might try the same with your main_adhoccommandevent table

\dt main_inventoryupdateevent_inventory_update_id_start_line_idx
\dt main_projectupdateevent_project_update_id_event_idx
\dt main_systemjobevent_system_job_id_uuid_idx
mnhan3 commented 2 years ago

I compared my awx db with a fresh installed awx 19.2.0 db and the tables are identical. All the indexes on both db are the same. Attempting to run your commands listed above just errors with index already exist.

cazzen2k4 commented 2 years ago

I was able to work around this issue in my installation by manually adding 3 missing indexes to main_inventoryupdateevent before migrating all the way to 19.2.0+

I have no idea why they were missing. I figured this out by manually comparing a vanilla 19.0.0 installation to my migrated 19.0.0 database

CREATE INDEX main_inventoryupdateevent_inventory_update_id_start_line_idx ON main_inventoryupdateevent (inventory_update_id, start_line);
CREATE INDEX main_inventoryupdateevent_inventory_update_id_end_line_idx ON main_inventoryupdateevent(inventory_update_id, end_line);
CREATE INDEX main_inventoryupdateevent_inventory_update_id_uuid_idx ON main_inventoryupdateevent(inventory_update_id, uuid);

CREATE INDEX main_projectupdateevent_project_update_id_end_line_idx ON main_projectupdateevent (project_update_id, end_line);
CREATE INDEX main_projectupdateevent_project_update_id_event_idx ON main_projectupdateevent (project_update_id, event);
CREATE INDEX main_projectupdateevent_project_update_id_start_line_idx ON main_projectupdateevent (project_update_id, start_line);
CREATE INDEX main_projectupdateevent_project_update_id_uuid_idx ON main_projectupdateevent (project_update_id, uuid);

CREATE INDEX main_systemjobevent_system_job_id_end_line_idx ON main_systemjobevent (system_job_id, end_line);
CREATE INDEX main_systemjobevent_system_job_id_start_line_idx ON main_systemjobevent (system_job_id, start_line);
CREATE INDEX main_systemjobevent_system_job_id_uuid_idx ON main_systemjobevent (system_job_id, uuid);

It looks like I came up with the exact same solution as you did, but forgot to reply to my own thread. I created the indexes based on each traceback I got by re-running the migrations after creating an index.

I created the following indexes to make the migrations to complete (looks to be identical to yours) :

CREATE INDEX main_projectupdateevent_project_update_id_job__bfd22241_idx ON main_projectupdateevent(project_update_id, start_line);
CREATE INDEX main_projectupdateevent_project_update_id_job__bfd22242_idx ON main_projectupdateevent(project_update_id, end_line);
CREATE INDEX main_projectupdateevent_project_update_id_job__bfd22243_idx ON main_projectupdateevent(project_update_id, event);
CREATE INDEX main_projectupdateevent_project_update_id_job__bfd22244_idx ON main_projectupdateevent(project_update_id, uuid);

CREATE INDEX main_inventoryupdateevent_inventory_update_id_job__bfd22241_idx ON main_inventoryupdateevent(inventory_update_id, start_line);
CREATE INDEX main_inventoryupdateevent_inventory_update_id_job__bfd22242_idx ON main_inventoryupdateevent(inventory_update_id, end_line);
CREATE INDEX main_inventoryupdateevent_inventory_update_id_job__bfd22243_idx ON main_inventoryupdateevent(inventory_update_id, event);
CREATE INDEX main_inventoryupdateevent_inventory_update_id_job__bfd22244_idx ON main_inventoryupdateevent(inventory_update_id, uuid);

CREATE INDEX main_systemjobevent_system_job_id_job__bfd22241_idx ON main_systemjobevent(system_job_id, start_line);
CREATE INDEX main_systemjobevent_system_job_id_job__bfd22242_idx ON main_systemjobevent(system_job_id, end_line);
CREATE INDEX main_systemjobevent_system_job_id_job__bfd22244_idx ON main_systemjobevent(system_job_id, uuid);

good to know I wasn't alone with this issue.

mnhan3 commented 2 years ago

I installed a new 19.2.0 instance with db and turn the awx db to log all sql. Then ran the migration/upgrade to 19.2.1. I took the sql from the log for main_0144 and after backing up my real db, manually ran the migration of just main_0144, then deploy awx 19.2.1 and it was successfully migrated. All the commands from the sql generate from a clean installed ran without a hitch on the copy of my prod db.