Database Operations

Supported databases

Rucio uses SQLAlchemy as the object-relational mapper between Python and SQL. Rucio is extensively tested against SQLite, MySQL/MariaDB, PostgreSQL, and Oracle and should work with them out of the box. The appropriate connection string has to be set in the etc/rucio.cfg, for example:

Oracle: oracle://rucio:rucio@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=10121))(ENABLE=BROKEN)(CONNECT_DATA=(SERVICE_NAME=localhost)))

MySQL/MariaDB: mysql+pymysql://rucio:rucio@localhost/rucio

PostgreSQL: postgresql://rucio:rucio@localhost:6601/rucio

SQLite: sqlite:////tmp/rucio.db

Please ensure correct UNIX permissions on the SQLite file, such that the web server process can read and write to it.

Additional options

Connection pooling

Connection pooling is enabled by default, but can be disabled by setting the option

poolclass = nullpool

in the [database] section in etc/rucio.cfg.

Other valid values are singletonthreadpool, which is the default pooling mechanism when using the SQLite engine, and queuepool, which is the default otherwise.

Note that the chosen poolclass may conflict with other pooling options. For instance, one cannot combine poolclass = nullpool with the pool_size option.

Upgrading and downgrading the database schema

Rucio uses Alembic as a database migration tool, which works hand-in-hand with SQLAlchemy. Ensure that in your alembic.ini file the database connection string is set to the same database connection string as the rucio.cfg.

Prerequisites for running Alembic locally

Before running any Alembic commands, you must satisfy these requirements:

Run from a directory with Rucio installed - Not from inside lib/rucio/db/sqla, as this may cause Python module shadowing and import errors.
Have an alembic.ini file - Initialize it if not present using alembic init
Point migrations to upstream revisions - Configure script_location in alembic.ini to point to lib/rucio/db/sqla/migrate_repo where all upstream Rucio migrations are stored

Step-by-step setup:

# 1. Navigate to the Rucio repository root
cd /path/to/rucio

# 2. Install Rucio and related dependencies (e.g. alembic and psycopg2)
pip install .

# 3. If alembic.ini doesn't exist, initialize it
alembic init alembic

# 4. Edit alembic.ini and set the database connection string
# sqlalchemy.url = postgresql://rucio:PASSWORD@localhost:5432/rucio

# 5. Edit alembic.ini and point to the upstream migration revisions
# script_location = lib/rucio/db/sqla/migrate_repo

Running migrations

First, check the current migration state of your database:

alembic --config path-to/alembic.ini current

Manual migration state check

If the output is blank, it means your database is at the base state (no migrations applied). This could interfere with the upgrade process if your database schema is not actually empty, so you may need to manually set the alembic_version to the correct revision (see "Manual database migration troubleshooting" section below).

To verify the changes that will be applied during a schema upgrade:

alembic --config path-to/alembic.ini upgrade --sql $(alembic --config path-to/alembic.ini current | awk '{print $1}'):head

You can edit and then apply the SQL directly on your database, or run the automatic upgrade:

alembic --config path-to/alembic.ini upgrade head

We do not advise running automatic upgrades/downgrades with alembic in production environments.

info

Schema upgrades are reserved for feature releases and will not happen with patch releases.

Manual database migration troubleshooting

warning

Manual operations on the database are dangerous and should only be attempted when standard Alembic upgrades fail. Always backup your database before proceeding!

If alembic upgrade head fails or reports no pending migrations when you expect changes, you may need to manually inspect or reset the migration state. This can happen after major version upgrades or when the alembic_version table becomes out of sync.

Checking current migration state

alembic --config path-to/alembic.ini current

Understanding migration revisions

All Rucio migration revisions are stored in lib/rucio/db/sqla/migrate_repo/versions/. Each revision file has a standard structure with revision and down_revision identifiers:

revision = '295289b5a800'
down_revision = 'a6eb23955c28'

These identifiers form a linked chain that tracks the migration history. To use these migrations with Alembic, you must configure your alembic.ini to point to this directory as the script_location:

# In alembic.ini
script_location = lib/rucio/db/sqla/migrate_repo

tip

To find the migration sequence and associate revisions with specific Rucio versions:

Use git checkout tags/X.Y.Z to switch to a specific Rucio release
Run migration chain scripts to trace which revisions were in that release
Cross-reference with your current alembic_version to find the gap

To find the migration sequence, use:

# Find which revision comes after a given one
grep --recursive --ignore-case 'a6eb23955c28' lib/rucio/db/sqla/migrate_repo/versions | grep "down_revision = 'a6eb23955c28'"

Or use this bash script to trace the full migration chain from a starting revision:

#!/bin/bash
export I_REVISION=$1
export NEXT_REV=$I_REVISION
while [[ -n $NEXT_REV ]]; do
    echo $NEXT_REV
    NEXT_REV=$(grep --recursive --ignore-case "down_revision = '${NEXT_REV}'" lib/rucio/db/sqla/migrate_repo/versions \
      | awk -F'/' '{print $NF}' \
      | awk -F'_' '{print $1}' \
      | head -n1)
done

Forcefully resetting the migration version

If you need to reset your database to a specific migration state (for debugging or recovery after failed upgrades), you can manually update the alembic_version table:

# Connect to your Rucio database
psql --host <DB_HOST> --username rucio --port <PORT> --dbname rucio

# Check the current recorded migration version
SELECT * FROM alembic_version;

# Forcefully reset to a specific revision by replacing the current record (be cautious!)
# (Note: The alembic_version table typically contains a single row with the version_num column)
DELETE FROM alembic_version;
INSERT INTO alembic_version (version_num) VALUES ('140fef722e91');

Then verify with Alembic:

alembic --config path-to/alembic.ini current

Applying migrations incrementally

After resetting the alembic_version, you can apply migrations one at a time to identify which one fails:

alembic --config path-to/alembic.ini upgrade <revision_id>

Example sequence:

alembic --config path-to/alembic.ini upgrade fb28a95fe288
alembic --config path-to/alembic.ini upgrade a6eb23955c28
alembic --config path-to/alembic.ini upgrade 295289b5a800
# ... continue until head

Inspecting database schema

To verify if a specific migration has been applied, check the database directly:

psql --host <DB_HOST> --username rucio --port <PORT> --dbname rucio
\d requests  # List table structure

Compare the columns against the upgrade() function in the relevant migration file.

Creating a new version as a developer

If you want to create an upgrade path for the schema, you need to generate a schema upgrade+downgrade file:

alembic revision --message 'schema change message'

This will output the name of the file that has been generated with two functions def upgrade() and def downgrade() that need to be implemented. These should reflect the changes to the lib/rucio/db/sqla/models.py SQLAlchemy mapping.

Supported databases​

Additional options​

Connection pooling​

Upgrading and downgrading the database schema​

Prerequisites for running Alembic locally​

Running migrations​

Manual database migration troubleshooting​

Checking current migration state​

Understanding migration revisions​

Forcefully resetting the migration version​

Applying migrations incrementally​

Inspecting database schema​

Creating a new version as a developer​