I've got a strange error on a live site (as well as it's corresponding dev site) where the Postgres database does not want to start. This issue has occured suddenly, and I don't know what could have caused it.
Attempting to start in terminal gives the dreaded 'not accepting 5432 connections'
sudo -u postgres psql
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"?
If I try restarting postgres:
service postgresql restart
* Restarting PostgreSQL 9.1 database server * The PostgreSQL server failed to start. Please check the log output:
2015-07-15 13:49:24 EEST LOG: database system was interrupted; last known up at 2015-07-12 20:02:18 EEST
2015-07-15 13:49:24 EEST FATAL: could not open file "/etc/ssl/certs/ssl-cert-snakeoil.pem": Permission denied
2015-07-15 13:49:24 EEST LOG: startup process (PID 11172) exited with exit code 1
2015-07-15 13:49:24 EEST LOG: aborting startup due to startup process failure
Any ideas on what to try next, or what could have caused this?
Turns out this was related to an fsync permissions bug in an update, which was triggered when our VPS provider needed to reboot following another issue.
The steps listed in the article fixed the problem:
(as root)
# go to PGDATA directory
cd /var/lib/postgresql/9.1/main
# Backup server.crt server.key just to be sure!
cp server.crt server.crt.bk
cp server.key server.key.bk
ls -l server.crt server.key
# confirm both of those files are symbolic links
# to files in /etc/ssl before going further
# remove symlinks to SSL certs
rm server.crt
rm server.key
# copy the SSL certs to the local directory
cp /etc/ssl/certs/ssl-cert-snakeoil.pem server.crt
cp /etc/ssl/private/ssl-cert-snakeoil.key server.key
# set permissions on ssl certs
# and postgres ownership on everything else
# just in case
chown postgres *
chmod 640 server.crt server.key
service postgresql start