Custom pgDATA directory postgresql integration with systemctl

 Objective:

In this blog, we will see how we integrate the postgresql15 service into systemctl, when we have a custom PGDATA directory.

Remember the default action of enabling and starting postgresql-15 will lead to the error below:

sudo systemctl enable postgresql-15
sudo systemctl start postgresql-15

Output snippet:
Process: 1545 ExecStartPre=/usr/pgsql-15/bin/postgresql-15-check-db-dir ${PGDATA} (code=exited, status=1/FAILURE)

Actual output:

[root@10 ~]# systemctl enable postgresql-15

Created symlink from /etc/systemd/system/multi-user.target.wants/postgresql-15.service to /usr/lib/systemd/system/postgresql-15.service.

[root@10 ~]# systemctl start postgresql-15

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

[root@10 ~]# systemctl status postgresql-15.service

● postgresql-15.service - PostgreSQL 15 database server

   Loaded: loaded (/usr/lib/systemd/system/postgresql-15.service; enabled; vendor preset: disabled)

   Active: failed (Result: exit-code) since Mon 2022-10-17 12:11:58 IST; 9s ago

     Docs: https://www.postgresql.org/docs/15/static/

  Process: 1545 ExecStartPre=/usr/pgsql-15/bin/postgresql-15-check-db-dir ${PGDATA} (code=exited, status=1/FAILURE)

Oct 17 12:11:58 10.0.2.4 systemd[1]: Starting PostgreSQL 15 database server...

Oct 17 12:11:58 10.0.2.4 systemd[1]: postgresql-15.service: control process exited, code=exited status=1

Oct 17 12:11:58 10.0.2.4 systemd[1]: Failed to start PostgreSQL 15 database server.

Oct 17 12:11:58 10.0.2.4 systemd[1]: Unit postgresql-15.service entered failed state.

Oct 17 12:11:58 10.0.2.4 systemd[1]: postgresql-15.service failed.

[root@10 ~]#

Since the service is looking at the default path for pgdata, the start of the service is failing.

We have to edit the service... postgresql-15.service, to include the correct data directory.

>> introduce the below contents to the postgresq-15.service and try start again:

Command: systemctl edit postgresql-15.service

[Service]

Environment=PGDATA=/pgDATA/data

Actual:

[root@10 ~]# systemctl edit postgresql-15.service

[root@10 ~]# cat /etc/systemd/system/postgresql-15.service.d/override.conf

[Service]

Environment=PGDATA=/pgDATA/data

[root@10 ~]#

[root@10 ~]# systemctl daemon-reload

[root@10 ~]# cat /etc/systemd/system/postgresql-15.service.d/override.conf

[Service]

Environment=PGDATA=/pgDATA/data

[root@10 ~]# systemctl enable postgresql-15

[root@10 ~]# systemctl start postgresql-15

[root@10 ~]# systemctl status postgresql-15

● postgresql-15.service - PostgreSQL 15 database server

   Loaded: loaded (/usr/lib/systemd/system/postgresql-15.service; enabled; vendor preset: disabled)

  Drop-In: /etc/systemd/system/postgresql-15.service.d

           └─override.conf

   Active: active (running) since Mon 2022-10-17 12:23:30 IST; 7s ago

     Docs: https://www.postgresql.org/docs/15/static/

  Process: 1678 ExecStartPre=/usr/pgsql-15/bin/postgresql-15-check-db-dir ${PGDATA} (code=exited, status=0/SUCCESS)

 Main PID: 1683 (postmaster)

   CGroup: /system.slice/postgresql-15.service

           ├─1683 /usr/pgsql-15/bin/postmaster -D /pgDATA/data

           ├─1685 postgres: logger

           ├─1686 postgres: checkpointer

           ├─1687 postgres: background writer

           ├─1689 postgres: walwriter

           ├─1690 postgres: autovacuum launcher

           └─1691 postgres: logical replication launcher

Oct 17 12:23:29 10.0.2.4 systemd[1]: Starting PostgreSQL 15 database server...

Oct 17 12:23:30 10.0.2.4 postmaster[1683]: 2022-10-17 12:23:30.042 IST [1683] LOG:  redirecting log output to logg...ocess

Oct 17 12:23:30 10.0.2.4 postmaster[1683]: 2022-10-17 12:23:30.042 IST [1683] HINT:  Future log output will appear...log".

Oct 17 12:23:30 10.0.2.4 systemd[1]: Started PostgreSQL 15 database server.

Hint: Some lines were ellipsized, use -l to show in full.

[root@10 ~]#

Check postgresql status:

[root@10 ~]# ps -ef|grep -i postgres

root      1343  1123  0 11:17 ?        00:00:00 sshd: postgres [priv]

postgres  1348  1343  0 11:17 ?        00:00:00 sshd: postgres@pts/1

postgres  1349  1348  0 11:17 pts/1    00:00:00 -bash

postgres  1683     1  1 12:23 ?        00:00:00 /usr/pgsql-15/bin/postmaster -D /pgDATA/data

postgres  1685  1683  0 12:23 ?        00:00:00 postgres: logger

postgres  1686  1683  0 12:23 ?        00:00:00 postgres: checkpointer

postgres  1687  1683  0 12:23 ?        00:00:00 postgres: background writer

postgres  1689  1683  0 12:23 ?        00:00:00 postgres: walwriter

postgres  1690  1683  0 12:23 ?        00:00:00 postgres: autovacuum launcher

postgres  1691  1683  0 12:23 ?        00:00:00 postgres: logical replication launcher

root      1694  1323  0 12:23 pts/0    00:00:00 grep --color=auto -i postgres

[root@10 ~]#

This completes the systemctl integeration step for postgresql