Custom postgresql.conf location

postgresql.conf file is the one file that should be found by postgres command line tools in order to boot the database server with all the correct configurations aligned by the database administrator.

The default location of this file is the data directory of the postgresql database server. This location is indicated by $PGDATA environment variable.

In my installation on centos, I prefer to install the PGDATA to a separate disk and mount on the /pg_data/<pg_version>/data path. But the configuration files (postgresql.conf and pg_hba.conf) are usually in the /etc/postgresql/<version> directory.

Since there is hba_file parameter can be defined within the postgresql.conf file, the location of the pg_hba.conf is not a big deal (hba_file = '/etc/postgresql/15/pg_hba.conf').

The first thing to do is the modify the postgresql-15.service file to locate the correct path of the postgresql.conf. The default location of this service file is; /usr/lib/systemd/system/postgresql-15.service.

You can find two highlighted modifications as follows. the PGDATA environment variable (since I am not using the default PGDATA location) and the ExecStart parameter for an extra parameter to be passed to postmaster.

# It's not recommended to modify this file in-place, because it will be
# overwritten during package upgrades.  It is recommended to use systemd
# "dropin" feature;  i.e. create file with suffix .conf under
# /etc/systemd/system/postgresql-15.service.d directory overriding the
# unit's defaults. You can also use "systemctl edit postgresql-15"
# Look at systemd.unit(5) manual page for more info.
# Note: changing PGDATA will typically require adjusting SELinux
# configuration as well.
# Note: do not use a PGDATA pathname containing spaces, or you will
# break postgresql-15-setup.

[Unit]
Description=PostgreSQL 15 database server
Documentation=https://www.postgresql.org/docs/15/static/
After=syslog.target
After=network-online.target

[Service]
Type=notify
User=postgres
Group=postgres
# Note: avoid inserting whitespace in these Environment= lines, or you may
# break postgresql-setup.
# Location of database directory
#Environment=PGDATA=/var/lib/pgsql/15/data/
Environment=PGDATA=/pg_data/15/data/
# Where to send early-startup messages from the server (before the logging
# options of postgresql.conf take effect)
# This is normally controlled by the global default set by systemd
# StandardOutput=syslog
# Disable OOM kill on the postmaster
OOMScoreAdjust=-1000
Environment=PG_OOM_ADJUST_FILE=/proc/self/oom_score_adj
Environment=PG_OOM_ADJUST_VALUE=0
ExecStartPre=/usr/pgsql-15/bin/postgresql-15-check-db-dir ${PGDATA}
#ExecStart=/usr/pgsql-15/bin/postmaster -D ${PGDATA}
ExecStart=/usr/pgsql-15/bin/postmaster -D ${PGDATA} --config-file=/etc/postgresql/15/postgresql.conf
ExecReload=/bin/kill -HUP $MAINPID
KillMode=mixed
KillSignal=SIGINT
# Do not set any timeout value, so that systemd will not kill postmaster
# during crash recovery.
TimeoutSec=0
# 0 is the same as infinity, but "infinity" needs systemd 229
TimeoutStartSec=0
TimeoutStopSec=1h

[Install]
WantedBy=multi-user.target

This modification should be enough for starting and stopping postgresql with systemctl. But if you are using pg_ctl you should pass an option to postmaster with an -o option, otherwise pg_ctl assumes postgresql.conf file resides under the PGDATA directory.

[postgres@centos01 system]$ pg_ctl start 

waiting for server to start....postgres: could not access the server configuration file "/pg_data/15/data/postgresql.conf": No such file or directory

stopped waiting

pg_ctl: could not start server

Examine the log output.

[postgres@centos01 system]$ pg_ctl -o "--config-file=/etc/postgresql/15/postgresql.conf"  start 

waiting for server to start....<time=2024-01-08 16:19:18.086 GMT app= host= user= db= pid=2956 line=1 trx=0>LOG:  redirecting log output to logging collector process

<time=2024-01-08 16:19:18.086 GMT app= host= user= db= pid=2956 line=2 trx=0>HINT:  Future log output will appear in directory "log".

 done
server started

Or, if you are using postgres command to start the server process then;

postgres --config-file=/etc/postgresql/15/postgresql.conf 

Separating the configuration file is a good idea to not to change the parameters for each backup/restore of the database. But this extra administration overhead should be considered.