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

@tmoertel I don't know anything about duckdb, but I'd be happy to experiment on the next office hours stream. If you'd like to set up to call in and talk through fun with duckdb we could do that. Seems like a fun extension of the existing queries policy.

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;.