Files

537 lines
23 KiB
Python

#!/usr/bin/env python3
"""Universal transactional database restore adapter for PostgreSQL and MariaDB/MySQL.
Supports Docker-based deployments with:
- Staging database isolation and integrity verification prior to live cutover.
- Atomic database/table swap.
- Reversible rollback if post-restore health checks fail.
- Plausibility checks (non-empty tables, migration checks).
- Session truncation on cutover.
- Fully parameter-driven via targets.json.
Exit codes:
- 0: Restore and post-restore checks succeeded
- 1: Execution error
- 2: Precondition / confirmation / host guard error
- 3: Schema incompatibility
- 4: Checksum mismatch
- 5: Post-restore healthcheck failed (automatic rollback executed)
"""
import argparse
import datetime as dt
import hashlib
import json
import os
from pathlib import Path
import platform
import re
import socket
import subprocess
import sys
import time
from urllib.request import urlopen
EXIT_OK = 0
EXIT_EXEC_ERROR = 1
EXIT_PRECONDITION = 2
EXIT_SCHEMA_INCOMPATIBLE = 3
EXIT_CHECKSUM_MISMATCH = 4
EXIT_POST_RESTORE_FAILED = 5
def compute_sha256(path):
h = hashlib.sha256()
with Path(path).open('rb') as fp:
while True:
chunk = fp.read(65536)
if not chunk:
break
h.update(chunk)
return h.hexdigest()
def detect_dump_format(path):
with Path(path).open('rb') as fp:
magic = fp.read(5)
if magic == b'PGDMP':
return 'pg_custom'
return 'plain_sql'
def write_result(path, data):
if path:
p = Path(path)
p.parent.mkdir(parents=True, exist_ok=True)
p.write_text(json.dumps(data, ensure_ascii=False, indent=2) + '\n', encoding='utf-8')
def load_targets(custom_file=None):
candidates = []
if custom_file:
candidates.append(Path(custom_file))
candidates.extend([
Path('/etc/backup-orchestrator/targets.json'),
Path(__file__).resolve().parents[2] / 'targets.json',
Path(__file__).resolve().parents[2] / f'hosts/{socket.gethostname()}/targets.json',
])
for c in candidates:
if c.is_file():
try:
data = json.loads(c.read_text(encoding='utf-8'))
if 'targets' in data:
return data
except (OSError, json.JSONDecodeError):
continue
return {'targets': {}}
# -----------------------------------------------------------------------------
# PostgreSQL Database Helpers
# -----------------------------------------------------------------------------
def psql_exec(tcfg, dbname, sql):
cmd = [
'docker', 'exec', '-i', tcfg['db_container'],
'sh', '-ec',
f'export PGPASSWORD="${{POSTGRES_PASSWORD:-}}"; exec psql -v ON_ERROR_STOP=1 -U "{tcfg.get("db_user", "postgres")}" -d "{dbname}" -At',
]
return subprocess.run(cmd, input=sql, capture_output=True, text=True, timeout=120)
def postgres_drop_db_if_exists(tcfg, dbname):
term_sql = f"""
SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE datname = '{dbname}' AND pid <> pg_backend_pid();
"""
psql_exec(tcfg, 'postgres', term_sql)
psql_exec(tcfg, 'postgres', f'DROP DATABASE IF EXISTS "{dbname}";')
def postgres_create_db(tcfg, dbname):
postgres_drop_db_if_exists(tcfg, dbname)
res = psql_exec(tcfg, 'postgres', f'CREATE DATABASE "{dbname}";')
return res.returncode == 0, res.stderr.strip()
def postgres_import_dump(tcfg, dbname, dump_path):
fmt = detect_dump_format(dump_path)
if fmt == 'pg_custom':
cmd = [
'docker', 'exec', '-i', tcfg['db_container'],
'sh', '-ec',
f'export PGPASSWORD="${{POSTGRES_PASSWORD:-}}"; exec pg_restore -v --single-transaction --exit-on-error -U "{tcfg.get("db_user", "postgres")}" -d "{dbname}"',
]
else:
cmd = [
'docker', 'exec', '-i', tcfg['db_container'],
'sh', '-ec',
f'export PGPASSWORD="${{POSTGRES_PASSWORD:-}}"; exec psql -v ON_ERROR_STOP=1 --single-transaction -U "{tcfg.get("db_user", "postgres")}" -d "{dbname}"',
]
with Path(dump_path).open('rb') as fp:
res = subprocess.run(cmd, stdin=fp, capture_output=True, text=True, timeout=1800)
return res.returncode == 0, res.stderr.strip() or res.stdout.strip()
def postgres_swap_databases(tcfg, live_db, staging_db, backup_db):
term_sql = f"""
SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE datname IN ('{live_db}', '{staging_db}', '{backup_db}') AND pid <> pg_backend_pid();
"""
psql_exec(tcfg, 'postgres', term_sql)
# 1. Rename live to backup
r1 = psql_exec(tcfg, 'postgres', f'ALTER DATABASE "{live_db}" RENAME TO "{backup_db}";')
if r1.returncode != 0:
return False, f"Rename {live_db} -> {backup_db} failed: {r1.stderr.strip()}"
# 2. Rename staging to live
r2 = psql_exec(tcfg, 'postgres', f'ALTER DATABASE "{staging_db}" RENAME TO "{live_db}";')
if r2.returncode != 0:
# Emergency rollback
psql_exec(tcfg, 'postgres', f'ALTER DATABASE "{backup_db}" RENAME TO "{live_db}";')
return False, f"Rename {staging_db} -> {live_db} failed: {r2.stderr.strip()}"
return True, "ok"
def postgres_rollback_swap(tcfg, live_db, staging_db, backup_db):
term_sql = f"""
SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE datname IN ('{live_db}', '{staging_db}', '{backup_db}') AND pid <> pg_backend_pid();
"""
psql_exec(tcfg, 'postgres', term_sql)
psql_exec(tcfg, 'postgres', f'ALTER DATABASE "{live_db}" RENAME TO "{staging_db}";')
psql_exec(tcfg, 'postgres', f'ALTER DATABASE "{backup_db}" RENAME TO "{live_db}";')
# -----------------------------------------------------------------------------
# MariaDB / MySQL Database Helpers
# -----------------------------------------------------------------------------
def mariadb_exec(tcfg, dbname, sql):
db_flag = f'"{dbname}"' if dbname else ''
cmd = [
'docker', 'exec', '-i', tcfg['db_container'],
'sh', '-ec',
f'export MYSQL_PWD="${{MARIADB_PASSWORD:-${{MYSQL_PASSWORD:-}}}}"; '
f'exec mariadb --default-character-set=utf8mb4 -u "${{MARIADB_USER:-${{MYSQL_USER:-root}}}}" {db_flag} -sN',
]
return subprocess.run(cmd, input=sql, capture_output=True, text=True, timeout=120)
def mariadb_drop_db_if_exists(tcfg, dbname):
mariadb_exec(tcfg, '', f'DROP DATABASE IF EXISTS `{dbname}`;')
def mariadb_create_db(tcfg, dbname):
mariadb_drop_db_if_exists(tcfg, dbname)
res = mariadb_exec(tcfg, '', f'CREATE DATABASE `{dbname}` CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;')
return res.returncode == 0, res.stderr.strip()
def mariadb_import_dump(tcfg, dbname, dump_path, original_dbname):
"""Import MariaDB dump into staging DB, rewriting CREATE/USE database references if needed."""
cmd = [
'docker', 'exec', '-i', tcfg['db_container'],
'sh', '-ec',
f'export MYSQL_PWD="${{MARIADB_PASSWORD:-${{MYSQL_PASSWORD:-}}}}"; '
f'exec mariadb --default-character-set=utf8mb4 -u "${{MARIADB_USER:-${{MYSQL_USER:-root}}}}" `{dbname}`',
]
proc = subprocess.Popen(cmd, stdin=subprocess.PIPE, stdout=subprocess.PIPE, stderr=subprocess.PIPE, text=True)
# Stream and rewrite USE `original_dbname` / CREATE DATABASE statements to staging db
re_use = re.compile(rf'^\s*USE\s+[`"\']?{re.escape(original_dbname)}[`"\']?\s*;', re.IGNORECASE)
re_create = re.compile(rf'^\s*CREATE\s+DATABASE.*?[`"\']?{re.escape(original_dbname)}[`"\']?.*?;', re.IGNORECASE)
try:
with Path(dump_path).open('r', encoding='utf-8', errors='replace') as fp:
for line in fp:
if re_use.match(line):
proc.stdin.write(f'USE `{dbname}`;\n')
elif re_create.match(line):
proc.stdin.write(f'-- {line.strip()}\n')
else:
proc.stdin.write(line)
stdout, stderr = proc.communicate(timeout=1800)
return proc.returncode == 0, stderr.strip() or stdout.strip()
except Exception as exc:
proc.kill()
return False, str(exc)
def mariadb_get_tables(tcfg, dbname):
sql = f"SELECT table_name FROM information_schema.tables WHERE table_schema = '{dbname}' AND table_type = 'BASE TABLE';"
res = mariadb_exec(tcfg, '', sql)
if res.returncode != 0:
return []
return [line.strip() for line in res.stdout.splitlines() if line.strip()]
def mariadb_swap_databases(tcfg, live_db, staging_db, backup_db):
"""Atomically swap tables between schemas using MySQL/MariaDB RENAME TABLE."""
mariadb_create_db(tcfg, backup_db)
live_tables = set(mariadb_get_tables(tcfg, live_db))
staging_tables = set(mariadb_get_tables(tcfg, staging_db))
rename_clauses = []
# Move current live tables to backup_db
for tbl in live_tables:
rename_clauses.append(f"`{live_db}`.`{tbl}` TO `{backup_db}`.`{tbl}`")
# Move staging tables to live_db
for tbl in staging_tables:
rename_clauses.append(f"`{staging_db}`.`{tbl}` TO `{live_db}`.`{tbl}`")
if not rename_clauses:
return False, "No tables to rename in swap operation"
rename_sql = "RENAME TABLE " + ", ".join(rename_clauses) + ";"
res = mariadb_exec(tcfg, '', rename_sql)
if res.returncode != 0:
return False, f"Atomic table rename failed: {res.stderr.strip()}"
return True, "ok"
def mariadb_rollback_swap(tcfg, live_db, staging_db, backup_db):
live_tables = set(mariadb_get_tables(tcfg, live_db))
backup_tables = set(mariadb_get_tables(tcfg, backup_db))
rename_clauses = []
for tbl in live_tables:
rename_clauses.append(f"`{live_db}`.`{tbl}` TO `{staging_db}`.`{tbl}`")
for tbl in backup_tables:
rename_clauses.append(f"`{backup_db}`.`{tbl}` TO `{live_db}`.`{tbl}`")
if rename_clauses:
mariadb_exec(tcfg, '', "RENAME TABLE " + ", ".join(rename_clauses) + ";")
# -----------------------------------------------------------------------------
# Common Compose & Healthcheck Functions
# -----------------------------------------------------------------------------
def compose_cmd(tcfg, *args):
cmd = ['docker', 'compose']
if tcfg.get('compose_project'):
cmd.extend(['-p', tcfg['compose_project']])
for cf in tcfg.get('compose_files', ['docker-compose.yml']):
cf_path = Path(cf) if Path(cf).is_absolute() else (Path(tcfg.get('app_dir', '.')) / cf)
if cf_path.is_file():
cmd.extend(['-f', str(cf_path)])
if tcfg.get('env_file') and Path(tcfg['env_file']).is_file():
cmd.extend(['--env-file', tcfg['env_file']])
cmd.extend(args)
cwd = tcfg.get('app_dir') if tcfg.get('app_dir') and Path(tcfg['app_dir']).is_dir() else None
return subprocess.run(cmd, cwd=cwd, capture_output=True, text=True, timeout=300)
def wait_ready(url, timeout_sec=180):
deadline = time.monotonic() + timeout_sec
last_body = None
while time.monotonic() < deadline:
try:
with urlopen(url, timeout=5) as resp:
raw = resp.read(8192).decode('utf-8', errors='replace')
if resp.status == 200:
try:
parsed = json.loads(raw)
# If JSON contains 'status', verify it is 'ok' or 'ready'
if isinstance(parsed, dict) and 'status' in parsed:
if parsed['status'] in ('ready', 'ok'):
return True, parsed
else:
return True, {'raw': raw[:200]}
except (ValueError, json.JSONDecodeError):
return True, {'status': 'http_200'}
except Exception as exc:
last_body = {'error': str(exc)}
time.sleep(3)
return False, last_body
# -----------------------------------------------------------------------------
# Main Restore Engine
# -----------------------------------------------------------------------------
def main():
parser = argparse.ArgumentParser(description='Universal transactional database restore adapter')
parser.add_argument('--target', required=True, help='Target name from targets.json')
parser.add_argument('--dump', type=Path, help='Path to SQL dump')
parser.add_argument('--result', required=True, type=Path, help='Output path for result JSON')
parser.add_argument('--confirm', type=str, help='Required confirmation token')
parser.add_argument('--allow-overwrite', action='store_true', help='Allow overwrite of non-empty live database')
parser.add_argument('--sessions', choices=('clear', 'keep'), default='clear')
parser.add_argument('--drop-previous', type=str, help='Explicitly drop a retained <app>_before_<ts> database')
parser.add_argument('--targets-file', type=Path, help='Optional explicit path to targets.json')
args = parser.parse_args()
targets_registry = load_targets(args.targets_file)
if args.target not in targets_registry.get('targets', {}):
msg = f"Target {args.target!r} not found in targets.json"
write_result(args.result, {'status': 'error', 'exit_code': EXIT_PRECONDITION, 'detail': msg})
print(f"ERROR: {msg}", file=sys.stderr)
return EXIT_PRECONDITION
tcfg = targets_registry['targets'][args.target]
engine = tcfg.get('db_engine', 'postgresql').lower()
if engine not in ('postgresql', 'postgres', 'mariadb', 'mysql'):
msg = f"Unsupported db_engine: {engine}"
write_result(args.result, {'status': 'error', 'exit_code': EXIT_PRECONDITION, 'detail': msg})
print(f"ERROR: {msg}", file=sys.stderr)
return EXIT_PRECONDITION
is_postgres = engine in ('postgresql', 'postgres')
# Host & Arch guard
cur_host = socket.gethostname()
cur_arch = platform.machine()
if os.environ.get('SKIP_HOST_GUARD') != '1':
req_host = tcfg.get('host')
req_arch = tcfg.get('arch')
if req_host and req_host != cur_host:
msg = f"Host mismatch: running on {cur_host}, target requires {req_host}"
write_result(args.result, {'status': 'error', 'exit_code': EXIT_PRECONDITION, 'detail': msg})
print(f"ERROR: {msg}", file=sys.stderr)
return EXIT_PRECONDITION
if req_arch and req_arch != cur_arch:
msg = f"Arch mismatch: running on {cur_arch}, target requires {req_arch}"
write_result(args.result, {'status': 'error', 'exit_code': EXIT_PRECONDITION, 'detail': msg})
print(f"ERROR: {msg}", file=sys.stderr)
return EXIT_PRECONDITION
# Confirmation token guard
req_confirm = tcfg.get('require_confirm')
if req_confirm and args.confirm != req_confirm:
msg = f"Target {args.target} requires confirmation: --confirm {req_confirm}"
write_result(args.result, {'status': 'error', 'exit_code': EXIT_PRECONDITION, 'detail': msg})
print(f"ERROR: {msg}", file=sys.stderr)
return EXIT_PRECONDITION
# Drop previous retained DB if requested
live_db = tcfg['db_name']
if args.drop_previous:
expected_pattern = rf'^{re.escape(live_db)}_before_\d{{8}}_\d{{6}}$'
if not re.match(expected_pattern, args.drop_previous):
msg = f"Refused to drop database with unexpected name: {args.drop_previous}"
write_result(args.result, {'status': 'error', 'exit_code': EXIT_PRECONDITION, 'detail': msg})
return EXIT_PRECONDITION
if is_postgres:
postgres_drop_db_if_exists(tcfg, args.drop_previous)
else:
mariadb_drop_db_if_exists(tcfg, args.drop_previous)
write_result(args.result, {'status': 'ok', 'exit_code': EXIT_OK, 'dropped_database': args.drop_previous})
print(f"OK: Dropped previous database {args.drop_previous}")
return EXIT_OK
if not args.dump:
write_result(args.result, {'status': 'error', 'exit_code': EXIT_PRECONDITION, 'detail': '--dump is required'})
return EXIT_PRECONDITION
dump_path = args.dump.resolve()
sha_path = Path(str(dump_path) + '.sha256')
if not dump_path.is_file():
msg = f"Dump file missing: {dump_path}"
write_result(args.result, {'status': 'error', 'exit_code': EXIT_PRECONDITION, 'detail': msg})
return EXIT_PRECONDITION
# Checksum validation if .sha256 file is present
if sha_path.is_file():
expected_sha = sha_path.read_text(encoding='utf-8').strip().split()[0].lower()
actual_sha = compute_sha256(dump_path)
if expected_sha != actual_sha:
msg = f"SHA256 mismatch: expected {expected_sha}, got {actual_sha}"
write_result(args.result, {'status': 'error', 'exit_code': EXIT_CHECKSUM_MISMATCH, 'detail': msg})
print(f"ERROR: {msg}", file=sys.stderr)
return EXIT_CHECKSUM_MISMATCH
# Check if live DB has non-empty tables and --allow-overwrite is needed
check_tables = tcfg.get('verify_tables_not_empty', [])
if check_tables and not args.allow_overwrite:
tbl = check_tables[0]
cnt = 0
if is_postgres:
r = psql_exec(tcfg, live_db, f'SELECT COUNT(*)::int FROM "{tbl}";')
cnt = int(r.stdout.strip()) if (r.returncode == 0 and r.stdout.strip().isdigit()) else 0
else:
r = mariadb_exec(tcfg, live_db, f'SELECT COUNT(*) FROM `{tbl}`;')
cnt = int(r.stdout.strip()) if (r.returncode == 0 and r.stdout.strip().isdigit()) else 0
if cnt > 0:
msg = f"Target database {live_db} contains {cnt} rows in table {tbl}; pass --allow-overwrite to replace it."
write_result(args.result, {'status': 'error', 'exit_code': EXIT_PRECONDITION, 'detail': msg})
print(f"ERROR: {msg}", file=sys.stderr)
return EXIT_PRECONDITION
ts_label = dt.datetime.now().strftime('%Y%m%d_%H%M%S')
staging_db = f"{live_db}_restore_{ts_label}"
backup_db = f"{live_db}_before_{ts_label}"
print(f"1. Creating staging database {staging_db} ({engine})...")
if is_postgres:
ok, err = postgres_create_db(tcfg, staging_db)
else:
ok, err = mariadb_create_db(tcfg, staging_db)
if not ok:
msg = f"Failed to create staging DB: {err}"
write_result(args.result, {'status': 'error', 'exit_code': EXIT_EXEC_ERROR, 'detail': msg})
return EXIT_EXEC_ERROR
print(f"2. Importing dump into {staging_db}...")
if is_postgres:
ok, err = postgres_import_dump(tcfg, staging_db, dump_path)
else:
ok, err = mariadb_import_dump(tcfg, staging_db, dump_path, live_db)
if not ok:
msg = f"Failed to import dump into staging DB: {err}"
if is_postgres:
postgres_drop_db_if_exists(tcfg, staging_db)
else:
mariadb_drop_db_if_exists(tcfg, staging_db)
write_result(args.result, {'status': 'error', 'exit_code': EXIT_EXEC_ERROR, 'detail': msg})
return EXIT_EXEC_ERROR
print("3. Validating staging database integrity...")
table_counts = {}
for tbl in tcfg.get('verify_tables_not_empty', []):
if is_postgres:
r = psql_exec(tcfg, staging_db, f'SELECT COUNT(*)::int FROM "{tbl}";')
else:
r = mariadb_exec(tcfg, staging_db, f'SELECT COUNT(*) FROM `{tbl}`;')
cnt = int(r.stdout.strip()) if (r.returncode == 0 and r.stdout.strip().isdigit()) else 0
table_counts[tbl] = cnt
if cnt == 0:
msg = f"Verification failed: required table {tbl} is empty in staging DB"
if is_postgres:
postgres_drop_db_if_exists(tcfg, staging_db)
else:
mariadb_drop_db_if_exists(tcfg, staging_db)
write_result(args.result, {'status': 'error', 'exit_code': EXIT_EXEC_ERROR, 'detail': msg})
return EXIT_EXEC_ERROR
# Invalidate / truncate session tables if requested
if args.sessions == 'clear':
for tbl in tcfg.get('truncate_tables', ['sessions']):
print(f"Truncating session table: {tbl}")
if is_postgres:
psql_exec(tcfg, staging_db, f'TRUNCATE TABLE "{tbl}" CASCADE;')
else:
mariadb_exec(tcfg, staging_db, f'TRUNCATE TABLE `{tbl}`;')
services_to_stop = tcfg.get('services_to_stop', [])
if services_to_stop:
print(f"4. Stopping dependent services: {services_to_stop}...")
compose_cmd(tcfg, 'stop', *services_to_stop)
print(f"5. Performing atomic database swap ({live_db} -> {backup_db}, {staging_db} -> {live_db})...")
if is_postgres:
swap_ok, swap_err = postgres_swap_databases(tcfg, live_db, staging_db, backup_db)
else:
swap_ok, swap_err = mariadb_swap_databases(tcfg, live_db, staging_db, backup_db)
if not swap_ok:
msg = f"Database swap failed: {swap_err}"
if services_to_stop:
compose_cmd(tcfg, 'up', '-d', *services_to_stop)
write_result(args.result, {'status': 'error', 'exit_code': EXIT_EXEC_ERROR, 'detail': msg})
return EXIT_EXEC_ERROR
if services_to_stop:
print(f"6. Starting dependent services: {services_to_stop}...")
compose_cmd(tcfg, 'up', '-d', *services_to_stop)
# Healthcheck / Readiness verification
health_url = tcfg.get('ready_url') or tcfg.get('health_url')
if health_url:
print(f"7. Verifying service readiness at {health_url}...")
timeout_sec = tcfg.get('health_timeout_seconds', 180)
ready_ok, ready_data = wait_ready(health_url, timeout_sec=timeout_sec)
if not ready_ok:
print(f"ERROR: Health check failed after {timeout_sec}s: {ready_data}. Initiating automatic rollback!", file=sys.stderr)
if services_to_stop:
compose_cmd(tcfg, 'stop', *services_to_stop)
if is_postgres:
postgres_rollback_swap(tcfg, live_db, staging_db, backup_db)
else:
mariadb_rollback_swap(tcfg, live_db, staging_db, backup_db)
if services_to_stop:
compose_cmd(tcfg, 'up', '-d', *services_to_stop)
msg = f"Post-restore healthcheck failed: {ready_data}. Automatic rollback executed."
write_result(args.result, {'status': 'error', 'exit_code': EXIT_POST_RESTORE_FAILED, 'detail': msg, 'health_data': ready_data})
return EXIT_POST_RESTORE_FAILED
summary = {
'status': 'ok',
'exit_code': EXIT_OK,
'target': args.target,
'db_engine': engine,
'live_db': live_db,
'previous_db': backup_db,
'verified_tables': table_counts,
'sessions': args.sessions,
'completed_at': dt.datetime.now().isoformat(),
}
write_result(args.result, summary)
print(f"OK: Restore completed successfully for target {args.target} (previous DB: {backup_db})")
return EXIT_OK
if __name__ == '__main__':
sys.exit(main())