Skip to content
FUTO Notes

Migrate a FUTO Notes server from Postgres to SQLite

Older FUTO Notes servers ran on Postgres. New installs use SQLite, which keeps the database in the same data directory as your encrypted notes, so the server is one container and a backup is one folder. A single script moves a Docker Compose install across, and it never modifies Postgres, so going back stays possible.

Moving is optional. The current server still supports Postgres, and an existing Postgres install keeps working as it is.

Check whether your server is on Postgres

From the install directory, list the running containers:

docker compose ps

If you see a Postgres container beside the server, you are on Postgres. Installs made with the current installer are already on SQLite, and the script exits with “Nothing to do” if you run it on one.

Why move

On SQLite the server runs as one container with no database server beside it, and a backup is one folder. The server repository measured the old TypeScript server on Postgres against the current Go server on SQLite, running the same sync workload on the same machine:

TypeScript + PostgresGo + SQLite
Memory while idle220 MB16 MB
Container images to pull631 MB107 MB
Processes to keep running21
Backuppg_dump plus the blob foldercopy one folder

Those numbers include the language change as well as the database change, so a Go server that stays on Postgres sits somewhere in between. The full write-up is docs/Resource comparison, TypeScript vs Go.md in the server repository.

Migrate with the script

For Docker Compose installs. Run it from the directory that holds your docker-compose.yml and .env:

cd ~/futo-notes
curl -fsSL https://gitlab.futo.org/futo-notes/futo-notes-server/-/raw/main/migrate-to-sqlite.sh | sh

It shows what it is about to do and asks before starting. Expect about a minute of downtime, since the server restarts twice. To run it without prompts, set FUTO_NOTES_YES=1.

What it does, in order:

  1. Inspects the running containers to find your data, database and published port, rather than assuming a layout.
  2. Stops the server and saves a Postgres dump, .env and docker-compose.yml into a before-sqlite-<date> folder next to your compose file.
  3. Upgrades the server to the current image, still on Postgres, and waits for /health. Starting the current server applies any missing database migrations, which the copy needs. This step also brings an old TypeScript server up to the Go server.
  4. Pauses. Open the app and check that your notes are there and an edit syncs. Nothing has been converted yet, and the script prints the command to back out.
  5. Stops the server and copies Postgres into <data directory>/db/notes.db.
  6. Replaces docker-compose.yml with the single-container SQLite version, carries your password, port and settings over into .env, starts the server, and waits for /health again.

Your blob files are never touched. Postgres is only read. Devices stay signed in, because sessions are copied too.

When the script stops early

The migration script stops rather than guessing when the install does not look like one it knows how to convert:

  • The blob directory is not a folder on the host, or the folder is not named blobs. The data directory becomes the folder that contains blobs.
  • The server runs with an AUTH_MODE other than password.
  • The compose file has no Postgres service named postgres, db or database.
  • <data directory>/db/notes.db already exists, for example from an earlier attempt. Move it aside to try again.
  • docker-compose.go-image.yml exists, left over from a run that stopped partway. Delete it and run the script again.

Each message says what it found. At that point nothing has changed beyond, at most, a stopped server and a backup folder, and the message says how to start the server again.

After the script finishes

The data directory now holds db/notes.db and blobs/, and backing it up is covered in backup and restore.

The before-sqlite-<date> folder holds a full Postgres dump and a copy of .env, which contains your sync password, side by side. Keep it until you are sure you will not roll back, but not in the same backup as your data directory, and delete it afterwards.

Migrate by hand

For a binary or systemd install, or if you would rather run the steps yourself. The server has a built-in copy command:

futo-notes-server migrate-to-sqlite -to sqlite:/path/to/notes.db

It reads the source from DATABASE_URL, which must point at Postgres while it runs, and writes the SQLite file named by -to (default sqlite:./data/notes.db).

Before you start:

  1. If you are still on the TypeScript server, upgrade to the Go server first, still on Postgres, and let it start once. The copy expects the schema the Go server applies at startup.
  2. Check the server is healthy and syncing.
  3. Pick the SQLite path. Nothing must exist there yet, and the user the server runs as must be able to write to its folder.

Then, with the layout from the server README:

sudo systemctl stop futo-notes-server
pg_dump -Fc 'postgres://user:password@localhost/notes' > ~/futo-notes-before-sqlite.dump
sudo -u futo-notes env DATABASE_URL='postgres://user:password@localhost/notes' \
  /usr/local/bin/futo-notes-server migrate-to-sqlite -to sqlite:/srv/futo-notes/notes.db

Stop the server first: the copy reads one snapshot, and anything written after it would be left behind. Run the copy as the server’s user, or the new file ends up owned by root and the server cannot open it.

Then point DATABASE_URL at the new file and start the server:

sudo sed -i 's|^DATABASE_URL=.*|DATABASE_URL=sqlite:/srv/futo-notes/notes.db|' /etc/futo-notes-server.env
sudo systemctl start futo-notes-server
curl --fail http://localhost:3005/health

Leave BLOB_DIR exactly as it was. The copy does not move blob files.

On a Compose stack, by hand

Run the copy in a one-off container of your current, Go-image server service, which still has DATABASE_URL pointing at Postgres, with the SQLite folder mounted where the new server will look for it:

docker compose stop server
docker compose run --rm \
  --volume "/absolute/path/to/futo-notes-data/db:/data/db" server \
  futo-notes-server migrate-to-sqlite -to sqlite:/data/db/notes.db

Then switch to docker-compose.production.yml, whose image already defaults to sqlite:/data/db/notes.db, and set FUTO_NOTES_DATA_DIR to the folder that contains both blobs and db.

What the copy checks

The migrate-to-sqlite command reads one consistent Postgres snapshot and copies users, collections, objects, the blob ledger, retry records, sessions, and server settings. Before it reports success it checks that every table’s row count matches, that every collection’s version matches, that the ledger’s total bytes match, and that SQLite’s integrity and foreign-key checks pass. It prints a table of row counts only when all of those pass.

If anything fails, it deletes the partial SQLite file, so the server cannot start on half a copy. It refuses to write over an existing, non-empty SQLite file.

Roll back to Postgres

Postgres is exactly as it was when the copy ran. After using the script, restore the two saved files:

cd ~/futo-notes
docker compose down
cp before-sqlite-<date>/docker-compose.yml before-sqlite-<date>/.env .
docker compose up -d

For a manual migration, stop the server and point DATABASE_URL back at Postgres.

Edits made while on SQLite exist only in the SQLite file, and rolling back discards them. There is no converter from SQLite back to Postgres. Keep the db folder if you might want them. Blob files uploaded during that time stay on disk; the Postgres server has no record of them, so its maintenance adopts them and deletes them a day later.

To migrate again later, move the old notes.db out of the way first.

Clean up

Once you are sure you will stay on SQLite, the Postgres data directory or Docker volume and the POSTGRES_PASSWORD line in .env are no longer used and can be removed. The script prints where the Postgres data lives when it finishes.

Next steps