SIMS #3268 - DB connection parameter change

Applications DB connection parameters are changed from patroni to crunchy.
DB_SERVICE_NAME is moved as part of crunchy secret, so removed it from makefile parameters.

Steps for Migration

When installing crunchy in a namespace, there might be CPU resource crunch, so decrease the resources of FORMS, Clamav and Patroni,(Check similarly done changes in 0c27fb-dev for reference). This will not affect any existing application liveliness and also any deployments happening.

Bring down the pods Worker, API, Queue-Consumers for any data loss missed during the migration.

Install crunchy

Change the SIMS_DB_NAME name in the github secrets for the environments from SIMSDB to simsdb.

Run Env Setup - Deploy SIMS Secrets to Openshift github action to have the DB name change in the openshift secrets.

Note: Once this change is done, the older Patroni connections of the application might fail until the new crunchy db migration is done. If there are any issues to revert back to the old Patroni, please change the github secrets SIMS_DB_NAME to SIMSDB and run the github action to deploy the secret to openshift and restart the application pods - Worker, API, Queue-Consumers

CREATE SCHEMA IF NOT EXISTS sims
    AUTHORIZATION postgres;

CREATE EXTENSION pg_trgm with schema sims;

Backup using the below screenshots

Change the app_database_user to app-database-user and SIMSDB to simsdb

RUN Restore of the backup taken


GRANT ALL ON SCHEMA sims TO "non-super-user";

ALTER DEFAULT PRIVILEGES FOR ROLE postgres IN SCHEMA sims
GRANT INSERT, SELECT, UPDATE, DELETE ON TABLES TO "non-super-user";

ALTER DEFAULT PRIVILEGES FOR ROLE postgres IN SCHEMA sims
GRANT ALL ON SEQUENCES TO "non-super-user";

DO $$
DECLARE
    r RECORD;
    schema_name TEXT := 'sims'; -- Replace with your schema name
    user_name TEXT := 'non-super-user';     -- Replace with the user you want to grant privileges to
BEGIN
    FOR r IN
        SELECT table_name
        FROM information_schema.tables
        WHERE table_schema = schema_name
    LOOP
        EXECUTE format('GRANT ALL PRIVILEGES ON TABLE %I.%I TO %I',
                        schema_name, r.table_name, user_name);
    END LOOP;
END $$;