I have PostgreSQL 13.8 running under Raspbian GNU/Linux 11 (bullseye). I had to restart the Raspberry Pi it is running on and unplug it. After powering up the Pi again, everything seems to be running, but I can't get PostgreSQL to start completey. Previously, I started the service via sudo systemctl start postgresql.service. This now yields no output at all, it doesn't even ask for the password.
Running systemctl status postgresql gives the following:
● postgresql.service - PostgreSQL RDBMS
Loaded: loaded (/lib/systemd/system/postgresql.service; enabled; vendor preset: enabled)
Active: active (exited) since Tue 2023-07-11 08:02:48 CEST; 1s ago
Process: 2232 ExecStart=/bin/true (code=exited, status=0/SUCCESS)
Main PID: 2232 (code=exited, status=0/SUCCESS)
CPU: 7ms
Jul 11 08:02:48 raspberrypi systemd[1]: Starting PostgreSQL RDBMS...
Jul 11 08:02:48 raspberrypi systemd[1]: Finished PostgreSQL RDBMS.
I dug around and for the active (exited) part, I found several different discussions on stackoverflow and other sites. (E.g. here, here, here)
None of these cleared up the issue for me. However, following some tipps from these threads, I looked at /usr/lib/systemd/system/postgresql@.service:
# systemd service template for PostgreSQL clusters. The actual instances will
# be called "postgresql@version-cluster", e.g. "postgresql@9.3-main". The
# variable %i expands to "version-cluster", %I expands to "version/cluster".
# (%I breaks for cluster names containing dashes.)
[Unit]
Description=PostgreSQL Cluster %i
AssertPathExists=/etc/postgresql/%I/postgresql.conf
RequiresMountsFor=/etc/postgresql/%I /var/lib/postgresql/%I
PartOf=postgresql.service
ReloadPropagatedFrom=postgresql.service
Before=postgresql.service
# stop server before networking goes down on shutdown
After=network.target
[Service]
Type=forking
# -: ignore startup failure (recovery might take arbitrarily long)
# the actual pg_ctl timeout is configured in pg_ctl.conf
ExecStart=-/usr/bin/pg_ctlcluster --skip-systemctl-redirect %i start
# 0 is the same as infinity, but "infinity" needs systemd 229
TimeoutStartSec=0
ExecStop=/usr/bin/pg_ctlcluster --skip-systemctl-redirect -m fast %i stop
TimeoutStopSec=1h
ExecReload=/usr/bin/pg_ctlcluster --skip-systemctl-redirect %i reload
PIDFile=/run/postgresql/%i.pid
SyslogIdentifier=postgresql@%i
# prevent OOM killer from choosing the postmaster (individual backends will
# reset the score to 0)
OOMScoreAdjust=-900
# restarting automatically will prevent "pg_ctlcluster ... stop" from working,
# so we disable it here. Also, the postmaster will restart by itself on most
# problems anyway, so it is questionable if one wants to enable external
# automatic restarts.
#Restart=on-failure
# (This should make pg_ctlcluster stop work, but doesn't:)
#RestartPreventExitStatus=SIGINT SIGTERM
[Install]
WantedBy=multi-user.target
Running pg_lsclusters results in this:
Error: Invalid mode in /etc/postgresql/13/main/start.conf, must be one of auto, manual, disabled
Looking at the file with cat /etc/postgresql/13/main/start.conf shows this:
▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒
which looks somewhat incorrect.
I have no idea how to proceed with all this information. Is it simply a problem of start.conf being corrupted? How would I fix that? Or ist it a configuration problem of the service? Do I need to make an entry in a pid file as some commenters mentioned in other questions? But what and where? Do I have multiple problems here?
I am still new to linux-systems and very confused by this, especially after the service ran without problems for months. Any help would be greatly appreciated.
While I cannot discard the hardware hypothesis put forth by laurenz-albe in the comments, as much as I want to, I want to make it known that I did fix the issue for the moment.
In order to do so, I manually replaced the content of the broken start.conf with this:
# Automatic startup configuration
# auto: automatically start the cluster
# manual: manual startup with pg_ctlcluster/postgresql@.service only
# disabled: refuse to start cluster
# See pg_createcluster(1) for details. When running from systemd,
# invoke 'systemctl daemon-reload' after editing this file.
auto
After restarting the Pi, pg_lsclusters did not show any errors anymore. However, the postgresql-service was still in the status exited.
Then on a hunch I started the applications that connect to the database anyway and lo and behold, they did in fact connect and are working.
Sadly, this did not fix everything:
sudo -u postgres psql postgres
sudo: psql: command not found
So I couldn't log into the database and do anything administrative. However, after using locate to find the path to psql, adding it to $PATH in /etc/profile and creating a link using sudo ln -s /usr/lib/postgresql/13/bin/psql /usr/bin/psql, I can now log into the postgresql database again. I am not entirely sure that the modification of $PATH is really necessary.
This solution may be a bit hacky and not a long-term solution. Digging around the system revealed a lot of corrupted files that I had to fix by hand, so setting up the system anew seems to be something I should look into in the very near future. Still, I wanted to document my steps for anyone happening upon a similar problem.