Packit-service: Move from redis to psql as a data store

[TomasTomecek]

Plan

Stage 1

• [ ] store data in psql and redis in parallel, read data from psql, fall back to redis
  
  
  
  • [x] required change REST API code to do this p-s/service/api #479
  
  
  • assigned to @IceWreck
  
  
  • [x] required turn whitelist into psql (schema needs to be created) p-s/worker/whitelist
  
  
  • assigned to @IceWreck
  
  
  • [ ] store installations-related data into psql (also needs schema and design) p-s/worker/handlers/github
  
  
  • assigned to @rishavanand
  
  
  • [x] store testing farm data in psql p-s/worker/handlers/testing-farm (done by @lachmanfrantisek in #502 )
  
  
  • [x] store information about all tasks/jobs executed in p-s into psql (needs schema and design)
    
    (done by @lbarcziova in #520)
  
  
  • [ ] make sure that data we store in redis are a subset of what we put in psql
  
  
  • assigned to @rishavanand
  
  
  • [ ] get a bottle of champagne and prepare for p2
  
  
  • assigned to @rishavanand
  
  

Stage 2

• [ ] create a script to read data from redis and transform them into sqlalchemy objects and save them into psql
  
  
  
  • make the script idempotent - can be run multiple times on the same data set without creating duplicate entries
  
  
  • remove the data from redis
  
  
  • DON'T RUN THE SCRIPT IN PROD JUST YET
  
  

Stage 3

• [ ] once everything is migrated into psql, get rid off all the redis-related code
• [ ] deploy the code to prod
• [ ] run the script from Stage 2 on prod

---

• needs-design mean that you should first propose a solution (mainly the schema) to the maintainers and get it ack'd before working on the code itself

please update this as needed

[IceWreck]

Hey, I can work on atleast parts of Stage 1 (the first two checkboxes), though after 9th March

[grapheo12]

I am also interested in this. Can we work together? @TomasTomecek @IceWreck

[grapheo12]

@TomasTomecek @lachmanfrantisek I have an action plan in mind for this:

1. Let's use the raw SQLAlchemy (not the flask_sqlalchemy plugin) so that the db connection object can be used outside the context of the Flask app.

2. Let's take the code for models out of the service folder as the definitions might be useful for other programs in workers and handlers too. (Possibly will not let some complicated import issues later on)

3. In models.py we can let the Model class inherit the Model class exposed by SQLAlchemy. Let's adhere to strict duck-typing so that all the methods signatures are preserved. But under the hood, let's use psql first and then redis as a fallback.

4. Later this redis part can be dropped at will.

Please review if this falls in line with what is wanted.

[IceWreck]

I am also interested in this. Can we work together? @TomasTomecek @IceWreck

Yes, sure.

[IceWreck]

@grapheo12

Let's use the raw SQLAlchemy (not the flask_sqlalchemy plugin) so that the db connection object can be used outside the context of the Flask app.

This is already the case as far as I know

Let's take the code for models out of the service folder as the definitions might be useful for other programs in workers and handlers too. (Possibly will not let some complicated import issues later on)

The postgres models.py is already outside the service folder

[grapheo12]

The postgres models.py is already outside the service folder

Thanks for pointing out. I probably missed that out.
Is there any reason to keep them separate? We can possibly merge them together.

[IceWreck]

The postgres models.py is already outside the service folder

Thanks for pointing out. I probably missed that out.
Is there any reason to keep them separate? We can possibly merge them together.

The one inside the service folder is the redis models.py and the one outside is the postgres models.py

[grapheo12]

Yeah but class definitions can be made such that same model can be used for both. That'd be better since then we will not have to modify each and every use of the models with two method calls.

[IceWreck]

Yeah but class definitions can be made such that same model can be used for both. That'd be better since then we will not have to modify each and every use of the models with two method calls.

Yes, this is what the issue is about. Redis isn't a good way to store large amounts of data, but it is what was being used before. It is slowly being replaced postgres (some of the code migration is already done).
@TomasTomecek wants to replace the leftover redis code (such as in the Rest API) with postgres equivalent code.
After everything is complete, the redis models.py will be removed.
@grapheo12 Read the issue post.

[grapheo12]

@grapheo12 Read the issue post.

I am talking of Stage 1 specifically.

[IceWreck]

I am talking of Stage 1 specifically.

In multiple places throughout this repo, data is stored in redis, according to the models defined redis models.py We have to change it to use the models in postgres models.py
In some cases equivalent structures have already been made in the postgres models.py In other cases we will have to modify the postgres models.py to add the required schema

[TomasTomecek]

@IceWreck thanks for the explanation, you're completely right! I assigned the first two points from p1 to you - good luck!

@grapheo12 if you're still interested in working on this, please let us know which point(s) from the main listing are of interest to you; you have raised some good points in your post, though that's about refactoring and improving how p-s deals with sqlalch - please create a new issue for that, since that's out of scope of this one - with this issue, we're trying to shred packit-service/service/models.py as @IceWreck pointed out

[grapheo12]

@TomasTomecek I would like to work on 3 and 4 of Stage 1.

[rishavanand]

@TomasTomecek I would like to take up 5, 6 and 7 (of course) of stage 1.

[TomasTomecek]

@grapheo12 @rishavanand thank you and best of luck to you! please reach out to us if you need any help here

[TomasTomecek]

@rishavanand @grapheo12 how are you guys doing, any progress?

[rishavanand]

@TomasTomecek I was occupied with my GSoC proposal. I've also included this issue in the proposal but I'll be starting working on it this week itself after resolving this dashboard issue

[grapheo12]

@TomasTomecek Sorry. I got occupied in some other work. I will not be applying for GSoC this year, most probably. Will look into packit in my free time. Can you please reassign someone?

[rishavanand]

@TomasTomecek I'll be starting tomorrow with this issue, you may re-assign the tasks to me.

[TomasTomecek]

@TomasTomecek I was occupied with my GSoC proposal. I've also included this issue in the proposal but I'll be starting working on it this week itself after resolving this dashboard issue

Okay, got it!

@TomasTomecek Sorry. I got occupied in some other work. I will not be applying for GSoC this year, most probably. Will look into packit in my free time. Can you please reassign someone?

Thanks for the update, best of luck!

[lbarcziova]

@rishavanand I have started to write the script to migrate data from redis to psql and it requires the model for installations in psql, would you like to work on this part too? (I can do it if not)

[rishavanand]

@lbarcziova Yeah I can take it up, instead of starting with task 5 I'll start with task 3. I might be a bit slow as I have to understand the codebase first.

[lbarcziova]

hey @rishavanand, how is it going?

[rishavanand]

hey @lbarcziova I'm still stuck at local setup

[lbarcziova]

oh, so then if you do not mind, I will do the installation model by myself so that I can finish the migration script

[rishavanand]

sure, go ahead

[lbarcziova]

This is already done.