[Deployment] Transfer selective data to govt. server for audit readiness

[aakashyadav-kgp]

Description: As chakshu suggested we can selectively transfer data to govt server for audit readiness. We want to identify and transfer required data to govt server accordingly.

Acceptance Criteria:

1. All services are audit ready. [ @karntrehan see if you can add more concrete things to acceptance criteria after talking with chakshu]

Linked to #45

[karntrehan]

The audit server is only 200 GB whereas our data is currently 180 GB. We need to figure a way to add only limited data for auditing
I and @choxx discussed on this today.

Approach 1: Manually pull data from tables and push into the audit db.
This is a very manual process where relations between the tables could break the insertions.

Approach 2: Identify most used tables using pg_stat and use audit logger to get all changes to those tables.
Audit logged would give us all the changes to a table and we would insert only those changes. Wherever things break we would need to manually add the data.

Approach 3: Skip user generated submissions, assessments and attendance data.
The user generated content is the highest contributor to size. We can skip the entire table or use only the last 1000 entries for each table.

Given the pros and cons we are suggesting approach 3.
We will take a dump of the entire data in the evening. We shall host this dump on a temporary server and clean it up - Remove tables not needed. Take a dump of the cleaned up data and host onto audit server. We can start tomorrow post confirmation of approach with team. We shall ask a WFC talent to work on this cleanup and hosting to audit server.

cc @choxx

[aakashyadav-kgp]

@ChakshuGautam please review this once for audit readiness.

[karntrehan]

Going ahead with approach 3. Taking dump of data today evening. Monday dump data to a server (@karntrehan) to help creating one. Tuesday Ansh to be aligned to work on this.

[choxx]

Back up done.

root@samarthDb:~# cd /mnt/psql_data/
root@samarthDb:/mnt/psql_data# docker ps | grep postgre
5c965f6a0abf        postgres:9.6                                    "docker-entrypoint.s…"   2 years ago         Up 14 months        0.0.0.0:5432->5432/tcp                     postgres50
root@samarthDb:/mnt/psql_data# docker exec postgres50 pg_dump -U postgres -d postgres > backup_2023_03_06_18_00_00.sql
root@samarthDb:/mnt/psql_data# df -h backup_2023_03_06_18_00_00.sql 
Filesystem      Size  Used Avail Use% Mounted on
/dev/sde        496G  216G  259G  46% /mnt/psql_data
root@samarthDb:/mnt/psql_data# du -hs backup_2023_03_06_18_00_00.sql 
33G	backup_2023_03_06_18_00_00.sql
root@samarthDb:/mnt/psql_data# 

[choxx]

Taken a dump[5 GB]. Currently on prod server.
Next Step:

• 1. Create temp server
• 2. Install Postgres using Docker or Docker compose
• 3. Upload the data
• 4. Clean up data - @choxx and @karntrehan to provide details
  • 4.1. keep say last 1000 records from the tables listed below & delete the rest records from main table as well as related table.
  • 4.2. Truncate data from all materialized views.
• 5. Check size & take the dump of cleaned DB.
• 6. Upload to the prod server
• 7. Restore DB on to Postgres container on the prod server

Below listed tables are all related to submissions/collecting data from app:

• assessmentrawtable1
• attendance
• component_submmission
• idb_student_attendance_attendance_merged
• student_submission_v2
• student_submission_v2_marks_submissions
• assessment_cache_v5
• sms
• studentassessmentfinaltable4
• student_assessment_data
• old_assessment
• studentassessmentfinaltable3
• student_submission
• mystudent_testperformance
• class_submission

All Materialized views can be cleared entirely.

[choxx]

DB import is still in progress (since last 12 hours). Waiting for it to get imported on demo server & then proceed with the cleanup step.

[choxx]

Restoring dump is still in progress; kinda in hanged state.
Process to be restarted again by increasing allocated memory resources for Postgres container.

[choxx]

Steps 1,2 & 3 are done. Ansh to proceed with the clean-up steps. Pinged him on Discord.

[choxx]

Ansh to start working on it today.

[aakashyadav-kgp]

@choxx to pick this up today.

[choxx]

Aligned with @yuvrajsab to pick up today.

[yuvrajsab]

dump has been taken and shared with @tushar5526

[charanpreet-s]

Blocked till access is provided

[choxx]

@tushar5526 the files have been transferred to their windows server at location C:\samagra files\latest. Please go ahead with the next steps.