Migrate Matrix server database to postsql

While getting a Matrix chat server up and running is a relatively easy and well documented task, managing and maintaining the server is somewhat nebulous as far as existing documentation is concerned. For example, when my wife asked me to reset her password on our server, I quickly realized that I had no idea how to do such a thing. After some digging around on the net, it became apparent that other people were having similar problems.

Regardless of which method used to install a Matrix server, a homeserver.db file is always present after installation. This file represents all data associated with the server such as rooms, users, their passwords, and their attributes. By default, Matrix uses sqlite3 to access this .db file. However, Matrix’s own documentation recommends migrating the database to use postgres instead.

This guide will walk you through the process of installing postgres, getting it running, migrating the existing Matrix database, and how to change a user’s password. As always, this guide assumes the reader is running a Debian based linux operating system with access to root privileges via sudo. Additionally, this guide assumes that you already have a Matrix instance up and running on your machine. If you do not, you can get on easily using Matrix’s installation guide.

First things first, we need to install postgres.

sudo apt install postgresql postgresql-contrib

During the installation process, a postgres user will be created. Switch to the new postgres user while still maintaining sudo privileges using the following command.

sudo -u postgres bash

Once switched, create a new postgresql user.

createuser –pwprompt synapse_user

Enter the postgres shell and create the new database.

postgres=# createdb –encoding=UTF8 –locale=C –template=template0 --owner=synapse_user synapse;

Next, allow the new postgres user synapse_user connect to the synapse databases. While still in postgres shell, type the following:

postgres=# show hba_file;

Something like this should follow: /etc/postgresql/11/main/pg_hba.conf

Leave postgres shell using \q and open pg_hba.conf in a text editor. Add the following line:

host        synapse synapse_user        ::1/128         md5
host        synapse synapse_user    md5

Restart the postgresql server for the changes to take effect.

sudo systemctl restart postgresql

Now we need to configure the homeserver.yaml file to use postgresql instead of sqlite3. But before making changes it’s important to keep backups of the original .yaml and .db files just in case anything goes wrong. Additionally important, it’s important to make sure that the .db and .yaml file belongs to the proper user. In this case the user should be matrix-synapse.

sudo chown matrix-synapse:nogroup /etc/matrix-synapse/homeserver.yaml
sudo chown matrix-synapse:nogroup /var/lib/matrix-synapse/homeserver.db

sudo cp /etc/matrix-synapse/homeserver.yaml /etc/matrix-synapse/homeserver.yaml.backup
sudo cp /var/lib/matrix-synapse/homeserver.db /var/lib/matrix-synapse/homeserver.db.backup

Open homeserver.yaml in a text editor and change the following text.

#  name: sqlite3
#  args:
#    database: /path/to/homeserver.db

    name: psycopg2
    txn_limit: 10000
        user: synapse_user
        password: $your_password
        database: synapse
        host: localhost
        port: 5432
        cp_min: 5
        cp_max: 10

Once saved, stop the Matrix server.

sudo systemctl stop matrix-synapse

Next, port the existing database using the built in command synapse_port_db. For whatever reason I had an issue using relative file paths with this command, so I’ve included the full file path instead.

synapse_port_db --curses --sqlite-database /var/lib/matrix-synapse/homeserver.db \
--postgres-config /etc/matrix-synapse/homeserver.yaml

Restart your Matrix server and verify that it is running correctly.

sudo systemctl start matrix-synapse

Change a Matrix user password using postgresql

Now that your Matrix server is using a postgres database, we need to be able to access and change data items as we please. For example, let’s say one of your users wants their password reset.

Open up the synapse database

psql synapse

If you’d like to see the usernames and password hashes of all users in your database, run the following command

synapse=# select name, password_hash from users;

name              |                        password_hash
@user1:domain.com | $2b$12$lk.TLplwaOThuenkqzj9k.6RxqtK9eaReS2faBVFofWwEdc4P.wbS
@user2:domain.com | $2b$12$xEAMCZZ9r8ShtEgkiCitUOf603W2IUIKh7oj8Ni3A6InOUY1h8iHC
(2 rows)

You may have noticed that the passwords are stored as a hashfile. This means that the new password you create for your user needs to be hashed before updating the database. Hashes are created using the command hash_password using a technique called bcrypt. By default, the number bcrypt_rounds in the homeserver.yaml file is 12, but yours might be configured different. In any case, it’s a good idea to reference the server’s homeserver.yaml file using the -c flag.

hash_password -p $new_password -c /etc/matrix-synapse/homeserver.yaml

The result should be a hash of the specified password.


Copy the hash and go back into the postgres shell

psql synapse

Update the desired user’s password

synapse=# update users
set password_hash = ‘$2b$12$y/jlqx9Cc5TPTr3O9itSMuyYnFb2L6XsGseIYD5W.yP0ypJYXtLhy’
where name = ‘@user1:domain.com’;

If done so correctly, you should see


The user should now be able to log in with their new password. Once logged in, they should be able to change it through the client.