#!/usr/bin/env python3 """Трек 2 (часть B): backup -> восстановление в НОВОЕ хранилище -> сверка количества и выбранных сообщений. Источник — БД эксперимента (после track2_indexes.py, 100 000+ сообщений). Приёмник — другой кластер/сервер PostgreSQL (новый каталог данных, другой порт), т.е. настоящее «новое хранилище», а не вторая БД в том же кластере. python scripts/track2_restore.py --src-url URL --dst-admin-url URL(postgres db на новом сервере) \ --dst-db messenger_restored --pg-bin /usr/lib/postgresql/16/bin --dump-file /tmp/t2.dump --out evidence/track2-restore.json Что измеряется: длительность pg_dump и pg_restore (+ ANALYZE), размер дампа, полнота сверки, окно потери данных (RPO): записи, сделанные ПОСЛЕ начала дампа, в копии отсутствуют; отозванная после дампа сессия в копии «воскресает». """ import argparse import json import os import subprocess import sys import time import uuid from pathlib import Path from urllib.parse import urlsplit, urlunsplit import psycopg def with_db(url, db): parts = urlsplit(url) return urlunsplit(parts._replace(path="/" + db)) def facts(conn): """Отпечаток состояния: счётчики, md5 содержимого, схема, выбранные строки.""" one = lambda sql, *p: conn.execute(sql, p).fetchone()[0] out = {"counts": {t: one("SELECT count(*) FROM " + t) for t in ("users", "chats", "chat_members", "messages", "sessions")}} out["messages_md5"] = one("""SELECT md5(string_agg(id::text || '|' || chat_id::text || '|' || sender_id::text || '|' || created_at::text || '|' || md5(text), ',' ORDER BY id)) FROM messages""") out["per_chat_counts_md5"] = one("SELECT md5(string_agg(chat_id::text || ':' || n, ',' ORDER BY chat_id)) FROM (SELECT chat_id, count(*) n FROM messages GROUP BY chat_id) s") out["indexes_md5"] = one("SELECT md5(string_agg(indexname || indexdef, E'\\n' ORDER BY indexname)) FROM pg_indexes WHERE schemaname='public'") out["constraints_md5"] = one("SELECT md5(string_agg(conname || pg_get_constraintdef(oid), E'\\n' ORDER BY conname)) FROM pg_constraint WHERE connamespace = 'public'::regnamespace") out["migrations"] = [r[0] for r in conn.execute("SELECT version FROM schema_migrations ORDER BY 1").fetchall()] big = one("SELECT chat_id FROM messages GROUP BY chat_id ORDER BY count(*) DESC, chat_id LIMIT 1") picks = { "first_overall": "SELECT * FROM messages ORDER BY created_at, id LIMIT 1", "last_overall": "SELECT * FROM messages ORDER BY created_at DESC, id DESC LIMIT 1", "giant_chat_item_15000": "SELECT * FROM messages WHERE chat_id = '%s' ORDER BY created_at, id OFFSET 15000 LIMIT 1" % big, "longest_text": "SELECT * FROM messages ORDER BY char_length(text) DESC, id LIMIT 1", "shortest_text": "SELECT * FROM messages ORDER BY char_length(text), id LIMIT 1", "pseudo_random_by_md5": "SELECT * FROM messages ORDER BY md5(id::text) LIMIT 1", } out["selected_messages"] = {k: one("SELECT row_to_json(s)::text FROM (%s) s" % q) for k, q in picks.items()} return out def run(cmd, env): t0 = time.perf_counter() proc = subprocess.run(cmd, env=env, capture_output=True, text=True) dt = time.perf_counter() - t0 if proc.returncode: raise SystemExit("command failed (%s): %s" % (cmd[0], proc.stderr[-800:])) return dt def main(): ap = argparse.ArgumentParser() ap.add_argument("--src-url", required=True) ap.add_argument("--dst-admin-url", required=True, help="URL БД postgres на НОВОМ сервере (создание БД восстановления)") ap.add_argument("--dst-db", default="messenger_restored") ap.add_argument("--pg-bin", default="/usr/lib/postgresql/16/bin") ap.add_argument("--dump-file", default="/tmp/track2.dump") ap.add_argument("--out", default="evidence/track2-restore.json") a = ap.parse_args() dst_url = with_db(a.dst_admin_url, a.dst_db) env = dict(os.environ) with psycopg.connect(a.src_url, autocommit=True) as src: # 1. Приводим источник к «боевой» схеме: без вспомогательной таблицы, канонический индекс истории, одна сессия. src.execute("DROP TABLE IF EXISTS messages_src") src.execute("DROP INDEX IF EXISTS hist_idx") src.execute("CREATE INDEX IF NOT EXISTS messages_chat_created_id_idx ON messages (chat_id, created_at, id)") user = src.execute("SELECT id FROM users LIMIT 1").fetchone()[0] token_hash = os.urandom(32) src.execute("INSERT INTO sessions (token_hash, user_id, expires_at) VALUES (%s,%s, now() + interval '1 day')", (token_hash, user)) src.execute("VACUUM (ANALYZE)") before = facts(src) db_bytes = src.execute("SELECT pg_database_size(current_database())").fetchone()[0] # 2. Backup: логический, custom-формат; снапшот согласован на момент НАЧАЛА pg_dump. t_dump = run([a.pg_bin + "/pg_dump", "-Fc", "--no-owner", "-f", a.dump_file, "-d", a.src_url], env) dump_bytes = Path(a.dump_file).stat().st_size # 3. Окно потери (RPO): изменения ПОСЛЕ дампа — 500 новых сообщений и отзыв сессии. chat = src.execute("SELECT chat_id FROM messages LIMIT 1").fetchone()[0] sender = src.execute("SELECT sender_id FROM messages WHERE chat_id=%s LIMIT 1", (chat,)).fetchone()[0] for i in range(500): src.execute("INSERT INTO messages (id, chat_id, sender_id, text) VALUES (%s,%s,%s,%s)", (uuid.uuid4(), chat, sender, "после бэкапа %d" % i)) src.execute("DELETE FROM sessions WHERE token_hash = %s", (token_hash,)) after_source = facts(src) # 4. Новое хранилище: другая БД на другом сервере. with psycopg.connect(a.dst_admin_url, autocommit=True) as admin: admin.execute('DROP DATABASE IF EXISTS "%s"' % a.dst_db) admin.execute('CREATE DATABASE "%s"' % a.dst_db) t_restore = run([a.pg_bin + "/pg_restore", "--no-owner", "--exit-on-error", "-d", dst_url, a.dump_file], env) with psycopg.connect(dst_url, autocommit=True) as dst: t0 = time.perf_counter() dst.execute("ANALYZE") # pg_dump не переносит статистику планировщика: без ANALYZE планы могут быть плохими t_analyze = time.perf_counter() - t0 restored = facts(dst) plans = {} big = dst.execute("SELECT chat_id FROM messages GROUP BY chat_id ORDER BY count(*) DESC, chat_id LIMIT 1").fetchone()[0] plan = dst.execute("EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON) SELECT id, chat_id, sender_id, text, created_at FROM messages " "WHERE chat_id=%s ORDER BY created_at, id LIMIT 51", (big,)).fetchone()[0][0] node = plan["Plan"] while node.get("Plans"): node = node["Plans"][0] plans["first_page_giant"] = {"node": node["Node Type"], "index": node.get("Index Name"), "execution_ms": round(plan["Execution Time"], 3)} session_present = dst.execute("SELECT count(*) FROM sessions WHERE token_hash=%s", (token_hash,)).fetchone()[0] # 5. Сверка: копия обязана совпасть с источником НА МОМЕНТ ДАМПА. checks = {k: before[k] == restored[k] for k in before} checks["selected_messages"] = {k: before["selected_messages"][k] == restored["selected_messages"][k] for k in before["selected_messages"]} ok = all(v if isinstance(v, bool) else all(v.values()) for v in checks.values()) rpo = { "messages_lost_in_restore": after_source["counts"]["messages"] - restored["counts"]["messages"], "revoked_session_resurrected_in_restore": bool(session_present), } result = { "snapshot_consistent_with_restore": ok, "checks": checks, "rpo_window_observed": rpo, "timing_seconds": {"pg_dump": round(t_dump, 2), "pg_restore": round(t_restore, 2), "analyze_after_restore": round(t_analyze, 2), "rto_dump_free_restore_plus_analyze": round(t_restore + t_analyze, 2)}, "sizes_bytes": {"source_database": db_bytes, "dump_file": dump_bytes}, "counts_at_dump": before["counts"], "counts_restored": restored["counts"], "counts_source_after_extra_writes": after_source["counts"], "restored_plan": plans, } Path(a.out).parent.mkdir(parents=True, exist_ok=True) Path(a.out).write_text(json.dumps(result, ensure_ascii=False, indent=2) + "\n") print(json.dumps(result, ensure_ascii=False, indent=2)) raise SystemExit(0 if ok else 1) if __name__ == "__main__": main()