diff --git a/docs/postgres.md b/docs/postgres.md
index 29cf762858..70fe29cdcc 100644
--- a/docs/postgres.md
+++ b/docs/postgres.md
@@ -27,17 +27,21 @@ connect to a postgres database.
## Set up database
-Assuming your PostgreSQL database user is called `postgres`, create a
-user `synapse_user` with:
+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
+ su - postgres # Or: sudo -u postgres bash
psql
and then run:
@@ -57,7 +61,50 @@ Note that the PostgreSQL database *must* have the correct encoding set
You may need to enable password authentication so `synapse_user` can
connect to the database. See
-<https://www.postgresql.org/docs/11/auth-pg-hba-conf.html>.
+<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.
+
## Tuning Postgres
@@ -83,19 +130,41 @@ of free memory the database host has available.
When you are ready to start using PostgreSQL, edit the `database`
section in your config file to match the following lines:
- database:
- name: psycopg2
- args:
- user: <user>
- password: <pass>
- database: <db>
- host: <host>
- cp_min: 5
- cp_max: 10
+```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.
+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
+# 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
+```
## Porting from SQLite
|
