Rebuild Kingfisher server

[jpmckinney]

Moving from CRM-8007

It will be easier to deploy the new Kingfisher Process version with a new, empty database where all the new tables schemas can be applied instead of planning a migration for the current tables.

• We can re-use most of the data registry's configuration.
• Check whether non-OCP postgres users are still using their logins (point them to the data registry instead)
• Remove the "Kingfisher personal SQL user account" issue template (and any links to it)

---

To do after PR merge:

• Add Redash data source for new server
• Merge upgrade-jsonschema in kingfisher-collect and change to main branch in this repo
• Update Redash data source to use postgres.kingfisher.open-contracting.org (instead of ocp23)
• Update collect.kingfisher CNAME
• Delete process.kingfisher CNAME
• Deploy docs server to apply tinyproxy changes to allowed IPs
• I think we can not have the CNAME process.kingfisher and process1.kingfisher (we have enough with collect.kingfisher, postgres.kingfisher, etc.)
• Notify korsikov/Yaroslav/Sergey and Vadim on Slack of the new domain name and database name (kingfisher_collect) for Qlik BI

[jpmckinney]

See this milestone for some issues to ideally close before the new deployment: https://github.com/open-contracting/kingfisher-process/milestone/7

Edit: Moved off-topic comment to open-contracting/data-registry#291

[jpmckinney]

Related:

• Upgrade PostgreSQL #177
• Create individual user accounts instead of sharing ocdskfp #142
• Document steps for migrating servers #198
• Remove /prometheus/metrics job after Kingfisher analyst server rebuild #373
• Update create server process to not add port knocking if Docker to be added later #427

[jpmckinney]

Once this is done, I can also upgrade the requirements files for:

• kingfisher-collect (notably Scrapy, jsonschema, requests) build: Upgrade jsonschema kingfisher-collect#1003 Upgrade Scrapy kingfisher-collect#998
• kingfisher-summarize (notably requests)

And set python-version: '3.10' in workflows for:

• kingfisher-collect
• kingfisher-summarize

Kingfisher Process is deployed as 3.8 on the registry server. While we could update the Docker image to 3.10, we might as well wait until 3.8 is EOL (in about a year and a half). Also, workflows already use ubuntu-latest.

Also these PRs/issues:

• Add --filter-sql-json-path option (requires PostgreSQL 12) kingfisher-summarize#217
• Change view_data_ schema prefix kingfisher-summarize#174 if data team agrees

[jpmckinney]

Okay, Kingfisher Collect and Summarize are ready for Python 3.10.

To test the deployment, you can use the branches upgrade-jsonschema (Collect) and 190-filter-sql-json-path (Summarize).

[jpmckinney]

To do:

• Share instructions with data support managers
• Configure sudoers so that individual users can run docker compose run --rm web python manage.py with arguments, as the deployer user.
• Kingfisher Process changes (CLI step of "Local load data" in kingfisher-process.rst). Author documentation kingfisher-process#371 finisher: Set cached_*_count fields kingfisher-process#370
• RabbitMQ management interface Put Apache (ProxyPass) in front of RabbitMQ's management interface #430 (I think there is just an admin user, currently, not a read-only user)