Logical vs Physical Backups, Snapshots, Continuous Backups, and Change-Log Archiving

1. The backup question is “what can I restore, to which point, under which dependencies?”

AtlasMart can copy data in several ways, but a copy becomes a recovery asset only when its consistency point, required logs, schema metadata, encryption keys, topology assumptions, and restore procedure are known. A logical backup represents database objects/rows in logical form; a physical backup copies storage-engine files or database-native blocks; a snapshot freezes storage references at a point in time; an incremental backup captures changes relative to prior backup state; and a continuous archive preserves the change log needed to replay beyond a base copy.

These categories overlap differently across products. Do not assume a filesystem snapshot is transaction-consistent merely because the storage platform calls it atomic, especially when data, write-ahead log (WAL), tablespaces, or metadata span separate volumes.

2. Consistency point and restore chain

A recovery chain should identify a base copy and the ordered changes required after it. For a WAL-based system, the chain can be “base backup at LSN 100 + archived WAL 101…120.” For an immutable-file engine, it may be a snapshot plus incremental SSTables and schema definitions. A restore must know where replay starts, where it may stop, and which artifacts are mutually compatible.

3. Logical, physical, snapshot, and continuous recovery tradeoffs

Logical exports are portable and selective but may take longer to restore because indexes and physical layout are rebuilt. Physical/database-native copies often restore large datasets faster but are more coupled to engine/version/storage layout. Snapshots can be fast and space-efficient but need a documented database-consistency mechanism. Continuous log archiving narrows the recovery point objective (RPO) and enables point-in-time recovery, but only if log retention is complete, monitored, encrypted, and tested.

4. AtlasMart lab: base copy, replay, and an inconsistent snapshot

from copy import deepcopy

base_100 = {
    "lsn": 100,
    "orders": {"o-7": {"status": "created", "total": 50}},
    "inventory": {"sku-1": 10},
    "schema_version": 3,
}
log = [
    (101, "order_paid", {"order_id":"o-7"}),
    (102, "reserve_stock", {"sku":"sku-1", "qty":2}),
    (103, "create_order", {"order_id":"o-8", "total":30}),
    (104, "reserve_stock", {"sku":"sku-1", "qty":1}),
]

def apply(state, entry):
    lsn, kind, payload = entry
    if kind == "order_paid": state["orders"][payload["order_id"]]["status"] = "paid"
    elif kind == "reserve_stock": state["inventory"][payload["sku"]] -= payload["qty"]
    elif kind == "create_order": state["orders"][payload["order_id"]] = {"status":"created", "total":payload["total"]}
    state["lsn"] = lsn

def recover_to(target_lsn):
    state = deepcopy(base_100)
    for entry in log:
        if entry[0] <= target_lsn:
            apply(state, entry)
    return state

print("CONSISTENT BASE + CHANGE LOG")
for target in (100, 102, 103, 104):
    s = recover_to(target)
    print(target, s)

print("\nLOGICAL EXPORT AT LSN 102")
logical = recover_to(102)
print({"orders": logical["orders"], "inventory": logical["inventory"]})
print("logical export reconstructs rows/values; indexes and physical layout are rebuilt separately")

print("\nPITR TO LSN 103")
pitr = recover_to(103)
print("restored_lsn=", pitr["lsn"], "orders=", sorted(pitr["orders"]), "stock=", pitr["inventory"]["sku-1"])

print("\nBROKEN NON-ATOMIC MULTI-VOLUME SNAPSHOT")
orders_at_104 = recover_to(104)["orders"]
inventory_at_102 = recover_to(102)["inventory"]
broken = {"orders":orders_at_104, "inventory":inventory_at_102}
print("orders copied from lsn 104; inventory copied from lsn 102")
print(broken)
print("evidence: o-8 exists but its lsn-104 stock reservation is missing")

print("\nSAFE RULE")
print("record one consistency point, required logs, schema/version metadata, encryption-key IDs, and restore compatibility together")

5. What the evidence proves—and does not prove

A deterministic replay proves that the modeled artifacts are sufficient for the modeled state transition. It does not prove a production backup captures every database file, secret, external blob, extension, queue, or KMS dependency. Production evidence should include backup start/end positions, archived-log continuity, manifest hashes, retention class, encryption/key IDs, restore compatibility, and a successful isolated restore.

6. Production judgment

Choose backup methods from RPO, recovery time objective (RTO), dataset size, restore granularity, topology, retention, legal-hold, encryption, and operator skill. Monitor backup age, archive gaps, failed uploads, copy immutability, key availability, restore duration, and version compatibility. The next lesson tests the most dangerous misconception in distributed systems: live replicas are not independent recovery copies.

References

Foundational claims use standards, specifications, primary research, or current official documentation where practical. Product references are optional implementation anchors; the mandatory labs are vendor-neutral.

• PostgreSQL 18 — Backup and Restore — Official overview of SQL dumps, filesystem backups, and continuous archiving/PITR.
• PostgreSQL 18 — Continuous Archiving and PITR — Base backups, WAL archives, recovery targets, timelines, and PITR caveats.
• Apache Cassandra — Architecture Overview — Current documentation describing atomic snapshots and incremental backups in Cassandra.
• Apache Cassandra — Bulk Loading — Current restore-oriented documentation for loading SSTable backup/snapshot data.