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?
Best Answer
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.
Next is pg_cluster.conf.
And finally pg_agent.conf.
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 eitherpg_ctlcluster
orinitctl
, but for that reason I suggest usinginitctl
to control your clusters if you deploy these jobs.In any case, these work quite well for me.