pgdump-full-docker.sh

#!/usr/bin/env bash
set -euo pipefail


PG_HOST="git-user_pgsql"
PG_DBNAME="gitea"
PG_SCHEMA="gitea"
PG_USER="pgadmin"
PG_PORT="5432"
PG_PASSWORD="AAAABBBB"
PG_DUMP_DATE=$( date "+%Y%m%d_%H%M" )
PG_DUMP_DAY_NUM=$(date +%u)


PG_DUMP_HOST_PATH="/mnt/db-backup/pgdump-data"
PG_DUMP_DST_PATH="/pgdump"
PG_DUMP_FILENAME="${PG_DBNAME}_${PG_SCHEMA}_${PG_DUMP_DATE}"


PG_DUMP_OPTS=" --verbose --no-comments --encoding=utf8 --inserts --format=custom --compress=9 "
PG_DUMP_OPTS+=" --host=${PG_HOST} --port=${PG_PORT} --username=${PG_USER} --dbname=${PG_DBNAME} --schema=${PG_SCHEMA} "


PG_DUMP_CMD_FULL="mkdir -p ${PG_DUMP_DST_PATH}/${PG_HOST} ; sleep 5 ;"
if [ ${PG_DUMP_DAY_NUM} -eq 3 ]; then
  PG_DUMP_CMD_FULL+="echo '---------------------------------- START VACCUM ----------------------------------' ; sleep 5 ;"
  PG_DUMP_CMD_FULL+="psql --host=${PG_HOST} --port=${PG_PORT} --username=${PG_USER} --dbname=${PG_DBNAME} --command='VACUUM (VERBOSE, FULL);' 2>&1 ;"
  PG_DUMP_CMD_FULL+="echo '---------------------------------- END VACCUM ----------------------------------' ; sleep 5 ;"
fi
PG_DUMP_CMD_FULL+="echo '---------------------------------- START PGDUMP FULL ----------------------------------' ; sleep 5 ;"
PG_DUMP_CMD_FULL+="pg_dump ${PG_DUMP_OPTS} --file=${PG_DUMP_DST_PATH}/${PG_HOST}/${PG_DUMP_FILENAME}_full.sql 2>&1 ;"
PG_DUMP_CMD_FULL+="echo '---------------------------------- END PGDUMP FULL ----------------------------------' ; sleep 5 ;"
PG_DUMP_CMD_FULL+="echo '---------------------------------- START PGDUMP STRUCTURE ----------------------------------' ; sleep 5 ;"
PG_DUMP_CMD_FULL+="pg_dump ${PG_DUMP_OPTS} --schema-only --file=${PG_DUMP_DST_PATH}/${PG_HOST}/${PG_DUMP_FILENAME}_structure.sql 2>&1 ;"
PG_DUMP_CMD_FULL+="echo '---------------------------------- END PGDUMP STRUCTURE ----------------------------------' ; sleep 5 ;"
PG_DUMP_CMD_FULL+="echo '---------------------------------- START PGDUMP DATA ----------------------------------' ; sleep 5 ;"
PG_DUMP_CMD_FULL+="pg_dump ${PG_DUMP_OPTS} --data-only --file=${PG_DUMP_DST_PATH}/${PG_HOST}/${PG_DUMP_FILENAME}_data.sql 2>&1 ;"
PG_DUMP_CMD_FULL+="echo '---------------------------------- END PGDUMP DATA ----------------------------------' ; sleep 5"


DOCKER_SVC="pgdump_${PG_HOST}_${PG_DBNAME}_${PG_SCHEMA}"
# DOCKER_HOST="tcp://socket:2375"
DOCKER_HOST="unix:///var/run/host_docker.sock"
DOCKER_CONSTRAINT="node.role==worker"
DOCKER_NET="postgres_shared"
DOCKER_IMAGE="registry.infra.ginhoux.net:5000/postgres:18.0-trixie"


# cleanup
/usr/bin/docker --host ${DOCKER_HOST} service rm "${DOCKER_SVC}" || true


# create task
/usr/bin/docker --host ${DOCKER_HOST} service create \
  --name "${DOCKER_SVC}" \
  --detach=true \
  --user root \
  --mode replicated --replicas 1 \
  --restart-condition=none \
  --constraint "${DOCKER_CONSTRAINT}" \
  --network ${DOCKER_NET} \
  --mount type=bind,src=${PG_DUMP_HOST_PATH},dst=${PG_DUMP_DST_PATH} \
  -e PGPASSWORD="${PG_PASSWORD}" \
  ${DOCKER_IMAGE} \
  sh -c "${PG_DUMP_CMD_FULL}"


# wait for the single task to complete and capture exit code swarm sets DesiredState=shutdown when the task exit
task_id=""
while [ -z "$task_id" ]; do
  task_id="$(/usr/bin/docker --host ${DOCKER_HOST} service ps --no-trunc --filter desired-state=shutdown -q "${DOCKER_SVC}" || true)"
  sleep 1
done


exitcode="$(/usr/bin/docker --host ${DOCKER_HOST} inspect -f '{{.Status.ContainerStatus.ExitCode}}' "$task_id")"
logs="$(/usr/bin/docker --host ${DOCKER_HOST} service logs --raw "${DOCKER_SVC}" || true)"


# cleanup
/usr/bin/docker --host ${DOCKER_HOST} service rm "${DOCKER_SVC}" || true


echo "$logs"
exit "$exitcode"