Fixing Synapse PostgreSQL Collation Issue

When setting up Matrix Synapse with PostgreSQL, you might encounter the following error:

synapse.storage.engines._base.IncorrectDatabaseSetup: ("Database has incorrect collation of 'en_US.utf8'. Should be 'C'")

This happens because Synapse requires the database collation to be set to C, but the default PostgreSQL configuration uses en_US.utf8.

Solution

1. Check Current Collation

First, connect to the database inside the PostgreSQL container:

sudo docker exec -it <postgres-container-name> psql -U synapse

Then, check the collation settings:

SHOW LC_COLLATE;
SHOW LC_CTYPE;

If they are set to en_US.utf8, proceed to the next step.

2. Create a New Database with Correct Collation

Exit psql and run:

CREATE DATABASE synapse_new
  ENCODING 'UTF8'
  LC_COLLATE='C'
  LC_CTYPE='C'
  TEMPLATE=template0;

Then, switch to the new database:

\c synapse_new

3. Migrate Data

Backup the old database:

pg_dump -U synapse -Fc synapse > /tmp/synapse_backup.dump

Restore the backup to the new database:

pg_restore -U synapse -d synapse_new /tmp/synapse_backup.dump

4. Update Synapse Configuration

Modify homeserver.yaml to use the new database:

database:
  name: psycopg2
  args:
    user: synapse
    password: <password>
    database: synapse_new
    host: <host>

5. Restart Synapse

Finally, restart Synapse to apply the changes:

sudo docker-compose restart synapse

This should resolve the collation issue and allow Synapse to start properly.