Move database to postgresql #94

New Issue

oysteikt · 2025-12-16T21:00:43+01:00

oysteikt commented

2025-12-16 21:00:43 +01:00

Most of our services are running on postgres

oysteikt referenced this issue

2025-12-17 04:19:05 +01:00

Fix escaping of newlines on homepage welcome paragraph #97

vegardbm commented

2025-12-17 04:37:59 +01:00

Based on the consequences of bad data in the database, I would like to be able to help to make sure the website is working well again, but I'd need some help to get started.

oysteikt commented

2025-12-17 04:56:42 +01:00

There is a WIP branch for this (move-to-postgres), you'll find it at the sidebar.

Easiest way is probably to make a new postgres database (either with nixos or docker), configure a test instance of the website to use that database, and then test out all the pages and queries until they work. There are some PDO types that needs to be changed at the very least, maybe some raw queries need to be rewritten to postgres dialect as well.

I have already created a copy of the prod database on our postgres server, so that's good to go.

Part 2 of the task (i.e., #97) is to go through the data in the database and clean it up. We can just do that in the psql shell, or with vim and a database dump.

There is a WIP branch for this (`move-to-postgres`), you'll find it at the sidebar. Easiest way is probably to make a new postgres database (either with nixos or docker), configure a test instance of the website to use that database, and then test out all the pages and queries until they work. There are some PDO types that needs to be changed at the very least, maybe some raw queries need to be rewritten to postgres dialect as well. I have already created a copy of the prod database on our postgres server, so that's good to go. Part 2 of the task (i.e., #97) is to go through the data in the database and clean it up. We can just do that in the psql shell, or with vim and a database dump.

vegardbm was assigned by oysteikt

2025-12-17 07:44:29 +01:00

vegardbm commented

2025-12-17 15:44:01 +01:00

Since my original motivation for doing this in the first place was that there was some issues with the data in the database, which seemed to not be the case (see #97). I ask why you want to move to postgresql in the first place. What are the benefits of doing this? Data integrity?

vegardbm commented

2025-12-17 15:46:05 +01:00

Also, I question whether or not I am fit to do this now since I have essentially zero experience with databases.

oysteikt commented

2025-12-17 15:52:25 +01:00

The initial intention was just the irritation that came with mysql not coming up while the database host was down. I was wondering why the website needed to be hosted on mysql at all while most of our other services are running on postgres. It's not important for the integrity, speed or anything else really, the argument for moving is a bit weak.

After thinking it through a few more times, I also realize that we have currently written our sql queries to be compatible with both mysql and sqlite at the same time. We obviously want the production database to be either mysql or postgres so that any database dumps, optimizations, storage redundancy, etc. is done properly, so dropping mysql and using sqlite only is a no go. But since we soon have muscl for mysql soon deployed, that would let people hacking on the website test their changes against their own test database instead of having a single shared test database. Maybe moving to postgres is not such a good move after all.

The initial intention was just the irritation that came with mysql not coming up while the database host was down. I was wondering why the website needed to be hosted on mysql at all while most of our other services are running on postgres. It's not important for the integrity, speed or anything else really, the argument for moving is a bit weak. After thinking it through a few more times, I also realize that we have currently written our sql queries to be compatible with both mysql and sqlite at the same time. We obviously want the production database to be either mysql or postgres so that any database dumps, optimizations, storage redundancy, etc. is done properly, so dropping mysql and using sqlite only is a no go. But since we soon have [muscl](https://git.pvv.ntnu.no/Projects/muscl) for mysql soon deployed, that would let people hacking on the website test their changes against their own test database instead of having a single shared test database. Maybe moving to postgres is not such a good move after all.

oysteikt commented

2025-12-17 15:53:31 +01:00

I don't doubt you would've figured the database stuff out quickly though, it's rather intuitive once you've seen a few statements and played around with it a bit :)