Converting a Matrix server to use Postgres.

29 January 2020

database howto libreops matrix postgres synapse

In my last post about the Matrix network I covered how to set up a public Synapse server as well as a web-based client called Riot.  In so doing I left out a part of the process for the sake of clarity (because it's a hefty procedure and there's no reason not to break it down into logical modules), which was using a database back-end that's designed for workloads above and beyond what SQLite was meant for.  I'll be the first to tell you, I'm not a database professional, I don't know a whole lot about how to use or admin them aside from installing stuff that uses databases, so there's probably a lot of stuff I'm leaving out.  I'd love to talk to some folks who are more knowledgable than I am.

That said, the only other database server that Synapse supports is Postgres, which seems to have a user interface very different from that of what I use most often (MySQL) so the same procedures don't apply.  I'm going to do my best to break things down.  To make it more clear, I will not be referring back or comparing to MySQL because that will needlessly muddy already unclear waters.

Postgres, like most other database servers has the idea of user accounts.  Rather than manage accounts internally it relies upon the system it's running on top of and merely makes allowances for access.  To put it another way, if I have a user "matrix" on my server, I can tell Postgres "Hey, there's a user account on this box that you should give access to called 'matrix'."  And Postgres will check the system's user database for the account and then an internal table and figure out what to do with login attempts.

For some reason, Postgres calls any kind of access to itself a role.  There's probably a good reason for it but I don't know what it is.

However, we must first install Postgres on the server.  Here are the instructions I followed, and here's what the process looked like:

drwho@jackpoint:~(2) $ sudo -s
root@jackpoint:~() # apt-get update
# The repository databases update here...

root@jackpoint:~() # apt-get install -y postgresql postgresql-contrib libpq-dev
# Packages install here...

# Make sure Postgres starts on reboot, then fire it up.
root@jackpoint:~() # systemctl enable postgresql.service
root@jackpoint:~() # systemctl start postgresql.service

# Configure username-and-password authentication for Postgres.  This is
# a requirement for Synapse.
root@jackpoint:~() # cd /etc/postgresql/10/main

# It never hurts to make a backup.
root@jackpoint:~() # cp pg_hba.conf pg_hba.conf.orig

# Of course, use your text editor of choice.
root@jackpoint:~() # vi pg_hba.conf

The format of this file is a little weird so I'll do my best to explain it.  This file tells Postgres how to authenticate different kinds of users (running on the same machine, connecting to an IP address or hostname explicitly, and how to handle the SSL and no SSL cases).  Here things get a little fuzzy for me so the best I can do is show only what I did (again, sans lots and lots of comments) in the file:

# host - connections to a network socket
# matrix - name of the database
# matrix - username on the system trying to connect
# 127.0.0.1 - the IP address the user is connecting from (I think)
# password - the user will authenticate with a password
host    matrix          matrix  127.0.0.1       password

I left everything else in the file alone.

After this I restarted Postgres (just in case it needs that to re-read the pg_hba.conf file) and switched over to the "postgres" user on my system:

root@jackpoint:~() # systemctl restart postgresql.service
root@jackpoint:~() # su - postgres

# Here's where I actually create the Postgres account/role.
postgres@jackpoint:~$ createuser --interactive --pwprompt
Enter name of role to add: matrix
Enter password for new role: <here I typed in a big-ass password>
Enter it again: <I typed it in again>
Shall the new role be a superuser? N
Shall the new role be allowed to create databases? N
Shall the new role be allowed to create more new roles? N

postgres@jackpoint:~$

Fantastic.  The process hasn't been that bad so far.  Now it's time to go into a Postgres shell and create the Matrix database to spec according to Synapse's instructions:

postgres@jackpoint:~$ psql
psql (10.10 (Ubuntu 10.10-0ubuntu0.18.04.1))
Type "help" for help.

postgres=# CREATE DATABASE matrix
postgres-# ENCODING 'UTF8'
postgres-# LC_COLLATE='C'
postgres-# LC_CTYPE='C'
postgres-# template=template0
postgres-# OWNER matrix;
CREATE DATABASE
postgres=# \q
postgres@jackpoint:~$ logout
root@jackpoint:~() #

Note that this command appears to be case-sensitive.  I'd originally typed "LC_COLLATE='c'" in the psql shell and it threw an error, so I went back and corrected my typo and it worked.  Further note that the command it broken up across multiple lines, and every part after the first has a different prompt ("postgres-#" instead of "postgres=#").  I don't know why this is and it can be somewhat confusing if you're not used to it.  Also, to exit the psql shell you have to type \q, though I found that typing control-D works just as well.  The database now exists and is ready to be populated.

Here's where we configure Synapse to use the Postgres database instead of SQLite.  The process involves stopping Synapse, installing another component of Synapse, editing the config file, and importing the contents of the SQLite database into Postgres.  First, the commands:

root@jackpoint:~() # systemctl stop matrix-synapse
root@jackpoint:~() # su - matrix
matrix@jackpoint:~$ . synapse/bin/activate
# This is, incidentally, why we installed libpq-dev earlier.  This won't work
# unless that package is on the system.
(synapse) matrix@jackpoint:~$ pip install matrix-synapse[postgres]

# Make sure that the matrix user can access the database.
(synapse) matrix@jackpoint:~$ psql
psql (10.10 (Ubuntu 10.10-0ubuntu0.18.04.1))
Type "help" for help.

matrix=> \q
# It worked.

(synapse) matrix@jackpoint:~$

Now we reconfigure Synapse so that it knows how to log into Postgres.  We have to do this before we can import the SQLite database because the utility that does it (synapse_port_db) uses the same config file as Synapse so it knows how to log into the database.  Edit the file homeserver.yaml with your favorite text editor and make two sets of changes: Comment out the SQLite stuff, and add the Postgres stuff.

...
# Commented out.
#database:
#  # The database engine name
#  name: "sqlite3"
#  # Arguments to pass to the engine
#  args:
#    # Path to the database
#    database: "/home/matrix/homeserver.db"

# Added right below it.
database:
    name: psycopg2
    args:
        user: matrix
        password: <the junk I used as the Postgres role's password>
        database: matrix
        host: 127.0.0.1
        cp_min: 5
        cp_max: 10
...

Now the fun part: Loading the old database into the new one.

# Arguments:
# --sqlite-database: Path to the homeserver.db file.
# --postgres-config: Path to the Synapse server's configuration file.
# --curses: Show a progress bar.
(synapse) matrix@jackpoint:~$ synapse_port_db --sqlite-database homeserver.db
    --postgres-config homeserver.yaml --curses
(synapse) matrix@jackpoint:~$ logout
root@jackpoint:~() # systemctl start matrix-synapse.service
root@jackpoint:~() #

If all has gone according to plan you have imported the old database into the new one, started the Matrix server back up and it is now connected to the Postgres server instead.  If you look at the network connections to port 5432/tcp (Postgres' default network port) you should see a bunch of activity:

root@jackpoint:~() # netstat -pan | grep postgres
tcp        0      0 127.0.0.1:5432          0.0.0.0:*               LISTEN      1244/postgres
tcp        0      0 127.0.0.1:5432          127.0.0.1:38406         ESTABLISHED 14533/postgres: 10/
tcp        0      0 127.0.0.1:5432          127.0.0.1:38312         ESTABLISHED 14521/postgres: 10/
tcp        0      0 127.0.0.1:5432          127.0.0.1:38462         ESTABLISHED 14538/postgres: 10/
tcp        0      0 127.0.0.1:5432          127.0.0.1:38320         ESTABLISHED 14524/postgres: 10/
tcp        0      0 127.0.0.1:5432          127.0.0.1:38310         ESTABLISHED 14517/postgres: 10/
tcp        0      0 127.0.0.1:5432          127.0.0.1:38316         ESTABLISHED 14522/postgres: 10/
tcp        0      0 127.0.0.1:5432          127.0.0.1:38318         ESTABLISHED 14523/postgres: 10/
tcp        0      0 127.0.0.1:5432          127.0.0.1:38404         ESTABLISHED 14531/postgres: 10/
tcp        0      0 127.0.0.1:5432          127.0.0.1:38464         ESTABLISHED 14539/postgres: 10/
tcp        0      0 127.0.0.1:5432          127.0.0.1:38408         ESTABLISHED 14534/postgres: 10/
udp        0      0 127.0.0.1:39773         127.0.0.1:39773         ESTABLISHED 1244/postgres
unix  2      [ ACC ]     STREAM     LISTENING     9822205  1244/postgres        /var/run/postgresql/.s.PGSQL.5432
root@jackpoint:~() #

Please note that all of the connections are to and from the network loopback interface (127.0.0.1).  From both a practical and security perspective this means that network connections are only able to take place on your server and not from anywhere else on the network.  Now, if you log into your Matrix server with Riot or a desktop application, you should be logged in and able to talk to the rest of the network.

In the next post I'll describe how to set up a STUN/TURN server to make multimedia conferencing over the Matrix network easier and a bit more reliable.

back to top