Not being able to connect to the server using postgresql 11 with rails

Question

I am getting:

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?

Yesterday I had this problem and now running into this one I tried solving it guiding myself with this post, so far I am getting this issues:

$ pg_lsclusters
Ver Cluster Port Status Owner    Data directory              Log file
11  main    5432 down   postgres /var/lib/postgresql/11/main /var/log/postgresql/postgresql-11-main.log

going into the log file ( /var/log/postgresql/postgresql-11-main.log ) I found:

2019-09-18 15:26:41.172 CEST [17215] social@postgres DETAIL:  Connection matched pg_hba.conf line 90: "local   all             all                                     peer"
2019-09-18 23:01:36.723 CEST [11076] LOG:  received fast shutdown request
2019-09-18 23:01:36.728 CEST [11076] LOG:  aborting any active transactions

Trying to access Postgresql will prompt me:

sudo su - postgres
postgres@sampc:~$ 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"?

Trying to start the server I get:

$ sudo systemctl start postgresql@11-main
Job for postgresql@11-main.service failed because the service did not take the steps required by its unit configuration.
See "systemctl status postgresql@11-main.service" and "journalctl -xe" for details.

status:

 $service postgresql status
● postgresql.service - PostgreSQL RDBMS
   Loaded: loaded (/lib/systemd/system/postgresql.service; enabled; vendor preset: enabled)
   Active: active (exited) since Thu 2019-09-19 13:30:51 CEST; 58min ago
  Process: 26154 ExecStart=/bin/true (code=exited, status=0/SUCCESS)
 Main PID: 26154 (code=exited, status=0/SUCCESS)

sep 19 13:30:51 sampc systemd[1]: Starting PostgreSQL RDBMS...
sep 19 13:30:51 sampc systemd[1]: Started PostgreSQL RDBMS.

trying systemctl status postgresql@11-main.service

sep 19 15:13:38 sampc systemd[1]: postgresql@11-main.service: Can't open PID file /run/postgresql/11-main.pid (yet
sep 19 15:13:38 sampc systemd[1]: postgresql@11-main.service: Failed with result 'protocol'.
sep 19 15:13:38 sampc systemd[1]: Failed to start PostgreSQL Cluster 11-main.

journalctl -xe

sep 19 16:35:01 sampc CRON[6123]: pam_unix(cron:session): session closed for user root
sep 19 16:35:47 sampc gvfsd-metadata[1657]: g_udev_device_has_property: assertion 'G_UDEV_IS_DEVICE (device)' failed
sep 19 16:35:47 sampc gvfsd-metadata[1657]: g_udev_device_has_property: assertion 'G_UDEV_IS_DEVICE (device)' failed
sep 19 16:35:47 sampc gvfsd-metadata[1657]: g_udev_device_has_property: assertion 'G_UDEV_IS_DEVICE (device)' failed
sep 19 16:35:47 sampc gvfsd-metadata[1657]: g_udev_device_has_property: assertion 'G_UDEV_IS_DEVICE (device)' failed
sep 19 16:35:47 sampc gvfsd-metadata[1657]: g_udev_device_has_property: assertion 'G_UDEV_IS_DEVICE (device)' failed
sep 19 16:35:47 sampc gvfsd-metadata[1657]: g_udev_device_has_property: assertion 'G_UDEV_IS_DEVICE (device)' failed
sep 19 16:35:47 sampc gvfsd-metadata[1657]: g_udev_device_has_property: assertion 'G_UDEV_IS_DEVICE (device)' failed
sep 19 16:35:47 sampc gvfsd-metadata[1657]: g_udev_device_has_property: assertion 'G_UDEV_IS_DEVICE (device)' failed
sep 19 16:36:19 sampc nm-applet[1553]: gtk_widget_destroy: assertion 'GTK_IS_WIDGET (widget)' failed
sep 19 16:36:19 sampc nm-applet[1553]: gtk_widget_destroy: assertion 'GTK_IS_WIDGET (widget)' failed
sep 19 16:36:19 sampc nm-applet[1553]: Can't set a parent on widget which has a parent
sep 19 16:37:10 sampc nm-applet[1553]: gtk_widget_destroy: assertion 'GTK_IS_WIDGET (widget)' failed
sep 19 16:37:10 sampc nm-applet[1553]: gtk_widget_destroy: assertion 'GTK_IS_WIDGET (widget)' failed
sep 19 16:37:10 sampc nm-applet[1553]: Can't set a parent on widget which has a parent
sep 19 16:37:10 sampc nm-applet[1553]: gtk_widget_destroy: assertion 'GTK_IS_WIDGET (widget)' failed
sep 19 16:37:10 sampc nm-applet[1553]: gtk_widget_destroy: assertion 'GTK_IS_WIDGET (widget)' failed
sep 19 16:37:10 sampc nm-applet[1553]: Can't set a parent on widget which has a parent
sep 19 16:37:20 sampc sudo[6404]:   samuel : TTY=pts/4 ; PWD=/var/lib/postgresql/11 ; USER=root ; COMMAND=/bin/chmod -R 777 main/
sep 19 16:37:20 sampc sudo[6404]: pam_unix(sudo:session): session opened for user root by (uid=0)
sep 19 16:37:20 sampc sudo[6404]: pam_unix(sudo:session): session closed for user root
sep 19 16:38:21 sampc xfce4-terminal[2333]: gdk_window_get_window_type: assertion 'GDK_IS_WINDOW (window)' failed
sep 19 16:38:38 sampc /usr/lib/gdm3/gdm-x-session[1316]: [3303:3303:0919/163838.578602:ERROR:buffer_manager.cc(488)] [.DisplayCompositor]GL ERROR :GL_INVALID_OPERATION : glBufferData: <- error

You need to do as suggested by systemctl: _See "systemctl status postgresql@11-main.service" and "journalctl -xe" for details_ to know why the service doesn't start. — Daniel Vérité, Sep 19 '19 at 12:35
Hi, I have updated with that prompt, sorry I did not add it before. — Gotey, Sep 19 '19 at 13:17
`journalctl -xe` is also needed for more details on that error on the PID file. — Daniel Vérité, Sep 19 '19 at 14:08
I couldn't locate the pid file by the way (With: locate 11-main.pid) — Gotey, Sep 19 '19 at 14:17
The `journalctl -xe` output should be captured immediately after the failed attempt to start, otherwise it has only unrelated contents. Although this `/var/cache/samba: Permission denied` makes you suspect that the permissions on /var have been tightened up exaggeratly, and that would be a problem for Postgres, too. — Daniel Vérité, Sep 19 '19 at 14:33
I changed the permissions to all the subfolders in /var/lib/postgresql/11/main/ with sudo chmod -R 777 main/ . Same problem. Updated the journalctl -xe prompt with recent results after attempting to access the rails application again — Gotey, Sep 19 '19 at 14:39
uninstalling postgresql and re-installing ``` sudo systemctl start postgresql@11-main ``` worked this time, and it's now running. I am not sure about what was causing the problem since I did not touch anything ever since the moment it started running yesterday. — Gotey, Sep 19 '19 at 15:35
`Can't open PID file /run/postgresql/11-main.pid`: does `/run/postgresql` exist and have the proper permissions so the postgres user can write to it? — Schwern, Sep 19 '19 at 17:00

score 1 · Accepted Answer · answered Dec 03 '20 at 07:30

I faced the same issue. Actually while installing postgres user dont have permission to create files into /var/lib and /var/log so some files are missing. Thats why you are facing issue.

Try to change permission as

chmod 755 /var
chmod 755 /var/lib
chmod 755 /var/log

Then install postgresql again.

Not being able to connect to the server using postgresql 11 with rails

1 Answers1