PostgreSQL is the world’s most advanced SQL open source database. It contains comparable enterprise class features such as full ACID compliance and Multi-Version Concurrency Control for supporting high concurrent loads. And it supports standards such as ANSI SQL and SQL/MED (including foreign data wrappers for Oracle, MySQL, MongoDB and many others) and yet is highly extensible with support for over 12 procedural languages, GIN and GIST Indexes, Spatial data support, and multiple NoSQL like features for document (JSON) or key-value based applications.
PostgreSQL common problems and solutions
In this post we will write about some PostgreSQL common problems and solutions which most of the developers or server admins face frequently.
# Problem 1
database system was interrupted; last known up at xxxx-xx-xx yy:yy:yy
EEST FATAL: could not open file "/etc/ssl/certs/ssl-cert-snakeoil.pem": Permission denied
startup process (PID 11172) exited with exit code 1
aborting startup due to startup process failure
This is a Fsync Permissions Bug of PostgreSQL.
The steps listed in the article fixed the problem:
# go to PGDATA directory
# 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
# 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