data_backup.sh

#!/usr/bin/env bash

set -euo pipefail

DB_NAME="testlink"
BACKUP_ROOT="./db_backups"
TIMESTAMP=$(date +"%Y%m%d_%H%M%S")
BACKUP_DIR="${BACKUP_ROOT}/${DB_NAME}_${TIMESTAMP}"

mkdir -p "$BACKUP_DIR"

echo "=== Detecting environment ==="

DOCKER_MODE=false

if command -v docker >/dev/null 2>&1; then
    if docker ps >/dev/null 2>&1; then
        DOCKER_MODE=true
    fi
fi

ENGINE="unknown"
DATA_PATH=""
CONTAINER_NAME=""

detect_engine() {

    if $DOCKER_MODE; then

        if docker ps --format '{{.Names}}' | grep -qi maria; then
            ENGINE="mariadb"
            CONTAINER_NAME=$(docker ps --format '{{.Names}}' | grep -i maria | head -1)
            DATA_PATH="/var/lib/mysql"

        elif docker ps --format '{{.Names}}' | grep -qi mysql; then
            ENGINE="mysql"
            CONTAINER_NAME=$(docker ps --format '{{.Names}}' | grep -i mysql | head -1)
            DATA_PATH="/var/lib/mysql"

        elif docker ps --format '{{.Names}}' | grep -qi postgres; then
            ENGINE="postgresql"
            CONTAINER_NAME=$(docker ps --format '{{.Names}}' | grep -i postgres | head -1)
            DATA_PATH="/var/lib/postgresql/data"

        elif docker ps --format '{{.Names}}' | grep -qi sqlserver; then
            ENGINE="mssql"
            CONTAINER_NAME=$(docker ps --format '{{.Names}}' | grep -i sqlserver | head -1)
            DATA_PATH="/var/opt/mssql"

        fi

    else

        if pgrep mysqld >/dev/null; then
            ENGINE="mysql"
            DATA_PATH="/var/lib/mysql"

        elif pgrep postgres >/dev/null; then
            ENGINE="postgresql"
            DATA_PATH="/var/lib/postgresql"

        elif pgrep sqlservr >/dev/null; then
            ENGINE="mssql"
            DATA_PATH="/var/opt/mssql"
        fi
    fi
}

freeze_db() {

    echo "=== Freezing DB (${ENGINE}) ==="

    case "$ENGINE" in

        mysql|mariadb)
            if $DOCKER_MODE; then
                docker exec "$CONTAINER_NAME" mysql -uroot -prootpass \
                    -e "FLUSH TABLES WITH READ LOCK;"
            else
                mysql -uroot -prootpass \
                    -e "FLUSH TABLES WITH READ LOCK;"
            fi
            ;;

        postgresql)
            if $DOCKER_MODE; then
                docker exec "$CONTAINER_NAME" psql -U postgres \
                    -c "CHECKPOINT;"
            else
                psql -U postgres -c "CHECKPOINT;"
            fi
            ;;

        mssql)
            echo "MSSQL snapshot consistency depends on filesystem/VSS."
            ;;

    esac
}

unfreeze_db() {

    echo "=== Releasing DB locks ==="

    case "$ENGINE" in

        mysql|mariadb)
            if $DOCKER_MODE; then
                docker exec "$CONTAINER_NAME" mysql -uroot -prootpass \
                    -e "UNLOCK TABLES;"
            else
                mysql -uroot -prootpass \
                    -e "UNLOCK TABLES;"
            fi
            ;;

        *)
            ;;
    esac
}

backup_data() {

    echo "=== Creating binary backup ==="

    if $DOCKER_MODE; then

        docker run --rm \
            --volumes-from "$CONTAINER_NAME" \
            -v "$(pwd)/${BACKUP_DIR}:/backup" \
            alpine \
            tar czf "/backup/${ENGINE}_${DB_NAME}_${TIMESTAMP}.tar.gz" \
            "$DATA_PATH"

    else

        sudo tar czf \
            "${BACKUP_DIR}/${ENGINE}_${DB_NAME}_${TIMESTAMP}.tar.gz" \
            "$DATA_PATH"

    fi
}

detect_engine

if [ "$ENGINE" = "unknown" ]; then
    echo "Unsupported or undetected DB engine."
    exit 1
fi

echo "Detected engine: $ENGINE"

freeze_db

trap unfreeze_db EXIT

backup_data

echo "=== Backup completed ==="
echo "Location: ${BACKUP_DIR}"