Error starting Postgres

Question

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?

Андрій Лецкалюк · Accepted Answer · 2021-11-26 13:01:22Z

5

make-ssl-cert generate-default-snakeoil --force-overwrite

When I'd rewrited certificates I started to have access to postgresql. And after all - reason why was very simple (when you reload server Ubuntu 18.04. in my case) you're losing some default settings. Be careful. Thanks a lot of @AndrewSavinykh for help. Дякую теска))).

answered Nov 26, 2021 at 13:01

Андрій Лецкалюк

511 silver badge3 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Iharob Al Asimi · Accepted Answer · 2015-07-15 11:44:50Z

1

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

edited Jul 15, 2015 at 11:44

Iharob Al Asimi

53.1k6 gold badges66 silver badges100 bronze badges

answered Jul 15, 2015 at 11:40

user2761030

1,5052 gold badges21 silver badges35 bronze badges

3 Comments

user2761030 Over a year ago

Thanks @iharob - can only do this after 2 days!

Andrew Savinykh Over a year ago

My /var/lib/postgresql/9.1/main does not have server.crt and server.key. In fact they are not to be found anywhere on my whole hard drive (

BANJOSA Over a year ago

@AndrewSavinykh is you still have the issue you can re-generate the certs using the following command make-ssl-cert generate-default-snakeoil --force-overwrite Had the same issue and this solved it.

Kfeina · Accepted Answer · 2020-08-26 09:36:29Z

0

In my case, some ubuntu/postgres upgrade changed the permissions of the file to 600.

Changing it to 644 solved the problem and I could start the postgre daemon.

chmod 644 /etc/ssl/certs7ssl-cert-snakeoil.pem

answered Aug 26, 2020 at 9:36

Kfeina

391 bronze badge

Collectives™ on Stack Overflow

Error starting Postgres

3 Answers 3

Comments

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related