Backing up and restoring a database
Saving and recovering a PostgreSQL database: logical or physical backup, full, differential, incremental, WAL, point-in-time recovery, loss and downtime targets, the 3-2-1 rule.
- 26 cards
- 6 topics
- Free
The outline, topic by topic
Each topic is weighted the way the exam weighs it.
- Concepts
- Questions
- Sort and link
- The numbers
- Scenarios
- Understand
A few cards from the deck
Logical backup: what is it?
Show the answer
A copy of a database's content as SQL statements or data an import tool can read, produced by reading the database.
This is what
pg_dumpdoes. It can be reloaded into another PostgreSQL version and lets you restore a single table, but it freezes the state of the dump's moment and replays no log.Full backup on Sunday, incremental every night, failure on Thursday at noon. Which backups must be restored, in order?
- Sunday, then Monday, Tuesday and Wednesday
- Wednesday only, as it is the last backup taken before the failure
- Sunday and Wednesday, as each incremental holds everything since the full backup
- Wednesday, then Sunday, as the most recent is put in place first and you work backwards
Show the answer
Sunday, then Monday, Tuesday and Wednesday
An incremental holds only the previous day's change: you need the full backup then each incremental, in order. In a differential plan, Sunday and Wednesday would suffice.
The RPO measures how much data we accept to …; the RTO, the … we accept before service is restored.
Show the answer
The RPO measures how much data we accept to lose; the RTO, the downtime we accept before service is restored.
RPO looks backwards (how far back the last saved data goes), RTO looks forwards (how long to restart). Two separate targets, set separately.
Physical backup: what is it?
Show the answer
A copy of the instance's actual data files, taken hot or cold, restored by putting them back as they are.
This is the job of
pg_basebackup. It is faster to restore on large volumes and, with archived WAL, it lets you go back to a precise moment. It requires the same major version.