Migrate Synapse from sqlite3 to PostgreSQL on Synology NAS

Introduction

We assume that we alrerady have a running synapse Docker container and that that instance has been setup to use sqlite3 (the default setting).

Creating PostgreSQL container

• We want to add this to the docker-compose file:

postgres:
    container_name: postgres
    image: postgres
    ports:
      - 5438:5432
    environment:
      - POSTGRES_USER=postgres
      - POSTGRES_PASSWORD=${MARIADB_ROOTPSW}
      - POSTGRES_INITDB_ARGS="--encoding='UTF8' --lc-collate='C' --lc-ctype='C'"
    volumes:
      - /volume1/docker/postgres/data:/var/lib/postgresql/data/
    restart: unless-stopped

• Then we want to create our postgres database. We connect to our home server via SSH, and execute the following command:
  docker exec -it postgres bash -c "createdb --encoding=UTF8 --locale=C --template=template0 --owner=postgres DB_NAME -U postgres"

Migrating the sqlite database

Preparing our files

• First of all, let's get the migration script called synapse_port_db from here
• Save the file in the ut it in the /data volume we mounted before (in our case /volume1/docker/postgres/data
• We need to now stop the synapse container, so that synapse is not running while we do the migration. docker stop synapse.
• Navigate to your mounted folder, and make a local backup of homeserver.db and homeserver.yaml. You can call them homeserver-bkp.db and homeserver-bkp.yaml.

Preparing the config file

Here we will make sure that the script can locate the original sqlite database, and migrate its content to our postgres container.

• We first need to change out original homeserver.yaml file to connect to our postgres container. This is important because the script will need to read this information in order to migrate the data.
  Open the file and look for the ## Database ## heading; then use the following settings:

database:
  name: psycopg2
  args:
    user: postgres
    password: YOURPASSWORD
    database: DB_NAME
    host: YOUR_IP:POSTGRES_CONTAINER_PORT
    cp_min: 5
    cp_max: 10
    keepalives_idle: 10
    keepalives_interval: 10
    keepalives_count: 3

Preparing the script

In order to run the script, we first need to install the Python3 Synology package and then install some other modules. This is not an ideal procedure, but a necessary evil.

• Install the Python 3 package from the DSM Package Center.
• Go back to the SSH terminal and install pip:
  • sudo python3 -m ensurepip
  • sudo python3 -m pip install --upgrade pip
• Once pip is installed, we need to also install some modules that the migration script needs:
  • python3 -m pip install matrix-synapse

Running the script

• We first need to bash in the synapse container, by doing docker exec -it synapse bash
• Finally, we can run the script by doing python3 ./synapse_port_db --sqlite-database homeserver.db --postgres-config homeserver.yaml

The migration process will start, and it might take quite a bit of time depending on how big your homeserver.db file was.

Once the migration is done, you can restart synapse with docker restart synapse

Uninstalling modules

Now that you are done with the script, you can safely run python3 -m pip uninstall matrix-synapse.