FreshPorts has seven databases. Each website has its own: dvl, dev, test, stage, foo, bar, and production on RDS. They are all built from the database-schema repo. They should all be the same.
Today I decided to find out if they are. They mostly are. The repo is not.
I’m documenting this so I have a record of what was different, and why.
Getting the schemas
On each database host or jail, I ran:
pg_dump --schema-only --no-owner --no-privileges -U postgres -f freshports.bar.schema.sql freshports.org
That’s structure only. No data, no owners, no grants. About 300 KB of SQL per database. It only reads the system catalogs, so it’s fine to run on production.
A plain diff of two dumps gets noisy. Instead, I parsed them with pglast, which uses the PostgreSQL parser, and compared them object by object: tables, columns, defaults, constraints, foreign keys, indexes, functions, triggers, types, views, extensions, and schemas. Then I ran a plain diff against production anyway, to catch anything the parser comparison missed. It did catch a few things.
The repo
This was the worst of it.
createdb.sql creates the tables for a new database. The header still had a CVS $Id from 2012, and instructions for generating the file from a data modelling tool I haven’t used in years. Since then, new tables have gone into updates-*.ddl files and never made it back into createdb.sql.
For some history, docs.freshports.org has two old diagrams. physical_database.gif is the FreshPorts 2 physical database model, dated 10 November 2001. It has users.password, users.cookie, and watch_notice_log.watch_notice_count. All three were still in createdb.sql until today. freshports.org.svg is newer, generated by pgModeler. It has no packages tables, which came along in April 2020. Neither is current.
Compared to the database, createdb.sql had:
- about 30 tables missing, including
abi,repo, thepackagestables,flavors,generate_plist,user_cookie, andvuxml_import - 13 tables which no longer exist, such as
archs,events,top_questions, andmaster_slave - about 35 columns missing, including all the git columns on
commit_log - 18 columns which are
timestampin the file buttimestamptzin the database - about 36 indexes missing, and 10 foreign keys which are in the file but in no database
I wasn’t going to fix that by hand. createdb.sql is now generated from the production dump. Functions stay in sp.txt, triggers in ri.txt, and composite types in datatype.txt.
Those files were in better shape. However, 16 functions, one trigger, and one type were in every database but in none of those files. Four of those functions handle website logins. Two are used by the VuXML import. They’re in now.
Five functions were the other way around: the repo had changes which no database ever got. Most of that was kernel module package support from January 2025. None of the seven databases has kmod in the package_sets enum. I never applied that update anywhere. Those functions in the repo now match the databases again. The kmod update file is still there.
The databases
Good news here. All seven have the same extensions, enums, views, and triggers, and the same 228 functions, give or take a few strays.
I made production the reference. It gets the real traffic. Where a database differs from production, production wins.
Here is how each one differs from production:
| Database | Missing | Extra or different |
|---|---|---|
| dvl | ports_short_description_trgm_idx; autovacuum analyze settings on commit_log, commit_log_branches, commit_log_ports |
old categoryportcount(text); table delete_me_daily_refreshes; max_number_watch_lists default 5 |
| dev | port_dependencies_upon_type_idx |
old categoryportcount(text); table delete_me_daily_refreshes; default 5 |
| test | port_dependencies_upon_type_idx |
schema utf8 with three functions; NULLS NOT DISTINCT on one constraint; default 5 |
| stage | element_name_trgm_idx; the two pkgmessage expression indexes; table wlc |
two generated pkgmessage_textsearchable columns and their indexes; plpgsql_call_handler(); NULLS NOT DISTINCT; a comment; default 5 |
| foo | the two pkgmessage expression indexes; ports_uses_simple_idx |
two generated pkgmessage_textsearchable columns and their indexes; the same foreign key three times; NULLS NOT DISTINCT; default 5 |
| bar | the two pkgmessage expression indexes; ports_uses_simple_idx |
one generated pkgmessage_textsearchable column and its index; is_utf8(text); NULLS NOT DISTINCT; default 5 |
Differences from production, 20 October 2026. “Default 5” means users.max_number_watch_lists defaults to 5. On production it’s 10.
Some of those need explaining.
pkg-message search, done two ways
Full-text search on pkg-message started with a generated column: ports.pkgmessage_textsearchable holds to_tsvector('english', pkgmessage), with a GIN index on it. Later I replaced that with expression indexes directly on to_tsvector(..., pkgmessage), one English, one simple. No extra column needed.
stage, foo, and bar never got the switch. Nothing in the website or ingress code uses the old columns. They get dropped, after the new indexes are built.
One foreign key, three times
On foo, ports.last_commit_id has the same foreign key to commit_log three times: ports_last_commit_id_fkey, ports_last_commit_id_fkey8, and ports_last_commit_id_fkey9.
I’m pretty sure that’s ri.txt. It added that foreign key without a name. Each time I ran the file, PostgreSQL made up a new name and added another copy. Every one of them gets checked on every insert and update to ports.
ri.txt now names the constraint and drops it first. Running it again is harmless.
Strays
test has a utf8 schema with is_utf8(), is_double_utf8(), and badutf8(). They’re regex checks for badly encoded text. bar has a copy of is_utf8() in public. I suspect these are left over from some encoding cleanup long ago. I don’t remember.
stage has plpgsql_call_handler() in public. That dates from before PL/pgSQL was built into PostgreSQL. That’s old.
dvl and dev have a table named delete_me_daily_refreshes. I think the name says it all.
Nothing uses any of these.
Then there’s one which makes no difference. On four databases, the unique constraint on report_subscriptions_abi is UNIQUE NULLS NOT DISTINCT. On production, it’s plain UNIQUE. All four columns are NOT NULL, so they behave the same. I’m changing it anyway, so the definitions match.
The fix
Each non-production database gets its own update file:
updates-2026-10-20-reconcile-dvl.ddlupdates-2026-10-20-reconcile-dev.ddlupdates-2026-10-20-reconcile-test.ddlupdates-2026-10-20-reconcile-stage.ddlupdates-2026-10-20-reconcile-foo.ddlupdates-2026-10-20-reconcile-bar.ddl
Production needs nothing. By definition.
Those files, the regenerated createdb.sql, and the other schema file changes are all in commit e69e8ae.
Each one builds the missing indexes first, with CREATE INDEX CONCURRENTLY, so the website keeps running. Then it drops things, in one transaction. The top of each file says what it changes and why, and how to undo it.
To check them, I applied each file to a model built from that database’s dump. Every one came out identical to production: tables, columns, defaults, constraints, indexes, functions, and schemas.
None of these have been run yet. That will come, possibly with another blog post.
Things I learned
- Update files are good for changing a database. They’re bad at describing one. If I don’t update the main schema files at the same time, they drift.
- Name your constraints. An unnamed
ADD FOREIGN KEYin a file you run more than once leaves a copy behind every time. - Compare the databases with each other, not just with the repo. It’s cheap. The differences were few, but I didn’t know about any of them.
