forked from mtusi2026/mtusi2026lab2
143 lines
9.1 KiB
Python
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()
|