Backup, Recovery and Database Security

7 pages · ~5 min✓ Reviewed

Builds on Concurrency Control. Next up: SQL vs NoSQL.

DBMSPage 1

Backups and Recovery: The Mental Model

1. Backup Types and Restore Chains

Type	Copies	Restore needs
Full	everything	full only
Differential	changes since last full	full + differential (2 sets)
Incremental	changes since last backup of any type	full + every incremental, in order (longest, most fragile)

Incremental: smallest and fastest backup, slowest restore
Differential: grows daily, restores with just 2 sets

2. Logical vs Physical

	Logical	Physical
Examples	`pg_dump`, `mysqldump`	file / block copy
Content	SQL text	raw data files
Portable	yes	same version and platform
Restore	slow	fast

3. RPO and RTO

RPO = how much data you can lose
RTO = how long you can be down
Both decide the backup frequency and the tools you pick

Pick by the targets

A tight RPO needs frequent backups. A tight RTO needs fast restores: physical copies and short restore chains.

DBMSPage 2

WAL, Checkpoints and Point-in-Time Recovery

1. Write-Ahead Logging

WAL rule: the log record hits durable disk before the changed data page
Commit = log flushed, not data pages
Redo log: replays committed changes lost from memory
Undo log: rolls back uncommitted changes

2. Checkpoints

A checkpoint flushes dirty pages and records a log position
Crash recovery starts there, not at the start of the log

Crash
Last checkpointstart here
Redocommitted work
Undouncommitted work

3. Point-in-Time Recovery

PITR = restore a base backup, then replay archived WAL/binlog
Stop at a target timestamp or LSN, just before the bad statement
PostgreSQL: archive_command ships WAL; set the target, add recovery.signal, start

bash

archive_command = 'cp %p /archive/%f'
# restore base backup, then in postgresql.conf:
recovery_target_time = '2026-10-28 14:05:00'
touch $PGDATA/recovery.signal

start the server; it replays WAL up to the target

DBMSPage 3

ARIES Crash Recovery Basics

1. Principles and LSNs

Write-ahead logging: log reaches disk before the data page
Repeat history during redo, even for losers
Undo is logged too, as CLRs (compensation log records)
Every log record has an LSN; each page stores its pageLSN
Redo skips a record when pageLSN >= LSN

Buffer policy	Meaning	Needs
Steal	Dirty pages of uncommitted txns may reach disk	Undo
No-force	Commit does not force pages to disk	Redo

2. Checkpoint and Three Passes

Fuzzy checkpoint saves the dirty page table and transaction table
Transactions keep running while it is taken

Analysisrebuild dirty pages + active txns from checkpoint
Redoreplay from earliest recLSN, losers too
Undoroll back losers, write CLRs

DBMSPage 4

Replication for High Availability

1. Primary-Replica Replication

Primary ships its WAL / binlog to every replica
Replicas serve reads
A replica can be promoted if the primary fails

2. Synchronous vs Asynchronous

	Synchronous	Asynchronous
Commit	waits for replica ack	returns at once
Data loss	zero	recent commits lost
Latency	higher	low
Risk	slow commits	replication lag on failover

3. Failover Steps

1Detectprimary is down
2Promotemost up-to-date replica
3Repointclients to new primary
4Fenceold primary, avoids split-brain

4. Mistakes

Replication is not a backup

A DROP TABLE or bad UPDATE hits every replica immediately. You still need PITR and backups.

DBMSPage 5

Access Control: GRANT, REVOKE, Roles and Row-Level Security

1. GRANT, REVOKE, Roles

WITH GRANT OPTION lets grantees delegate: only when truly needed
Roles bundle privileges; users get roles, not direct grants

sql

GRANT SELECT, INSERT ON orders TO analyst;
REVOKE INSERT ON orders FROM analyst;
CREATE ROLE readonly;
GRANT SELECT ON ALL TABLES IN SCHEMA app TO readonly;
GRANT readonly TO alice;

2. Least Privilege

Each app gets its own role with only the needed rights
Never run the app as the owner or a superuser
Revoke default PUBLIC privileges

3. Row-Level Security

Enable RLS on the table, then add a policy

sql

ALTER TABLE docs ENABLE ROW LEVEL SECURITY;
CREATE POLICY own_rows ON docs USING (owner = current_user);

Case	Effect
No policy	non-owners see zero rows (default deny)
Owner, superuser	bypass RLS unless `FORCE ROW LEVEL SECURITY`

DBMSPage 6

Backup, Recovery and Security Cheat Sheet

1. Backups and Restore

Type	Copies	Restore needs
Full	everything	full only
Differential	changes since last full	full + latest differential
Incremental	changes since last backup of any type	full + all incrementals in order

2. WAL, PITR and ARIES

Base backuprestore first
Replay WALarchived logs
Stop at targetrecovery_target_time

WAL is written before data pages; commit = log flushed
Analysis, then Redo (repeat all history), then Undo (roll back losers, write CLRs)
Buffer policy: steal + no-force

3. High Availability

	Sync	Async

DBMSPage 7

Check yourself

Full backup Sunday, incremental Mon to Wed. The disk dies Thursday morning. What do you restore, and what changes if the daily backups were differential?

Incremental: Sunday full, then Mon, Tue, Wed in order.
Differential: Sunday full plus only Wednesday's, since it holds everything since the full.

A DROP TABLE ran at 14:05:10. The team has a synchronous replica and plans to fail over to it. Why does that not get the table back?

The DROP replicated to the replica immediately.
Use PITR instead: restore the base backup, replay archived WAL to a time just before 14:05:10.
This only works if WAL archiving was on before the incident.

Crash: T1 committed but its pages never reached disk. T2 never committed but one of its dirty pages did. What does ARIES do?

Analysis finds the dirty pages and active transactions.
Redo repeats all history, including T1 (no-force) and T2.
Undo then rolls back T2 (steal), writing CLRs.