Seeding databases using docker-compose on hosted servers with persistence

[psychemedia]

The TM351 environment set-up requires the availability of two database servers, a PostgreSQL server and a MongoDB server.

• postgres: requires creation of users, user roles, databases, seeding of databases;
• mongo: requires creation of db and seeding of collections.

In a hosted setting, with persistence, we can imagine the following possible configurations:

• the database files are contained solely within a container but any changes / modifications are lost when the container instance is destroyed;
• the database files are saved in a mounted volume that persists when the database container instance is destroyed and that is remounted to a when a new database container instance is created; the volume and the files it contains is not visible as such to the user;
• the database files are saved in a volume mounted from a user's account; the user has access to the files from their account.

In the first instance, where the database files reside solely in the container, we can seed the database when the image is built. Where volumes are mounted, we need to ensure that we don't clobber the database files when the volume is mounted, and also ensure that when the database is seeded the files are saved into the mounted volume. If a docker-composition is started, stopped, and started again, we need to ensure that the persistent mounted database storage volume is seeded on the first run only. (Ideally, we also need a way of destroying the mounted storage volume and reseeding a fresh one if the user mangles the database somehow.)

Seeding a postgres database:

• using the official postgres docker image Dockerfile, we can COPY startup MY_INIT_SCRIPT.sql files into /docker-entrypoint-initdb.d/, remembering to set chmod a+r /docker-entrypoint-initdb.d/*; mounting a volume against /var/lib/postgresql/data will persist the data. (When you tear down a postgres container and then reinstantiate another one the the init script should only run once, eg as described here (docs))

Seeding a mongo database:

• the recipe at https://stackoverflow.com/a/55819683 describes how to seed a mongo database by running a mongorestore command from another connected container on start-up; the restore command could presumably be prefaced by a test for the absence of a .firstrun file in the volume that is touch-created after a successful restore?
• the official Docker MongoDB image supports execution of .sh and .js files in /docker-entrypoint-initdb.d (docs).

Mongo and Postgres official Docker images will also both try to run .sh files found in /docker-entrypoint-initdb.d.

[psychemedia]

Looking at eg the postgres Dockerfle we see the following entrypoint command: ENTRYPOINT ["docker-entrypoint.sh"]

The docker-entrypoint.sh script includes the logic for testing whether the initialisation has already run, and if not, handles it, as well as making sure the server is running.

The notebook server started from vce-jupyter-stacks/base-notebook/ uses a CMD run under the ENTRYPOINT started tini server that calls on start-notebook.sh (or in JupyterHub, start-singleuser.sh). Updates to these files could handle Jupyter initialisations such as the copying (if required) of custom branding elements into $HOME/.jupyter etc.

[psychemedia]

Demo in https://github.com/OpenComputingLab/vce-jupyter-stacks/tree/main/tm351-compose