checklist.md

2026-07-11 Trying the migration to sqlite again; we previously tried on 2026-02-21 but had to revert. Today's PR is #1927. I'm not streaming this because lots of steps make it too easy to flash PII or secrets on the screen.

For all the gory details, we planned this in issue #539.

sqlite migration checklist:

• link to this gist from app/views/layouts/application.html.erb
• lobsters-deploy
• set the site in read-only mode (config/application.rb on prod)
• take mariadb backup to pushcx's desktop
• jj f; bump merge commit for notice + td's latest
• scp lib/tasks/migrate.rake l:lobsters/current/lib/tasks
• hatchbox panel: pause screensharing, add env vars (below)
• be rails dump_db
• scp db to my local dev
• local: edit config/database.yml from trilogy to sqlite (below)
• local: rm db/development/primary.sqlite3 and be rails db:create:primary and be rails db:schema:load:primary
• local: be rails load_db
• scp db/development/primary.sqlite3 l:lobsters/shared/storage/
• merge the PR
• pause streaming, delete DATABASE_URL (can't get relative with sqlite working immediately, tried sqlite://storage/primary.sqlite3 and config/database.yml has the absolute path)
• hatchbox: stop solid_queue process
• lobsters-deploy
• investigate exceptions - they're ok, the mariadb code was live for ~30s with the sqlite database config, so it threw syntax errors
• build the search indexes (below)
• inspect the slow query log for scary things
• take a sqlite backup to local dev
• run the backup script
• debug the backup script; it's not running the restic
• restore the restic backup to local
• if prod doesn't immediately melt down, rm read-only mode, notice, and trilogy gem
• hatchbox: start solid_queue process

database.yaml:

primary:
  #    <<: *trilogy
  #    database: lobsters
  <<: *sqlite3
    database: db/primary.sqlite3

ENV vars:

ENABLE_SLOW_QUERY_LOGS = true
SLOW_QUERY_THRESHOLD_MS = 100

build initial search indexes:

insert into comments_fts(rowid, comment) select id, comment from comments;
insert into story_texts_fts(rowid, title, description, body) select id, title, description, body from story_texts;

revert checklist

• revert the merge commit
• config/application.rb - set read_only = true
• hatchbox: stop puma
• hatchbox: stop background jobs
• be rails dump_db # sqlite -> dump.yml
• backup new dump.yaml down to local, move prod copy to ~ (so it's not rotated by deploy)
• restore DATABASE_URL
• be rails load_db # dump.yml -> mariadb
• lobsters-deploy
• hatchbox: restart puma
• hatchbox: restart bg jobs
• remove the layout note
• config/application.rb: set read_only = true
• lobsters-deploy
• prod: mv storage/primary.sqlite3* ~, back it up

followup chores

• rewrite my 'prod db -> dev' script to fire resticjob and restore from snapshot
• remove ~/.tmp/2026-07-11-dump.yml
• tomorrow 2027-07-11: confirm the automated backup script ran
• 2026-07-14: power off and image mariadb droplet
• 2026-07-21: delete mariadb droplet

DigitalOcean has basic activity monitors. These charts cover the last 14 days to give a baseline; the spike on disk I/O near the right is the migration. CPU and load look normal, maybe spikier, and memory is down, which is wonderful.

@pushcx as SQLite is an embedded DB, I would expect CPU to get slightly more load; out of curiosity, how much MEM & CPU do you have on DO for the main Lobsters instance?

It's a s-4vcpu-8gb. They don't list the CPU, but /proc/cpuinfo lists 4 identical:

vendor_id	: GenuineIntel
cpu family	: 6
model		: 85
model name	: DO-Premium-Intel
stepping	: 7
microcode	: 0x1
cpu MHz		: 2494.136
cache size	: 4096 KB

After I see how Monday goes I may nudge up the box, but so far, so boring.

We're having a quiet Monday.

The spike of disk access yesterday was me moving rack::attack to a its own db and then running delete from solid_cache_entries where cast(key as text) like 'production:rack::attack%'; and vacuum;.