Issue configuring Postgresql for remote connection: 'Job for postgresql.service failed'

Question

I'm trying to configure Postgresql 14.7 for remote connections in a SLES15 environment. I've been following the instructions from Postgres for Zypper installation supplemented with these instructions as I've noticed some minor differences between directory conventions between the two. it appears I can get the postgresql service started, but then it fails after updating the conf files to accept remote connections.

After initializing the Postgresql service with sudo systemctl start postgresql and ensuring that it is active, I have edited the postgres.conf and 'pg_hba.conf` files as mentioned here and here.

To the postgresql.conf file I added:

#listen_addresses = 'localhost'
listen_addresses = '*'

and to the pg_hba.conf I added:

# TYPE  DATABASE    USER    ADDRESS     METHOD
host    all         all     0.0.0.0/0       md5
host    all             all     :/0             md5

I then restarted the service with sudo systemctl restart postgresql and receive the following error:

Job for PostgreSQL.service failed because the control process exited with error code.
See "systemctl status postgresql.service" and "journalctl -xeu postgresql.service for details"`

The output from journalctl -xeu postgresql.service gives:

journalctl -xeu postgresql.service
░░ Defined-By: systemd
░░ Support: https://lists.freedesktop.org/mailman/listinfo/systemd-devel
░░ 
░░ A stop job for unit postgresql.service has begun execution.
░░ 
░░ The job identifier is 62101.
Apr 05 15:19:15 sph-gcmc2 systemd[1]: postgresql.service: Deactivated successfully.
░░ Subject: Unit succeeded
░░ Defined-By: systemd
░░ Support: https://lists.freedesktop.org/mailman/listinfo/systemd-devel
░░ 
░░ The unit postgresql.service has successfully entered the 'dead' state.
Apr 05 15:19:15 sph-gcmc2 systemd[1]: Stopped PostgreSQL database server.
░░ Subject: A stop job for unit postgresql.service has finished
░░ Defined-By: systemd
░░ Support: https://lists.freedesktop.org/mailman/listinfo/systemd-devel
░░ 
░░ A stop job for unit postgresql.service has finished.
░░ 
░░ The job identifier is 62101 and the job result is done.
Apr 05 15:19:15 sph-gcmc2 systemd[1]: Starting PostgreSQL database server...
░░ Subject: A start job for unit postgresql.service has begun execution
░░ Defined-By: systemd
░░ Support: https://lists.freedesktop.org/mailman/listinfo/systemd-devel
░░ 
░░ A start job for unit postgresql.service has begun execution.
░░ 
░░ The job identifier is 62101.
Apr 05 15:19:15 sph-gcmc2 postgresql-script[66239]: 2023-04-05 15:19:15.322 EDT   [66239]LOG:  redirecting log output to logging collector process
Apr 05 15:19:15 sph-gcmc2 postgresql-script[66239]: 2023-04-05 15:19:15.322 EDT   [66239]HINT:  Future log output will appear in directory "log".
Apr 05 15:19:15 sph-gcmc2 postgresql-script[66237]: pg_ctl: could not start server
Apr 05 15:19:15 sph-gcmc2 postgresql-script[66237]: Examine the log output.
Apr 05 15:19:15 sph-gcmc2 systemd[1]: postgresql.service: Control process exited, code=exited, status=1/FAILURE
░░ Subject: Unit process exited
░░ Defined-By: systemd
░░ Support: https://lists.freedesktop.org/mailman/listinfo/systemd-devel
░░ 
░░ An ExecStart= process belonging to unit postgresql.service has exited.
░░ 
░░ The process' exit code is 'exited' and its exit status is 1.
Apr 05 15:19:15 sph-gcmc2 systemd[1]: postgresql.service: Failed with result 'exit-code'.
░░ Subject: Unit failed
░░ Defined-By: systemd
░░ Support: https://lists.freedesktop.org/mailman/listinfo/systemd-devel
░░ 
░░ The unit postgresql.service has entered the 'failed' state with result 'exit-code'.
Apr 05 15:19:15 sph-gcmc2 systemd[1]: Failed to start PostgreSQL database server.
░░ Subject: A start job for unit postgresql.service has failed
░░ Defined-By: systemd
░░ Support: https://lists.freedesktop.org/mailman/listinfo/systemd-devel
░░ 
░░ A start job for unit postgresql.service has finished with a failure.
░░ 
░░ The job identifier is 62101 and the job result is failed.
lines 387-440/440 (END)

At this point, I haven't tried updating the firewall rules which are the next steps in the workflow since something with my changes is causing the service to crash.

My goal is to use the PostgreSQL DB as the backbone for an ESRI enterprise geodatabase instance but the DB setup is tripping me up.

Answer 1

If service start is crashing after editing it's configuration file, the problem in that edition was seems. From my side that part of new config looks problematic:

:/0

That thing from above isn't correct IPv4 nether IPv6 address. Probably at this place should be

::/0

And at my place should be someone else, but it is not the question for that site.

Answer 2

Undo your modifications and start the service. Connect and see where the data directory is:

SHOW data_directory;

According to your messages, the log files will reside in the log subdirectory of that directory. Find the appropriate log file for your failed start attempt, read the error messages and fix the problem.

If you don't know how to fix the problem, ask a question including the error message.