Decommissioning PostgreSQL database

[ibuziuk]

Is your task related to a problem? Please describe

PostgreSQL database does not store relevant data anymore. Whereas some of the date might still be used the vast majority of it is either irrelevant or outdated e.g. #21168 (comment)
After the keycloak deprecation, the next logical step is the deprecation of the PostgreSQL database.

Describe the solution you'd like

Decommissioning PostgreSQL database completely from Eclipse Che codebase. We need to figure out which relations are still in use and have a clear plan for database removal:

                            List of relations
 Schema |                      Name                       | Type  | Owner 
--------+-------------------------------------------------+-------+-------
 public | account                                         | table | pgche
 public | che_factory                                     | table | pgche
 public | che_factory_action                              | table | pgche
 public | che_factory_action_properties                   | table | pgche
 public | che_factory_ide                                 | table | pgche
 public | che_factory_on_app_closed_action                | table | pgche
 public | che_factory_on_app_closed_action_value          | table | pgche
 public | che_factory_on_app_loaded_action                | table | pgche
 public | che_factory_on_app_loaded_action_value          | table | pgche
 public | che_factory_on_projects_loaded_action           | table | pgche
 public | che_factory_on_projects_loaded_action_value     | table | pgche
 public | che_free_resources_limit                        | table | pgche
 public | che_free_resources_limit_resource               | table | pgche
 public | che_k8s_machine                                 | table | pgche
 public | che_k8s_machine_attributes                      | table | pgche
 public | che_k8s_runtime                                 | table | pgche
 public | che_k8s_server                                  | table | pgche
 public | che_k8s_server_attributes                       | table | pgche
 public | che_member                                      | table | pgche
 public | che_member_actions                              | table | pgche
 public | che_organization                                | table | pgche
 public | che_organization_distributed_resources          | table | pgche
 public | che_organization_distributed_resources_resource | table | pgche
 public | che_resource                                    | table | pgche
 public | che_sign_key                                    | table | pgche
 public | che_sign_key_pair                               | table | pgche
 public | che_system_permissions                          | table | pgche
 public | che_system_permissions_actions                  | table | pgche
 public | che_userdevfile_permissions                     | table | pgche
 public | che_userdevfile_permissions_actions             | table | pgche
 public | che_worker                                      | table | pgche
 public | che_worker_actions                              | table | pgche
 public | che_workspace_activity                          | table | pgche
 public | che_workspace_cfg_attributes                    | table | pgche
 public | command                                         | table | pgche
 public | command_attributes                              | table | pgche
 public | devfile                                         | table | pgche
 public | devfile_action                                  | table | pgche
 public | devfile_attributes                              | table | pgche
 public | devfile_command                                 | table | pgche
 public | devfile_command_attributes                      | table | pgche
 public | devfile_component                               | table | pgche
 public | devfile_component_arg                           | table | pgche
 public | devfile_component_command                       | table | pgche
 public | devfile_component_preferences                   | table | pgche
 public | devfile_component_selector                      | table | pgche
 public | devfile_endpoint                                | table | pgche
 public | devfile_endpoint_attributes                     | table | pgche
 public | devfile_entrypoint                              | table | pgche
 public | devfile_entrypoint_arg                          | table | pgche
 public | devfile_entrypoint_commands                     | table | pgche
 public | devfile_entrypoint_selector                     | table | pgche
 public | devfile_env                                     | table | pgche
 public | devfile_project                                 | table | pgche
 public | devfile_volume                                  | table | pgche
 public | environment                                     | table | pgche
 public | externalmachine                                 | table | pgche
 public | externalmachine_attributes                      | table | pgche
 public | externalmachine_env                             | table | pgche
 public | k8s_runtime_command                             | table | pgche
 public | k8s_runtime_command_attributes                  | table | pgche
 public | machine_volume                                  | table | pgche
 public | preference                                      | table | pgche
 public | preference_preferences                          | table | pgche
 public | profile                                         | table | pgche
 public | profile_attributes                              | table | pgche
 public | projectattribute                                | table | pgche
 public | projectattribute_values                         | table | pgche
 public | projectconfig                                   | table | pgche
 public | projectconfig_mixins                            | table | pgche
 public | schema_version                                  | table | pgche
 public | sequence                                        | table | pgche
 public | serverconf                                      | table | pgche
 public | serverconf_attributes                           | table | pgche
 public | serverconf_properties                           | table | pgche
 public | sourcestorage                                   | table | pgche
 public | sourcestorage_parameters                        | table | pgche
 public | sshkeypair                                      | table | pgche
 public | user_aliases                                    | table | pgche
 public | userdevfile                                     | table | pgche
 public | usr                                             | table | pgche
 public | workspace                                       | table | pgche
 public | workspace_attributes                            | table | pgche
 public | workspaceconfig                                 | table | pgche
(84 rows)                     Name                       | Type  | Owner 

Describe alternatives you've considered

N/A

Additional context

Additional motivation for removing db is the fact that users are deprovioned every 30 days according to the TOS on Developer Sandbox, and during the de-provioning toolchain needs to explicitly cleanup the users entries from the Eclipse Che db. In the latest version of Eclipse Che we need to let OpenShift handle the user management instead.

In general, once decommissioned it will be much less codebase to support and -1 operand to manage.

[l0rd]

Duplicate #21183?

[ibuziuk]

yeah, we can either close the original or add the details to it ;-)

[ibuziuk]

PR for the first phase of decommissioning eclipse-che/che-server#398
once merged, all the data stored in the db would become inconsistent and irrelevant.
the second phase would be removing Postgres operand completely

[l0rd]

Can we add switching to rolling update deployment strategy for che-server as a subtask of this issue?

[ibuziuk]

we probably can track it separately as part of #7662 - verify and switch to rolling update once db is decommissioned. The current issue looks like a pre-req, not a sub-task for the roling update epic

[ibuziuk]

Waiting for the final docs PR to be merged before resolving the issue eclipse-che/che-docs#2561

[ibuziuk]

Docs PR has been merged. Closing

[devstudio-release]

sync'd to Red Hat JIRA https://issues.redhat.com/browse/CRW-4105

• chore: Decommissioning Organization, Resource, Permission, Keycloak, SSH, and Activity services che-server#383
• feat: Removing dependency on usr, accout, profile db entries che-server#398
• refactor: Removing dependency on UserManager from FactoryService che-server#436