Creating a PostgreSQL Service on Ubuntu

Ok, so you have a PostgreSQL distribution on your Ubuntu server. You have created the data directory and initialized the database. Now you want to run the server in a way that users can start, stop, & restart the server in pretty much the same way they do for other daemons and services running on your server. How do you setup a PostgreSQL service on Ubuntu?

Once you get to know it, the process is pretty straightforward.

The first step is to create a script. Below is a sample.

#!/bin/bash   

## EDIT FROM HERE

# Installation prefix
prefix=/path/to/PostgreSQL
   
# Data directory
PGDATA="/path/to/data"
   
# Who to run the postmaster as, usually "postgres".  (NOT "root")
PGUSER=postgres
   
# Where to keep a log file
PGLOG="$PGDATA/serverlog"
   
# It's often a good idea to protect the postmaster from being killed by the
# OOM killer (which will tend to preferentially kill the postmaster because
# of the way it accounts for shared memory).  To do that, uncomment these
# three lines:
#PG_OOM_ADJUST_FILE=/proc/self/oom_score_adj
#PG_MASTER_OOM_SCORE_ADJ=-1000
#PG_CHILD_OOM_SCORE_ADJ=0
# Older Linux kernels may not have /proc/self/oom_score_adj, but instead
# /proc/self/oom_adj, which works similarly except for having a different
# range of scores.  For such a system, uncomment these three lines instead:
#PG_OOM_ADJUST_FILE=/proc/self/oom_adj
#PG_MASTER_OOM_SCORE_ADJ=-17
#PG_CHILD_OOM_SCORE_ADJ=0
   
## STOP EDITING HERE
   
# The path that is to be used for the script
PATH=/usr/local/sbin:/usr/local/bin:/sbin:/bin:/usr/sbin:/usr/bin
   
# What to use to control the postmaster
PGCTL="$prefix/bin/pg_ctl"
   
set -e
   
# Only start if we can find pg_ctl
test -x $PGCTL ||
{
    echo "$PGCTL not found"
    if [ "$1" = "stop" ]
    then exit 0
    else exit 5
    fi
}
   
# If we want to tell child processes to adjust their OOM scores, set up the
# necessary environment variables.  Can't just export them through the "su".
if [ -e "$PG_OOM_ADJUST_FILE" -a -n "$PG_CHILD_OOM_SCORE_ADJ" ]
then
    DAEMON_ENV="PG_OOM_ADJUST_FILE=$PG_OOM_ADJUST_FILE PG_OOM_ADJUST_VALUE=$PG_CHILD_OOM_SCORE_ADJ"
fi
   
# Parse command line parameters.
case $1 in
  start)
    echo -n "Starting PostgreSQL: "
    test -e "$PG_OOM_ADJUST_FILE" && echo "$PG_MASTER_OOM_SCORE_ADJ" > "$PG_OOM_ADJUST_FILE"
    su - $PGUSER -c "$DAEMON_ENV $PGCTL start -w -D '$PGDATA' &" >>$PGLOG 2>&1
    echo "ok"
    ;;
  stop)
    echo -n "Stopping PostgreSQL: "
    su - $PGUSER -c "$PGCTL stop -D '$PGDATA' -s -m fast"
    echo "ok"
    ;;
  restart)
    echo -n "Restarting PostgreSQL: "
    su - $PGUSER -c "$PGCTL stop -D '$PGDATA' -s -m fast -w"
    test -e "$PG_OOM_ADJUST_FILE" && echo "$PG_MASTER_OOM_SCORE_ADJ" > "$PG_OOM_ADJUST_FILE"
    su - $PGUSER -c "$DAEMON_ENV $PGCTL start -w -D '$PGDATA' &" >>$PGLOG 2>&1
    echo "ok"
    ;;
  reload)
    echo -n "Reload PostgreSQL: "
    su - $PGUSER -c "$PGCTL reload -D '$PGDATA' -s"
    echo "ok"
    ;;
  status)
    su - $PGUSER -c "$PGCTL status -D '$PGDATA'"
    ;;
  *)
    # Print help
    echo "Usage: $0 {start|stop|restart|reload|status}" 1>&2
    exit 1
    ;;
esac
exit 0

Please note the placeholders /path/to/PostgreSQL and /path/to/data that need to be replaced with the location of your PostgreSQL distribution and your data directory respectively.

Once your script is created, perform the following steps:

1) Copy script over to

/etc/init.d

2) Give execute rights to this script

chmod 755 postgresql-service

3) Ensure that the script is available for startup and other users. Run the command:

sudo update-rc.d postgresql-service defaults

You should be all set!

Now, in order to look at the status, start, stop, or restart the PostgreSQL server, you can run the following commands from anywhere on your Ubuntu system:

sudo service postgresql-service status
sudo service postgresql-service start
sudo service postgresql-service stop
sudo service postgresql-service restart

Hope this helps!

NOTE: The above script was created on Ubuntu 14.04.