Enable automated migration of the PostgreSQL database.

[yakutovicha]

This issue has the following dependencies:

• Enable automated migration of PostgreSQL database. aiidateam/aiida-prerequisites#41
• Provide an interface to perform the PostgreSQL database migration aiidalab-home#94

---

The migration scripts down below are based on the official migration recipes as suggested by @ltalirz.

To clarify:

• Identify at which point of the container's life the migration should happen: at startup or at runtime?
  [Answer]: It should be run at runtime as we cannot guarantee that there is enough time during the startup.
• Decide whether we want to enable the migration inside the aiidalab (or aiida) container, or maybe we want to provide a separate dedicated container for this?
  [Answer]: It will be installed in the same container.
  • Should we install both: the old and new versions of Postgres in the same container. [Answer: YES]
  • Depending on the previous answer: how would we interface migration to the user? Should we run it in the background, or should we put a big message on the startup screen saying that "Image is updated, migration is required"?
    [Answer]: In AiiDAlab, it would be a part of the Home app. The user will be notified about the possible migration and provided with an option to run it. The migration will block all the AiiDA operations.
• Agree whether we want to use the recommended PostgreSQL way of migration (e.g. via pg_dump) or should we use less tested pg_upgrade instead?
  [Answer: pg_dump]

Possible upgrade path:

1. Have both (previous and current) versions of PostgreSQL in the same container
2. In case the default version is not the newest one - signal to the user about this: "New version of PostgreSQL is available, please upgrade".
3. Block the interface for the whole upgrade process.
4. Perform the dump of the database from the old PostgreSQL.
5. Switch to the newer PostgreSQL and import the dump.
6. Switch the default PostgreSQL
7. Restart the container.
8. Signal to the user that the old database and database dump are still in the filesystem. Propose them to delete those.

[yakutovicha]

content of migrate_databases.sh

#!/bin/bash
set -x # Print what is happening (debug mode).

if [ ${SYSTEM_USER} != `whoami` ]
  then
    echo "You should run this script as ${SYSTEM_USER} user, while currently you are logged in as `whoami`"
    exit 1
fi

# Make sure to be in user's home folder.
cd ~

# Make sure the new PostgreSQL is shut down (to free the 5432 port).
/usr/lib/postgresql/10/bin/pg_ctl -D .postgresql stop

# Start old PostgreSQL on old database.
/usr/lib/postgresql/9.6/bin/pg_ctl --timeout=180 -w -D .postgresql_old/ -l .postgresql_old/logfile start

# Dump old PostgreSQL database.
/usr/lib/postgresql/9.6/bin/pg_dump  --host /home/aiida/.postgresql_old/ aiidadb > aiida_db.dump

# Stop old PostgreSQL server.
/usr/lib/postgresql/9.6/bin/pg_ctl -D .postgresql_old stop

# Start new PostgreSQL server.
/usr/lib/postgresql/10/bin/pg_ctl --timeout=180 -w -D .postgresql/ -l .postgresql/logfile start

# Creste new aiidadb database.
psql -h localhost -d template1 -c "CREATE DATABASE aiidadb OWNER aiida;"

# Grant access to aiidadb database to aiida user.
/usr/lib/postgresql/10/bin/psql -h /home/aiida/.postgresql -d template1 -c "GRANT ALL PRIVILEGES ON DATABASE aiidadb to aiida;"

# Import old database dump into the new PostgreSQL database.
/usr/lib/postgresql/10/bin/psql -d aiidadb -h /home/aiida/.postgresql -f aiida_db.dump

# Stop aiida daemon to start the migration.
verdi daemon stop

# Migrate AiiDA database.
verdi -p default database migrate -f

[yakutovicha]

content of migrate_user.sh

#!/bin/bash
set -x # Print what is happening (debug mode).
set -e # Exit if an operation fails.

if [ 'jupyterhub' != `whoami` ]
  then
    echo "You should run this script as jupyterhub user, while currently you are logged in as `whoami`"
    exit 1
fi

if [ $# -ne 1 ]
  then
    echo "Please provide one argument only"
    exit 1
fi

USER=${1}

if [ ! -d ${USER} ]
  then
    echo "Folder ${USER} does not exist."
    exit 1
fi


# Container name should not contain @ symbols
CONTAINER_NAME=`echo ${USER} | sed 's/@/_/'`

# For safety backup user's home directory.
cp -r ${USER} ${USER}_bak

# Backup postgres database.
mv ${USER}/.postgresql/ ${USER}/.postgresql_old

# In new aiidalab home folder is /home/aiida and not /project as it was.
sed 's/\/project/\/home\/aiida/' ${USER}/.postgresql_old/postgresql.conf -i

# Change postgres database folder location in the backup.
sed 's/.postgresql/.postgresql_old/' ${USER}/.postgresql_old/postgresql.conf -i

# Modify home folder in aiida config.
sed 's/\/project/\/home\/aiida/' ${USER}/.aiida/config.json -i

# Remove .bashrc file - it will be re-created by the new aiida.
rm ${USER}/.bashrc

# Remove old .ipython configuration folder, otherwise 'verdi shell' may not work as expected.
rm ${USER}/.ipython -rf

# Remove all the aiidalab apps to avoid problems.
rm ${USER}/apps/ -rf

# Launch the docker container attaching user's home folder to it.
docker run -d --name ${CONTAINER_NAME} -v /var/jupyterhub/volumes/${USER}:/home/aiida/  -e SYSTEM_USER_UID=1234 -e SYSTEM_USER_GID=1234 aiidateam/aiida-core:latest

# Wait until the container is properly started.
docker exec --tty --user root ${CONTAINER_NAME} wait-for-services

# Install old PostgreSQL in the running container.
docker exec --tty --user root ${CONTAINER_NAME} /opt/setup_old_postgres.sh

# Migrate PostgreSQL and AiiDA databases.
docker exec --tty --user aiida ${CONTAINER_NAME} /opt/migrate_databases.sh

[yakutovicha]

content of setup_old_postgres.sh

#!/bin/bash

# Run this script as root, since it installs Postgres

set -x # Print what is happening (debug mode).
set -e # Exit if an operation fails.

if [ $# -gt 1 ]
  then
    echo "Please provide none or one argument only."
    exit 1
fi

POSTGRES_VERSION=9.6
if [ $# -eq 1 ]
  then
    POSTGRES_VERSION=${1}
fi

# Add postgres repository to the list of ubuntu repositories.
sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt/ `lsb_release -cs`-pgdg main" >> /etc/apt/sources.list.d/pgdg.list'

# I don't know what
wget -q https://www.postgresql.org/media/keys/ACCC4CF8.asc -O - | apt-key add -

# Update the list of packages.
apt-get update
apt-get upgrade -y

# Install old PostgreSQL.
apt-get install postgresql-${POSTGRES_VERSION} -y

[csadorf]

My recommendation is to

1. Install the migration script in the container.
2. Add a button to the home screen that signals that a migration is required/recommended (depends on the migration).
3. Automatically run the migration script on managed instances.

[yakutovicha]

Here are the issues in the corresponding repositories:
aiida-prerequisites: aiidateam/aiida-prerequisites#41
aiidalab-home: aiidalab/aiidalab-home#94

[unkcpz]

This should be fixed by aiidateam/aiida-prerequisites#63