Files

399 lines
17 KiB
Python

#!/usr/bin/env python3
"""Safe transactional restore of Shinkan database with staging verification and automatic rollback.
Implements RECOVERY_CONTRACT.md section 3.2:
- Exit 0: Restore and post-restore checks succeeded
- Exit 1: Execution error
- Exit 2: Precondition / confirmation / host guard error
- Exit 3: Schema incompatibility (dump contains migrations unknown to deployed code)
- Exit 4: Checksum mismatch
- Exit 5: Post-restore healthcheck failed (automatic rollback to shinkan_before_<ts> 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
REQUIRED_TABLES = (
'schema_migrations',
'skills',
'skill_main_categories',
'skill_categories',
'maturity_models',
'sessions',
'club_members',
'club_member_roles',
'profiles',
)
TARGETS = {
'llm-prod': {
'host': 'llm-node',
'arch': 'x86_64',
'require_confirm': 'shinkan@llm-node',
'app_dir': '/home/llmadmin/docker/shinkan-prod',
'compose_project': 'shinkan-prod',
'compose_files': ['docker-compose.yml', 'docker-compose.llm-node.yml'],
'env_file': '/etc/shinkan/prod.env',
'db_container': 'shinkan-db-prod',
'db_name': 'shinkan',
'db_user': 'shinkan_user',
'version_url': 'http://127.0.0.1:8003/api/version',
'ready_url': 'http://127.0.0.1:8003/api/health/ready',
'frontend_ready_url': 'http://127.0.0.1:3003/api/health/ready',
},
'llm-restoretest': {
'host': 'llm-node',
'arch': 'x86_64',
'require_confirm': None,
'app_dir': '/home/llmadmin/docker/shinkan-prod',
'compose_project': 'shinkan-restoretest',
'compose_files': ['/etc/backup-orchestrator/scripts/shinkan/docker-compose.restoretest.yml'],
'env_file': '/etc/shinkan/prod.env',
'db_container': 'shinkan-restoretest-postgres-1',
'db_name': 'shinkan',
'db_user': 'shinkan_user',
'version_url': 'http://127.0.0.1:18003/api/version',
'ready_url': 'http://127.0.0.1:18003/api/health/ready',
'frontend_ready_url': 'http://127.0.0.1:13003/api/health/ready',
},
}
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:
return 'custom' if fp.read(5) == b'PGDMP' else 'plain'
def write_result(path, data):
if path:
Path(path).parent.mkdir(parents=True, exist_ok=True)
Path(path).write_text(json.dumps(data, ensure_ascii=False, indent=2) + '\n', encoding='utf-8')
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["db_user"]}" -d "{dbname}" -At',
]
return subprocess.run(cmd, input=sql, capture_output=True, text=True, timeout=60)
def 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 compose_cmd(tcfg, *args):
cmd = ['docker', 'compose', '-p', tcfg['compose_project']]
for cf in tcfg['compose_files']:
cf_path = Path(cf) if Path(cf).is_absolute() else (Path(tcfg['app_dir']) / cf)
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)
return subprocess.run(cmd, cwd=tcfg['app_dir'], 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')
last_body = json.loads(raw)
if (
resp.status == 200
and last_body.get('status') == 'ready'
and last_body.get('database') is True
and last_body.get('schema_complete') is True
):
return True, last_body
except Exception as exc:
last_body = {'error': str(exc)}
time.sleep(3)
return False, last_body
def discover_code_migrations(app_dir):
mig_dir = Path(app_dir) / 'backend' / 'migrations'
if not mig_dir.is_dir():
return []
stems = []
for f in mig_dir.iterdir():
if f.is_file() and f.name.endswith('.sql') and f.name[0].isdigit():
stems.append(f.name[:-4])
return sorted(stems, key=lambda s: (int(re.match(r'^(\d+)', s).group(1)), s))
def main():
parser = argparse.ArgumentParser(description='Restore Shinkan database via staging DB with rollback safety')
parser.add_argument('--target', required=True, choices=sorted(TARGETS))
parser.add_argument('--dump', type=Path)
parser.add_argument('--result', required=True, type=Path)
parser.add_argument('--confirm', type=str)
parser.add_argument('--allow-overwrite', action='store_true')
parser.add_argument('--sessions', choices=('clear', 'keep'), default='clear')
parser.add_argument('--drop-previous', type=str, help='Explicitly drop a retained shinkan_before_<ts> database')
args = parser.parse_args()
start_ts = time.monotonic()
tcfg = dict(TARGETS[args.target])
if os.environ.get('SHINKAN_APP_DIR_OVERRIDE'):
tcfg['app_dir'] = os.environ['SHINKAN_APP_DIR_OVERRIDE']
cur_host = socket.gethostname()
cur_arch = platform.machine()
if os.environ.get('SHINKAN_SKIP_HOST_GUARD') != '1':
if cur_host != tcfg['host'] or cur_arch != tcfg['arch']:
msg = f"Host/arch mismatch: running on {cur_host}/{cur_arch}, expected {tcfg['host']}/{tcfg['arch']}"
write_result(args.result, {'status': 'error', 'exit_code': EXIT_PRECONDITION, 'detail': msg})
print(f'ERROR: {msg}', file=sys.stderr)
return EXIT_PRECONDITION
if args.drop_previous:
if not re.fullmatch(r'shinkan_before_\d{8}_\d{6}', args.drop_previous):
msg = f'Refused to drop database with non-backup name: {args.drop_previous}'
write_result(args.result, {'status': 'error', 'exit_code': EXIT_PRECONDITION, 'detail': msg})
return EXIT_PRECONDITION
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
if tcfg['require_confirm'] and args.confirm != tcfg['require_confirm']:
msg = f"Target {args.target} requires --confirm {tcfg['require_confirm']}"
write_result(args.result, {'status': 'error', 'exit_code': EXIT_PRECONDITION, 'detail': msg})
print(f'ERROR: {msg}', file=sys.stderr)
return EXIT_PRECONDITION
dump_path = args.dump.resolve()
sha_path = Path(str(dump_path) + '.sha256')
if not dump_path.is_file() or not sha_path.is_file():
msg = f'Dump or .sha256 file missing: {dump_path}'
write_result(args.result, {'status': 'error', 'exit_code': EXIT_PRECONDITION, 'detail': msg})
print(f'ERROR: {msg}', file=sys.stderr)
return EXIT_PRECONDITION
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 already has user profiles (non-empty)
prof_res = psql_exec(tcfg, tcfg['db_name'], 'SELECT COUNT(*)::int FROM profiles;')
live_profiles = int(prof_res.stdout.strip()) if (prof_res.returncode == 0 and prof_res.stdout.strip().isdigit()) else 0
if live_profiles > 0 and not args.allow_overwrite:
msg = f"Target database {tcfg['db_name']} contains {live_profiles} profiles; 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'shinkan_restore_{ts_label}'
previous_db = f'shinkan_before_{ts_label}'
print(f'1. Creating staging database {staging_db}...')
drop_db_if_exists(tcfg, staging_db)
create_res = psql_exec(tcfg, 'postgres', f'CREATE DATABASE "{staging_db}";')
if create_res.returncode != 0:
msg = f'Failed to create staging DB: {create_res.stderr.strip()}'
write_result(args.result, {'status': 'error', 'exit_code': EXIT_EXEC_ERROR, 'detail': msg})
return EXIT_EXEC_ERROR
dump_format = detect_dump_format(dump_path)
print(f'2. Importing dump ({dump_format}) into {staging_db} in a single transaction...')
if dump_format == 'custom':
import_cmd = [
'docker', 'exec', '-i', tcfg['db_container'],
'sh', '-ec',
'export PGPASSWORD="${POSTGRES_PASSWORD:-}"; '
f'exec pg_restore -U "{tcfg["db_user"]}" -d "{staging_db}" '
'--single-transaction --exit-on-error --no-owner --no-acl',
]
else:
import_cmd = [
'docker', 'exec', '-i', tcfg['db_container'],
'sh', '-ec',
'export PGPASSWORD="${POSTGRES_PASSWORD:-}"; '
f'exec psql -v ON_ERROR_STOP=1 --single-transaction -U "{tcfg["db_user"]}" -d "{staging_db}"',
]
with dump_path.open('rb') as dump_stream:
imp_proc = subprocess.run(import_cmd, stdin=dump_stream, capture_output=True, timeout=600)
if imp_proc.returncode != 0:
drop_db_if_exists(tcfg, staging_db)
msg = f'Dump import into staging DB failed (rc={imp_proc.returncode})'
write_result(args.result, {'status': 'error', 'exit_code': EXIT_EXEC_ERROR, 'detail': msg})
print(f'ERROR: {msg}', file=sys.stderr)
return EXIT_EXEC_ERROR
print('3. Verifying schema_migrations compatibility against deployed code...')
mig_res = psql_exec(tcfg, staging_db, 'SELECT migration FROM schema_migrations ORDER BY id;')
if mig_res.returncode != 0:
drop_db_if_exists(tcfg, staging_db)
msg = 'Could not read schema_migrations from staging DB'
write_result(args.result, {'status': 'error', 'exit_code': EXIT_EXEC_ERROR, 'detail': msg})
return EXIT_EXEC_ERROR
dump_migrations = [m.strip() for m in mig_res.stdout.splitlines() if m.strip()]
code_migrations = discover_code_migrations(tcfg['app_dir'])
unknown_migrations = sorted(set(dump_migrations) - set(code_migrations))
if unknown_migrations:
drop_db_if_exists(tcfg, staging_db)
msg = f'Dump contains migration(s) unknown to deployed code: {", ".join(unknown_migrations)}'
write_result(args.result, {
'status': 'error',
'exit_code': EXIT_SCHEMA_INCOMPATIBLE,
'detail': msg,
'unknown_migrations': unknown_migrations,
'dump_last_migration': dump_migrations[-1] if dump_migrations else None,
'code_last_migration': code_migrations[-1] if code_migrations else None,
})
print(f'ERROR: {msg}', file=sys.stderr)
return EXIT_SCHEMA_INCOMPATIBLE
print('4. Running plausibility checks on staging database...')
for tbl in REQUIRED_TABLES:
chk = psql_exec(
tcfg, staging_db,
f"SELECT EXISTS (SELECT 1 FROM information_schema.tables WHERE table_schema='public' AND table_name='{tbl}');",
)
if chk.returncode != 0 or chk.stdout.strip() != 't':
drop_db_if_exists(tcfg, staging_db)
msg = f'Plausibility check failed: missing required table {tbl}'
write_result(args.result, {'status': 'error', 'exit_code': EXIT_EXEC_ERROR, 'detail': msg})
return EXIT_EXEC_ERROR
for non_empty_tbl in ('profiles', 'skills'):
cnt = psql_exec(tcfg, staging_db, f'SELECT COUNT(*)::int FROM {non_empty_tbl};')
if cnt.returncode != 0 or not cnt.stdout.strip().isdigit() or int(cnt.stdout.strip()) < 1:
drop_db_if_exists(tcfg, staging_db)
msg = f'Plausibility check failed: table {non_empty_tbl} is empty in staging DB'
write_result(args.result, {'status': 'error', 'exit_code': EXIT_EXEC_ERROR, 'detail': msg})
return EXIT_EXEC_ERROR
# Verify active media_assets count in staging database
act_res = psql_exec(tcfg, staging_db, "SELECT COUNT(*)::int FROM media_assets WHERE lifecycle_state = 'active';")
if act_res.returncode == 0 and act_res.stdout.strip().isdigit():
act_cnt = int(act_res.stdout.strip())
if act_cnt < 1:
drop_db_if_exists(tcfg, staging_db)
msg = f'Plausibility check failed: expected active media_assets, found {act_cnt}'
write_result(args.result, {'status': 'error', 'exit_code': EXIT_EXEC_ERROR, 'detail': msg})
return EXIT_EXEC_ERROR
if args.sessions == 'clear':
print('5. Clearing active user sessions in staging DB (--sessions clear)...')
psql_exec(tcfg, staging_db, 'TRUNCATE TABLE sessions;')
print('6. Stopping application containers and swapping databases...')
compose_cmd(tcfg, 'stop', 'backend', 'frontend')
compose_cmd(tcfg, '--profile', 'media-retention', 'stop', 'retention-cron')
swap_sql = f"""
SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE datname IN ('{tcfg["db_name"]}', '{staging_db}') AND pid <> pg_backend_pid();
ALTER DATABASE "{tcfg['db_name']}" RENAME TO "{previous_db}";
ALTER DATABASE "{staging_db}" RENAME TO "{tcfg['db_name']}";
"""
swap_res = psql_exec(tcfg, 'postgres', swap_sql)
if swap_res.returncode != 0:
drop_db_if_exists(tcfg, staging_db)
compose_cmd(tcfg, 'up', '-d', 'backend', 'frontend')
msg = f'Database rename failed: {swap_res.stderr.strip()}'
write_result(args.result, {'status': 'error', 'exit_code': EXIT_EXEC_ERROR, 'detail': msg})
return EXIT_EXEC_ERROR
print('7. Starting backend and frontend and verifying readiness...')
compose_cmd(tcfg, 'up', '-d', 'backend', 'frontend')
ok_api, ready_body = wait_ready(tcfg['ready_url'], timeout_sec=180)
ok_fe, fe_body = (wait_ready(tcfg['frontend_ready_url'], timeout_sec=60) if ok_api and tcfg.get('frontend_ready_url') else (ok_api, None))
if not (ok_api and ok_fe):
print('ERROR: Post-restore readiness check failed! Executing automatic rollback...', file=sys.stderr)
compose_cmd(tcfg, 'stop', 'backend', 'frontend')
failed_db = f'shinkan_failed_{ts_label}'
rollback_sql = f"""
SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE datname IN ('{tcfg["db_name"]}', '{previous_db}') AND pid <> pg_backend_pid();
ALTER DATABASE "{tcfg['db_name']}" RENAME TO "{failed_db}";
ALTER DATABASE "{previous_db}" RENAME TO "{tcfg['db_name']}";
"""
psql_exec(tcfg, 'postgres', rollback_sql)
drop_db_if_exists(tcfg, failed_db)
compose_cmd(tcfg, 'up', '-d', 'backend', 'frontend')
rb_ok, rb_body = wait_ready(tcfg['ready_url'], timeout_sec=120)
write_result(args.result, {
'status': 'rolled_back',
'exit_code': EXIT_POST_RESTORE_FAILED,
'dump_sha256': actual_sha,
'failed_health': {'api': ready_body, 'frontend': fe_body},
'rollback_health': {'ok': rb_ok, 'body': rb_body},
'duration_seconds': round(time.monotonic() - start_ts, 2),
})
return EXIT_POST_RESTORE_FAILED
pending_applied = sorted(set(code_migrations) - set(dump_migrations))
result_payload = {
'status': 'ok',
'exit_code': EXIT_OK,
'target': args.target,
'dump_sha256': actual_sha,
'dump_last_migration': dump_migrations[-1] if dump_migrations else None,
'code_last_migration': code_migrations[-1] if code_migrations else None,
'migrations_applied_on_start': pending_applied,
'sessions': args.sessions,
'previous_db': previous_db,
'health': {'api': ready_body, 'frontend': fe_body},
'duration_seconds': round(time.monotonic() - start_ts, 2),
}
write_result(args.result, result_payload)
print(f"OK: Restore to {args.target} succeeded in {result_payload['duration_seconds']}s (previous DB retained as {previous_db})")
return EXIT_OK
if __name__ == '__main__':
sys.exit(main())