Synapse & postgresql

Bachy · October 2, 2022, 7:37pm

Matériel: serveur dédié chez ovh
Version de YunoHost: 11.x
**J’ai accès à mon serveur : En SSH & Par la webadmin
Êtes-vous dans un contexte particulier ou avez-vous effectué des modificiations particulières sur votre instance ? : non

Description du problème

Durant la migration vers ynh 11 j’ai eu un problème avec postgresql, que j’ai résolu tant bien que mal, en tout cas c’est ce que je croyais car j’ai pu relancer la migration jusqu’au bout.

Sauf que maintenant synapse ne veut plus se lancer, problème de connexion avec postgresql justement

- Oct 02 21:19:55 systemd[1]: Starting Synapse Matrix homeserver...
  - Oct 02 21:19:57 systemd[1]: Started Synapse Matrix homeserver.
  - Oct 02 21:19:59 python[18618]: This server is configured to use 'matrix.org' as its trusted key server via the
  - Oct 02 21:19:59 python[18618]: 'trusted_key_servers' config option. 'matrix.org' is a good choice for a key
  - Oct 02 21:19:59 python[18618]: server since it is long-lived, stable and trusted. However, some admins may
  - Oct 02 21:19:59 python[18618]: wish to use another server for this purpose.
  - Oct 02 21:19:59 python[18618]: To suppress this warning and continue using 'matrix.org', admins should set
  - Oct 02 21:19:59 python[18618]: 'suppress_key_server_warning' to 'true' in homeserver.yaml.
  - Oct 02 21:19:59 python[18618]: --------------------------------------------------------------------------------
  - Oct 02 21:20:00 python[18618]: 2022-10-02 21:20:00,074 - root - 343 - WARNING - main- ***** STARTING SERVER *****
  - Oct 02 21:20:00 python[18618]: 2022-10-02 21:20:00,074 - root - 344 - WARNING - main- Server /opt/yunohost/matrix-synapse/lib/python3.9/site-packages/synapse/app/homeserver.py version 1.67.0
  - Oct 02 21:20:00 python[18618]: 2022-10-02 21:20:00,075 - synapse.app._base - 207 - ERROR - main- Exception during startup
  - Oct 02 21:20:00 python[18618]: Traceback (most recent call last):
  - Oct 02 21:20:00 python[18618]:   File "/opt/yunohost/matrix-synapse/lib/python3.9/site-packages/synapse/app/homeserver.py", line 375, in setup
  - Oct 02 21:20:00 python[18618]:     hs.setup()
  - Oct 02 21:20:00 python[18618]:   File "/opt/yunohost/matrix-synapse/lib/python3.9/site-packages/synapse/server.py", line 309, in setup
  - Oct 02 21:20:00 python[18618]:     self.datastores = Databases(self.DATASTORE_CLASS, self)
  - Oct 02 21:20:00 python[18618]:   File "/opt/yunohost/matrix-synapse/lib/python3.9/site-packages/synapse/storage/databases/__init__.py", line 65, in __init__
  - Oct 02 21:20:00 python[18618]:     with make_conn(database_config, engine, "startup") as db_conn:
  - Oct 02 21:20:00 python[18618]:   File "/opt/yunohost/matrix-synapse/lib/python3.9/site-packages/synapse/storage/database.py", line 149, in make_conn
  - Oct 02 21:20:00 python[18618]:     native_db_conn = engine.module.connect(**db_params)
  - Oct 02 21:20:00 python[18618]:   File "/opt/yunohost/matrix-synapse/lib/python3.9/site-packages/psycopg2/__init__.py", line 122, in connect
  - Oct 02 21:20:00 python[18618]:     conn = _connect(dsn, connection_factory=connection_factory, **kwasync)
  - Oct 02 21:20:00 python[18618]: psycopg2.OperationalError: could not connect to server: Connection refused
  - Oct 02 21:20:00 python[18618]:         Is the server running on host "localhost" (::1) and accepting
  - Oct 02 21:20:00 python[18618]:         TCP/IP connections on port 5432?
  - Oct 02 21:20:00 python[18618]: could not connect to server: Connection refused
  - Oct 02 21:20:00 python[18618]:         Is the server running on host "localhost" (127.0.0.1) and accepting
  - Oct 02 21:20:00 python[18618]:         TCP/IP connections on port 5432?
  - Oct 02 21:20:00 python[18618]: **********************************************************************
  - Oct 02 21:20:00 python[18618]:  Error during initialisation:
  - Oct 02 21:20:00 python[18618]:     could not connect to server: Connection refused
  - Oct 02 21:20:00 python[18618]:          Is the server running on host "localhost" (::1) and accepting
  - Oct 02 21:20:00 python[18618]:          TCP/IP connections on port 5432?
  - Oct 02 21:20:00 python[18618]:  could not connect to server: Connection refused
  - Oct 02 21:20:00 python[18618]:          Is the server running on host "localhost" (127.0.0.1) and accepting
  - Oct 02 21:20:00 python[18618]:          TCP/IP connections on port 5432?
  - Oct 02 21:20:00 python[18618]:  
  - Oct 02 21:20:00 python[18618]:  There may be more information in the logs.
  - Oct 02 21:20:00 python[18618]: **********************************************************************
  - Oct 02 21:20:00 systemd[1]: matrix-synapse.service: Main process exited, code=exited, status=1/FAILURE
  - Oct 02 21:20:00 systemd[1]: matrix-synapse.service: Failed with result 'exit-code'.
  - Oct 02 21:20:00 systemd[1]: matrix-synapse.service: Consumed 4.822s CPU time.
  - Oct 02 21:20:03 systemd[1]: matrix-synapse.service: Scheduled restart job, restart counter is at 172.
  - Oct 02 21:20:03 systemd[1]: Stopped Synapse Matrix homeserver.
  - Oct 02 21:20:03 systemd[1]: matrix-synapse.service: Consumed 4.822s CPU time.

En faite quand je redémarre le serveur, postgresql est dead et je dois relancer le cluster a la main pour qu’il se lance

yunohost service status postgresql
configuration: unknown
description: Stores app data (SQL database)
last_state_change: 1970-01-01 02:00:00
start_on_boot: enabled
status: dead

pg_lsclusters 
Ver Cluster Port Status Owner    Data directory              Log file
13  main    5433 down   postgres /var/lib/postgresql/13/main /var/log/postgresql/postgresql-13-main.log

pg_ctlcluster 13 main restart
pg_lsclusters 
Ver Cluster Port Status Owner    Data directory              Log file
13  main    5433 online postgres /var/lib/postgresql/13/main /var/log/postgresql/postgresql-13-main.log

yunohost service status postgresql
configuration: unknown
description: Stores app data (SQL database)
last_state_change: 2022-10-02 21:12:41
start_on_boot: enabled
status: running

pourtant mon service postgresql tourne bien

yunohost service status postgresql
configuration: unknown
description: Stores app data (SQL database)
last_state_change: 2022-10-02 21:12:41
start_on_boot: enabled
status: running

yunohost service log postgresql
journalctl: 
  - -- Journal begins at Wed 2022-09-28 20:05:29 CEST, ends at Sun 2022-10-02 21:28:34 CEST. --
  - Oct 02 15:53:52 systemd[1]: Starting PostgreSQL Cluster 13-main...
  - Oct 02 15:53:54 systemd[1]: Started PostgreSQL Cluster 13-main.
  - Oct 02 15:57:52 systemd[1]: Reloading PostgreSQL Cluster 13-main.
  - Oct 02 15:57:52 systemd[1]: Reloaded PostgreSQL Cluster 13-main.
  - Oct 02 15:58:04 systemd[1]: Stopping PostgreSQL Cluster 13-main...
  - Oct 02 15:58:04 systemd[1]: postgresql@13-main.service: Succeeded.
  - Oct 02 15:58:04 systemd[1]: Stopped PostgreSQL Cluster 13-main.
  - Oct 02 16:40:59 systemd[1]: Starting PostgreSQL Cluster 13-main...
  - Oct 02 16:41:01 systemd[1]: Started PostgreSQL Cluster 13-main.
  - Oct 02 16:52:56 systemd[1]: Reloading PostgreSQL Cluster 13-main.
  - Oct 02 16:52:56 systemd[1]: Reloaded PostgreSQL Cluster 13-main.
  - Oct 02 17:56:14 systemd[1]: Reloading PostgreSQL Cluster 13-main.
  - Oct 02 17:56:14 systemd[1]: Reloaded PostgreSQL Cluster 13-main.
  - Oct 02 18:04:33 systemd[1]: Reloading PostgreSQL Cluster 13-main.
  - Oct 02 18:04:33 systemd[1]: Reloaded PostgreSQL Cluster 13-main.
  - Oct 02 18:11:29 systemd[1]: Reloading PostgreSQL Cluster 13-main.
  - Oct 02 18:11:29 systemd[1]: Reloaded PostgreSQL Cluster 13-main.
  - Oct 02 19:03:59 systemd[1]: Reloading PostgreSQL Cluster 13-main.
  - Oct 02 19:03:59 systemd[1]: Reloaded PostgreSQL Cluster 13-main.
  - Oct 02 20:40:47 systemd[1]: Reloading PostgreSQL Cluster 13-main.
  - Oct 02 20:40:47 systemd[1]: Reloaded PostgreSQL Cluster 13-main.
  - Oct 02 20:53:57 systemd[1]: Stopping PostgreSQL Cluster 13-main...
  - Oct 02 20:53:57 systemd[1]: postgresql@13-main.service: Succeeded.
  - Oct 02 20:53:57 systemd[1]: Stopped PostgreSQL Cluster 13-main.
  - -- Boot 6c36ff34cc6240b5b558934a072d800e --
  - Oct 02 21:12:38 systemd[1]: Starting PostgreSQL Cluster 13-main...
  - Oct 02 21:12:41 systemd[1]: Started PostgreSQL Cluster 13-main.

Je cherche, je cherche, mais avec 0 exeprience en pg c’est pas évident.
un petit coup de main ne serai pas de refus

Bachy · October 3, 2022, 9:33am

bon j’avance,
va savoir pourquoi postgresql était configuré pour écouter le port 5433

netstat -nltp | grep postgres
tcp        0      0 127.0.0.1:5433          0.0.0.0:*               LISTEN      13665/postgres      
tcp6       0      0 ::1:5433                :::*                    LISTEN      13665/postgres

donc j’ai changé la config en éditant le fichier suivant

grep -r "5433" /etc/
/etc/postgresql/13/main/postgresql.conf:port = 5433				# (change requires restart)

par contre mon cluster ne démarre pas au démarrage du service postgresql, je dois encore résoudre ça

# je demarre le service postgres
$ yunohost service start postgresql
Success! Service 'postgresql' started

# raté
$ yunohost service status postgresql
configuration: unknown
description: Stores app data (SQL database)
last_state_change: 1970-01-01 02:00:00
start_on_boot: enabled
status: dead

# le cluster est down
$ pg_lsclusters 
Ver Cluster Port Status Owner    Data directory              Log file
13  main    5432 down   postgres /var/lib/postgresql/13/main /var/log/postgresql/postgresql-13-main.log

# je redémarre le cluster
$ pg_ctlcluster 13 main restart

# il est up
$ pg_lsclusters 
Ver Cluster Port Status Owner    Data directory              Log file
13  main    5432 online postgres /var/lib/postgresql/13/main /var/log/postgresql/postgresql-13-main.log

# le service est active
$ yunohost service status postgresql
configuration: unknown
description: Stores app data (SQL database)
last_state_change: 2022-10-03 11:14:44
start_on_boot: enabled
status: running

# synapse fonctionne

Aleks · October 3, 2022, 12:26pm

En réalité il y a deux services postgresql, il y postgresql et postgresql@13-main (pas 100% sur du nom exact du deuxième)

Du coup regardons si le deuxième est bien configuré pour se lancer au boot:

sudo systemctl status postgresql@13-main

(il faut regarder si c’est marqué ‘enabled’ ou ‘disabled’ après le active/running vert)

et si ce n’est pas le cas:

sudo systemctl enable postgresql@13-main

Bachy · October 3, 2022, 2:52pm

ha mais oui, c’est tout bête
effectivement j’ai enabled le service.
bon je vais pas redémarrer tout le serveur pour ça, mais je pense qu’on est bon !

merci @Aleks

system · October 18, 2022, 2:53pm

This topic was automatically closed 15 days after the last reply. New replies are no longer allowed.