Switching databases: SQLite ⇄ Postgres
Every OpenSteward service picks its database at boot: Postgres when
DATABASE_URL is set, SQLite otherwise. Moving a deployment from one to the
other used to mean starting over on an empty database. Two tools carry the
data across:
scripts/switch-db.shmoves the local compose stack in one command: it checks and confirms the copy while the stack keeps running, stops it, copies every service database (with--copy), and starts the stack in the other mode.db-copy(node dist/db-copy.jsin each service image) copies one service database between the backends, in either direction. The script runs it for you; you can also run it yourself, for Kubernetes or a non-compose deployment.
The databases
Section titled “The databases”| Service | SQLite file (default path, from the service’s config) | --db |
On Postgres |
|---|---|---|---|
| admin-api | ./data/admin.db (db.path in config.yml) |
admin |
the DATABASE_URL database |
| integration-server | ./data/credentials.db (storePath, STORE_PATH) |
credentials |
the same database |
| integration-server | ./data/resources.db (DATA_STORE_PATH) |
resources |
the same database |
| webhook-gateway | ./data/webhook-gateway.db (db.path) |
webhook |
only if you give it a DATABASE_URL |
Relative paths resolve from the service’s directory in its image
(/app/apps/<service>), as the service resolves them. On Postgres, admin-api’s
and integration-server’s tables share one database (the analytics overlay
points both at opensteward); each copy touches only its own tables, and a
test keeps every profile’s tables and migration namespaces disjoint. The
analytics overlay leaves webhook-gateway on SQLite, so the script leaves its
database alone unless your overlay moves it too.
Switching the compose stack
Section titled “Switching the compose stack”Build the images from a checkout that has db-copy first — the script builds
nothing. On a small Docker VM build one image at a time
(COMPOSE_PARALLEL_LIMIT=1, then docker compose … build admin-api, then
… build integration-server). Then rehearse, then switch:
scripts/switch-db.sh postgres --copy --dry-run # print every command, run nonescripts/switch-db.sh postgres --copy # SQLite volumes → Postgresscripts/switch-db.sh sqlite --copy # Postgres → SQLite volumesscripts/switch-db.sh sqlite # switch back, copy nothingIn order, the script:
- With
--copy, checks everything while the stack keeps running. It startspostgresif it is not running, checks each image hasdist/db-copy.js, and runsdb-copy --dry-runfor every database that moves — schema check and row counts, nothing written. Any refusal ends the script here. A database with nothing to copy (exit 5, e.g. no resources database underDATA_STORE_MODE=passthrough) ends it too, unless--skip-missing; skipped databases are listed. Then it asks you to typeyes(--yesskips the prompt; without a terminal and without--yesit refuses before doing anything). Cancelling leaves the stack exactly as it was — apostgresstarted only for the checks is stopped again. - Stops the whole stack with
docker compose stop— neverdown, never-v; no container or volume is removed. Superset stops too, so nothing holds a connection to a database about to be rewritten. - Copies. Before overwriting Postgres it dumps it with
pg_dump -Fcinside thepostgrescontainer to~/.opensteward/db-backups/(SWITCH_DB_BACKUP_DIR), a directory it creates — or tightens — to0700, the file0600. Then, for each database,db-copy --overwriteruns in a one-off container of that service’s image (docker compose run --rm --no-deps), with its SQLite volume mounted and on the compose network.DATABASE_URLcomes from the service’s Postgres-mode environment and is read bydb-copyitself (env:DATABASE_URL), so it never appears in a command line. Copying into SQLite,db-copysnapshots each existing file next to itself as<file>.pre-copy-<time>.bak(owner-only,0600), inside the volume, and after a successful copy keeps only the newest three per file —--keep-backups Nto keep more or fewer. A copy that fails prunes nothing. - Starts the stack in the target mode:
docker-compose.yml, plusdocker-compose.analytics.example.yml(--overlayfor your own) and theanalyticsprofile in Postgres mode, plusdocker-compose.override.ymlwhen it exists — any-fturns off compose’s automatic loading of it, so the script always passes it explicitly. Profiles whose containers exist (sso-dev,https,scale) are kept;--profiles "sso-dev https"overrides the detection. It prints what it did.
If anything fails after the stop, the script restarts the stack in the mode
it was in — its databases are untouched, since a copy only ever writes to
the target — and says so. The exception is a same-mode refresh (already on
Postgres, running postgres --copy; or on SQLite, running sqlite --copy):
there the previous mode’s database is the target, and a failure partway
through the list leaves it partly overwritten. The script then leaves the
stack stopped, prints the exact restore command for the dump it took a
moment earlier (docker compose … exec -T postgres pg_restore -U opensteward --clean -d opensteward < <dump>) — or, for SQLite, points at the
.pre-copy-<time>.bak snapshot next to each file — and exits non-zero. Without --copy, each backend keeps whatever it
held: the SQLite volumes are never modified by a switch, and Postgres keeps
its postgres-data volume.
The SQLite paths come from the files docker-compose.yml mounts into the
services (apps/admin-api/config/config.yml,
apps/integration-server/config/config.example.yml,
apps/webhook-gateway/config/config.yml) and, inside the container, from
STORE_PATH / DATA_STORE_PATH as the service reads them.
Running db-copy yourself
Section titled “Running db-copy yourself”# In a service container (or `pnpm --filter admin-api db-copy -- …` in a checkout)node dist/db-copy.js --from sqlite:./data/admin.db --to env:DATABASE_URL --dry-runnode dist/db-copy.js --from sqlite:./data/admin.db --to env:DATABASE_URLnode dist/db-copy.js --db credentials --from env:DATABASE_URL \ --to sqlite:./data/credentials.db --overwriteStop the service whose database you copy. Endpoints are sqlite:<path>, a
postgres:// URL, or env:<NAME> (the URL in that variable). integration-
server has two databases, so it needs --db credentials or --db resources;
--list shows them.
| Option | |
|---|---|
--dry-run |
check both sides, print the row count of every table and what would be backed up; write nothing |
--overwrite |
replace a target that holds any row in this database’s tables |
--no-backup |
skip the backup taken before writing to an existing target: the snapshot of a SQLite file, or the pg_dump of a Postgres target that holds rows. Without pg_dump on PATH (the service images have none) such an overwrite needs this flag |
--keep-backups <n> |
snapshots of a SQLite target to keep per file, newest first (default 3); older ones are deleted after a successful copy, never after a failed one |
--backup-dir <dir> |
where the pg_dump goes (default: the SQLite source’s directory, else the current one) |
--ignore-unknown |
copy a SQLite source that also holds tables the profile does not know (they are not copied) |
--batch-size <n> |
rows per batch (default 500) |
| Exit | Meaning |
|---|---|
| 0 | copied (or the dry run found nothing to refuse) |
| 1 | failed and rolled back — including a row-count mismatch; the target’s rows are unchanged |
| 2 | usage error |
| 3 | refused: the target holds rows; add --overwrite |
| 4 | refused: schema versions or tables differ (see below) |
| 5 | nothing to copy: the source file is missing or holds no schema for this database |
| 6 | refused: overwriting a Postgres target with rows needs a backup and pg_dump is not available — back it up, then --no-backup |
How the copy works
Section titled “How the copy works”The schema comes from the service, not from the source. Each service’s
entry point (apps/<service>/src/db-copy.ts) lists the stores its
buildServer() opens on that database — the same XStore.create(handle, …)
calls. db-copy runs them once against a scratch in-memory SQLite database
through a handle that records every migrate(namespace, migrations) call. That
yields, from the build that is running, every migration namespace with its
exact DualMigration list and the tables those migrations create (the copy
set). Nothing about the schema is written down twice, so a new migration needs
no change here. A test per service fails when server.ts hands a database
handle to anything — positionally, in an object literal, or to a constructor —
that the profile neither opens nor lists as a deliberate exclusion.
The target is then brought to exactly those versions through the service’s
own migrator (DbHandle.migrate()), so it gets the same DDL the service
would create at boot — not a translation of the source’s. This does not depend
on the service having booted against the target first. The copy refuses
(exit 4) when:
- the source’s
_schema_migrationsversion differs from this build’s for any namespace — boot the service once against the source with this build (that migrates it forward), or copy with the image that last wrote it; - the target’s schema is newer than this build;
- a SQLite source holds a table or namespace the profile does not know — its
rows would be left behind. If a store was added to the service, add it to
the profile; if the tables are obsolete,
--ignore-unknown.
_schema_migrations itself is never copied: the target writes its own rows
while migrating, and the versions are compared instead.
Nothing is replaced on a guess. Any row in any of the database’s tables
means the target holds data and needs --overwrite — even the rows a freshly
started service creates by itself (the default tenant, built-in roles,
setting defaults); the refusal says when that is all it found. Before the
first write an existing SQLite target file is always snapshotted (owner-only;
the newest three per file are kept, --keep-backups), and a
Postgres target holding rows is dumped (pg_dump --format=custom of this
database’s tables, the connection passed through PG* environment variables,
never on the command line), or the copy refuses with exit 6.
One transaction. The source is read from one snapshot (a SQLite read
transaction, or a Postgres REPEATABLE READ READ ONLY transaction). The
target gets one transaction: clear the profile’s tables (TRUNCATE on
Postgres, without CASCADE), copy them parents-first by foreign key in
batches, fix the sequences, count every table, and commit only if every count
equals the source’s. Anything else rolls back and leaves the target’s rows as
they were.
Sequences. Ids are copied as they are (OVERRIDING SYSTEM VALUE for
GENERATED ALWAYS AS IDENTITY columns such as audit_log.id). Afterwards
every serial or identity sequence on Postgres (setval) and every
AUTOINCREMENT counter on SQLite (sqlite_sequence) is moved past both the
largest copied id and the source’s own high-water mark, so an id handed out
and deleted before the copy is not handed out again — resource_tombstones.id
is a change-feed cursor that relies on that. A Postgres sequence is only ever
raised, never lowered: setval is not undone by a rollback, so lowering it in
a copy that then rolled back would leave it behind the target’s own ids.
Values. SQLite integers are read as 64-bit (a value above 2^53 survives),
Postgres bigint, numeric, JSON and timestamps as their exact text, then
converted per target column: INTEGER 0/1 ⇄ BOOLEAN, BLOB ⇄ BYTEA (logo
and avatar bytes), JSON text ⇄ JSONB, NULL stays NULL. Encrypted columns
(connection credentials, API-key secrets, SSO secrets) are base64 TEXT on
both backends and are copied byte-for-byte, so both sides must use the same
STORE_SECRET — they do when they share one .env.
Nothing secret is printed. Output is table names and row counts; a Postgres URL is shown as host, port and database only; driver errors are reduced to their message, SQLSTATE and constraint or column name, never the row they were about.
What is not copied
Section titled “What is not copied”- SQL-access principals (integration-server’s
analyticsnamespace) exist only on Postgres and describe Postgres roles. They stay in Postgres when you switch back to SQLite and are there again when you return. - Superset’s own metadata lives in its separate
supersetdatabase and is untouched. - webhook-gateway’s captured webhooks, unless your Postgres overlay gives
webhook-gateway a
DATABASE_URL.
Going back
Section titled “Going back”-
scripts/switch-db.sh sqlite(no--copy) returns to the SQLite volumes as they were before the last copy into them. -
A SQLite file that
db-copyoverwrote sits next to its snapshots,<file>.pre-copy-<time>.bak(the newest three by default), in the same volume; copy one back over the file with the stack stopped. -
A Postgres database that the script overwrote was dumped first. Restore it from the repository root, with the same
-ffiles and profiles the script printed (add-f docker-compose.override.ymlwhen you have one):Terminal window docker compose -f docker-compose.yml -f docker-compose.analytics.example.yml \--profile analytics exec -T postgres \pg_restore -U opensteward --clean -d opensteward \< ~/.opensteward/db-backups/opensteward-postgres-<time>.dumpStop the services first (
… stop admin-api integration-server superset) so nothing holds the tables.
See also: Embedded Superset · Postgres analytics · Backup and restore.
opensteward.dev ·Plans and free license ·Terms ·Privacy ·support@opensteward.dev
© 2026 Falco Consulting AB. Built from open-steward/opensteward@a3961d8. This site sets no cookies, runs no analytics and loads nothing from other sites, except platform logos from Brandfetch on the Platforms page.