FAC Backup Utility Enhancements

GSA-TTS / FAC

GSA's Federal Audit Clearinghouse

Other

18 stars 5 forks source link

Description: Once https://github.com/GSA-TTS/FAC/pull/3916 is successfully merged and validated, we want to enhance the utility to make sure it is even more robust than it currently is.

In order to: optimize the restore process as a: engineer I want: to not have to store the table.dump file on the file system, and instead do everything it needs with pipes.

Acceptance Criteria

Storing the s3 table dump is no longer required to be on the file system, and the tool directly performs the restoration without leaving files on the system.

Scenario:

Given: I am doing a restore from s3 when: the command runs I should be able to restore the table without leaving files on the system due to the table size. ...

### then...
- [ ] Modify the format of the db_to_s3 dump to be raw sql
- [ ] Refactor the psql pipe to support inpipe sql
- [ ] Investigate the stdout, and ensure the commands aren't output to avoid unncessary strain on the cpu
- [ ] Test and validate that table restoration occurs with the pipe, rather than files on disk (--data-only)

In order to: ensure that this tool can properly account for data restoration efforts as a: engineer I want: the tool to backup more information on the database, to ensure it can handle a partial or full table corruption

Acceptance Criteria

Working with the data team, we want to have a collection of items in the s3/rds that we can use to perform an entire table restoration if the table is missing or corrupted.

Scenario:

Given: a table is missing from the rds when: the utility goes to restore ...

### then...
- [x] the utility knows what tables should be there in the database
- [x] the utility checks to see if they exist but aren't
- [x] the utility should isolate any missing tables
- [ ] restore the specific table(s)

Consideration

Thorough testing should occur with this restoration, and ensure that the application does not become impacted with the table being restored

While this is preliminary, @jadudm and I have discussed some key points from this.

New story https://github.com/GSA-TTS/FAC/issues/3953 was created as a story to check tables if they exist, and what we do after the fact.
Currently we look at --data-only restoration efforts, and part of this discussion, is for the core app tables, are we really sure we want to do restore the data? If we lost the data, looking at a 2 hour window, we have a problem, and that problem could cascade to other data loss issues by simply restoring the data only
We noted that the "case of the missing table" occurred, and we believe the the tool should be able to look through them, and if it deems it necessary, restore it.

The big item we have to investigate is:

We recognized that we have a synchronization problem between the two code bases (with regards to the list of expected tables), we have to figure out the cause and notification of tables went missing, or what circumstance cause us to do a restoration. While this tool, theoretically can do a non --data-only restoration (see above bullet #2) if this tool is modified to do full table restoration, is that really something we want to automate.
- An option is to use the story to check the status of the tables within the database, notify the team, and do manual intervention.
- There is an itch to say "Well, if an app table is missing, we should shut down the login/submission process"
If a table does go missing, and the backups do not backup the table, we need to figure out these items:
- what happened
- what backups do we need to use
- if we lost SAC, we lost a lot of data (app stops working though)
- if we lost dissemination we can rebuild that

GSA-TTS / FAC