Support CockroachDB as back end DB for Ansible Tower

[mgoddard]

We briefly explored the feasibility of substituting CockroachDB for PostgreSQL 10 as the back end database for our Ansible Tower installation. We don't have access to the Ansible Tower code base, but wanted to just check whether we could do this swap. We're hoping for some guidance on how we should proceed, whether this is solvable from the Cockroach end, or if it's better taken up on the Ansible end.

Here are the details:

• Environment: CentOS Linux release 7.8.2003 (Core)
• Ansible install docs: https://docs.ansible.com/ansible-tower/latest/html/quickinstall/prepare.html
• Ansible Tower is a Python Django app, so we first installed the CRDB Django driver:
  • https://github.com/cockroachdb/django-cockroachdb
  • # python3 -m pip install django-cockroachdb==3.0.*
  • Now we have the driver files installed here: /usr/local/lib/python3.6/site-packages/django_cockroachdb
• Started CockroachDB: $ cockroach start-single-node --certs-dir=certs --listen-addr=localhost:26257 --http-addr=localhost:8080 --background
• Logged in as root and set up an ansible user, with password
• Verified user ansible can log in:

Password for user ansible:
psql (9.2.24, server 9.5.0)
WARNING: psql version 9.2, server version 9.5.
         Some psql features might not work.
SSL connection (cipher: ECDHE-RSA-AES128-GCM-SHA256, bits: 128)
Type "help" for help.

ansible=>

• The README.md file within the ansible-tower-setup-bundle-latest.el7.tar.gz archive states you can install in any of three ways, with one of them being On a single machine with a remote PostgreSQL database. Supplying the pg_host and pg_port variables will trigger this mode of installation.. This was the option we chose.
• After running the setup.sh install script, we saw errors showing, among other issues (we will attach some error log output), the following:

"psycopg2.errors.SyntaxError: at or near \"varchar_pattern_ops\": syntax error",
    "DETAIL:  source SQL:",
    "CREATE INDEX \"taggit_tag_name_58eb2ed9_like\" ON \"taggit_tag\" (\"name\" varchar_pattern_ops)",
    "                                                                     ^",
    "HINT:  try \\h CREATE INDEX",

• It looks like this error is referencing "operator classes" (https://www.postgresql.org/docs/9.4/indexes-opclass.html), but we haven't yet read up on the specifics of this feature.
• Ultimately, we determined that our attempt to have Ansible Tower use the CRDB Django driver failed as our mods to the config file were rewritten by the setup process.

Jira issue: CRDB-4026

[blathers-crl]

Hello, I am Blathers. I am here to help you get the issue triaged.

It looks like you have not filled out the issue in the format of any of our templates. To best assist you, we advise you to use one of these templates.

I have CC'd a few people who may be able to assist you:

• @solongordon (found keywords: pg_)
• @rafiss (found keywords: Django,psycopg2)

If we have not gotten back to your issue within a few business days, you can try the following:

• Join our community slack channel and ask on #cockroachdb.
• Try find someone from here if you know they worked closely on the area and CC them.

_{🦉 Hoot! I am a Blathers, a bot for CockroachDB. My owner is otan.}

[mgoddard]

Django_CRDB_Issues.txt
db_migrate_fail_07.02.2020.txt

[rafiss]

@mgoddard It looks like the Ansible Tower setup.sh does overwrite that configuration file. Here is the change in the Ansible side that adds that behavior: ansible/awx#3235

I think the way forward would be to make an issue in the https://github.com/ansible/awx/ repository, and ask if they can allow the tool to accept a user-specified Django engine.

As a shorter workaround, perhaps you could edit the following file inside of the ansible-tower-setup bundle: roles/awx_install/templates/postgres.py.j2. If you replace awx.main.db.profiled_pg with django_cockroachdb there, I wonder if that would make the file not get overwritten.

Longer-term, to avoid issues like this happening again, we'd have to ask the Ansible Tower team to add first-class support for CockroachDB.

@jordanlewis FYI the missing SQL feature here is operator classes, see #47420

[mgoddard]

Thank you, @rafiss ! And apologies for taking this long to see your note. I'll pass this advice on to Daniel.