280 lines
8.3 KiB
Markdown
280 lines
8.3 KiB
Markdown
# Backup And Restore Validation
|
|
|
|
This document captures the operational readiness artifact for plan item `Backup and restore validation`.
|
|
|
|
## Scope
|
|
|
|
The service stores durable state in two places:
|
|
|
|
- PostgreSQL metadata
|
|
- S3-compatible object storage
|
|
|
|
Both must be considered together during backup and restore because database records reference object-storage keys directly.
|
|
|
|
State that matters for recovery:
|
|
|
|
- `video_assets`
|
|
- `upload_sessions`
|
|
- `clip_jobs`
|
|
- `clips`
|
|
- `job_events`
|
|
- `asset_idempotency`
|
|
- `job_idempotency`
|
|
- `queue_messages`
|
|
- source objects under `uploads/<uploadId>/source`
|
|
- generated clips under `clips/<jobId>/<clipIndex>.mp4`
|
|
|
|
Ephemeral state that does not need backup:
|
|
|
|
- local worker input files
|
|
- local FFmpeg output files
|
|
- `tmp/in-memory-storage`
|
|
- `tmp/stub-output`
|
|
|
|
## Recovery Goal
|
|
|
|
After restore, the system must be able to:
|
|
|
|
- read existing assets, jobs, clips, and events
|
|
- generate signed clip download URLs for restored clips
|
|
- continue processing recoverable queued jobs
|
|
- avoid data-loss surprises caused by missing source or clip objects
|
|
|
|
## Backup Boundary
|
|
|
|
Treat PostgreSQL and object storage as a coordinated recovery set.
|
|
|
|
Minimum acceptable backup content:
|
|
|
|
- full PostgreSQL snapshot including all tables listed above
|
|
- object-storage snapshot or versioned copy of:
|
|
- `uploads/`
|
|
- `clips/`
|
|
|
|
Important consistency note:
|
|
|
|
- A database-only backup is insufficient because `video_assets.source_object_key` and `clips.object_key` point to object-storage objects.
|
|
- An object-storage-only backup is insufficient because job, asset, clip, upload-session, and idempotency metadata live in PostgreSQL.
|
|
|
|
## Validation Preconditions
|
|
|
|
Perform validation in a non-production environment using:
|
|
|
|
- the same schema migrations as production
|
|
- a restored PostgreSQL backup
|
|
- a restored object-storage snapshot or replicated bucket contents
|
|
- application config set to:
|
|
- `video-clipping.repository=jpa`
|
|
- `video-clipping.storage=s3`
|
|
- queue mode chosen explicitly:
|
|
- `video-clipping.queue=db` if queue recovery is part of the drill
|
|
- otherwise disable worker processing during the first verification pass
|
|
|
|
Before restore testing:
|
|
|
|
- stop API and worker writes to the target environment, or restore into a clean isolated environment
|
|
- apply Flyway migrations before importing data if restoring into an empty database
|
|
- restore object-storage contents before testing download or processing flows
|
|
|
|
## Restore Order
|
|
|
|
1. Restore object-storage contents for `uploads/` and `clips/`.
|
|
2. Restore PostgreSQL metadata snapshot.
|
|
3. Apply any required schema migrations not already present in the restored database.
|
|
4. Start the API service only.
|
|
5. Validate read-only recovery checks.
|
|
6. Start workers only after queue-state checks are complete.
|
|
|
|
This order reduces the risk of API or worker code touching metadata that references objects not yet present.
|
|
|
|
## Validation Checks
|
|
|
|
### 1. Schema And Row Presence
|
|
|
|
Confirm the restored database contains:
|
|
|
|
- expected tables from `V1` through `V6`
|
|
- representative rows in:
|
|
- `video_assets`
|
|
- `upload_sessions`
|
|
- `clip_jobs`
|
|
- `clips`
|
|
- `job_events`
|
|
- idempotency tables if the backup includes recent write activity
|
|
|
|
Minimum SQL checks:
|
|
|
|
```sql
|
|
select count(*) from video_assets;
|
|
select count(*) from upload_sessions;
|
|
select count(*) from clip_jobs;
|
|
select count(*) from clips;
|
|
select count(*) from job_events;
|
|
select count(*) from queue_messages;
|
|
```
|
|
|
|
### 2. Referential Consistency
|
|
|
|
Validate there are no obvious orphan relationships.
|
|
|
|
Suggested SQL:
|
|
|
|
```sql
|
|
select count(*)
|
|
from upload_sessions u
|
|
left join video_assets a on a.id = u.asset_id
|
|
where a.id is null;
|
|
|
|
select count(*)
|
|
from clip_jobs j
|
|
left join video_assets a on a.id = j.asset_id
|
|
where a.id is null;
|
|
|
|
select count(*)
|
|
from clips c
|
|
left join clip_jobs j on j.id = c.job_id
|
|
where j.id is null;
|
|
```
|
|
|
|
Expected result: `0` for each query.
|
|
|
|
### 3. Object-Key Coverage
|
|
|
|
Validate that metadata-backed object keys exist in object storage.
|
|
|
|
Must-check samples:
|
|
|
|
- a restored source object from `video_assets.source_object_key`
|
|
- multiple restored clip objects from `clips.object_key`
|
|
|
|
Expected result:
|
|
|
|
- every sampled key resolves successfully in object storage
|
|
- API-generated download URLs work for restored clips
|
|
|
|
### 4. API Read Validation
|
|
|
|
With API pods running and workers still disabled or scaled to zero:
|
|
|
|
- fetch a restored asset with `GET /v1/video-assets/{assetId}`
|
|
- fetch its upload session with `GET /v1/video-assets/{assetId}/upload-session`
|
|
- fetch related jobs with `GET /v1/video-assets/{assetId}/clip-jobs`
|
|
- fetch a restored job with `GET /v1/clip-jobs/{jobId}`
|
|
- fetch clips with `GET /v1/clip-jobs/{jobId}/clips`
|
|
- generate a signed download URL with `POST /v1/clips/{clipId}/download-url`
|
|
|
|
Expected result:
|
|
|
|
- endpoints return restored metadata successfully
|
|
- signed clip download URL points to an object that can be retrieved
|
|
|
|
### 5. Queue Recovery Validation
|
|
|
|
If `video-clipping.queue=db` is used in production, validate queue state deliberately.
|
|
|
|
Inspect restored queue message status distribution:
|
|
|
|
```sql
|
|
select status, count(*)
|
|
from queue_messages
|
|
group by status
|
|
order by status;
|
|
```
|
|
|
|
Interpretation:
|
|
|
|
- `COMPLETED` rows are historical and should remain inert.
|
|
- `DLQ` rows should not be replayed automatically.
|
|
- `PENDING` rows may be processed again once workers start.
|
|
- `PROCESSING` rows may represent interrupted work from the backup point.
|
|
|
|
Safety rule:
|
|
|
|
- Do not start workers blindly if many restored messages are in `PROCESSING`.
|
|
|
|
Because the code reclaims expired `PROCESSING` rows after `video-clipping.database-queue.visibility-timeout-ms`, starting workers can cause restored in-flight jobs to retry automatically.
|
|
|
|
Pre-worker SQL to inspect interrupted work:
|
|
|
|
```sql
|
|
select id, job_id, status, attempt_count, available_at, claimed_at
|
|
from queue_messages
|
|
where status in ('PENDING', 'PROCESSING', 'DLQ')
|
|
order by available_at nulls first, claimed_at nulls first
|
|
limit 50;
|
|
```
|
|
|
|
Recommended recovery decision:
|
|
|
|
- convert only intentionally recoverable work back to active processing
|
|
- keep `DLQ` rows quarantined
|
|
- review `PROCESSING` rows before allowing workers to reclaim them
|
|
|
|
### 6. Worker Resume Validation
|
|
|
|
After queue review:
|
|
|
|
- start one worker replica
|
|
- observe whether a restored `PENDING` job completes successfully
|
|
- verify resulting clip metadata and object uploads remain consistent
|
|
|
|
Expected result:
|
|
|
|
- no retry storm
|
|
- no duplicate clip-index conflicts
|
|
- no immediate DLQ growth
|
|
|
|
### 7. Cleanup Safety Validation
|
|
|
|
Confirm retention jobs do not accidentally delete freshly restored data because of old timestamps.
|
|
|
|
Check:
|
|
|
|
- `video_assets.expires_at`
|
|
- `clips.expires_at`
|
|
- cleanup configuration in the restored environment
|
|
|
|
Safety rule:
|
|
|
|
- if restored data is intentionally old but still under investigation, temporarily disable cleanup before starting scheduled jobs
|
|
|
|
Relevant config:
|
|
|
|
- `video-clipping.cleanup.enabled`
|
|
- `video-clipping.cleanup.source-retention-hours`
|
|
- `video-clipping.cleanup.clip-retention-hours`
|
|
|
|
## Pass Criteria
|
|
|
|
Backup and restore validation is considered successful when:
|
|
|
|
- schema restores cleanly
|
|
- representative rows exist for assets, jobs, clips, uploads, and events
|
|
- sampled `source_object_key` and `object_key` values exist in object storage
|
|
- restored API reads succeed
|
|
- signed clip download URL generation succeeds
|
|
- reviewed queue state does not cause unsafe automatic replay
|
|
- at least one representative queued job can resume successfully when workers restart
|
|
|
|
## Failure Conditions
|
|
|
|
Validation fails if any of the following occur:
|
|
|
|
- restored metadata references missing storage objects
|
|
- signed download URLs are generated for missing objects
|
|
- queue replay starts processing work that should have remained quarantined
|
|
- restored jobs immediately churn into DLQ due to unresolved dependency or data issues
|
|
- cleanup jobs delete restored investigation data before verification completes
|
|
|
|
## Recommended Drill Cadence
|
|
|
|
- Run a non-production restore drill at least quarterly.
|
|
- Run an additional drill before major schema or storage-layout changes.
|
|
- Repeat validation after changing retention policy, queue semantics, or storage provider configuration.
|
|
|
|
## Current Repo Limitation
|
|
|
|
This repository does not include production backup automation or an executable restore harness.
|
|
|
|
This artifact defines the validation standard and the checks operators should run in the target environment. Production completion of the milestone still requires an actual restore drill using real infrastructure snapshots.
|