74 lines
2.7 KiB
Bash
Executable File
74 lines
2.7 KiB
Bash
Executable File
#!/bin/bash
|
|
# tver DB復元確認（Synology ds923pで手動実行）
|
|
#
|
|
# 配置先: /volume1/docker/tver-db/restore-test.sh
|
|
# 実行コマンド: bash /volume1/docker/tver-db/restore-test.sh
|
|
# 最新のバックアップを同じPostgreSQLコンテナ内の一時DBへ復元し、主要4表の
|
|
# 行数を本番DBと比較する。本番DBにはSELECTだけを実行する。
|
|
|
|
set -euo pipefail
|
|
|
|
BACKUP_DIR="${BACKUP_DIR:-/volume1/backups/tver}"
|
|
LOG_FILE="${LOG_FILE:-${BACKUP_DIR}/restore-test.log}"
|
|
CONTAINER="${CONTAINER:-tver-postgres}"
|
|
DB_USER="${DB_USER:-tver}"
|
|
DB_NAME="${DB_NAME:-tver}"
|
|
RESTORE_DB="${RESTORE_DB:-tver_restore_test}"
|
|
DOCKER="${DOCKER:-$(command -v docker || printf '/usr/local/bin/docker')}"
|
|
TABLES=(download_jobs subscriptions known_episodes schema_migrations)
|
|
|
|
mkdir -p "$BACKUP_DIR"
|
|
touch "$LOG_FILE"
|
|
chmod 600 "$LOG_FILE"
|
|
exec > >(tee -a "$LOG_FILE") 2>&1
|
|
|
|
if [[ "$RESTORE_DB" == "$DB_NAME" ]]; then
|
|
echo "ERROR: RESTORE_DB must differ from DB_NAME" >&2
|
|
exit 1
|
|
fi
|
|
|
|
# 終了状態にかかわらず、一時DBの削除を最後に試みる。
|
|
cleanup() {
|
|
status=$?
|
|
trap - EXIT
|
|
set +e
|
|
"$DOCKER" exec "$CONTAINER" dropdb --force --if-exists -U "$DB_USER" "$RESTORE_DB"
|
|
cleanup_status=$?
|
|
if ((status != 0)); then
|
|
echo "復元確認に失敗"
|
|
exit "$status"
|
|
fi
|
|
exit "$cleanup_status"
|
|
}
|
|
trap cleanup EXIT
|
|
|
|
LATEST="$(find "$BACKUP_DIR" -name 'tver_*.sql.gz' -type f -print | sort | tail -n 1)"
|
|
if [[ -z "$LATEST" ]]; then
|
|
echo "ERROR: backup dump was not found in $BACKUP_DIR" >&2
|
|
exit 1
|
|
fi
|
|
|
|
echo "復元確認を開始: $LATEST"
|
|
"$DOCKER" exec "$CONTAINER" dropdb --force --if-exists -U "$DB_USER" "$RESTORE_DB"
|
|
"$DOCKER" exec "$CONTAINER" createdb -U "$DB_USER" "$RESTORE_DB"
|
|
|
|
gzip -dc "$LATEST" \
|
|
| "$DOCKER" exec -i "$CONTAINER" psql -q -o /dev/null -v ON_ERROR_STOP=1 -U "$DB_USER" -d "$RESTORE_DB"
|
|
|
|
for table in "${TABLES[@]}"; do
|
|
query="SELECT count(*) FROM public.${table};"
|
|
production_count="$("$DOCKER" exec "$CONTAINER" psql -A -t -q -U "$DB_USER" -d "$DB_NAME" -c "$query" | tr -d '[:space:]')"
|
|
restored_count="$("$DOCKER" exec "$CONTAINER" psql -A -t -q -U "$DB_USER" -d "$RESTORE_DB" -c "$query" | tr -d '[:space:]')"
|
|
if [[ ! "$production_count" =~ ^[0-9]+$ || ! "$restored_count" =~ ^[0-9]+$ ]]; then
|
|
echo "ERROR: $table の行数を取得できませんでした: production=$production_count restore=$restored_count" >&2
|
|
exit 1
|
|
fi
|
|
if [[ "$production_count" != "$restored_count" ]]; then
|
|
echo "ERROR: $table の行数が一致しません: production=$production_count restore=$restored_count" >&2
|
|
exit 1
|
|
fi
|
|
echo "OK: $table production=$production_count restore=$restored_count"
|
|
done
|
|
|
|
echo "復元確認に成功: $LATEST"
|