Database Cleanup

DB Cleanup

In the case need to remove duplicate fail records in the database for old growth CHEFS API version:

1. Run the following query in the database in test deployment:

delete from public.email_submission_log OT
where email_submission_log_id not in
(
    SELECT max(email_submission_log_id) as email_submission_log_id
    FROM public.email_submission_log
    where code = 'FAILED'
    group by DATE(create_timestamp), form_id
    ORDER BY MAX(email_submission_log_id)
)
and code = 'FAILED'

2. Confirm the result in test
3. Run the same query in the database in prod deployment
4. Confirm the result

Connect to the openshift database from local

1. Run the following command in the terminal

# login to the namespace. 
oc project [namespace]
# port forward the pod
oc port-forward [postgres-db-pod] 4200:5432

2. Open the PgAdmin on the local

• right click on servers -> porstgresSQL 14
• select properties -> click on connection tab
• update the port to be 4200, username to be nrog (the db username), click save
• refresh the database, and then quit and reopen the pgadmin, will be able to connect to the deployed database in openshift