Recovery and retention

What you will be able to do

Prove that you can restore a backup, before you need it. Then delete one customer’s data from the database, the files and the vectors, and find what a half-finished deletion leaves behind.

Before you startWhat to know and to have readyAbout 60 minutes

What you need to know

• Transactions: all of it, or nothing, from Transactions.
• The foreign keys and their delete rules, from Integrity constraints.
• Files in object storage with a row per file, from Object-storage patterns; vector versions and the text checksum, from Vector storage.

What you need on your computer

• The project after Vector storage, with PostgreSQL and Azurite running. python -m pytest prints 101 passed, 1 deselected.
• pg_dump and pg_restore version 18, from the PostgreSQL installer or the Homebrew package libpq. Without them, scripts/backup.py runs them inside the Docker container; the course did not test that route.

This lesson adds migration 012_erasure.sql, four scripts (backup, restore_drill, erase_customer, scan) and a test file. After it, your project matches the snapshot end-of-m05, the end of the course project.

The spare key in the drawer

Many families keep a spare house key in a drawer. Nobody has tried it since the locks were changed. The day somebody needs it, it does not turn.

Schools practise fire drills for the same reason. A plan that nobody tried is a hope. The drill shows the blocked door while there is no fire.

The problem: “we have nightly backups”

Tomás is relaxed about recovery. “We have nightly backups, we’re fine.” Nobody has ever restored one.

The course did what nobody at Larkfield had done: it restored backups, and checked what came back. Three backups failed, each for a real reason, and each made a file that looked fine:

• A backup whose description was written a moment after the dump, while the API kept writing. The restore had 331 messages; the description said 333.
• A good backup, restored after the team had erased a customer. The database came back, but 33 of 43 files were there. And the erased customer was back too.
• A good backup, restored into a PostgreSQL server without the pgvector extension: extension "vector" is not available, and errors ignored on restore: 8. The vectors table never came back.

The first and the third are constructed cases, and the course labels them every time. They are not rare in real life: a team often restores into a new server, or takes the dump while the service runs.

The idea: a backup, and a restore drill that proves it

A backup is a copy of the data, kept apart, from which you can make the database again. A restore drill is a practice restore: you restore the backup into a new, empty database, and check that what comes back is complete. Only then is the backup proven.

To check “complete”, the drill needs to know what the backup should contain. So scripts/backup.py writes two files: the dump (pg_dump -Fc, PostgreSQL’s compressed format) and a manifest. The manifest lists the applied migrations and, for each table, the number of rows and a checksum of all its contents. Here is part of a real one, from the small data:

backups/tickets-20261010-050211.json (part)

"sha256": "1deab8057b8d60a693bad2b983380dde6b60428f0dc3761d12f982e82b6abc6a",
"tables": {
  "customers": { "rows": 40, "md5": "ddfcc042800ed374de0bca8c04f75b95" },
  "tickets":   { "rows": 200, "md5": "0dc72e50a0a7a9e2e7673167b1ac5ffc" },
  "messages":  { "rows": 331, "md5": "e37cb3f89c8ac805536997ad420990a3" },

Row counts alone can hide a change. In the first failure, tickets had 200 rows on both sides, but one ticket’s message_count differed. The checksum caught it. The drill then runs seven checks:

A backup is two files: the dump and its manifest. The drill restores into an empty database and runs seven checks. Each red box is a real failure from this lesson.

Text description of the diagram

backup.py makes the dump with pg_dump and the manifest from one snapshot of the database. The small data’s dump is 0.4 MB. restore_drill.py restores the dump into a new, empty database and runs seven checks. One: the dump matches its manifest. Two: pg_restore finished. Three: the same migrations are applied. Four: every table has the same rows and contents. Five: every constraint is valid. Six: the similar-ticket search works. Seven: every stored file is in the object storage, because the files are not in the dump. Three real failures: a manifest made after the dump, while messages arrived, fails check four with 331 messages where the manifest had 333. A restore after an erasure fails check seven: 33 of 43 files found. A server without pgvector fails check two: extension vector is not available.

The drill never touches your database: it restores into tickets_restore_drill, and drops that copy unless you ask to keep it. On the large data, the dump was 53.9 MB, made in 4.5 to 4.7 s. pg_restore took 4.6 to 7.9 s. That drill passed 6 of 6; check 7 was skipped, because the large data has no files.

One snapshot for the dump and the manifest

The first failure explains a line of backup.py. The constructed run wrote the dump, then two messages arrived, then the manifest was written: two moments, two pictures. The restore matched the dump, and the manifest described a later database.

The fix is to describe the same moment that the dump copies. backup.py opens a transaction, asks PostgreSQL for its snapshot with pg_export_snapshot(), and passes it to pg_dump --snapshot. Then it reads the manifest in that same transaction. Writes that arrive meanwhile are in neither file, and the API keeps running.

Encryption: who else can read a backup

A dump holds every customer’s name and email. It is compressed, not encrypted, and any pg_restore reads it:

pg_restore --data-only --table customers --file - backups/tickets-20261010-050211.dump | grep -m2 "Olga\|COPY"

COPY public.customers (customer_id, name, email, segment, region, joined_on, created_at) FROM stdin;
C-0001  Olga Olsen  olga.olsen@example.com  home  north  2024-10-18  2024-10-18 09:42:49+00

So protect data in two places. In transit, between the API and a hosted database, use TLS (sslmode=require or verify-full in the connection string). At rest, the cloud services encrypt their disks and blob storage by default, according to their documentation. A dump that you copy somewhere is your job: encrypt it, give few people access, and delete it when the retention plan says. On your computer, .gitignore already keeps backups/ out of Git.

Deleting a customer everywhere

Grace asks the question that she asked in the first module: “If a customer asks us to delete their data, can we?” One customer’s data is now in three kinds of place: rows in PostgreSQL, files in object storage, and vectors made from their text.

PostgreSQL can delete the rows in one transaction. The foreign keys of Integrity constraints help: messages and vectors go with their ticket (CASCADE). But the object storage is not in the transaction. So the program must delete the files, and the order matters:

• Files first, then rows. If the program crashes in between, rows point to files that are gone. The ticket page breaks.
• Rows first, then files. If it crashes in between, files stay with no row that knows them. Nobody will ever find them, and the customer’s photos stay.

The project uses rows first, with a record of the work still to do. In the same transaction that deletes the rows, it writes the keys of the files into a table, file_deletions. This is an outbox: a list of actions that must still happen outside the database, written in the same transaction as the change. After the commit, a second step deletes each file and marks its key as done. If that step stops, the list is still there, and it can run again. Deleting a file that is already gone is fine, because the goal is that it does not exist. That makes the second step safe to repeat, as in Side effects and retries.

The erasure of C-0028, with the real counts. Step 1 is one transaction, and writes the outbox. Step 2 deletes the files. The scan finds what a crash between them leaves, and a restore can bring erased rows back.

Text description of the diagram

Step 1 is one transaction in PostgreSQL. It deletes customer C-0028 and the customer’s 19 tickets. The 33 messages and the 19 vectors go with their tickets, through CASCADE. The 10 attachment rows are deleted, and their object keys are written to the outbox table file_deletions. The 31 AI runs lose their text and their ticket, but keep their cost. The erasures table gets one row with counts only. Either all of it commits, or nothing does. Step 2 comes after the commit. It deletes each of the 10 files in object storage, for example tickets/T-30018/2646ce7c…/photo-1.png, and marks each key as done. If the program crashes between the steps, the rows are gone and the files stay. The scan then shows 10 waiting deletions, and scan –fix finishes them. The scan also looks for orphan files, missing files, old pending uploads and stale vectors. A backup holds the PostgreSQL part only. A restore from before the erasure brings the customer’s rows back without their files, and the drill finds 33 of 43 files.

Three details of migration 012 and scripts/erase_customer.py:

• AI runs keep their cost. Priya’s monthly totals must stay right. The runs lose their ticket and their output text, which can contain the customer’s words. Migration 012 relaxes one CHECK: a successful run may have no output if it has no ticket.
• The erasures table keeps counts, not data. It records that customer C-0028 was erased, when, and how many rows of each kind. That is the proof for Grace.
• An index makes it fast. For each deleted ticket, PostgreSQL checks that no attachment row still points to it. Without an index on attachments (ticket_id), each check reads the whole table. On the large data, erasing the business customer C-3835 (4,322 tickets) took 7.7 s. With the index that 012 adds, it took 0.39 s.

A deletion that stopped halfway, and the scan

What does a crash between the two steps leave? The course stopped the program there on purpose, with --crash-after-database (a constructed failure):

Database: deleted 19 tickets, 33 messages, 10 attachment rows, 19 vectors; removed the text of 31 AI runs (their cost stays). 10 files are waiting in file_deletions.
Stopped on purpose before the files were deleted (--crash-after-database).

The ten photos and receipts are still in the storage. Without a check, nobody would notice. Two words name what can go wrong between two stores:

• An orphan file is a file in object storage that no attachment row knows, and that no waiting deletion lists. Nobody can find it from the database, and nobody will delete it.
• A stale vector is a vector whose ticket text changed after the vector was made. Its source_sha256 no longer matches the checksum of the text. Searches then find the ticket by its old words.

scripts/scan.py compares the stores and counts each problem. To show all of them, the course added two more constructed faults: a file that no row knows, and a ticket edited by hand. Then it ran the scan:

orphan files                          1   tickets/T-30005/7c1f0e7a-0000-4000-8000-000000000000/photo-1.png
missing files                         0
waiting deletions                    10   tickets/T-30018/2646ce7c-9180-4b82-84b6-eb99d6bd4b6e/photo-1.png, tickets/T-30021/380da7d7-c283-4bc6-9524-e6a2fc48780e/receipt-LK-413252.pdf, tickets/T-30021/da92666b-f5fd-472d-bbec-f739c3916847/receipt-LK-413252.pdf ...
waiting deletions already gone        0
old pending uploads                   0
embedding version                e5-small-v1
stale vectors                         1   T-30010
vectors of other versions             0
tickets without current vector        0

Each line is one comparison, written as a question:

Line	The question	How the scan answers it
orphan files	Is a file in storage that no row and no waiting deletion knows?	storage keys minus attachments.object_key minus waiting keys
missing files	Does a stored row point to a file that is not there?	stored rows minus storage keys
waiting deletions	Did a deletion stop halfway?	rows of file_deletions not done, whose file still exists
old pending uploads	Did an upload start more than a day ago and never finish?	attachments with status = 'pending' and an old created_at
stale vectors	Did a ticket’s text change after its vector was made?	source_sha256 against the SHA-256 of the current text

scan --fix only finishes the waiting deletions. Those are safe: the database already decided to delete those files. The orphan file and the stale vector stay for a person. The orphan could be a file that somebody still needs. For the stale vector, python -m scripts.embed makes it again with the model.

Retention: backups remember what you erased

Now the second failure of the drill makes sense. The backup was made before the erasure. The restore brought back C-0028, Noah Park, with 19 tickets. The record of the erasure was gone, because it was newer than the backup. The files were not restored, because they were never in the backup:

FAIL  attachment files are in the object storage: 33 of 43 found; missing e.g. tickets/T-30018/2646ce7c-9180-4b82-84b6-eb99d6bd4b6e/photo-1.png
Restore drill: 6 of 7 checks passed.

Here, the failure is good news: the files of an erased customer should be gone. But the rows should be gone too. A retention plan answers this with three rules:

• Keep each backup for a fixed time, then delete it, for example 30 days. An erased customer’s data then leaves the last backup within 30 days. Tell the customer that.
• Keep the list of erasures outside the backups, for example as a file next to them. After any restore, erase again every customer on the list who is newer than the backup.
• Back up the files separately, with their own retention: object versioning, or a copy to a second container. A lifecycle rule from Object-storage patterns can delete old copies.

Guided practice: back up, restore, erase, scan

1. Copy this lesson’s files from the snapshot. On macOS or Linux:

   S=../nextia-learning-labs/C20/snapshots/end-of-m05
   cp $S/migrations/012_erasure.sql migrations/
   cp $S/scripts/{backup,restore_drill,erase_customer,scan}.py scripts/
   cp $S/tests/test_recovery.py tests/

   On Windows, with $S as before: Copy-Item "$S\migrations\012_erasure.sql" migrations\, then foreach ($f in "backup","restore_drill","erase_customer","scan") { Copy-Item "$S\scripts\$f.py" scripts\ }, then Copy-Item "$S\tests\test_recovery.py" tests\.

2. Apply 012, and run the tests:

   python -m ticket_api.migrate
   python -m pytest

   The migration prints applied 012_erasure and Database is up to date (12 migrations). The tests print 108 passed, 1 deselected. Your project now matches end-of-m05.

3. See what erasing customer C-0003 would delete, without deleting it:

   python -m scripts.erase_customer C-0003 --dry-run

   C-0003 has: tickets 18, messages 27, files 2, vectors 36, ai_runs 27

   36 vectors for 18 tickets: each ticket has an e5 vector and a MiniLM vector, from the optional step of the last lesson. Without that step, you see vectors 18.

4. Load the small data again, so that your numbers match the lesson, and run the scan:

   python -m scripts.load --reset
   python -m scripts.scan

   The load ends with 43 files uploaded to the object storage. Every count of the scan is 0, and the embedding version is e5-small-v1.

5. Make a backup:

   python -m scripts.backup

   Backup: backups/tickets-20261010-050211.dump (0.4 MB, 0.1 s)
   Manifest: backups/tickets-20261010-050211.json: 12 migrations, customers 40, tickets 200, messages 331, attachments 43, documents 36, ai_runs 294, ticket_embeddings 200
   Not in the backup: the files in object storage. A backup is not proven until a restore works: python -m scripts.restore_drill backups/tickets-20261010-050211.dump

   Your file name has your own date and time. Use it in the next steps.

6. Prove it:

   python -m scripts.restore_drill backups/tickets-20261010-050211.dump

   PASS  dump matches its manifest
   PASS  pg_restore finished: 0.1 s
   PASS  same migrations: 12 of 12
   PASS  same rows and contents in every table: all 7 tables
   PASS  every constraint is valid
   PASS  similar-ticket search works: 5 results
   PASS  attachment files are in the object storage: 43 of 43 found
   Restore drill: 7 of 7 checks passed.

7. Erase C-0028, the customer with the most files, and stop on purpose after the database step:

   python -m scripts.erase_customer C-0028 --dry-run
   python -m scripts.erase_customer C-0028 --crash-after-database

   The dry run prints C-0028 has: tickets 19, messages 33, files 10, vectors 19, ai_runs 31. The second command prints the two lines shown above, ending with Stopped on purpose.

8. Run the scan. It finds waiting deletions 10 and ends with exit code 1, so a scheduled job would report it.

9. Add two constructed faults: a file that no row knows, and a hand edit of a ticket. The first command puts 100 bytes in the storage:

   python -c "from ticket_api.config import load_env, load_settings; from ticket_api.files import make_store; load_env(); make_store(load_settings()).put('tickets/T-30005/7c1f0e7a-0000-4000-8000-000000000000/photo-1.png', b'x' * 100, 'image/png')"

   Then, in psql: UPDATE tickets SET body = body || ' Update: it arrived today.' WHERE ticket_id = 'T-30010';. Run the scan again. It shows the output above: 1 orphan file, 10 waiting deletions, and 1 stale vector, T-30010.

10. Let the scan finish what is safe, and look again:

    python -m scripts.scan --fix
    python -m scripts.scan

    The first command ends with fixed finished 10 waiting deletions (10 files deleted). The second shows waiting deletions 0. The orphan file and the stale vector are still there, for a person to decide.

11. Restore the backup of step 5 again, now that the erasure has happened:

    python -m scripts.restore_drill backups/tickets-20261010-050211.dump --keep

    Six checks pass, and check 7 fails with 33 of 43 found. In psql, connect to the kept copy with \c tickets_restore_drill, and run SELECT customer_id, name FROM customers WHERE customer_id = 'C-0028';: Noah Park is back. Go back with \c tickets, and drop the copy: DROP DATABASE tickets_restore_drill;.

12. Clean up with python -m scripts.load --reset. The scan is clean again.

Common mistake

The restore runs, but the vectors do not come back

Symptom: a restore into a new server prints errors and continues. The drill, or your own check, finds no ticket_embeddings table, and similar-ticket search fails. The course reproduced it with a plain PostgreSQL 18.6 image, which has no pgvector (a constructed case):

pg_restore: error: could not execute query: ERROR:  extension "vector" is not available
pg_restore: error: could not execute query: ERROR:  extension "vector" does not exist
pg_restore: error: could not execute query: ERROR:  type "public.vector" does not exist
pg_restore: error: could not execute query: ERROR:  relation "public.ticket_embeddings" does not exist
pg_restore: warning: errors ignored on restore: 8

Cause: a dump contains CREATE EXTENSION vector, but not the extension’s program. The target server must have pgvector installed. pg_restore skips the statements that fail and restores the rest, so the restore looks almost fine.

Diagnose: read the first error, not the last line. extension "vector" is not available names the missing piece. The drill’s check 2 fails, and check 6, similar-ticket search, fails too.

Fix: restore into a server with the same major version and the same extensions: here, the image pgvector/pgvector:0.8.7-pg18-trixie. Write the server’s version and extensions into the recovery plan, and run the drill on the kind of server that you would restore to.

Module practice: a recovery and deletion plan

Write a one-page recovery and deletion plan for your own ticket-api. It has customers, tickets, messages, AI runs and documents in PostgreSQL. Its attachment files are in object storage, and its ticket vectors are in pgvector, with an embedding version. Answer these questions:

1. What do you back up, how often, and for how long? Name the data that needs no backup of its own, and why.
2. How do you prove a backup? Name the checks, and the kind of server for the drill.
3. A customer asks Larkfield to delete their data. List every place that their data or its copies live, and the order of the steps.
4. What can a crash in the middle leave, and how do you find it?
5. What happens to an erasure when you restore a backup that is older than the erasure?

For a second exercise, write the same plan for the document assistant of the first module’s practice.

Show an example answer

1. Backups. PostgreSQL: a nightly python -m scripts.backup, with the dump and the manifest from one snapshot. Keep each one for 30 days, encrypted, in another account or region. The attachment files: object versioning, or a nightly copy to a second container, also kept for 30 days. The ticket vectors and the HNSW index are copies of the ticket text. They are in the dump anyway, and python -m scripts.embed makes them again from the text with the recorded version.

2. Proof. A weekly python -m scripts.restore_drill into a server with PostgreSQL 18 and pgvector 0.8, the image pgvector/pgvector:0.8.7-pg18-trixie. Its seven checks: the dump matches its manifest, pg_restore has no errors, and the migrations are the same. The rows and checksums of every table match, the constraints are valid, and similar-ticket search works. Every stored file is in the object storage. A failed drill pages a person.

3. Erasure. python -m scripts.erase_customer <id>. One transaction deletes the customer, their tickets, messages, attachment rows and vectors of every version. It removes the text of their AI runs and keeps the cost. It writes the file keys to file_deletions and a row with counts to erasures. After the commit, the script deletes the files and marks each key done. Old backups still hold the data until they expire, so the plan tells the customer: gone everywhere within 30 days.

4. Partial failure. A crash after the commit leaves files whose rows are gone. python -m scripts.scan shows them as waiting deletions, and scan --fix finishes them. A file that no row and no waiting deletion knows is an orphan, for a person to check. A ticket edited after its vector was made shows up as a stale vector; scripts.embed makes it again. Run the scan every night, and alert on exit code 1.

5. Restore. The erased customer’s rows come back, without their files: the drill found 33 of 43. So keep a copy of the erasure list outside the backups. After every restore, run erase_customer again for every erasure newer than the backup, then run the scan.

Module check

You have finished Module 5 when you can show these on your own project:

• python -m pytest prints 108 passed, 1 deselected, and python -m pytest -m azurite prints 1 passed.
• An upload through a signed URL ends as stored, and a too-large upload ends as rejected with 413.
• python -m scripts.backup and then python -m scripts.restore_drill backups/<file>.dump print Restore drill: 7 of 7 checks passed.
• Completion check: explain how to detect orphan files and stale vectors after a partial failure. Name what each one is, what the scan compares to find it, and what scan --fix does and does not do. Use the crashed erasure of C-0028 as your example.

Reset. If your project is broken, rename it and copy the snapshot end-of-m05. Keep your .env. In ~/projects:

mv ticket-api ticket-api-old
cp -R nextia-learning-labs/C20/snapshots/end-of-m05 ticket-api
cp ticket-api-old/.env ticket-api/

On Windows, in PowerShell, in ~/projects: Rename-Item ticket-api ticket-api-old, then Copy-Item -Recurse nextia-learning-labs\C20\snapshots\end-of-m05 ticket-api, then Copy-Item ticket-api-old\.env ticket-api\. Make the virtual environment again, run docker compose up -d db azurite, and run python -m scripts.load --reset.

See it end to endDesign and tune storage for an open public datasetCase study

The Boston 311 case study restores a 40.6 MB backup with 7 checks. A restored database needs ANALYZE before its plans are right.

See it end to endVector search over an open document collectionCase study

The arXiv case study removes a paper and both its vectors in one transaction. After a constructed failure, a scan finds stale and orphan vectors.

Knowledge check

Check your understanding

Completion check: after a partial failure, how does the project detect orphan files and stale vectors? Say what each one is, what the scan compares, and what scan --fix does with it.

Show the answer

An orphan file is a file in object storage that no attachment row and no waiting deletion knows. The scan lists the storage keys under tickets/, and removes every attachments.object_key and every key in file_deletions that is not done. What is left is an orphan, such as the constructed tickets/T-30005/7c1f0e7a-…/photo-1.png. A stale vector is a vector made from an older ticket text. The scan compares each vector’s source_sha256 with the SHA-256 of the ticket’s current subject and body; T-30010 differed after the hand edit. After the crashed erasure of C-0028, the scan also showed 10 waiting deletions. scan --fix finishes only those, because the database already decided to delete them. It leaves the orphan for a person to check, and the stale vector for scripts.embed to make again.

Check your understanding

Why does erase_customer delete the rows before the files, and what makes the second step safe to run again?

Show the answer

Files and rows cannot be in one transaction. If the files went first and the program crashed, rows would point to files that are gone. Rows first means a crash can only leave files behind. The same transaction writes their keys into file_deletions, the outbox, so nothing is forgotten. The second step deletes each listed file and marks it done. Deleting a file that is already gone counts as success, so running the step twice gives the same result.

Check your understanding

Tomás says: 'We have nightly backups, we're fine.' Give him three reasons from this lesson why a backup that was never restored is not enough.

Show the answer

First, the backup may not be what you think: a manifest written a moment after the dump described 333 messages, and the restore had 331. Second, the restore needs the right server: without pgvector, pg_restore skipped the vectors and reported errors ignored on restore: 8. Third, the backup has only the database. The files were not in it (33 of 43 after an erasure), and an erased customer came back with their rows. Only a restore drill with checks shows these before an incident.

Recap

• A backup is a dump and a manifest, from one snapshot. A restore drill restores it into an empty database and runs seven checks: 7 of 7 on the small data.
• Three real failures: a manifest from another moment (331 vs 333 messages) and a server without pgvector (8 errors). And a restore after an erasure: 33 of 43 files, and the customer back.
• A dump is compressed, not encrypted. Protect backups and connections, and keep backups only as long as the plan says.
• An erasure deletes the rows in one transaction and writes the file keys to an outbox, then deletes the files. An index made the large erasure 0.39 s instead of 7.7 s.
• The scan finds orphan files, missing files, waiting deletions, old pending uploads and stale vectors. --fix finishes only the waiting deletions.
• Your project matches end-of-m05, the end of the course project: 108 passed, 1 deselected.

You have finished Module 5 and the course project. The case studies of Module 6 apply the same methods to real open data.

Next: Design and tune storage for an open public dataset