Prompt and context
A large PostgreSQL cluster wants shorter full-backup windows and lower storage cost. Design an incremental base-backup chain using pg_basebackup, explaining WAL dependencies, backup manifests, pg_combinebackup, verification, retention, and recovery drills.
PostgreSQL states that an incremental backup cannot be restored directly: it must be combined with its dependent earlier backups into a synthetic full. The tool checks relationships but does not track dependencies for you or prove every backup is intact. The interview tests recoverability evidence, not memorizing one backup command.
What the interviewer is testing
Explain full, incremental, WAL, and manifest roles; reference backups, dependency chains, and synthetic fulls; pg_verifybackup, checksums, immutable object versions, and integrity checks; chain breaks, standby backups, replication-slot/WAL retention, encryption, and recovery objectives. Prove RPO and RTO with drills rather than backup-success dashboards.
Questions to clarify first
Recovery objectives
Confirm RPO, RTO, target time points, cross-region recovery, allowed PostgreSQL version changes, and whether the cluster has multiple tablespaces.
Backup workload
Confirm full size, daily change volume, WAL rate, backup window, network bandwidth, object-store lifecycle, and concurrency limits.
Consistency and compliance
Confirm backup encryption and key rotation, immutable retention, deletion permissions, manifest checksum algorithm, audit records, and drill frequency.
A 30-second answer
“I start with a verifiable full backup, generate incrementals from a reference manifest, and retain each manifest, dependency record, and continuous WAL segment. An incremental is not directly restorable: I combine the chain in order with pgcombinebackup, then apply the required WAL. Object storage uses immutable versions and encryption, while pgverifybackup and sample restores validate content. If any dependency is missing, automation blocks deletion of its predecessors. Real size and failure drills prove RPO and RTO instead of relying on backup-success rates.”
Step-by-step deep answer
Step 1: Model the backup chain
Record the full backup, each incremental's reference backup, LSN range, manifest, key version, and storage URI. Every node points to its prerequisites; retention first calculates the earliest still-recoverable time point.
Step 2: Produce incrementals
pg_basebackup can request an incremental using a reference manifest; the incremental contains blocks changed since that reference. The backup still covers the entire cluster rather than one database object. The replication connection needs REPLICATION privilege or superuser rights and enough walsenders.
full_0 = pg_basebackup(full)
inc_1 = pg_basebackup(incremental, reference=full_0.manifest)
inc_2 = pg_basebackup(incremental, reference=inc_1.manifest)Step 3: Retain continuous WAL
WAL generated during a base backup must remain available. The stream method opens a second replication connection in parallel; the fetch method requires wal_keep_size or archiving to retain needed WAL until transfer completes. Replication slots reduce premature removal risk but can grow disk usage, so monitor the oldest required LSN.
Step 4: Verify manifests and relationships
pg_combinebackup verifies legal relationships among input backups but not the integrity of each backup. Run pg_verifybackup and manifest checksums for every node. Register a node in the catalog only after its object upload and verification complete.
Step 5: Build the restore input
For a target time point, invoke pg_combinebackup from the full through the target incremental in order to produce a synthetic full. It can seed a later combine operation but does not replace WAL. Place WAL from the backup-end LSN through the target time in the recovery directory and configure the recovery target.
Step 6: Handle breaks and retention
The scheduler maintains a dependency graph and earliest recoverable time. If a prerequisite is missing, fails checksum verification, or loses its key, mark all descendants unrecoverable and block automatic deletion of the prerequisite. Periodically create a new full or synthetic full to cap chain length and RTO.
Step 7: Drill RPO and RTO
Restore random time points in isolation and check system catalogs, tablespaces, WAL, extensions, and application consistency. Record downloaded bytes, combine duration, WAL replay rate, completion time, and checksums. Inject object-store 404s, bad manifests, key revocation, and primary failure.
Model answer
I treat backups as a dependency graph: a full is the root, each incremental points to a reference, and WAL fills the time point. Every node stores a manifest, checksum, LSN, key version, and immutable object URI. Recovery combines the chain in order with pgcombinebackup, then replays continuous WAL; its relationship check does not replace pgverifybackup content checks. Retention follows the graph, and drills cover broken chains, missing WAL, key revocation, and multiple tablespaces before measured RPO/RTO gates release.
Common mistakes
- Mistake: Treating an incremental as a bootable directory. → Why it fails: It depends on a reference backup. → Fix: Combine it into a synthetic full, then apply WAL.
- Mistake: Trusting a successful pg_combinebackup run alone. → Why it fails: It does not prove each input is intact. → Fix: Verify every manifest/checksum and perform sample restores.
- Mistake: Retaining or deleting full backups by age only. → Why it fails: Later incrementals may still depend on them. → Fix: Use the dependency graph and earliest recoverable time.
- Mistake: Reporting backup success without continuous WAL. → Why it fails: The target time point cannot be replayed. → Fix: Monitor required LSN, archive lag, and slot usage.
Follow-up questions and responses
What is the difference among full, synthetic full, and incremental?
A full is an independent cluster-file copy. An incremental contains blocks changed since a reference. A synthetic full is reconstructed from the chain and can be a restore input, but still needs WAL after the backup endpoint for a target time.
Why not extend an incremental chain forever?
Long chains increase download, combine, verification, and failure costs, raising RTO. Insert a new full or synthetic full using change rate, storage cost, and drill data.
What do you do if a manifest is missing?
Mark the node unavailable for automation; never guess dependencies from filenames. If a trusted copy restores the manifest, verify file checksums, LSNs, and chain relationships before registering it again.
How do you prove encryption will not block recovery?
Regularly restore samples in isolation with current and historical keys, testing rotation, revocation, permissions, and cross-region KMS access. Alert explicitly and remove the time point from the recoverability claim when a key is unavailable.