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. | |
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! |