Moving from SQLite to PostgreSQL, and what to back up

Help: Server
1

We’ve been running on the default SQLite database and want to move to our PostgreSQL cluster so it’s covered by our normal DB backups. Is there a migration tool? And separately, what exactly should we be backing up today?

3

Yes. PostgreSQL 13+, MySQL 8.0+ (MariaDB 10.6+) and SQL Server 2019+ are supported, and there’s a built-in copy tool. It’s one way only: SQLite to a network engine.

The short version:

  1. Stop the service.
  2. Create an empty database and an owning user. Don’t start ZoneRails against it first, because a boot seeds roles and the admin, and the copy then refuses with TARGET_NOT_EMPTY.
  3. Replace the database block in config.json, leaving encryption.key exactly as it is. config.example.postgres.json ships beside the server.
  4. Run the copy with the server’s own Node.js, as the service account. On Linux this ends with server.js --copy-database --from /var/lib/zonerails/zonerails.db; the docs have the full command per platform.
  5. Start the service and confirm curl -s http://localhost:3000/api/health shows "client": "postgres".

Keep the SQLite file until the new database has been backed up at least once.

Backups: back up the data directory and the database together. config.json carries the JWT secret and the encryption key; lose it and every stored credential is unreadable. Protect that backup like the server itself.

Docs: Documentation — ZoneRails