Postgresql - configuring as Upstart job problem

Question

I need Postgresql configured to start with the Upstart system because I use Upstarts events to start another app which depends on pgsql to be running. This is the tutorial/script I've used:

http://bradleyayers.blogspot.com/2011/10/upstart-job-for-postgresql-91-on-ubuntu.html

When I restart the server (shutdown -r now), postgresql isnt running (not visible as a job via 'top' command). I then tried running only the following command manually:

root@server:~# exec su -c "/usr/lib/postgresql/9.1/bin/postgres -D /var/lib/postgresql/9.1/main -c config_file=/etc/postgresql/9.1/main/postgresql.conf" postgres

And my ssh session simply disconects not returning anything. If I reconnect and again check running jobs, pgsql is still not running. So I tried running the command without 'exec' and here is the response:

root@server:~# su -c "/usr/lib/postgresql/9.1/bin/postgres -D /var/lib/postgresql/9.1/main -c config_file=/etc/postgresql/9.1/main/postgresql.conf" postgres

2012-12-03 19:31:36 MSK FATAL:  could not create lock file "/var/run/postgresql/.s.PGSQL.5432.lock": No such file or directory

I assume the problem is related to postgresql itself not upstart system. I suppose the file it mentions should exist so it can be accessed but it doesn't for some reason. did someone else stumble upon this, or has a potential solution to this?

Answer 1

I had the same desire to configure pg this way. For me, I wanted multiple clusters, each with their own independent scheduler (pgagent). When I shut down an individual cluster pgagent will stop automatically, but when I start a cluster I want pgagent to start automatically for that cluster also. If I forget to start the scheduler when I start a cluster, I'm in trouble.

I had Googled around, but never found a good solution to running PostgreSQL under Upstart. Most of the solutions explicitly started the postmaster instead of using the pg_wrapper commands. With the way Upstart works, this seems dangerous and could result in data loss in rare situations.

Thus, I forged ahead and tried to create my own Upstart scripts that would do the job. I found that it was very difficult to capture the correct PID of both the cluster and its pgagent instance. Eventually however, I realized that with PostgreSQL, you don't actually care about PIDs. You care about versions and clusters. Once I realized that, it all came together, and I created the following three scripts:

The first I call pg_versions.conf.

description "PostgreSQL Version Controller"
author "Brian Myers"

start on runlevel [2345]
stop on runlevel [016]

env DEFAULT_VERSIONS="9.3"

pre-start script
  if [ -z $VERSIONS ]; then
    VERSIONS=$DEFAULT_VERSIONS
  fi
  for version in $VERSIONS 
  do
    for cluster in $(pg_lsclusters -h | grep $version | cut -d" " -f 2) 
    do
      if [ `tail -1 /etc/postgresql/$version/$cluster/start.conf` = "auto" ]; then
        start pg_cluster version=$version cluster=$cluster
      fi
    done
  done
end script

post-stop script
  if [ -z $VERSIONS ]; then
    VERSIONS=$DEFAULT_VERSIONS
  fi
  for version in $VERSIONS 
  do
    for cluster in $(pg_lsclusters -h | grep $version | cut -d" " -f 2) 
    do
      stop pg_cluster version=$version cluster=$cluster
    done
  done
end script

Next is pg_cluster.conf.

description "PostgreSQL Cluster Controller"
author "Brian Myers"

instance $version-$cluster

pre-start script
  if [ `pg_lsclusters -h | grep $version | grep $cluster | cut -d" " -f 4` = "down" ]; then
    pg_ctlcluster $version $cluster start || :
    start pg_agent version=$version cluster=$cluster || :
  fi
end script

post-stop script
  if [ -e "/var/run/postgresql/pgagent-$version-$cluster.pid" ]; then
    stop pg_agent version=$version cluster=$cluster
  fi
  if [ `pg_lsclusters -h | grep $version | grep $cluster | cut -d" " -f 4` = "online" ]; then
    pg_ctlcluster $version $cluster stop
  fi
end script

And finally pg_agent.conf.

description "PgAgent Controller"
author "Brian Myers"

instance ${version}-${cluster}

setuid postgres

pre-start script
  PORT=`pg_lsclusters -h | grep $version | grep $cluster | cut -d" " -f 3`
  if [ -z `psql -c "select schema_name FROM information_schema.schemata WHERE schema_name = 'pgagent';" -d postgres -p $PORT | grep pgagent` ]; then
    stop ; exit 0
  fi
  PGAGENTDIR=`which pgagent`
  PGAGENTOPTIONS="host=/var/run/postgresql dbname=postgres user=postgres port=$PORT"
  start-stop-daemon --start --oknodo --name "pga$version$cluster" --exec $PGAGENTDIR -- $PGAGENTOPTIONS
  pgrep -f "$PGAGENTDIR.+$PORT" > /var/run/postgresql/pgagent-$version-$cluster.pid
end script

post-stop script
  start-stop-daemon --stop --oknodo --pidfile /var/run/postgresql/pgagent-$version-$cluster.pid
  if [ -w /var/run/postgresql/pgagent-$version-$cluster.pid ]; then
    rm -f /var/run/postgresql/pgagent-$version-$cluster.pid
  fi
end script

If you want more than just the 9.3 version, just add the versions to the env DEFAULT_VERSIONS="9.3" line separated by spaces.

With these, I can:

Start all clusters not already running: sudo initctl start pg_versions

Start all clusters for a particular version not already running: sudo initctl start pg_versions version=9.3

Start a particular cluster, auto-starting pgagent for that cluster, but only if the cluster is pgagent enabled: sudo initctl start pg_cluster version=9.3 cluster=main

Start a cluster's pgagent if the cluster is pgagent enabled: sudo initctl start pg_agent version=9.3 cluster=main

Change start to stop to get the inverse behavior. Of course everything starts up on boot and shuts down on stop via pg_ctlcluster, so no data loss. I did have to disable the init.d scripts via bum.

I'm sure these could be cleaned up or done in even better ways. The pg_agent script for example -- I could never figure out why using script or exec couldn't capture the correct PID. Eventually I gave up and managed the pid file myself, but it's still a mystery. It was probably my very soft shell scripting skills.

Note also that if you shut down clusters manually with pg_ctlcluster these Upstart jobs will still show as running even though the associated versions/clusters are not. Not a big deal because you can just restart them with either pg_ctlcluster or initctl, but for that reason I suggest using initctl to control your clusters if you deploy these jobs.

In any case, these work quite well for me.

Answer 2

Error means Postgres cannot create its lock file into /var/run/postgresql directory. The pre-script is expected to create it, and it is setting ownership to postgres. To me, it just looks like this script has not run at all. So check the output of start postgres (as superuser), existence and permissions ls -l /var/run/postgresql.

FYI: exec is useful in Upstart jobs so the script section running a shell script does not leave an additional PID floating around. In a shell session, it makes your shell quit when executed program exits.