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]