Databases

Operating database systems is today as simple as never before – thanks to operating system containers such as Docker.

While professionally I still learned to configure an Oracle database perfectly over days in most laborious small work, today one just brings up the container with a few standard configuration options.

Relational databases

The "classics" of databases.

For operation we need, besides the container as such, a volume for the persistent storage, environment variables, and a few configuration options.

Careful with the choice of the user under which the database container should run, since the permissions on the persistent volume carry through.

MariaDB

MariaDB is an open-source project that originated as a fork of MySQL and has largely displaced that one.

Configuration

Example here.

compose.yml:

mariadb:
  container_name: mariadb
  image: mariadb:10.11
  user: "1000:1000"
  restart: unless-stopped
  command: --transaction-isolation=READ-COMMITTED --log-bin=binlog --binlog-format=ROW --log_bin_trust_function_creators=true
  volumes:
    - ./db:/var/lib/mysql
  env_file: .env
  networks:
    - default

.env:

MARIADB_ROOT_PASSWORD=
MARIADB_DATABASE=
MARIADB_USER=
MARIADB_PASSWORD=
TZ=Europe/Berlin
PUID=1000
PGID=1000

Operation

Back up the database (backup or dump):

docker exec DBCONTAINERNAME sh -c 'exec mysqldump -u"root" -p"ROOTPASSWORD" "DBNAME"' > dump.sql

Restore the database from the dump:

cat dump.sql | docker exec -i DBCONTAINERNAME sh -c 'exec mysql -u"root" -p"ROOTPASSWORD" "DBNAME"'

PostgreSQL

PSQL is the most beloved relational database in the open-source community and in modern software projects usually the standard.

compose.yml:

services:
  psqldb:
    container_name: psqldb
    image: postgres:15-alpine
    user: "1000:1000"
    environment:
      POSTGRES_DB: ${POSTGRES_DB}
      POSTGRES_USER: ${POSTGRES_USER}
      POSTGRES_PASSWORD: ${POSTGRES_PASSWORD}
    restart: unless-stopped
    volumes:
      - ./db:/var/lib/postgresql/data
    networks:
      - default

.env:

POSTGRES_DB=
POSTGRES_PASSWORD=
POSTGRES_USER=
PUID=1000
PGID=1000
TZ=Europe/Berlin