docker-create-postgresql

#!/bin/bash
LANG=C
set -euo pipefail

cmdname="$(basename "$0")"
newtmpdir="$(mktemp -d /tmp/${cmdname}.XXXXXX)"
user="$(whoami)"
OS="$(uname)"

[ "${user}" != "root" ] && echo "sudo ${cmdname}" && exit 1

cleanup () {
    rm -rf "${newtmpdir}"
}

trap 'cleanup' EXIT
trap 'cleanup' SIGTERM

docker_bin="$(command -v docker || true)"
if [ -z "$docker_bin" ]; then
    echo "Docker not installed. Proceed with install? (CTRL+C to Abort)"
    echo "or run install manually: apt-get -y install docker-ce"
    read -r _
    apt-get -y install docker-ce
fi

usage() {
cat <<EOF

Usage: ${cmdname}

Interactive script to download and setup PostgreSQL docker configuration.

- Asks for PostgreSQL PORT and IMAGE TAG.
- Asks bind address: 127.0.0.1 / 0.0.0.0 / auto external IPv4.
- Creates persistent data dir under /var/lib/postgresql-<PORT>/data.
- Starts docker container with restart unless-stopped.
- Adjusts basic memory settings via -c on postgres.
- Adds entry to ~/.pgpass.
- Optionally sets up daily pg_dumpall backup via cron into /backup/<hostname>/.

No external postgresql.conf is mounted; container uses its own default config.

EOF
}

case "${1:-}" in
    -h|--help)
        usage
        exit 0
    ;;
esac

# -------------------------
# Helpers
# -------------------------

get_total_mem_bytes_linux() {
    awk '/MemTotal:/ {print $2 * 1024}' /proc/meminfo 2>/dev/null || echo 0
}

get_total_mem_bytes_darwin() {
    sysctl -n hw.memsize 2>/dev/null || echo 0
}

get_total_mem_bytes() {
    if [ "$OS" = "Darwin" ]; then
        get_total_mem_bytes_darwin
    else
        get_total_mem_bytes_linux
    fi
}

bytes_to_mb() { echo $(( $1 / 1024 / 1024 )); }
bytes_to_gb() { echo $(( $1 / 1024 / 1024 / 1024 )); }

detect_primary_ipv4() {
    local primary=""
    if command -v ip >/dev/null 2>&1; then
        primary="$(ip route get 1.1.1.1 2>/dev/null | awk '/src/ {for(i=1;i<=NF;i++) if ($i=="src") {print $(i+1); exit}}' || true)"
    fi
    if [ -z "$primary" ]; then
        primary="$(hostname -I 2>/dev/null | awk '{print $1}' || true)"
    fi
    echo "${primary:-}"
}

detect_target_home() {
    if [ "$OS" = "Darwin" ]; then
        eval echo "~${SUDO_USER:-$user}"
        return
    fi
    if [ -n "${SUDO_USER:-}" ] && [ -d "/home/${SUDO_USER}" ]; then
        echo "/home/${SUDO_USER}"
    else
        eval echo "~${SUDO_USER:-$user}"
    fi
}

# -------------------------
# Input: PORT
# -------------------------

echo "Please enter database PORT (eg 5432), CTRL+C to abort: "
read -r docker_port
[ -z "${docker_port}" ] && echo "Error: Empty port!" && exit 1

if command -v lsof >/dev/null 2>&1; then
    if lsof -i:"${docker_port}" 2>/dev/null | grep -q LISTEN; then
        echo "ERROR: Port ${docker_port} already in use!"
        lsof -i:"${docker_port}" 2>/dev/null | grep LISTEN || true
        exit 1
    fi
else
    echo "Warning: lsof not found, skipping port check."
fi

# -------------------------
# Input: BIND address
# -------------------------

primary_ip="$(detect_primary_ipv4)"

echo
echo "Choose bind address for published port ${docker_port}:"
echo "  1) Local only        (127.0.0.1)"
echo "  2) All IPv4          (0.0.0.0)"
if [ -n "$primary_ip" ]; then
    echo "  3) Primary external  (${primary_ip})"
else
    echo "  3) Primary external  (auto-detect failed; will ask)"
fi
echo "Enter choice [1-3] (default 1): "
read -r bind_choice
bind_choice="${bind_choice:-1}"

bind_ip="127.0.0.1"

case "$bind_choice" in
    1)
        bind_ip="127.0.0.1"
    ;;
    2)
        bind_ip="0.0.0.0"
    ;;
    3)
        if [ -z "$primary_ip" ]; then
            echo "Enter external IPv4 to bind (eg 203.0.113.10): "
            read -r primary_ip
            [ -z "$primary_ip" ] && echo "Error: Empty IP!" && exit 1
        fi
        bind_ip="$primary_ip"
    ;;
    *)
        echo "Error: Unknown choice!"
        exit 1
    ;;
esac

# -------------------------
# Input: VERSION/TAG
# -------------------------

echo
echo "Available common PostgreSQL tags (examples):"
echo "  9, 10, 11, 12, 13, 14, 15, 16, 17, 18, latest"
echo "You can also enter full tags like 16-alpine if you want."
echo "Please enter database IMAGE TAG (default latest), CTRL+C to abort: "
read -r docker_pgversion
docker_pgversion="${docker_pgversion:-latest}"

# -------------------------
# Names/paths/password
# -------------------------

docker_superuser="postgres"
docker_servicename="postgresql-${docker_port}"

# Safe password generation with pipefail
docker_pass="$(head -c 128 /dev/urandom | tr -dc 'a-zA-Z0-9' | head -c 14 || true)"
if [ -z "$docker_pass" ]; then
    docker_pass="$(date +%s%N | sha256sum | awk '{print $1}' | head -c 14)"
fi

base_path="/var/lib/${docker_servicename}"
data_path="${base_path}/data"

mkdir -p "${data_path}"

if [ "$OS" = "Darwin" ]; then
    chmod 777 "${base_path}" "${data_path}" || true
else
    chown -R 999:999 "${data_path}" 2>/dev/null || true
    chmod 700 "${data_path}" 2>/dev/null || true
fi

echo "created data dir: ${data_path}"

# -------------------------
# Memory-aware minimal tuning (only safe params)
# -------------------------

total_mem_bytes="$(get_total_mem_bytes)"
if [ "${total_mem_bytes}" -le 0 ]; then
    total_mem_bytes=$((4 * 1024 * 1024 * 1024)) # fallback 4GB
fi

total_mem_mb="$(bytes_to_mb "$total_mem_bytes")"
total_mem_gb="$(bytes_to_gb "$total_mem_bytes")"

# shared_buffers ~25% RAM
shared_buffers_mb=$(( total_mem_mb / 4 ))
[ "$shared_buffers_mb" -lt 128 ] && shared_buffers_mb=128

# effective_cache_size ~75% RAM
effective_cache_mb=$(( total_mem_mb * 3 / 4 ))
[ "$effective_cache_mb" -lt 512 ] && effective_cache_mb=512

# maintenance_work_mem ~5% RAM, with caps
maintenance_mb=$(( total_mem_mb / 20 ))
[ "$maintenance_mb" -lt 64 ] && maintenance_mb=64
[ "$maintenance_mb" -gt 2048 ] && maintenance_mb=2048

# conservative work_mem
work_mem_mb=$(( total_mem_mb / 100 / 10 ))
[ "$work_mem_mb" -lt 4 ] && work_mem_mb=4
[ "$work_mem_mb" -gt 64 ] && work_mem_mb=64

max_connections=100

echo "Host RAM detected: ~${total_mem_gb} GB (${total_mem_mb} MB)"
echo "Tuning:"
echo "  shared_buffers       = ${shared_buffers_mb}MB"
echo "  effective_cache_size = ${effective_cache_mb}MB"
echo "  maintenance_work_mem = ${maintenance_mb}MB"
echo "  work_mem             = ${work_mem_mb}MB"
echo "  max_connections      = ${max_connections}"

# -------------------------
# Run container
# -------------------------

if docker ps -a --format '{{.Names}}' | grep -q "^${docker_servicename}$"; then
    echo "Container ${docker_servicename} already exists. Removing..."
    docker rm -f "${docker_servicename}" >/dev/null
fi

publish_spec="${bind_ip}:${docker_port}:5432"

echo
echo "Starting container:"
echo "  name   = ${docker_servicename}"
echo "  image  = postgres:${docker_pgversion}"
echo "  bind   = ${publish_spec}"
echo "  data   = ${data_path}"

docker run --restart unless-stopped -d \
  --name "${docker_servicename}" \
  -e POSTGRES_USER="${docker_superuser}" \
  -e POSTGRES_PASSWORD="${docker_pass}" \
  -v "${data_path}:/var/lib/postgresql/data" \
  -p "${publish_spec}" \
  "postgres:${docker_pgversion}" \
  postgres \
    -c "shared_buffers=${shared_buffers_mb}MB" \
    -c "effective_cache_size=${effective_cache_mb}MB" \
    -c "maintenance_work_mem=${maintenance_mb}MB" \
    -c "work_mem=${work_mem_mb}MB" \
    -c "max_connections=${max_connections}"

# -------------------------
# ~/.pgpass
# -------------------------

target_home="$(detect_target_home)"
pgpass_file="${target_home}/.pgpass"

mkdir -p "${target_home}"
touch "${pgpass_file}"

# If bound to 0.0.0.0, local client still uses 127.0.0.1
pgpass_host="127.0.0.1"
if [ "$bind_ip" != "0.0.0.0" ]; then
    pgpass_host="$bind_ip"
fi

pgpass_line="${pgpass_host}:${docker_port}:*:${docker_superuser}:${docker_pass}"
grep -qxF "${pgpass_line}" "${pgpass_file}" 2>/dev/null || echo "${pgpass_line}" >> "${pgpass_file}"

chmod 600 "${pgpass_file}" 2>/dev/null || true
if [ -n "${SUDO_USER:-}" ] && [ "$OS" != "Darwin" ]; then
    chown "${SUDO_USER}:${SUDO_USER}" "${pgpass_file}" 2>/dev/null || true
fi

# -------------------------
# Optional BACKUP
# -------------------------

echo
echo "Enable daily backup via cron? (y/N): "
read -r enable_backup
enable_backup="${enable_backup:-N}"

backup_script_path=""
backup_dir_base="/backup"
backup_host="$(hostname -s 2>/dev/null || hostname)"
retention_days=15

cron_hour=2
cron_minute=30
cron_status="disabled"

if [[ "${enable_backup}" =~ ^[Yy]$ ]]; then
    backup_script_path="/usr/local/bin/${docker_servicename}-backup"

    mkdir -p "${backup_dir_base}/${backup_host}"

    cat > "${backup_script_path}" <<EOF
#!/bin/bash
set -euo pipefail

# Auto-generated backup script for ${docker_servicename}
# You can edit this file safely.

CONTAINER_NAME="${docker_servicename}"
PGUSER="${docker_superuser}"

BACKUP_BASE="${backup_dir_base}"
HOSTNAME_STR="\$(hostname -s 2>/dev/null || hostname)"
BACKUP_DIR="\${BACKUP_BASE}/\${HOSTNAME_STR}"

RETENTION_DAYS=${retention_days}

DATE_STR="\$(date +%F_%H-%M-%S)"
OUT_FILE="\${BACKUP_DIR}/\${CONTAINER_NAME}_\${DATE_STR}.sql.gz"

mkdir -p "\${BACKUP_DIR}"

# Dump all databases and roles, gzip on host side.
docker exec -i "\${CONTAINER_NAME}" pg_dumpall -U "\${PGUSER}" | gzip -9 > "\${OUT_FILE}"

# Purge old backups for this service
find "\${BACKUP_DIR}" -type f -name "\${CONTAINER_NAME}_*.sql.gz" -mtime +\${RETENTION_DAYS} -delete

echo "Backup OK: \${OUT_FILE}"
EOF

    chmod 750 "${backup_script_path}" 2>/dev/null || true

    if command -v crontab >/dev/null 2>&1; then
        existing_cron="$(crontab -l 2>/dev/null || true)"
        cron_line="${cron_minute} ${cron_hour} * * * ${backup_script_path} >/dev/null 2>&1"

        if ! echo "${existing_cron}" | grep -Fq "${backup_script_path}"; then
            ( echo "${existing_cron}"; echo "${cron_line}" ) | crontab -
            cron_status="enabled"
        else
            cron_status="already-present"
        fi
    else
        cron_status="crontab-not-found"
    fi
fi

# -------------------------
# /etc/info
# -------------------------

cat <<EOF

OK - new PostgreSQL server created:

Service  = ${docker_servicename}
Image    = postgres:${docker_pgversion}

Host bind:
  host     = ${pgpass_host}
  port     = ${docker_port}
  user     = ${docker_superuser}
  password = ${docker_pass} (saved to ${pgpass_file})

Paths:
  data     = ${data_path}

Quick connect:
  psql -h ${pgpass_host} -p ${docker_port} -U ${docker_superuser}

Inside container:
  docker exec -it ${docker_servicename} psql -U ${docker_superuser}

Logs:
  docker logs -f ${docker_servicename}
EOF

if [[ "${enable_backup}" =~ ^[Yy]$ ]]; then
cat <<EOF
Backup:
  script    = ${backup_script_path}
  dir       = ${backup_dir_base}/${backup_host}
  retention = ${retention_days} days
  cron      = ${cron_status} (daily at ${cron_hour}:${cron_minute})
EOF
fi

echo
echo "This information is copied to /etc/info."

{
  echo "PostgreSQL server running:"
  echo "service   = ${docker_servicename}"
  echo "image     = postgres:${docker_pgversion}"
  echo
  echo "host      = ${pgpass_host}"
  echo "port      = ${docker_port}"
  echo "user      = ${docker_superuser}"
  echo "password  = ${docker_pass} (saved to ${pgpass_file})"
  echo
  echo "data      = ${data_path}"
  echo
  echo "connect   = psql -h ${pgpass_host} -p ${docker_port} -U ${docker_superuser}"
  echo "dockerpsql= docker exec -it ${docker_servicename} psql -U ${docker_superuser}"
  echo "logs      = docker logs -f ${docker_servicename}"
  if [[ "${enable_backup}" =~ ^[Yy]$ ]]; then
    echo
    echo "backup:"
    echo "  script    = ${backup_script_path}"
    echo "  dir       = ${backup_dir_base}/${backup_host}"
    echo "  retention = ${retention_days} days"
    echo "  cron      = ${cron_hour}:${cron_minute} daily"
  fi
  echo
} >> /etc/info

chmod 600 /etc/info 2>/dev/null || true

exit 0