===================
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
<https://github.com/rucio/rucio/blob/master/tools/bootstrap.py>`_ which builds
the database schema and creates a root account. Here, we just run the
equivalent python commands inside the server pod.

.. highlight:: python
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 :code:`[bootstrap]` section of :code:`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 :code:`None` to avoid using default values.   The
:code:`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 :code:`[client]` section of the :code:`rucio.cfg`
is "einstein".  Create the root account::

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

.. highlight:: bash
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
===================

.. highlight:: bash
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)


.. highlight:: bash
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]