0

I'm using Ubuntu 16.04 with Postgres 9.5

I was playing around with the settings in my postgres.conf and now my website is a getting 500 error with the following log:

OperationalError at /
could not connect to server: Connection refused
    Is the server running on host "localhost" (127.0.0.1) and accepting
    TCP/IP connections on port 5432?

When I run sudo -u postgres psql, I get the following 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"?

I'm getting a similar error to this question, which has no answer. postmaster.pid does not exist for me.

The last bit of my Postgres server logs are this:

2018-02-20 17:39:12 UTC [1558-2] LOG:  autovacuum launcher shutting down
2018-02-20 17:39:12 UTC [1546-2] LOG:  received smart shutdown request
2018-02-20 17:39:12 UTC [1989-1] postgres@postgres FATAL:  terminating connection due to administrator command
2018-02-20 17:39:12 UTC [1971-1] postgres@postgres FATAL:  terminating connection due to administrator command
2018-02-20 17:39:12 UTC [1555-1] LOG:  shutting down
2018-02-20 17:39:12 UTC [1555-2] LOG:  database system is shut down

Here is the log after doing sudo systemctl status postgresql:

● postgresql.service - PostgreSQL RDBMS
   Loaded: loaded (/lib/systemd/system/postgresql.service; enabled; vendor prese
   Active: active (exited) since Tue 2018-02-20 18:25:22 UTC; 23min ago
 Main PID: 1766 (code=exited, status=0/SUCCESS)
    Tasks: 0
   Memory: 0B
      CPU: 0
   CGroup: /system.slice/postgresql.service

Feb 20 18:25:22 ubuntu-bungol-v0 systemd[1]: Starting PostgreSQL RDBMS...
Feb 20 18:25:22 ubuntu-bungol-v0 systemd[1]: Started PostgreSQL RDBMS.
Feb 20 18:47:57 ubuntu-bungol-v0 systemd[1]: Started PostgreSQL RDBMS.
Feb 20 18:48:15 ubuntu-bungol-v0 systemd[1]: Started PostgreSQL RDBMS.

sudo systemctl restart postgres doesn't work. Neither does sudo systemctl start postgres or sudo systemctl enable postgres

I'm out of ideas. Anyone can offer some solutions?

Edit:

Result of grep -Ev '^$|^\s*#' postgresql.conf and systemctl status postgresql@9.5-main.service:

grep: postgresql.conf: No such file or directory
grep: and: No such file or directory
grep: systemctl: No such file or directory
grep: status: No such file or directory
grep: postgresql@9.5-main.service: No such file or directory

Result of sudo ps aux |grep postgres:

root      1728  0.0  0.3  51420  3932 pts/0    T    18:22   0:00 sudo systemctl status postgresql
root      1729  0.0  0.3  24888  3316 pts/0    T    18:22   0:00 systemctl status postgresql
root      1733  0.0  0.3  51420  3760 pts/0    T    18:24   0:00 sudo systemctl status postgresql
root      1734  0.0  0.3  24888  3372 pts/0    T    18:24   0:00 systemctl status postgresql
valachio  1782  0.0  0.2  13112  2912 pts/0    T    18:28   0:00 nano /usr/local/var/log/postgres.log
valachio  1783  0.0  0.2  13112  2940 pts/0    T    18:28   0:00 nano /usr/local/var/postgres/server.log
root      2058  0.0  0.3  51420  3840 pts/0    T    19:35   0:00 sudo systemctl status postgresql@9.5-main.service
root      2059  0.0  0.3  24888  3368 pts/0    T    19:35   0:00 systemctl status postgresql@9.5-main.service
valachio  2225  0.0  0.0  12944   924 pts/0    R+   19:43   0:00 grep --color=auto postgres

Result of sudo ls -la /var/run/postgresql/:

total 0
drwxrwsr-x  3 postgres postgres  60 Feb 20 19:41 .
drwxr-xr-x 25 root     root     940 Feb 20 19:43 ..
drwxr-s---  2 postgres postgres  40 Feb 20 19:41 9.5-main.pg_stat_tmp

Result of grep -Ev '^$|^\s*#' /etc/postgresql/9.5/main/postgresql.conf

data_directory = '/var/lib/postgresql/9.5/main'         # use data in another directory
hba_file = '/etc/postgresql/9.5/main/pg_hba.conf'       # host-based authentication file
ident_file = '/etc/postgresql/9.5/main/pg_ident.conf'   # ident configuration file
external_pid_file = '/var/run/postgresql/9.5-main.pid'                  # write an extra PID file
port = 5432                             # (change requires restart)
max_connections = 100                   # (change requires restart)
unix_socket_directories = '/var/run/postgresql' # comma-separated list of directories
ssl = true                              # (change requires restart)
ssl_cert_file = '/etc/ssl/certs/ssl-cert-snakeoil.pem'          # (change requires restart)
ssl_key_file = '/etc/ssl/private/ssl-cert-snakeoil.key'         # (change requires restart)
shared_buffers = 256MB                  # min 128kB
dynamic_shared_memory_type = posix      # the default is the first option
log_line_prefix = '%t [%p-%l] %q%u@%d '                 # special values:
log_timezone = 'UTC'
stats_temp_directory = '/var/run/postgresql/9.5-main.pg_stat_tmp'
datestyle = 'iso, mdy'
timezone = 'UTC'
lc_messages = 'en_US.UTF-8'                     # locale for system error message
lc_monetary = 'en_US.UTF-8'                     # locale for monetary formatting
lc_numeric = 'en_US.UTF-8'                      # locale for number formatting
lc_time = 'en_US.UTF-8'                         # locale for time formatting
default_text_search_config = 'pg_catalog.english'

Result of sudo systemctl status postgresql@9.5-main.service (The log output stayed the same as before):

● postgresql@9.5-main.service - PostgreSQL Cluster 9.5-main
   Loaded: loaded (/lib/systemd/system/postgresql@.service; disabled; vendor preset: enabled)
   Active: failed (Result: exit-code) since Tue 2018-02-20 19:42:11 UTC; 5min ago
  Process: 2135 ExecStart=postgresql@%i --skip-systemctl-redirect %i start (code=exited, status=1/FAILURE)

Feb 20 19:41:41 ubuntu-bungol-v0 systemd[1]: Starting PostgreSQL Cluster 9.5-main...
Feb 20 19:42:11 ubuntu-bungol-v0 postgresql@9.5-main[2135]: The PostgreSQL server failed to start. Please check the log output.
Feb 20 19:42:11 ubuntu-bungol-v0 systemd[1]: postgresql@9.5-main.service: Control process exited, code=exited status=1
Feb 20 19:42:11 ubuntu-bungol-v0 systemd[1]: Failed to start PostgreSQL Cluster 9.5-main.
Feb 20 19:42:11 ubuntu-bungol-v0 systemd[1]: postgresql@9.5-main.service: Unit entered failed state.
Feb 20 19:42:11 ubuntu-bungol-v0 systemd[1]: postgresql@9.5-main.service: Failed with result 'exit-code'.
Jack
  • 417
  • 2
  • 5
  • 16
  • You must have made a typo in your changes to the configuration file. Do you have a backup of the file before you changed it? – Lucas Holt Feb 20 '18 at 18:48
  • @LucasHolt Unfortunately I was careless and did not. I tried to retrace the edits but the error remained. I don't think a typo is the issue, as I followed the instructions here - http://pgtune.leopard.in.ua/ – Jack Feb 20 '18 at 18:50
  • Hi Jack, if it is a configuration error you should see additional log messages in the /var/log/postgresql/postgresql-X.X-main.log like "LOG: unrecognized configuration parameter "shba_file" in file "/etc/postgresql/9.5/main/postgresql.conf" line 43 FATAL: configuration file "/etc/postgresql/9.5/main/postgresql.conf" contains errors". Additionally you could check if the postgresql process is running and opened any ports by executing the following bash command: sudo netstat -pant | grep "LISTEN" | grep postgres . Maybe you just changed the standard postgresql port. – quantumbyte Feb 20 '18 at 19:14
  • Hi, could you please add output of grep -Ev '^$|^\s*#' postgresql.conf and systemctl status postgresql@9.5-main.service – samed.yildirim Feb 20 '18 at 19:16
  • @quantumbyte I do not see any errors like that in my server error log file. So I guess it is not a configuration error? I tried `sudo netstat -pant | grep "LISTEN" | grep postgres`, but nothing happened. The command was submitted and that's it, similar to what happens when you do `sudo systemctl restart postgresql` – Jack Feb 20 '18 at 19:21
  • @samed.yildirim That command returned a series of `No such file or directory`. I added full response in the question. – Jack Feb 20 '18 at 19:23
  • @Jack: Ok the next 2 steps are checking for postgresql processes and checking for pid files: First `sudo ps aux |grep postgres` Second `sudo ls -la /var/run/postgresql/` – quantumbyte Feb 20 '18 at 19:26
  • 1
    @Jack, There is two different command you should run. First command: `grep -Ev '^$|^\s*#' /etc/postgresql/9.5/main/postgresql.conf` Second command: `sudo systemctl status postgresql@9.5-main.service` – samed.yildirim Feb 20 '18 at 19:30
  • @quantumbyte Okay I added the response of those 2 commands. What should I be looking at? – Jack Feb 20 '18 at 19:49
  • 1
    @samed.yildirim I added the response from those 2 commands. `sudo systemctl status postgresql@9.5-main.service` was initially giving me `Config owner (valachio:1000) and data owner (postgres:116) do not match, and config owner is not root`. I fixed that by changing the file owner. Now I'm getting another error. – Jack Feb 20 '18 at 19:50
  • @Jack: So if the postgresql server would run you would see processes like "postgres: 9.5/main: checkpointer process" in the first output. Also the folder with the pid files is empty (second ouput). This means your problem is not due to a left over pid file. – quantumbyte Feb 20 '18 at 20:14
  • @Jack Based on your answer to @samed.yildirim you had a issue that the postgresql config did have the wrong owner/permission. Just for comparison my config file permissions look like this (command) `ls -la /etc/postgresql/9.5/main/postgresql.conf ` (result) `-rw-r--r-- 1 postgres postgres 21460 Feb 20 19:11 /etc/postgresql/9.5/main/postgresql.conf`. Are there any new messages in the log file now if you do `systemctl start postgresql` and after you fixed the permission issue? – quantumbyte Feb 20 '18 at 20:15
  • 1
    I just did rebooted my Ubuntu server and things are working now. The issue was the fact that I changed the owner of `postgresql.conf` to my user. After I changed the owner back to `postgres`, and did a server restart, it worked. @samed.yildirim your `sudo systemctl status postgresql@9.5-main.service` command suggestion was key to finding the problem. Maybe write an answer down and I can mark it – Jack Feb 20 '18 at 20:16
  • @quantumbyte Yes it was the owner permission problem. After changing the permissions, I did `sudo systemctl restart postgresql` and that didn't work. However after a complete server restart, things are working fine now. Thank you! – Jack Feb 20 '18 at 20:17
  • Great that it works again. I upvoted the 3 posts which cotain the important stuff. If @samed.yildirim could write up an answer that would be great. – quantumbyte Feb 20 '18 at 20:52

0 Answers0