/var/run/postgresql/.s.PGSQL.5432 with a Virtual “var” Folder

Well if you’re reading this you’ve likely run into a problem with accessing your postgresql DB.  Whatever your situation this information should be useful for you. In my case I’m using it with Rails.  And this is the Rails error I received:

PG::ConnectionBad

Just so you know.  This is not a Rails related problem.  It is postgresql related.  I happen to work on a netbook with a 8GB SSD drive built in (first of it’s kind).  Seeing as how space is limited and SSD’s do wear out after so much use I’ve taken proactive measures to protect my drive.  So may /var and /tmp directory are virtual drives and stay in memory. Note: the info I will provide should help you regardless. But if you want more context of other kinds of system state that cause this problem see this forum thread http://ubuntuforums.org/showthread.php?t=869080 Now any system command such as ‘psql’ would produce the same kind of error

psql: could not connect to server: No such file or directory
         Is the server running locally and accepting
         connections on Unix domain socket "/var/run/postgresql/.s.PGSQL.5432"?

The command that will inform you of your troubles is /etc/init.d/postgresql

Usage: /etc/init.d/postgresql {start|stop|restart|reload|force-reload|status} [version ..]

So I tried it with the status option and got Running clusters:.  Then I tried the force-reload option and got this error:

Reloading PostgreSQL 9.1 database server: mainError: Could not create log file /var/log/postgresql/postgresql-9.1-main.log ... failed!
failed!

So to fix this I had to do the following:

sudo mkdir /var/log/postgresql
sudo chown postgres.postgres /var/log/postgresql
sudo su postgres
touch /var/log/postgresql/postgresql-9.1-main.log
/etc/init.d/postgresql stop
/etc/init.d/postgresql start

Note: you need to start the service as the postgres user, which is why the sudo su postgres command was included.  After this everything works! ^_^  Of course since this is only a temporary fix what needs to be done is to add the folder creation of /var/log/postgresql to the bootup script as well as creating the file postgresql-9.1-main.log with postgres ownership.  This should all be done before the service starts on bootup. Most of you won’t have my particular case of the var directory being a virtual drive and wiped at every reboot.  But this info is still useful.  Use the /etc/init.d/postgresql command for figuring out what’s going on.

And NOW this is how I modified my system startup to have my system work each reboot.

cd /usr/share/postgresql-common
sudo pico init.d-function

pico: Is a command line text editor, originally called pine from the alpine package.  It is also aliased with nano.  You can use any text editor, just make sure you sudo.  Go down to the line that has start() { and on the first line after that enter the following:

if [ -d "/var/log/postgresql"]; then
  chmod 2775 /var/log/postgresql
else
  mkdir -m 2775 -p /var/log/postgresql
  touch /var/log/postgresql/postgresql-9.1-main.log
  chmod 2666 /var/log/postgresql/postgresql-9.1-main.log
fi

And now every time your system boots up the appropriate log file will be there and PostgreSQL works.  ^_^

Please comment, share, subscribe to my RSS Feed,and follow me on twitter @6ftdan!

God Bless! – Daniel P. Clark