Skip to main content

Database

This section contains various topics on the RSD database.

Database structure​

database diagram

The SQL scripts used to create tables, relations, RLS and RPC's are in the database folder of our repo.

Connecting to the database​

In some cases, it can be useful to connect to the database directly, so you can query it. To do so, first connect to the VM on which your RSD instance lives and go to the directory containing the docker-compose.yml file. Then run the command

docker compose exec database bash

to enter the database Docker container. Then run

psql --dbname=rsd-db --username=rsd

where you should replace the values if you set them differently in your .env. You can now run arbitrary SQL queries as root user.

Database migration scripts​

We publish database migration script during the release. The migration script can be used to upgrade the database structure from the previous version to released version. We use the published database migration script to update out production RSD instance.

All migration scripts are stored in our production repository.

Scheduling repeated tasks​

We recommend to use cron to schedule repeated tasks, like Routine Database Maintenance Tasks. You can use crontab guru to assist with making and understanding crontab entries.

As an example, to clean up maintainer invites older than a year, you could add the following entries to your crontab (making sure to replace values where applicable, e.g. the file location):

30 1 * * * docker-compose --file /home/ubuntu/docker-compose.yml exec -T database psql --dbname=rsd-db --username=rsd --command="DELETE FROM invite_maintainer_for_software WHERE created_at < CURRENT_TIMESTAMP - INTERVAL '1 year';"
30 2 * * * docker-compose --file /home/ubuntu/docker-compose.yml exec -T database psql --dbname=rsd-db --username=rsd --command="DELETE FROM invite_maintainer_for_project WHERE created_at < CURRENT_TIMESTAMP - INTERVAL '1 year';"
30 3 * * * docker-compose --file /home/ubuntu/docker-compose.yml exec -T database psql --dbname=rsd-db --username=rsd --command="DELETE FROM invite_maintainer_for_organisation WHERE created_at < CURRENT_TIMESTAMP - INTERVAL '1 year';"

You can append >> /home/ubuntu/cron.log 2>&1 to a line to write all output and error messages to a text file.

As an alternative to cron, you could use pg_cron instead. This will require some more work to install it, set it up and keeping it up to date.