pg_waldump WAL Forenzika: Rekonštrukcia Čo Sa Stalo s Tvojimi Dátami

Ked su backupy prec, WAL je tvoj kriminalny spis. “50,000 riadkov zmizlo z orders tabuľky ale nie je záznam o tom kto to spravil.” Riešenie: pg_waldump vie čítať WAL súbory a ukázať presne aké zmeny boli urobené, kedy a ktorou transakciou.

Prostredie: PostgreSQL 14+, prístup k WAL súborom (archivovaným alebo živým), forenzné vyšetrovanie dátových zmien

Problém

Záhada Chýbajúcich Dát

-- Ráno: Všetko v poriadku
SELECT count(*) FROM orders WHERE created_at > '2024-01-01';
-- count: 150,000

-- Poobede: Dáta preč!
SELECT count(*) FROM orders WHERE created_at > '2024-01-01';
-- count: 100,000

-- Nikto nepriznáva spustenie DELETE
-- Aplikačné logy neukazujú nič podozrivé
-- Potrebujem zistiť čo sa stalo

Čo WAL Obsahuje

Write-Ahead Log (WAL) zaznamenáva KAŽDÚ zmenu:

┌─────────────────────────────────────────────────────────────┐
│ Transakcia 12345 v 2024-01-15 14:23:45                     │
│ - INSERT do orders (id=1001, amount=50.00)                 │
│ - UPDATE orders SET status='paid' WHERE id=1001            │
│ - DELETE z orders WHERE id < 1000                          │
│ COMMIT                                                      │
│                                                             │
│ Transakcia 12346 v 2024-01-15 14:23:47                     │
│ - INSERT do users (id=500, name='john')                    │
│ COMMIT                                                      │
└─────────────────────────────────────────────────────────────┘

pg_waldump číta tieto záznamy a ukáže ti históriu!

Použitie pg_waldump

Základné Použitie

# Nájdi WAL súbory obsahujúce časový rozsah
ls -la /var/lib/postgresql/14/main/pg_wal/

# Dump konkrétny WAL súbor
pg_waldump /var/lib/postgresql/14/main/pg_wal/0000000100000ABC00000042

# Výstup:
# rmgr: Heap        len (rec/tot):     54/    54, tx:    12345, lsn: ABC/42001234
#   blkref #0: rel 1663/16384/16385 blk 100
#   DELETE off 5

# Filtruj podľa relation (tabuľky) OID
pg_waldump --relation=1663/16384/16385 /path/to/wal/files

Nájdi Relation ID Tabuľky

-- Získaj relation identifier pre tvoju tabuľku
SELECT
    pg_relation_filenode('orders') AS relfilenode,
    (SELECT oid FROM pg_database WHERE datname = current_database()) AS dboid,
    (SELECT dattablespace FROM pg_database WHERE datname = current_database()) AS spcoid;

-- Výstup: relfilenode=16385, dboid=16384, spcoid=1663
-- Formát pre pg_waldump: spcoid/dboid/relfilenode = 1663/16384/16385

Filtruj Podľa Časového Rozsahu

# Konvertuj timestamp na LSN (Log Sequence Number)
psql -c "SELECT pg_current_wal_lsn();"
# Príklad: ABC/42123456

# Dump WAL medzi LSN
pg_waldump --start=ABC/40000000 --end=ABC/42FFFFFF /path/to/wal/*

# Filtruj výstup pre špecifické operácie
pg_waldump /path/to/wal/* | grep DELETE

Forenzná Analýza

Nájdi Veľké Delete

#!/bin/bash
# find_mass_deletes.sh

WAL_DIR="/var/lib/postgresql/14/main/pg_wal"
TARGET_REL="1663/16384/16385"  # Relation tvojej tabuľky

pg_waldump --relation=$TARGET_REL $WAL_DIR/* 2>/dev/null | \
    grep -E "DELETE|MULTI_INSERT|UPDATE" | \
    awk '{
        if (/tx:/) {
            match($0, /tx: *([0-9]+)/, arr);
            tx=arr[1];
            count[tx]++;
        }
    }
    END {
        for (t in count) if (count[t] > 1000)
            print "Transakcia " t " mala " count[t] " modifikácií"
    }'

# Výstup:
# Transakcia 12345 mala 50234 modifikácií  <- Našiel som vinníka!

Koreluj s Časom Transakcie

-- Ak máš pg_stat_statements alebo query logging
SELECT
    xact_start,
    query,
    usename,
    client_addr
FROM pg_stat_activity
WHERE xact_start > '2024-01-15 14:00:00'
AND xact_start < '2024-01-15 15:00:00';

-- Alebo skontroluj aplikačné connection logy
-- Matchni transaction ID s aplikačnou session

Detailná Analýza Transakcie

# Dump konkrétnu transakciu
pg_waldump --xid=12345 /path/to/wal/*

# Výstup ukazuje každú operáciu v tej transakcii:
# rmgr: Heap        tx: 12345, lsn: ABC/42001234, DELETE off 5
# rmgr: Heap        tx: 12345, lsn: ABC/42001290, DELETE off 6
# rmgr: Heap        tx: 12345, lsn: ABC/42001346, DELETE off 7
# ... 50,000 ďalších DELETE záznamov
# rmgr: Transaction tx: 12345, lsn: ABC/42999999, COMMIT 2024-01-15 14:23:45

Rekonštruuj Zmazané Dáta

# Pre každý DELETE, WAL obsahuje staré tuple dáta (ak wal_level = replica alebo logical)

# Skontroluj wal_level
psql -c "SHOW wal_level;"
# Musí byť 'replica' alebo 'logical' pre plné tuple dáta

# Dump s plnými záznamami
pg_waldump --bkp-details /path/to/wal/* | grep -A5 "DELETE"

# Parsuj tuple dáta (vyžaduje pochopenie tuple formátu)
# Alebo použi nástroje ako pg_filedump na backup blocks

Pokročilé Techniky

Zisti Kto To Spravil

# WAL priamo nezaznamenáva username, ale môžeš korelovať:

# 1. Získaj commit čas transakcie
pg_waldump --xid=12345 /path/to/wal/* | grep COMMIT
# COMMIT 2024-01-15 14:23:45.123

# 2. Skontroluj pg_log pre queries v tom čase
grep "14:23:45" /var/log/postgresql/postgresql-14-main.log

# 3. Ak používaš log_statement = 'all' alebo pgaudit:
grep "DELETE.*orders" /var/log/postgresql/postgresql-14-main.log
# 2024-01-15 14:23:45 user=admin DELETE FROM orders WHERE created_at < '2024-01-01'

Vytvor WAL Analýzu Summary

#!/bin/bash
# wal_summary.sh - Analyzuj WAL aktivitu

WAL_FILE=$1

echo "=== WAL File Analýza: $WAL_FILE ==="

echo -e "\n=== Súhrn Transakcií ==="
pg_waldump $WAL_FILE 2>/dev/null | \
    grep "tx:" | \
    sed 's/.*tx: *\([0-9]*\).*/\1/' | \
    sort | uniq -c | sort -rn | head -20

echo -e "\n=== Operácie Podľa Typu ==="
pg_waldump $WAL_FILE 2>/dev/null | \
    grep "rmgr:" | \
    awk '{print $2}' | sort | uniq -c | sort -rn

echo -e "\n=== Najväčšie Transakcie ==="
pg_waldump $WAL_FILE 2>/dev/null | \
    awk '/tx:/ {
        match($0, /tx: *([0-9]+)/, arr);
        match($0, /len \(rec\/tot\): *[0-9]+\/ *([0-9]+)/, len);
        size[arr[1]] += len[1];
    }
    END {
        for (t in size) print size[t], t
    }' | sort -rn | head -10

Príprava Point-in-Time Recovery

-- Ak potrebuješ obnoviť do bodu pred zlou operáciou

-- 1. Nájdi LSN tesne pred DELETE
pg_waldump /path/to/wal/* | grep "tx: 12345" | head -1
-- lsn: ABC/41999999

-- 2. Použi recovery_target_lsn v recovery
-- postgresql.conf na recovery serveri:
-- restore_command = 'cp /path/to/archive/%f %p'
-- recovery_target_lsn = 'ABC/41999990'
-- recovery_target_action = 'pause'

-- 3. Po recovery extrahuj dáta ktoré potrebuješ
-- Potom pokračuj normálnou prevádzkou

Checklist

## pg_waldump WAL Forenzika

### Príprava
- [ ] Zabezpeč wal_level = replica alebo logical
- [ ] Nakonfiguruj WAL archiváciu pre historický prístup
- [ ] Zapni query logging (log_statement)
- [ ] Zváž pgaudit pre detailné audit trails

### Kroky Vyšetrovania
- [ ] Identifikuj relation ID ovplyvnenej tabuľky
- [ ] Nájdi WAL súbory pre relevantný časový rozsah
- [ ] Filtruj pg_waldump výstup pre relation
- [ ] Identifikuj transakciu s masovými zmenami
- [ ] Koreluj čas transakcie s query logmi
- [ ] Identifikuj používateľa/aplikáciu ktorá spustila query

### Recovery
- [ ] Urči LSN pred zlou operáciou
- [ ] Nastav PITR na to LSN
- [ ] Extrahuj potrebné dáta z obnovenej inštancie
- [ ] Aplikuj dáta na produkciu ak vhodné

Záver

Lekcia: WAL je tvoj black box recorder. Keď sa niečo pokazí a nikto nepamätá čo sa stalo, pg_waldump môže rekonštruovať pravdu. Ale potrebuješ správne nakonfigurované logovanie a archiváciu PRED incidentom.

Kľúčové nástroje:

• pg_waldump - Čítanie a analýza WAL záznamov
• log_statement - Sledovanie queries
• pgaudit - Detailné audit logovanie
• WAL archivácia - Historický prístup k WAL súborom

Súvisiace články

• Logical Replication Slot WAL Retention - WAL management
• PostgreSQL HOT Updates Index Trap - Pochopenie PostgreSQL internals