Replication protects against a server dying, but not against data being destroyed — an accidental DROP TABLE, a buggy migration, or corruption is faithfully copied to every replica. Backups are the only protection against losing or ruining the data itself, and the ability to recover to a moment just before a mistake is what separates a survivable incident from a catastrophe. Backup and recovery is the most important operational skill there is.
PostgreSQL offers two families of backup. Logical backups, made with pg_dump and pg_dumpall, export the database as SQL or an archive that can be restored anywhere — portable and selective, but a point-in-time snapshot only. Physical backups, made with pg_basebackup, copy the cluster's data files and, combined with continuously archived WAL, enable point-in-time recovery to any moment.
Point-in-time recovery (PITR) is the gold standard: a physical base backup plus a continuous stream of archived WAL lets you restore the cluster and replay changes up to a precise time or transaction — rewinding past a mistake. This lesson covers the backup types, how PITR works, and the discipline that makes backups trustworthy: testing that you can actually restore them.
Analogy🏏Cricket
🏏 Think of it like cricket: Just as a selection decision can depend on a derived benchmark — 'pick batters whose average exceeds the squad's average', which itself must first be computed — a subquery computes an inner result that the outer query then uses. The insight is that some questions are inherently two-stage: you must establish the benchmark before you can judge against it, and composing queries is how SQL expresses that dependency. Watch the selector actually do it: first he tallies every batter's runs and computes the squad average — that inner computation stands alone, needing nothing from the final decision — and only then does he walk the list judging each player against the number he just derived. That independence is what makes it an uncorrelated subquery: the database can compute the benchmark once, keep it, and reuse it for every row, exactly as the selector does not recompute the squad average per player. The composition also comes in shapes: a benchmark producing one number slots in where a value goes (a scalar subquery in WHERE), while a computed shortlist of qualifying players is itself a table the outer query can select from — a subquery in FROM. Two-stage question, two nested queries, dependency flowing inward-out.
🏏 Showing the Cricket analogy — a Cricket version isn’t available for this concept yet.