Why is my PostgreSQL startup service not working properly until I restart it manually after reboot?

Question

I have PostgreSQL running on my Linux server.

PostgreSQL is enabled and is active after reboot.

In the config file from PostgreSQL I enabled remote access. But every time after I reboot my device, the remote access is no longer available:

---

systemctl status postgresql
● postgresql.service - PostgreSQL RDBMS
     Loaded: loaded (/lib/systemd/system/postgresql.service; enabled; vendor preset: enabled)
     Active: active (exited) since Tue 2022-04-12 02:05:22 CEST; 10min ago
    Process: 491 ExecStart=/bin/true (code=exited, status=0/SUCCESS)
   Main PID: 491 (code=exited, status=0/SUCCESS)
        CPU: 5ms

Apr 12 02:05:22 raspberrypi systemd[1]: Starting PostgreSQL RDBMS...
Apr 12 02:05:22 raspberrypi systemd[1]: Finished PostgreSQL RDBMS.

---

cat grafana.log
logger=tsdb.postgres t=2022-04-12T02:18:50.33+0200 lvl=eror msg="query error" err="dial tcp 192.1.1.1:5432: connect: connection refused"

---

After I restart the service, remote access on the PostgreSQL works perfectly fine:

sudo systemctl restart postgresql

---

systemctl status postgresql
● postgresql.service - PostgreSQL RDBMS
         Loaded: loaded (/lib/systemd/system/postgresql.service; enabled; vendor preset: enabled)
         Active: active (exited) since Tue 2022-04-12 02:53:47 CEST; 25min ago
        Process: 2541 ExecStart=/bin/true (code=exited, status=0/SUCCESS)
       Main PID: 2541 (code=exited, status=0/SUCCESS)
            CPU: 5ms

Apr 12 02:53:47 raspberrypi systemd[1]: Starting PostgreSQL RDBMS...
Apr 12 02:53:47 raspberrypi systemd[1]: Finished PostgreSQL RDBMS.

Answer 1

Postgres is enabled and is active after reboot.

Is it? The output looks like it's exited:

systemctl status postgresql
● postgresql.service - PostgreSQL RDBMS
     Loaded: loaded (/lib/systemd/system/postgresql.service; enabled; vendor preset: enabled)
     Active: active (exited) since Tue 2022-04-12 02:05:22 CEST; 10min ago
    Process: 491 ExecStart=/bin/true (code=exited, status=0/SUCCESS)
   Main PID: 491 (code=exited, status=0/SUCCESS)
        CPU: 5ms

Apr 12 02:05:22 raspberrypi systemd[1]: Starting PostgreSQL RDBMS...
Apr 12 02:05:22 raspberrypi systemd[1]: Finished PostgreSQL RDBMS.

Specifically, I'm looking at "Main PID: 491 (code=exited, status=0/SUCCESS)".

Edit:

Maybe not it seems. This ServerFault answer seems like it might have some good hints.

Seeing as this is a rasberry pi, I wouldn't be surprised if there's some finickyness in the boot process that results in the same symptoms seen here (this is for an Ubuntu environment, but hey, RPi is Debian too):

The postgresql service unit installed by the postgresql-common package is just a dummy service which causes the actual service [email protected] to be started via a dependency.

...

That dependency is not permanent, but generated during system boot by the systemd generator /lib/systemd/system-generators/postgresql-generator which also comes with the postgresql-common package. The generator checks whether the startup mode in the file /etc/postgresql/9.6/main/start.conf is set to auto, and if so, sets up the dependency that subsequently causes instance 9.6-main to be started.

...

Due to the limitations of systemd generators (see man systemd.generator) this process may fail, causing the dependencies to be absent after a reboot. Systemd will then start only the dummy service

...

Running the command

systemctl daemon-reload

manually as root will re-run the generator and in most cases fix the problem until the next reboot.

To solve the problem permanently you'll have to find the reason why the generator fails during boot. Possible causes can be found in the systemd.generator manpage. In my case it was the PostgreSQL config file /etc/postgresql/9.6/main/postgresql.conf which was symlinked to a different filesystem that wasn't available yet when the generator ran early during boot. postgresql-generator checks the existence of that file even though it doesn't otherwise need it.

Maybe the generator fails at boot, the dummy postgresql service fires, then the generator succeeds later, hence your ability to get the service running again by restarting it a la sudo systemctl restart postgresql.