PGDATA and systemd

Sometimes you may need to put your data files on a different filesystem or on different discs. To do this, you need to change the PGDATA environment variable. In this blog post I will show you a clean way to do this using systemd.

Lets start from scratch usually you would install postgresql via yum / dnf as follows:

$ yum module install postgresql:15
Code-Sprache: JavaScript (javascript)

Next, the postgresql daemon is not started, which is good, so we need to modify PGDATA first. Run “systemctl edit” and add the section in brackets and the PGDATA parameter. Reload the file to make sure systemd gets the modified version.

$ systemctl edit postgresql.service 
...
[Service]
Environment=PGDATA=/var/SP/postgres/pgdata
$ sudo systemctl daemon-reload
Code-Sprache: JavaScript (javascript)

Now you can check your changes using the “systemctl cat” command. As you will see, the OS will automatically create a new file “/etc/systemd/system/postgresql.service.d/override.conf” which will take into account and overwrite the original system file.

$ sudo systemctl cat postgresql.service
...
[Unit]
Description=PostgreSQL database server
# We should start postgresql service after network is up (rhbz#2127534 and rhbz#2157651)
After=network-online.target

[Service]
Type=notify

User=postgres
Group=postgres

# Disable OOM kill on the postmaster
OOMScoreAdjust=-1000
# ... but allow it still to be effective for child processes
# (note that these settings are ignored by Postgres releases before 9.5)
Environment=PG_OOM_ADJUST_FILE=/proc/self/oom_score_adj
Environment=PG_OOM_ADJUST_VALUE=0

Environment=PGDATA=/var/lib/pgsql/data

ExecStartPre=/usr/libexec/postgresql-check-db-dir %N
# Even though the $PGDATA variable is exported (postmaster would accept that)
# use the -D option here so PGDATA content is printed by /bin/ps and by
# 'systemctl status'.
ExecStart=/usr/bin/postmaster -D ${PGDATA}
ExecReload=/bin/kill -HUP $MAINPID
KillMode=mixed
KillSignal=SIGINT

# No artificial start/stop timeout (rhbz#1525477, pgrpms#2786).
TimeoutSec=0

[Install]
WantedBy=multi-user.target

# /etc/systemd/system/postgresql.service.d/override.conf
[Service]
Environment=PGDATA=/var/SP/postgres/pgdata
Code-Sprache: PHP (php)

Now you can start to initiate your postgresql cluster as usual

$ postgresql-setup initdb
Initializing database ... OK

Start postgresql-daemon and enable autostart. Thats it!

$ sudo systemctl enable --now postgresql.service