mirror of
https://github.com/element-hq/synapse
synced 2024-08-20 02:40:22 +00:00
* Fix stale external links * Fix some internal links * Fix URLs without trailing / where needed * Fix more links * Newsfile Signed-off-by: Olivier Wilkinson (reivilibre) <oliverw@matrix.org> * Reapply docs/openid.md fix after conflict Signed-off-by: Olivier Wilkinson (reivilibre) <oliverw@matrix.org>
268 lines
9.1 KiB
Markdown
268 lines
9.1 KiB
Markdown
# Using Postgres
|
|
|
|
The minimum supported version of PostgreSQL is determined by the [Dependency
|
|
Deprecation Policy](deprecation_policy.md).
|
|
|
|
## 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](setup/installation.md#matrixorg-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](setup/installation.md#installing-as-a-python-module-from-pypi), 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:
|
|
|
|
```sh
|
|
su - postgres
|
|
# Or, if your system uses sudo to get administrative rights
|
|
sudo -u postgres bash
|
|
```
|
|
|
|
Then, create a postgres user and a database with:
|
|
|
|
```sh
|
|
# this will prompt for a password for the new user
|
|
createuser --pwprompt synapse_user
|
|
|
|
createdb --encoding=UTF8 --locale=C --template=template0 --owner=synapse_user synapse
|
|
```
|
|
|
|
The above will create a user called `synapse_user`, and a database called
|
|
`synapse`.
|
|
|
|
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>.
|
|
|
|
## Synapse config
|
|
|
|
When you are ready to start using PostgreSQL, edit the `database`
|
|
section in your config file to match the following lines:
|
|
|
|
```yaml
|
|
database:
|
|
name: psycopg2
|
|
args:
|
|
user: <user>
|
|
password: <pass>
|
|
database: <db>
|
|
host: <host>
|
|
cp_min: 5
|
|
cp_max: 10
|
|
```
|
|
|
|
All key, values in `args` are passed to the `psycopg2.connect(..)`
|
|
function, except keys beginning with `cp_`, which are consumed by the
|
|
twisted adbapi connection pool. See the [libpq
|
|
documentation](https://www.postgresql.org/docs/current/libpq-connect.html#LIBPQ-PARAMKEYWORDS)
|
|
for a list of options which can be passed.
|
|
|
|
You should consider tuning the `args.keepalives_*` options if there is any danger of
|
|
the connection between your homeserver and database dropping, otherwise Synapse
|
|
may block for an extended period while it waits for a response from the
|
|
database server. Example values might be:
|
|
|
|
```yaml
|
|
database:
|
|
args:
|
|
# ... as above
|
|
|
|
# seconds of inactivity after which TCP should send a keepalive message to the server
|
|
keepalives_idle: 10
|
|
|
|
# the number of seconds after which a TCP keepalive message that is not
|
|
# acknowledged by the server should be retransmitted
|
|
keepalives_interval: 10
|
|
|
|
# the number of TCP keepalives that can be lost before the client's connection
|
|
# to the server is considered dead
|
|
keepalives_count: 3
|
|
```
|
|
|
|
## 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>.
|
|
|
|
In particular, we've found tuning the following values helpful for
|
|
performance:
|
|
|
|
- `shared_buffers`
|
|
- `effective_cache_size`
|
|
- `work_mem`
|
|
- `maintenance_work_mem`
|
|
- `autovacuum_work_mem`
|
|
|
|
Note that the appropriate values for those fields depend on the amount
|
|
of free memory the database host has available.
|
|
|
|
Additionally, admins of large deployments might want to consider using huge pages
|
|
to help manage memory, especially when using large values of `shared_buffers`. You
|
|
can read more about that [here](https://www.postgresql.org/docs/10/kernel-resources.html#LINUX-HUGE-PAGES).
|
|
|
|
## Porting from SQLite
|
|
|
|
### Overview
|
|
|
|
The script `synapse_port_db` allows porting an existing synapse server
|
|
backed by SQLite to using PostgreSQL. This is done in as a two phase
|
|
process:
|
|
|
|
1. Copy the existing SQLite database to a separate location and run
|
|
the port script against that offline database.
|
|
2. Shut down the server. Rerun the port script to port any data that
|
|
has come in since taking the first snapshot. Restart server against
|
|
the PostgreSQL database.
|
|
|
|
The port script is designed to be run repeatedly against newer snapshots
|
|
of the SQLite database file. This makes it safe to repeat step 1 if
|
|
there was a delay between taking the previous snapshot and being ready
|
|
to do step 2.
|
|
|
|
It is safe to at any time kill the port script and restart it.
|
|
|
|
However, under no circumstances should the SQLite database be `VACUUM`ed between
|
|
multiple runs of the script. Doing so can lead to an inconsistent copy of your database
|
|
into Postgres.
|
|
To avoid accidental error, the script will check that SQLite's `auto_vacuum` mechanism
|
|
is disabled, but the script is not able to protect against a manual `VACUUM` operation
|
|
performed either by the administrator or by any automated task that the administrator
|
|
may have configured.
|
|
|
|
Note that the database may take up significantly more (25% - 100% more)
|
|
space on disk after porting to Postgres.
|
|
|
|
### Using the port script
|
|
|
|
Firstly, shut down the currently running synapse server and copy its
|
|
database file (typically `homeserver.db`) to another location. Once the
|
|
copy is complete, restart synapse. For instance:
|
|
|
|
```sh
|
|
synctl stop
|
|
cp homeserver.db homeserver.db.snapshot
|
|
synctl start
|
|
```
|
|
|
|
Copy the old config file into a new config file:
|
|
|
|
```sh
|
|
cp homeserver.yaml homeserver-postgres.yaml
|
|
```
|
|
|
|
Edit the database section as described in the section *Synapse config*
|
|
above and with the SQLite snapshot located at `homeserver.db.snapshot`
|
|
simply run:
|
|
|
|
```sh
|
|
synapse_port_db --sqlite-database homeserver.db.snapshot \
|
|
--postgres-config homeserver-postgres.yaml
|
|
```
|
|
|
|
The flag `--curses` displays a coloured curses progress UI.
|
|
|
|
If the script took a long time to complete, or time has otherwise passed
|
|
since the original snapshot was taken, repeat the previous steps with a
|
|
newer snapshot.
|
|
|
|
To complete the conversion shut down the synapse server and run the port
|
|
script one last time, e.g. if the SQLite database is at `homeserver.db`
|
|
run:
|
|
|
|
```sh
|
|
synapse_port_db --sqlite-database homeserver.db \
|
|
--postgres-config homeserver-postgres.yaml
|
|
```
|
|
|
|
Once that has completed, change the synapse config to point at the
|
|
PostgreSQL database configuration file `homeserver-postgres.yaml`:
|
|
|
|
```sh
|
|
synctl stop
|
|
mv homeserver.yaml homeserver-old-sqlite.yaml
|
|
mv homeserver-postgres.yaml homeserver.yaml
|
|
synctl start
|
|
```
|
|
|
|
Synapse should now be running against PostgreSQL.
|
|
|
|
|
|
## Troubleshooting
|
|
|
|
### Alternative auth methods
|
|
|
|
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. Synapse will also refuse to start an existing database with incorrect values
|
|
of `COLLATE` and `CTYPE` unless the config flag `allow_unsafe_locale`, found in the
|
|
`database` section of the config, is set to true. 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.
|
|
|
|
If you have a databse with an unsafe locale, the safest way to fix the issue is to dump the database and recreate it with
|
|
the correct locale parameter (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.
|