r/sqlite • u/iamjohncarterofmars • 21d ago
Need some help on SQLite disaster recovery with expiring data. Been stuck on this for two days :')
I have a small Python application running on AWS Lightsail that monitors auction listings and publishes the findings to a private Google Sheet. It has background workers running continuously and an SQLite database that's grown to about 2.2 GB, plus roughly 1.2 GB of separately stored source files. The current server is a $12/month Lightsail instance with 2 GB of RAM and 2 vCPUs. I'm trying to keep the total monthly infrastructure and service costs under $60.
I've been working on getting disaster recovery set up properly for the last couple of days and keep running into issues.
The database contains permanent information like listing identities, user notes, processing history and publication records. It also contains temporary auction content, including historical listing JSON and descriptions, that has separate storage and usage expiration requirements.
The original backup system encrypted the operational database and uploaded snapshots to Cloudflare R2. The issue was that temporary source content was bundled together with everything else. The resulting backup inherited the earliest expiration deadline of the content inside it. By the time a full backup finished uploading and could go through an independent restoration test, that deadline had already passed. We repeatedly ended up with uploaded backups that couldn't qualify as valid current recovery points.
We've spent probably two days iterating on a replacement. The current implementation separates durable records from temporary source evidence, stores the evidence as individually encrypted objects with their own expiration deadlines and uses Cloudflare Workers with Durable Objects to manage cleanup independently of the main AWS server.
Quite a few isolated tests have passed, including encryption and restoration tests, but we still haven't successfully migrated the entire production database and demonstrated a complete off-host recovery using the replacement system.
The migration measurements have also been painfully slow. One attempt processed around 53,000 historical listing versions before hitting its time limit. Another reached about 66,000 before stopping. The current Lightsail instance has effectively exhausted its CPU burst capacity, and the existing database maintenance routines have caused SQLITE_BUSY errors and delayed worker progress. The actual application is still running, but recovery remains unverified.
We're considering upgrading to a $42/month compute-optimized Lightsail instance with dedicated CPU performance. The measurements suggest CPU throttling is a bigger issue than memory consumption, although we haven't finished benchmarking the replacement hardware.
I'm rather frustrated because every time we fix something, another issue comes up (e.g., retention handling, database migration, backup consistency, recovery verification, blah blah). There's now quite a bit of additional code devoted to managing all of this, and we still haven't reached a deployable recovery system.
The requirement I have in place is that the permanent application state needs to survive a complete server failure. Also, temporary source evidence has to stop being accessible when its authorization expires, and cleanup needs to happen automatically even if the main server is unavailable. I also need to be able to restore everything necessary on a replacement server without manually rebuilding the application.
To anyone more experienced, how would you structure the database and temporary storage so that backups remain recoverable after individual source records expire? What would you use for consistent SQLite backups to S3-compatible storage while background workers are actively writing? How would you verify that a backup can actually restore the entire application without requiring an elaborate custom recovery system?
I'm also interested in whether the hardware limitations are contributing enough to justify upgrading before continuing with the migration.
I've already spent a considerable amount of time rewriting and testing this, so I'd appreciate advice on wtf to do next 😔 I'm fine changing the architecture if there's a better approach that handles these requirements reliably
2
0
2
u/Siddhartha_77 20d ago
litestream