Shifting the Matrix database to another postgresql server

(**) Translated with www.DeepL.com/Translator

WARNING

This page contains a form to create your configurations interactively. (*) The data entered remains on your browser and is not sent anywhere. You can check this by opening your browser’s console (network tab). Source code attached to the input form elements:

//onload add listeners on input
document.addEventListener("DOMContentLoaded", function () {
  $(".replicator").each(function () {
    let id = $(this).attr("id");
    $("#" + id).on("keyup", function () {
      $(".s" + id).html($(this).val());
    });
  });
});
//reset form and call trigger to reset all spans
function resetForm(id) {
  $("#" + id)
    .find("input")
    .each(function () {
      $(this).val("").trigger("keyup");
    });
}

This program is simply in charge of performing a replication operation of the data entered in the “span” type elements integrated in the code representing the configuration elements necessary for the correct execution of this procedure.

Configurations

In this example, I shift the data from Matrix, located on the Postgresql A server (192.168.25.2) to the other Postgresql B server (192.168.25.100).

Preparation for migration

Matrix Service Shutdown

Connect “root” to the Matrix server console, then run :

systemctl stop synapse-matrix

Display the configuration data of the Matrix service (/opt/synapse/homeserver.yaml) and search for items related to the “database” property, then complete the parameters form. Example :

database:
  name: psycopg2
  args:
    user: 
    password: 
    database: 
    host: 
    port: 
    cp_min: 5
    cp_max: 10

Parameters Form

Backing up the database

On the Matrix server, and connected as “root” execute these commands:

# Postgresql client installation if not installed
apt-get update
apt-get -y install postgresql-client
cd 
#Backup the database in /root/backupmatrixdb.sql
export PGPASSWORD=""
pg_dump -d "" -h "" - p "" -U "" > "root/backupmatrixdb.sql"
unset PGPASSWORD

Copy

Prepare the new postgresql server B

NB The Postgresql B server must, to continue this operation, allow ssh connections for the “root” account (ssd_config).

Connect “root” to the Postgresql B server () and install the postgresql service:

apt-get upate
apt-get -y install postgresql

Copy Matrix data backup

Connected “root” on the Postgresql A server (), copy the backup file on the new Postgresql B server () :

scp /root/backupmatrixdb.sql root@:/var/lib/postgresql/.
# Specify the root password of the Postgresql B server

Copy

Restoring the backup

Connected “root” to the Postgresql B server (), execute these commands :

su - postgres
su - postgres  
# Creation of the user  and the base   
createuser   
psql  
ALTER USER synapse WITH ENCRYPTED password '';   
CREATE DATABASE  ENCODING 'UTF8' LC_COLLATE='C' LC_CTYPE='C' template=template0 OWNER ;
\q  
export PGPASSWORD=""
psql -U "" "" < "/var/lib/postgresql/backupmatrixdb.sql"
unset PGPASSWORD
exit

Copy

Final configuration files

On the server postgresql B ()

• File /etc/postgresql/[Postgresql version]/main/postgresql.conf

Find the line `listen_addresses = ’localhost’ and comment as follows:

#listen_addresses = 'localhost'

Add this line

listen_addresses = ''   

Copy

• Fichier /etc/postgresql/[cluster version]/pg_hba.conf

Add the the end of file :

host                            /32            md5  

Copy

Restart the service :

systemctl restart postgresql

On the Matrix server ()

• File /opt/synapse/homeserver.yaml, modify this file as follows :

database:
  name: psycopg2
  args:
    user: 
    password: 
    database: 
    host: 
    port: 
    cp_min: 5
    cp_max: 10

Copy

Restart Matrix

On the Matrix server () and connected as “root”, run the command :

systemctl start synapse-matrix

A little bit of cleaning

Delete backup files on :

• Postgresql A (): /root/backupmatrixdb.sql
• Postgresql B (): /var/lib/postgresql/backupmatrixdb.sql

Enjoy +++

Document licence : Creative Commons (CC BY-NC-ND 4.0)

THIS DOCUMENTATION IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND AND DISTRIBUTED FOR EDUCATIONAL PURPOSES ONLY. THE AUTHOR, CONTRIBUTORS TO THIS DOCUMENTATION OR ©MYTINYDC.COM SHALL IN NO EVENT BE LIABLE FOR ANY DIRECT OR INDIRECT DAMAGE THAT MAY RESULT FROM THE APPLICATION OF THE PROCEDURES IMPLEMENTED IN THIS DOCUMENTATION, OR FROM THE INCORRECT INTERPRETATION OF THIS DOCUMENT.

(**) Translated with www.DeepL.com/Translator