Copy page

Documentation

Schema migrations

Baler's Postgres schema is a numbered list of migrations in src/baler/migrations/, and every database records which ones it has (Baler#194). A building's record has to last 15+ years on one host, so the schema must be able to change in place, not only grow new tables.

Until this, every service ran its own CREATE TABLE IF NOT EXISTS ... each time it connected. That added tables well, but it couldn't change a column safely, recorded nothing, and ran DDL checks on every new connection of every service.

How it runs

• Once per deploy, before any service. The migrate compose service runs python -m baler.migrate, and every daemon waits for it to finish. It applies whatever migrations are pending, then does the Timberdoodle graph rewrite it always did. On a current database it logs schema: at migration N, nothing to do. On nash-srv, a deploy that adds a migration always takes the full path, which runs migrate, never the restart-only fast path (deploy/nash-srv/setup-runner.sh; it takes effect once the wrapper is reinstalled by rerunning that script).
• Never per connection. Each process checks the recorded version once per database, on its first connection (baler.schema.ensure, called by timeseries.connect/connect_pool and every module's ensure_schema). After that a new connection costs no catalog reads and takes no locks. If the database is behind, which happens to a process started without the migrate service (bare metal, tests), that process upgrades it. That suits light migrations. Before starting services on a heavy one (one that rewrites chunks), run python -m baler.schema upgrade or docker compose run --rm migrate yourself, so it isn't run inside a service's startup, whose connection pool gives up after 30 s.
• One at a time, each exactly once. Each migration runs in its own transaction, under the advisory lock the old connect-time DDL took (ddl.LOCK_KEY), so new and old code take turns during a deploy. After taking the lock it re-reads the recorded versions, so ten processes starting at once apply each migration once. Its row in baler_schema_migrations commits with it: a failed migration leaves nothing applied and nothing recorded, and the next run tries it again from the start. The migration transaction has no statement timeout.

The migrate service connects with the same BALER_PG_DSN as every daemon, built from BALER_PG_USER/BALER_PG_DB, so an install still on the old timberdoodle role and database (see deploy/MIGRATING-TO-BALER.md) is migrated in place.

Commands

Code
python -m baler.schema status        # each migration: when and how it was applied, or PENDING
python -m baler.schema plan          # dry run: what upgrade would run now
python -m baler.schema plan --sql    # ... with every statement
python -m baler.schema upgrade       # apply what's pending (what the migrate service runs)
python -m baler.schema stamp 1       # record 1..N as applied without running them

They use BALER_PG_DSN, or --dsn. Inside the stack: docker compose run --rm migrate python -m baler.schema status.

plan only reads. It plans each pending migration against the database as it is now; upgrade plans each one again just before running it, after the ones before it have run.

stamp is for a database whose schema someone brought to a version by other means. You won't need it for an install made by Baler's own code: the baseline adopts those by itself.

What's recorded

Code
SELECT * FROM baler_schema_migrations;
-- version | name     | how     | statements | duration_ms | applied_at
--       1 | baseline | adopted |         18 |         ... | 2026-11-08 ...

how is applied, adopted (the baseline on a database that already had Baler's tables) or stamped. statements is how many statements ran on this database.

The baseline, and the live install

Migration 0001 (v0001_baseline.py) is a frozen copy of the DDL the last release before migrations ran at connect time (main at 6946378): about 40 tables, point_history as a hypertable with its index, continuous aggregates, their refresh policies, compression and the compression policy.

On a fresh database it builds all of that. On an existing one, nash-srv's included, it adopts what's there. Nothing destructive runs, and nothing is rebuilt:

• point_history's pieces are each checked in the catalog first, and only what's missing runs. An install made by the previous release has all of them, so nothing touches point_history, its chunks or their compression. This matters because even a no-op CREATE INDEX IF NOT EXISTS takes a SHARE lock on every chunk (Baler#107).
• The other tables' statements are the same idempotent IF NOT EXISTS forms every service already ran on every start. On an install that has the tables they change nothing.

The upgrade test below checks exactly this. It builds a database with the previous release's own code, compressed chunks included, adopts it, and requires the result to be identical to a fresh database and every row and compressed chunk to be unchanged.

point_history_empty, the Cx report's helper index, isn't part of any migration. commissioning_api builds it in the background, one chunk per transaction (WITH (timescaledb.transaction_per_chunk), Baler#237), which can't run inside a migration's transaction, and reads work without it.

Writing a migration

1. Add src/baler/migrations/vNNNN_<what>.py with the next number, and list it in migrations/__init__.py's ALL. tests/test_schema.py checks the numbers run 1..N with no gap.

   Code
   from baler.schema import Step
   
   VERSION = 2
   NAME = "faults.resolved_by"
   
   
   def plan(conn) -> list[Step]:
       return [Step("ALTER TABLE faults ADD COLUMN IF NOT EXISTS resolved_by TEXT", "faults.resolved_by")]

2. plan(conn) returns the statements; it must not change anything itself. It may look in the catalog to skip what's already there.

3. Everything runs in one transaction, so no CREATE INDEX CONCURRENTLY, no transaction_per_chunk, and no VACUUM. Things that can't run in a transaction belong in a background task, as point_history_empty does.

4. Never edit a migration once it's on main. It has to mean the same thing on every database forever. tests/test_schema.py pins each released file's sha256; add a new migration instead. A comment-only fix is the one reason to update a hash there.

5. Add a line to the module whose table it is, if a reader there would look for it.

6. Migrations only go forward. To undo a change, write the next migration. To roll a deploy back, older code runs on a newer schema: ensure logs a warning and carries on. Make migrations additive where you can, so that holds. To undo a migration that destroyed data, restore a backup (see Backups).

Hypertables and compressed chunks

What TimescaleDB 2.31 (the version our pinned image runs) does with an ALTER on a hypertable whose chunks are compressed. These were measured on that image, not taken from the docs:

Change	On compressed chunks
ADD COLUMN, nullable or with a default	works
RENAME COLUMN, including a segmentby column	works
DROP COLUMN of a non-segmentby, non-orderby column	works
ALTER COLUMN SET/DROP DEFAULT, SET/DROP NOT NULL	works
CREATE INDEX, ADD CONSTRAINT ... CHECK	works
ALTER COLUMN ... TYPE	refused: operation not supported on hypertables with compressed chunks
DROP COLUMN of a segmentby or orderby column	refused
SET (timescaledb.compress = false)	refused while any chunk is compressed
new compress_segmentby / compress_orderby	accepted, but only chunks compressed later use it; existing ones need compress_chunk(c, recompress => true)

For the refused ones, baler.migrations.hypertable.with_decompressed wraps the statements. It decompresses the compressed chunks, runs the statements, then compresses the same chunks again, all in the migration's one transaction:

Code
from baler.migrations import hypertable

def plan(conn):
    return hypertable.with_decompressed(conn, "point_history",
                                        ["ALTER TABLE point_history ALTER COLUMN quality TYPE VARCHAR(32)"])

• If anything fails, the table is exactly as it was.
• Every row comes back. Nothing is ever downsampled.
• plan lists the chunks and their total uncompressed size. That much disk must be free while the migration runs.
• Measured on a 4-core dev machine: 3.0 M rows in 3 compressed chunks (411 MB uncompressed) took 18.6 s to decompress, alter and recompress. Time grows with the number of compressed rows. We haven't measured it on nash-srv's hardware, so run plan there and time a copy first (the restore drill gives you one).
• A column that a continuous aggregate uses (value, value_bool, ts, point_uri) can't change type at all while the aggregate exists. That migration has to drop and re-create the aggregate.

The upgrade-from-every-release test

tests/test_schema_upgrade.py (integration, run by CI's integration job) gives each case its own scratch database on the stack's Postgres:

• Fresh: every migration applies, plan matched what ran, and a second upgrade changes nothing.
• From every release: for the last release before migrations (PRE_MIGRATIONS_REF) and every v* tag, it takes that release's source with git archive and builds a database with it in a subprocess. It then writes history, compresses the older chunks, and writes late into a compressed one. Then this code upgrades it. The test requires:
  • the schema equals a fresh database's (schema.snapshot: tables, columns, constraints, indexes, hypertables, compression settings, aggregates and policies);
  • every table's rows, every point_history value and the set of compressed chunks are unchanged;
  • a second run changes nothing;
  • this code can still write into a compressed chunk afterwards.
• Concurrent starters: six connections upgrading at once apply each migration once.
• Compressed chunks: a type change on point_history, refused directly, goes through with_decompressed with every row kept.

CI checks out with fetch-depth: 0 so the releases are there. There are no v* tags yet. Each one you push is tested from then on, with no change to the test. A release whose services build their schema differently would need its own branch in the test's _BUILD script.

Upgrading Postgres or TimescaleDB

Both procedures below were rehearsed on 2026-11-08 against a database built by this code: 2.0 M point_history rows (100 points at 1/min for 14 days), one of three chunks compressed, with the aggregates and policies. Take a backup first either way (see Backups).

TimescaleDB minor version (same Postgres major)

The timescale/timescaledb image ships many 2.x versions side by side, and an existing database stays on the version it was created with until you update it.

1. Change the image in docker-compose.yml (keep the same -pg16 suffix) and recreate only postgres: docker compose up -d --wait postgres.

2. Update the extension. It must be the first statement of a fresh session, hence psql -X:

   Code
   docker compose exec postgres psql -X -U baler -c "ALTER EXTENSION timescaledb UPDATE"
   docker compose exec postgres psql -X -U baler -c "SELECT extversion FROM pg_extension WHERE extname = 'timescaledb'"

   Use your BALER_PG_USER (and -d with your BALER_PG_DB) if they differ.

3. docker compose up -d. The migrate service finds nothing to do.

Rehearsal: from 2.30.2 to 2.31.0 the UPDATE took 0.3 s. The row count, an md5 over every row, the compressed chunks and the three policies were all unchanged, and python -m baler.schema upgrade found nothing to do.

Postgres major version (e.g. 16 to 17)

A new Postgres major can't open the old data directory, so this is a dump and restore into a new volume, with the tools the restore drill already proves. The old volume stays as it was until you delete it, so going back means pointing at it again.

1. Back up the running stack: python scripts/backup.py.

2. Stop the stack: docker compose down, without -v.

3. Pick the new image with the same TimescaleDB version as the old one, e.g. timescale/timescaledb:2.31.0-pg17. restore.py needs the dump's TimescaleDB version to be in the image (see Backups). Put it, and a new name for the Postgres volume, in a compose override, so only Postgres starts empty and every other volume is kept:

   Code
   # pg17.compose.yml, used with -f docker-compose.yml -f pg17.compose.yml (or COMPOSE_FILE)
   services:
     postgres:
       image: timescale/timescaledb:2.31.0-pg17
   volumes:
     postgres-data:
       name: ${BALER_VOLUME_PREFIX:-baler}_postgres-pg17

   On nash-srv, add it after deploy/nash-srv/docker-compose.override.yml. Once it's done, move the new image into docker-compose.yml and the volume name into nash-srv's override, so later deploys keep both.

4. Start only postgres and oxigraph: docker compose up -d --wait postgres oxigraph. Wait a few seconds more before restoring: the image's healthcheck can pass while its first-start init is still restarting the server, and a restore started in that window fails to connect. Rerunning it is safe.

5. Restore: python scripts/restore.py --postgres-file backups/postgres-<ts>.sql.gz --oxigraph-file backups/oxigraph-<ts>.ttl.gz

6. Check it: python -m baler.schema status should show the same migrations as before, and plan nothing to do.

7. docker compose up -d.

Rehearsal, 16.15 to 17.11 with TimescaleDB 2.31.0 on both:

Step	Result
Backup	12 s, 20 MB gzip'd
Restore	11 s
Rows, md5 over every row	identical
Compressed chunks	identical (1 of 3)
Policies (compression, two refreshes)	present
baler_schema_migrations	carried over, upgrade found nothing to do
schema.snapshot, old vs new	identical
Hypertable size	248 MB before, 189 MB after: the restore rewrote the uncompressed chunks without dead space

Before a real upgrade, run the same steps on a copy. The rehearsal above did: the source and the target were scratch compose projects (COMPOSE_PROJECT_NAME=pgup16 and pgup17, each with its own BALER_VOLUME_PREFIX, and scripts/restore-drill.compose.yml on top for loopback ports), the target with the override above, and the backup came from the source with scripts/backup.py. Both were removed with docker compose down -v afterwards.