Files
2026-10-16 19:49:48 +03:00

143 lines
9.1 KiB
Python

#!/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()