PostgreSql service template for systemd

This is about adding a postgresql service template for systemd. This may be handy if you want to have several distinct pgsql server on the same host or if your operating system (like gentoo) does not provide a service file.

TL:DR: copy the following content to /etc/systemd/system/postgresql@.service

[Unit]
Description=PostgreSQL %i database server
After=network.target

[Service]
Type=forking
TimeoutSec=120
User=postgres
Group=postgres

SyslogIdentifier=postgresql-%i

Environment=PGROOT=/var/lib/postgresql/%i

ExecStart= /usr/bin/pg_ctl -s -D ${PGROOT}/data start -w -t ${TimeoutSec}
ExecReload=/usr/bin/pg_ctl -s -D ${PGROOT}/data reload
ExecStop=  /usr/bin/pg_ctl -s -D ${PGROOT}/data stop -m fast

# Due to PostgreSQL's use of shared memory, OOM killer is often overzealous in
# killing Postgres, so adjust it downward
OOMScoreAdjust=-200

[Install]
WantedBy=multi-user.target

This is not derivative from the ArchLinux package by mistake, we will use the same guidelines found in the wiki about installing pgsql :

The PostgreSQL database server configuration file is postgresql.conf. This file is located in the data directory of the server, typically /var/lib/postgres/data

Yes, thats it, we must move 3 files from /etc to the pgdata directory. Exactly like described in the pgsql documentation.

/var/lib/postgresql/9.3/data/postgresql.conf
/var/lib/postgresql/9.3/data/pg_hba.conf
/var/lib/postgresql/9.3/data/pg_ident.conf

We now have a standalone directory with everything in it , this is not mandatory but it helps to have less thing to worry about in the service file.

lets add a second instance with a different port (just duplicate the previous directory and edit postgresql.conf)

/var/lib/postgresql/9.3-1/data/postgresql.conf
/var/lib/postgresql/9.3-1/data/pg_hba.conf
/var/lib/postgresql/9.3-1/data/pg_ident.conf

You can now start and stop the new postgresql services

systemctl start postgresql@9.3.service 
systemctl start postgresql@9.3-1.service

You can monitor each server individually

journalctl -u postgresql@9.3-1 -f
-- Logs begin at lun. 2013-11-18 21:34:41 CET. --
mars 21 15:58:49 kos systemd[1]: Starting PostgreSQL 9.3-1 database server...
mars 21 15:58:49 kos systemd[1]: Started PostgreSQL 9.3-1 database server.

Voila, you now have two pgsql server running :

2014-03-21-170218_841x450_scrot

You may want to change the service file to take in account different pgsql version , here is the full systemd service documentation.

Have fun

Posted in Blog Tagged with: , , ,

Leave a Reply

Your email address will not be published. Required fields are marked *

*