Title: Migrating prosody internal storage to SQLite on OpenBSD
       Author: Solène
       Date: 18 August 2023
       Tags: prosody xmpp openbsd
       Description: In this article, you will learn how to improve prosody
       storage on OpenBSD by switching to SQLite
       
       # Introduction
       
       As some may know, I'm an XMPP user, an instant messaging protocol which
       used to be known as Jabber.  My server is running Prosody XMPP server
       on OpenBSD.  Recently, I got more users on my server, and I wanted to
       improve performance a bit by switching from the internal storage to
       SQLite.
       
       Actually, prosody comes with a tool to switch from a storage to
       another, but I found the documentation lacking and on OpenBSD the
       migration tool isn't packaged (yet?).
       
       The switch to SQLite drastically reduced prosody CPU usage on my small
       server, and went pain free.
       
 (HTM) Prosody documentation: Prosody migrator
       
       # Setup
       
       For the migration to be done, you will need a few prerequisites:
       
       * know your current storage, which is "internal" by default
       * know the future storage you want to use
       * know where prosody stores its files
       * the migration tool
       
       On OpenBSD, the migration tool can be retrieved by downloading the
       sources of prosody.  If you have the ports tree available, just run
       `make extract` in `net/prosody` and cd into the newly extracted
       directory.  The directory path can be retrieved using `make
       show=WRKSRC`.
       
       The migration tool can be found in the subdirectory `tools/migration`
       of the sources, the program `gmake` is required to build the program
       (it's only replacing a few variables in it, so no worry about a complex
       setup).
       
       In the migration directory, run `gmake`, you will obtain the migration
       tool `prosody-migrator.install` which is the program you will run for
       the migration to happen.
       
       # Prepare the configuration file
       
       In the migration directory, you will find a file
       `migrator.cfg.lua.install`, this is a configuration file describing
       your current prosody deployment and what you want with the migration,
       it defaults to a conversion from "internal" to "sqlite" which is what
       most users will want in my opinion.
       
       Make sure the variable `data_path` in the file refers to `/var/prosody`
       which is the default directory on OpenBSD, and check the hosts in the
       "input" part which describe the current storage.  By default, the new
       storage will be in `/var/prosody/prosody.sqlite`.
       
       
       # Run the tool
       
       Once you have the migrator and its configuration file, it's super easy
       to proceed:
       
       * Stop the prosody server with `rcctl stop prosody`
       * Modify `/etc/prosody/prosody.cfg.lua` to use the sql driver instead
       of internal
       
       ```
       storage = "sql"
       sql = {
           driver = "SQLite3";
           database = "prosody.sqlite";
       }
       ```
       
       * Backup `/var/prosody` in case something is going bad
       * Run the migration with `lua53 prosody-migrator.install --config
       ./migrator.cfg.lua.install`
       * Verify the file `/var/prosody/prosody.sqlite` exists and isn't empty
       * Chown `/var/prosody/prosody.sqlite` to `_prosody:_prosody`
       * Start the prosody server with `rcctl start prosody`, check everything
       is working fine
       
       If you had an error at the migration step, check the logs carefully to
       check if you missed something, a bad path, maybe.
       
       # Conclusion
       
       Prosody comes with a migration tool to switch from a storage backend to
       another, that's very handy when you didn't think about scaling the
       system correctly at first.
       
       The migrator can also be used to migrate from the server ejabberd to
       prosody.
       
       Thanks prx for your report about some missing steps!