Database Management

SQL database management (database creation, schema updates etc) can be run from a k8s pod with a rucio installation and alembic configuration.

Initialise the database

Alembic Configuration

Build database

Note: the rucio repository distributes a bootstrap tool which builds the database schema and creates a root account. Here, we just run the equivalent python commands inside the server pod.

Log into the rucio server pod and create the database directly in python:

>>> from rucio.db.sqla.util import build_database
>>> build_database()
INFO  [alembic.runtime.migration] Context impl MySQLImpl.
INFO  [alembic.runtime.migration] Will assume non-transactional DDL.
INFO  [alembic.runtime.migration] Running stamp_revision  -> 2cbee484dcf9

Create Root Account

Edit the [bootstrap] section of rucio.cfg to create initial root identities:

[bootstrap]
x509_identity = "DC=org/DC=XXXXXXX/C=XX/O=XXXX/CN=Albert Einstein albert.einstein@ligo.org"
x509_email = "albert.einstein@ligo.org"
userpass_identity = albert
userpass_pwd = bacb8d7621642e2f4ae0ea388d7a75f4f1ade1a2325f8be0bbe666300e114559
userpass_email = "albert.einstein@ligo.org"
gss_identity=None
gss_email=None
ssh_identity=None
ssh_email=None

Set gss/ssh values to None to avoid using default values. The userpass_pwd is a hexdigest of a password (“einstein” here) with a zero prepended. E.g.,:

>>> hashlib.sha256("0einstein").hexdigest()
'bacb8d7621642e2f4ae0ea388d7a75f4f1ade1a2325f8be0bbe666300e114559'

So that the password in the [client] section of the rucio.cfg is “einstein”. Create the root account:

>>> from rucio.db.sqla.util import create_root_account
>>> create_root_account()

Check the identities thus created:

bash-4.2# rucio-admin account list-identities root
Identity: /DC=org/DC=incommon/C=US/ST=CA/L=Pasadena/O=California Institute of Technology/OU=Laser Interferometer Gravitational-Wave Observatory/CN=rucio.ligo.caltech.edu,    type: X509
Identity: albert,     type: USERPASS
Identity: None,       type: GSS
Identity: None,       type: SSH

Updating The Schema

Check what version the database schema is:

bash-4.2# pwd
/opt/rucio/etc
bash-4.2# python -m alembic.config current
INFO  [alembic.runtime.migration] Context impl MySQLImpl.
INFO  [alembic.runtime.migration] Will assume non-transactional DDL.
2cbee484dcf9 (head)

Log into the rucio server pod and run:

bash-4.2# alembic upgrade head
INFO  [alembic.runtime.migration] Context impl MySQLImpl.
INFO  [alembic.runtime.migration] Will assume non-transactional DDL.

Database Backups & Kubernetes

To manually backup postgresql databases, use the pg_dump or pg_dumpall commands with the kubectl exec

DUMP
// pod-name         name of the postgres pod
// postgres-user    database user that is able to access the database
// database-name    name of the database
kubectl exec [pod-name] -- bash -c "pg_dump -U [postgres-user] [database-name]" > database.sql

RESTORE
// pod-name         name of the postgres pod
// postgres-user    database user that is able to access the database
// database-name    name of the database
cat database.sql | kubectl exec -i [pod-name] -- psql -U [postgres-user] -d [database-name]