postgres.md

Using Postgres

Postgres version 9.5 or later is known to work.

Install postgres client libraries

Synapse will require the python postgres client library in order to connect to a postgres database.

• If you are using the matrix.org debian/ubuntu packages, the necessary python library will already be installed, but you will need to ensure the low-level postgres library is installed, which you can do with apt install libpq5.

• For other pre-built packages, please consult the documentation from the relevant package.

• If you installed synapse in a virtualenv, you can install the library with:

  ~/synapse/env/bin/pip install matrix-synapse[postgres]

  (substituting the path to your virtualenv for ~/synapse/env, if you used a different path). You will require the postgres development files. These are in the libpq-dev package on Debian-derived distributions.

Set up database

Assuming your PostgreSQL database user is called postgres, first authenticate as the database user with:

su - postgres
# Or, if your system uses sudo to get administrative rights
sudo -u postgres bash

Then, create a user synapse_user with:

createuser --pwprompt synapse_user

Before you can authenticate with the synapse_user, you must create a database that it can access. To create a database, first connect to the database with your database user:

su - postgres # Or: sudo -u postgres bash
psql

and then run:

CREATE DATABASE synapse
 ENCODING 'UTF8'
 LC_COLLATE='C'
 LC_CTYPE='C'
 template=template0
 OWNER synapse_user;

This would create an appropriate database named synapse owned by the synapse_user user (which must already have been created as above).

Note that the PostgreSQL database must have the correct encoding set (as shown above), otherwise it will not be able to store UTF8 strings.

You may need to enable password authentication so synapse_user can connect to the database. See https://www.postgresql.org/docs/current/auth-pg-hba-conf.html.

If you get an error along the lines of FATAL: Ident authentication failed for user "synapse_user", you may need to use an authentication method other than ident:

• If the synapse_user user has a password, add the password to the database: section of homeserver.yaml. Then add the following to pg_hba.conf:

  host    synapse     synapse_user    ::1/128     md5  # or `scram-sha-256` instead of `md5` if you use that

• If the synapse_user user does not have a password, then a password doesn't have to be added to homeserver.yaml. But the following does need to be added to pg_hba.conf:

  host    synapse     synapse_user    ::1/128     trust

Note that line order matters in pg_hba.conf, so make sure that if you do add a new line, it is inserted before:

host    all         all             ::1/128     ident

Fixing incorrect COLLATE or CTYPE

Synapse will refuse to set up a new database if it has the wrong values of COLLATE and CTYPE set, and will log warnings on existing databases. Using different locales can cause issues if the locale library is updated from underneath the database, or if a different version of the locale is used on any replicas.

The safest way to fix the issue is to take a dump and recreate the database with the correct COLLATE and CTYPE parameters (as shown above). It is also possible to change the parameters on a live database and run a REINDEX on the entire database, however extreme care must be taken to avoid database corruption.

Note that the above may fail with an error about duplicate rows if corruption has already occurred, and such duplicate rows will need to be manually removed.

Fixing inconsistent sequences error

Synapse uses Postgres sequences to generate IDs for various tables. A sequence and associated table can get out of sync if, for example, Synapse has been downgraded and then upgraded again.

To fix the issue shut down Synapse (including any and all workers) and run the SQL command included in the error message. Once done Synapse should start successfully.

Tuning Postgres

The default settings should be fine for most deployments. For larger scale deployments tuning some of the settings is recommended, details of which can be found at https://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server.