[Resolved] Le service postgresql est failed :-(

Cellophile · February 18, 2022, 12:50pm

Mon serveur YunoHost

Matériel: VPS acheté en ligne
Version de YunoHost: 4.3.6.2
J’ai accès à mon serveur : En SSH | Par la webadmin | En direct avec un clavier/écran | …
Êtes-vous dans un contexte particulier ou avez-vous effectué des modificiations particulières sur votre instance ? : non

Description du problème

J’ai reçu ce message dans le diagnostique : Le service postgresql est failed

J’ai donc essayé les deux commandes proposées :

yunohost service restart postgresql

// qui me répond 
Success! Service 'postgresql' restarted

puis pour avoir une confirmation que ça fonctionne :

service postgresql status

// Qui me répond
●[vert] postgresql.service - PostgreSQL RDBMS
   Loaded: loaded (/lib/systemd/system/postgresql.service; enabled; vendor preset: enabled)
   Active: active (exited) since Fri 2022-02-18 12:37:31 UTC; 41s ago
  Process: 31303 ExecStart=/bin/true (code=exited, status=0/SUCCESS)
 Main PID: 31303 (code=exited, status=0/SUCCESS)

Feb 18 12:37:31 mon.serveur.fr systemd[1]: Starting PostgreSQL RDBMS...
Feb 18 12:37:31 mon.serveur.fr systemd[1]: Started PostgreSQL RDBMS.

Pourtant, si je fait un coup de logs :

yunohost service log postgresql

// Qui me répond

/var/log/postgresql/postgresql-11-main.log:
  - b'2021-12-08 14:01:40.417 UTC [22394] LOG:  listening on IPv6 address "::1", port 5432'
  - b'2021-12-08 14:01:40.418 UTC [22394] LOG:  listening on IPv4 address "127.0.0.1", port 5432'
  - b'2021-12-08 14:01:40.418 UTC [22394] LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"'
  - b'2021-12-08 14:01:40.434 UTC [22395] LOG:  database system was shut down at 2021-12-08 14:01:39 UTC'
  - b'2021-12-08 14:01:40.439 UTC [22394] LOG:  database system is ready to accept connections'
  - b'2021-12-08 14:01:41.004 UTC [22402] [unknown]@[unknown] LOG:  incomplete startup packet'
  - b'2021-12-08 14:01:50.193 UTC [22394] LOG:  received SIGHUP, reloading configuration files'
  - 2022-02-17 22:45:14.706 UTC [22394] LOG:  received fast shutdown request
  - 2022-02-17 22:45:14.709 UTC [22394] LOG:  aborting any active transactions
  - 2022-02-17 22:45:14.714 UTC [22394] LOG:  background worker "logical replication launcher" (PID 22401) exited with exit code 1
  - 2022-02-17 22:45:14.714 UTC [22396] LOG:  shutting down
  - 2022-02-17 22:45:14.723 UTC [22394] LOG:  database system is shut down
  - 2022-02-17 22:45:35.274 UTC [873] FATAL:  could not access private key file "/etc/ssl/private/ssl-cert-snakeoil.key": Permission denied
  - 2022-02-17 22:45:35.274 UTC [873] LOG:  database system is shut down
  - pg_ctl: could not start server
  - Examine the log output.
  - 2022-02-18 00:10:06.679 UTC [20324] FATAL:  could not access private key file "/etc/ssl/private/ssl-cert-snakeoil.key": Permission denied
  - 2022-02-18 00:10:06.680 UTC [20324] LOG:  database system is shut down
  - pg_ctl: could not start server
  - Examine the log output.
  - 2022-02-18 00:15:38.177 UTC [21274] FATAL:  could not access private key file "/etc/ssl/private/ssl-cert-snakeoil.key": Permission denied
  - 2022-02-18 00:15:38.177 UTC [21274] LOG:  database system is shut down
  - pg_ctl: could not start server
  - Examine the log output.
  - 2022-02-18 00:16:35.612 UTC [21627] FATAL:  could not access private key file "/etc/ssl/private/ssl-cert-snakeoil.key": Permission denied
  - 2022-02-18 00:16:35.612 UTC [21627] LOG:  database system is shut down
  - pg_ctl: could not start server
  - Examine the log output.
  - 2022-02-18 09:42:31.342 UTC [942] FATAL:  could not access private key file "/etc/ssl/private/ssl-cert-snakeoil.key": Permission denied
  - 2022-02-18 09:42:31.342 UTC [942] LOG:  database system is shut down
  - pg_ctl: could not start server
  - Examine the log output.
  - 2022-02-18 09:57:32.723 UTC [4490] FATAL:  could not access private key file "/etc/ssl/private/ssl-cert-snakeoil.key": Permission denied
  - 2022-02-18 09:57:32.723 UTC [4490] LOG:  database system is shut down
  - pg_ctl: could not start server
  - Examine the log output.
  - 2022-02-18 09:59:50.380 UTC [4783] FATAL:  could not access private key file "/etc/ssl/private/ssl-cert-snakeoil.key": Permission denied
  - 2022-02-18 09:59:50.380 UTC [4783] LOG:  database system is shut down
  - pg_ctl: could not start server
  - Examine the log output.
  - 2022-02-18 10:00:56.148 UTC [4952] FATAL:  could not access private key file "/etc/ssl/private/ssl-cert-snakeoil.key": Permission denied
  - 2022-02-18 10:00:56.148 UTC [4952] LOG:  database system is shut down
  - pg_ctl: could not start server
  - Examine the log output.
  - 2022-02-18 10:04:07.272 UTC [5530] FATAL:  could not access private key file "/etc/ssl/private/ssl-cert-snakeoil.key": Permission denied
  - 2022-02-18 10:04:07.272 UTC [5530] LOG:  database system is shut down
  - pg_ctl: could not start server
  - Examine the log output.
  - 2022-02-18 12:37:31.005 UTC [31302] FATAL:  could not access private key file "/etc/ssl/private/ssl-cert-snakeoil.key": Permission denied
  - 2022-02-18 12:37:31.005 UTC [31302] LOG:  database system is shut down
  - pg_ctl: could not start server
  - Examine the log output.
journalctl:
  - -- Logs begin at Fri 2022-02-18 09:42:26 UTC, end at Fri 2022-02-18 12:41:01 UTC. --
  - Feb 18 09:42:31 postgresql@11-main[758]: pg_ctl: could not start server
  - Feb 18 09:42:31 postgresql@11-main[758]: Examine the log output.
  - Feb 18 09:42:31 systemd[1]: postgresql@11-main.service: Can't open PID file /run/postgresql/11-main.pid (yet?) after start: No such file or directory
  - Feb 18 09:42:31 systemd[1]: postgresql@11-main.service: Failed with result 'protocol'.
  - Feb 18 09:42:31 systemd[1]: Failed to start PostgreSQL Cluster 11-main.
  - Feb 18 09:57:32 systemd[1]: Starting PostgreSQL Cluster 11-main...
  - Feb 18 09:57:32 postgresql@11-main[4482]: Error: /usr/lib/postgresql/11/bin/pg_ctl /usr/lib/postgresql/11/bin/pg_ctl start -D /var/lib/postgresql/11/main -l /var/log/postgresql/postgresql-11-main.log -s -o  -c config_file="/etc/postgresql/11/main/postgresql.conf"  exited with status 1:
  - Feb 18 09:57:32 postgresql@11-main[4482]: 2022-02-18 09:57:32.723 UTC [4490] FATAL:  could not access private key file "/etc/ssl/private/ssl-cert-snakeoil.key": Permission denied
  - Feb 18 09:57:32 postgresql@11-main[4482]: 2022-02-18 09:57:32.723 UTC [4490] LOG:  database system is shut down
  - Feb 18 09:57:32 postgresql@11-main[4482]: pg_ctl: could not start server
  - Feb 18 09:57:32 postgresql@11-main[4482]: Examine the log output.
  - Feb 18 09:57:32 systemd[1]: postgresql@11-main.service: Can't open PID file /run/postgresql/11-main.pid (yet?) after start: No such file or directory
  - Feb 18 09:57:32 systemd[1]: postgresql@11-main.service: Failed with result 'protocol'.
  - Feb 18 09:57:32 systemd[1]: Failed to start PostgreSQL Cluster 11-main.
  - Feb 18 09:59:50 systemd[1]: Starting PostgreSQL Cluster 11-main...
  - Feb 18 09:59:50 postgresql@11-main[4774]: Error: /usr/lib/postgresql/11/bin/pg_ctl /usr/lib/postgresql/11/bin/pg_ctl start -D /var/lib/postgresql/11/main -l /var/log/postgresql/postgresql-11-main.log -s -o  -c config_file="/etc/postgresql/11/main/postgresql.conf"  exited with status 1:
  - Feb 18 09:59:50 postgresql@11-main[4774]: 2022-02-18 09:59:50.380 UTC [4783] FATAL:  could not access private key file "/etc/ssl/private/ssl-cert-snakeoil.key": Permission denied
  - Feb 18 09:59:50 postgresql@11-main[4774]: 2022-02-18 09:59:50.380 UTC [4783] LOG:  database system is shut down
  - Feb 18 09:59:50 postgresql@11-main[4774]: pg_ctl: could not start server
  - Feb 18 09:59:50 postgresql@11-main[4774]: Examine the log output.
  - Feb 18 09:59:50 systemd[1]: postgresql@11-main.service: Can't open PID file /run/postgresql/11-main.pid (yet?) after start: No such file or directory
  - Feb 18 09:59:50 systemd[1]: postgresql@11-main.service: Failed with result 'protocol'.
  - Feb 18 09:59:50 systemd[1]: Failed to start PostgreSQL Cluster 11-main.
  - Feb 18 10:00:56 systemd[1]: Starting PostgreSQL Cluster 11-main...
  - Feb 18 10:00:56 postgresql@11-main[4947]: Error: /usr/lib/postgresql/11/bin/pg_ctl /usr/lib/postgresql/11/bin/pg_ctl start -D /var/lib/postgresql/11/main -l /var/log/postgresql/postgresql-11-main.log -s -o  -c config_file="/etc/postgresql/11/main/postgresql.conf"  exited with status 1:
  - Feb 18 10:00:56 postgresql@11-main[4947]: 2022-02-18 10:00:56.148 UTC [4952] FATAL:  could not access private key file "/etc/ssl/private/ssl-cert-snakeoil.key": Permission denied
  - Feb 18 10:00:56 postgresql@11-main[4947]: 2022-02-18 10:00:56.148 UTC [4952] LOG:  database system is shut down
  - Feb 18 10:00:56 postgresql@11-main[4947]: pg_ctl: could not start server
  - Feb 18 10:00:56 postgresql@11-main[4947]: Examine the log output.
  - Feb 18 10:00:56 systemd[1]: postgresql@11-main.service: Can't open PID file /run/postgresql/11-main.pid (yet?) after start: No such file or directory
  - Feb 18 10:00:56 systemd[1]: postgresql@11-main.service: Failed with result 'protocol'.
  - Feb 18 10:00:56 systemd[1]: Failed to start PostgreSQL Cluster 11-main.
  - Feb 18 10:04:07 systemd[1]: Starting PostgreSQL Cluster 11-main...
  - Feb 18 10:04:07 postgresql@11-main[5525]: Error: /usr/lib/postgresql/11/bin/pg_ctl /usr/lib/postgresql/11/bin/pg_ctl start -D /var/lib/postgresql/11/main -l /var/log/postgresql/postgresql-11-main.log -s -o  -c config_file="/etc/postgresql/11/main/postgresql.conf"  exited with status 1:
  - Feb 18 10:04:07 postgresql@11-main[5525]: 2022-02-18 10:04:07.272 UTC [5530] FATAL:  could not access private key file "/etc/ssl/private/ssl-cert-snakeoil.key": Permission denied
  - Feb 18 10:04:07 postgresql@11-main[5525]: 2022-02-18 10:04:07.272 UTC [5530] LOG:  database system is shut down
  - Feb 18 10:04:07 postgresql@11-main[5525]: pg_ctl: could not start server
  - Feb 18 10:04:07 postgresql@11-main[5525]: Examine the log output.
  - Feb 18 10:04:07 systemd[1]: postgresql@11-main.service: Can't open PID file /run/postgresql/11-main.pid (yet?) after start: No such file or directory
  - Feb 18 10:04:07 systemd[1]: postgresql@11-main.service: Failed with result 'protocol'.
  - Feb 18 10:04:07 systemd[1]: Failed to start PostgreSQL Cluster 11-main.
  - Feb 18 12:37:30 systemd[1]: Starting PostgreSQL Cluster 11-main...
  - Feb 18 12:37:31 postgresql@11-main[31293]: Error: /usr/lib/postgresql/11/bin/pg_ctl /usr/lib/postgresql/11/bin/pg_ctl start -D /var/lib/postgresql/11/main -l /var/log/postgresql/postgresql-11-main.log -s -o  -c config_file="/etc/postgresql/11/main/postgresql.conf"  exited with status 1:
  - Feb 18 12:37:31 postgresql@11-main[31293]: 2022-02-18 12:37:31.005 UTC [31302] FATAL:  could not access private key file "/etc/ssl/private/ssl-cert-snakeoil.key": Permission denied
  - Feb 18 12:37:31 postgresql@11-main[31293]: 2022-02-18 12:37:31.005 UTC [31302] LOG:  database system is shut down
  - Feb 18 12:37:31 postgresql@11-main[31293]: pg_ctl: could not start server
  - Feb 18 12:37:31 postgresql@11-main[31293]: Examine the log output.
  - Feb 18 12:37:31 systemd[1]: postgresql@11-main.service: Can't open PID file /run/postgresql/11-main.pid (yet?) after start: No such file or directory
  - Feb 18 12:37:31 systemd[1]: postgresql@11-main.service: Failed with result 'protocol'.
  - Feb 18 12:37:31 systemd[1]: Failed to start PostgreSQL Cluster 11-main.

On voit bien qu’à mon dernier essai; le 18 février à 12h37, ça fail, alors que quand je fais les status, ça me dit ok.
C’est comme si postrgresql n’avait pas les droits pour ouvrir private key file "/etc/ssl/private/ssl-cert-snakeoil.key". Alors je fais :

// ajouter l'utilisateur postgres au groupe ssl-cert
sudo gpasswd -a postgres ssl-cert

// vérifier les droits d'accès au fichier :
sudo chown root:ssl-cert  /etc/ssl/private/ssl-cert-snakeoil.key
sudo chmod 740 /etc/ssl/private/ssl-cert-snakeoil.key

// redémarrer le service
sudo /etc/init.d/postgresql start

mais ça ne marche pas, je me retrouve toujours avec le même problème. Du coup dans le diagnostique, j’ai une erreur, alors que quand je faire service postgresql status c’est censé être actif… Je n’y comprends rien !

nounix · February 19, 2022, 10:40am

J’ai rencontré un problème similaire avec metronome.
Un restart ne résolvait pas le problème mais un stop et un start ont été la solution

sudo /etc/init.d/postgresql stop
sudo /etc/init.d/postgresql status ← pour s’assuré que le service est bien arrêté
sudo /etc/init.d/postgresql start

Cellophile · February 19, 2022, 11:17am

Bonjour @nounix !

J’ai essayé, ça ne marche pas pour moi, toujours la même erreur. Entre temps s’en est rajouter une autre, borg is failed lui aussi…

Cellophile · February 19, 2022, 12:14pm

Alors, j’ai exploré le bug de Borg, en essayant de faire une sauvegarde de onlyoffice avec l’outil de yunohost.
Il se trouve que onlyoffice utilise postgresql comme base de données.
Et bien impossible de se connecter à la DB, il y a donc un vrai problème, l’erreur n’est pas l’affichage dans le diagnostique de ynh, mais un problème réel avec postgresql.

les logs complets de la sauvegarde échouée ici :
https://paste.yunohost.org/raw/afiweripac
l’extrait qui nous intéresse :

2022-02-19 12:05:58,830: DEBUG - + ynh_print_info '--message=Backing up the PostgreSQL database...'
2022-02-19 12:05:58,838: INFO - Backing up the PostgreSQL database...
2022-02-19 12:05:58,839: DEBUG - + ynh_psql_dump_db --database=onlyoffice
2022-02-19 12:05:58,847: DEBUG - + sudo --login --user=postgres pg_dump onlyoffice
2022-02-19 12:05:58,933: WARNING - pg_dump: [archiver (db)] connection to database "onlyoffice" failed: could not connect to server: No such file or directory
2022-02-19 12:05:58,933: WARNING - 	Is the server running locally and accepting
2022-02-19 12:05:58,934: WARNING - 	connections on Unix domain socket "/var/run/postgresql/.s.PGSQL.5432"?
2022-02-19 12:05:58,935: DEBUG - + ynh_exit_properly
2022-02-19 12:06:00,439: DEBUG - Échec de l'exécution du script : /var/cache/yunohost/app_tmp_work_dirs/app_e350da3v/scripts/backup
2022-02-19 12:06:00,440: ERROR - Impossible de sauvegarder onlyoffice

Cellophile · February 19, 2022, 2:36pm

Bon je n’avance pas, à la commande pg_lsclusters pour lister les clusters, j’ai

11  main    5432 down   postgres /var/lib/postgresql/11/main /var/log/postgresql/postgresql-11-main.log

et les logs donnent toujours un problème d’accès :

2022-02-19 11:13:13.530 UTC [12022] FATAL:  could not access private key file "/etc/ssl/private/ssl-cert-snakeoil.key": Permission denied
2022-02-19 11:13:13.530 UTC [12022] LOG:  database system is shut down
pg_ctl: could not start server
Examine the log output.

pourtant si je regarde la ligne :

-rwxr----- 1 root ssl-cert 1704 Dec  8 10:31 ssl-cert-snakeoil.key

j’ai bien les droits à 740, le groupe ssl-cert contient bien postgresql

ssl-cert:x:117:metronome,openldap,postgres

ça a l’air d’être un bug connu, mais je ne trouve pas de solution :
https://bugs.debian.org/cgi-bin/bugreport.cgi?bug=941973

github.com/sous-chefs/postgresql

FATAL: could not access private key file "/etc/ssl/private/ssl-cert-snakeoil.key": Permission denied

opened 12:17AM - 14 Jun 14 UTC

closed 02:38PM - 12 Jun 15 UTC

volkanunsal

Hi there, As I use this recipe to install postgresql, I run into this error: `…`` FATAL: could not access private key file "/etc/ssl/private/ssl-cert-snakeoil.key": Permission denied ``` I have tried all the recommended solutions to solve it, including changing the owner and group of the key to postgres. Nothing works. To verify that it doesn't work even with that, I ran the following command on the key: ``` sudo -u postgres cat ssl-cert-snakeoil.key ``` The result was: cat: ssl-cert-snakeoil.key: Permission denied (I am running Ubuntu 14.04 with Vagrant. Using passenger/ruby-20 image and Docker as provider.)

Cellophile · February 19, 2022, 4:50pm

Okay, après maints essais, ratages, plantages de nginx (!), restauration du system, retentage et j’en passe, j’ai enfin réussi à résoudre le problème

faire ces commandes (en root) :

mkdir /etc/ssl/private-copy; mv /etc/ssl/private/* /etc/ssl/private-copy/; rm -r /etc/ssl/private; mv /etc/ssl/private-copy /etc/ssl/private; chmod -R 0700 /etc/ssl/private; chown -R postgres /etc/ssl/private

// puis ne pas oublier de relancer postgresql

service postgresql stop
service postgresql status  // ← pour s’assurer que le service est bien arrêté
service postgresql start

et là, enfin, ça marche.

system · March 21, 2022, 4:51pm

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