Properly manage PostgreSQL on Linux distros with systemd

Trying to fire up AgensGraph after install resulted in errors, probably because a PostgreSQL cluster was already running in the background so I needed to look up how to properly stop it without kill -9.

Based on this AskUbuntu answer:

pg_ctl is the postreSQL way to stop postgreSQL (in Ubuntu and Debian we should use pg_ctlcluster which is a wrapper for pg_ctl).

pg_ctlcluster and pg_lsclusters is in postgresql-common package which is a collection of Perl scripts ¹.

$ pg_lsclusters
Ver Cluster Port Status Owner Data directory
9.5 main 5432 down postgres /var/lib/postgresql/9.5/main
9.6 main 5433 online postgres /var/lib/postgresql/9.6/main

Trying to shut down the online one with pg_ctlcluster I received the following message:

$ sudo -u postgres pg_ctlcluster 9.6 main stop
Warning: stopping the cluster using pg_ctlcluster will mark the systemd unit as failed. Consider using systemctl:
sudo systemctl stop postgresql@9.6-main

Using the exact systemctl command in the warning above did the trick. You can always check what services are running using systemctl status and browse them through its less-like interface or just grep whatever you need.

$ systemctl status | ack 'postgres'
│ │ ├─11945 /home/user/ag/bin/postgres -D ./agcluster
│ │ ├─11973 postgres: checkpointer process
│ │ ├─11974 postgres: writer process
│ │ ├─11975 postgres: wal writer process
│ │ ├─11976 postgres: autovacuum launcher process
│ │ ├─11977 postgres: stats collector process

If you see systemctl state as “degraded”, don’t fret, it just means some of your enabled units failed to start. Run systemctl --failed to see which.

  1. ^ To find out which package a command belongs to on a distro with APT package manager, just issue dpkg -S command_with_path. For example,
$ dpkg -S $(which pg_lsclusters)
postgresql-common: /usr/bin/pg_lsclusters