Not really sure what caused this but most likely exiting the terminal while my rails server which was connected to PostgreSQL database was closed (not a good practice I know but lesson learned!)

I've already tried the following:

1. Rebooting my machine (using MBA M1 2020)
2. Restarting PostgreSQL using homebrew brew services restart postgresql
3. Re-installing PostgreSQL using Homebrew
4. Updating PostgreSQL using Homebrew
5. I also tried following this link but when I run cd Library/Application\ Support/Postgres terminal tells me Postgres folder doesn't exist, so I'm kind of lost already. Although I have a feeling that deleting postmaster.pid would really fix my issue. Any help would be appreciated!

CodePudding user response：

it appears that the upgrade really messed up my postgres since per Nagev's answer it's listening to port 5433 instead of 5432. I downgraded to v13.3 to fix this issue.

brew uninstall postgresql
brew install postgresql@13
brew services start postgresql@13
brew link postgresql@13 --force

CodePudding user response：

Although I cannot provide an answer to your specific problem, I thought I'd share my troubleshooting steps, hoping that it might be of some help. It seems that you are on Mac, whereas I am running Ubuntu 21.04, so expect things to be different.

This is a client connection problem, as noted by section 19.3.2 in the docs.

The directory in my error message is different:

$ sudo su postgres -c "psql"
psql: error: connection to server on socket "/var/run/postgresql/.s.PGSQL.5432" failed: No such file or directory
        Is the server running locally and accepting connections on that socket?

I checked what unix sockets I had in that directory:

$ ls -lah /var/run/postgresql/
total 8.0K
drwxrwsr-x  4 postgres postgres  160 Oct 29 16:40 .
drwxr-xr-x 36 root     root     1.1K Oct 29 14:08 ..
drwxr-s---  2 postgres postgres   40 Oct 29 14:33 12-main.pg_stat_tmp
drwxr-s---  2 postgres postgres  120 Oct 29 16:59 14-main.pg_stat_tmp
-rw-r--r--  1 postgres postgres    6 Oct 29 16:36 14-main.pid
srwxrwxrwx  1 postgres postgres    0 Oct 29 16:36 .s.PGSQL.5433
-rw-------  1 postgres postgres   70 Oct 29 16:36 .s.PGSQL.5433.lock

Makes sense, there is a socket for 5433 not 5432. I confirmed this by running:

$ pg_lsclusters
Ver Cluster Port Status                Owner    Data directory              Log file
12  main    5432 down,binaries_missing postgres /var/lib/postgresql/12/main /var/log/postgresql/postgresql-12-main.log
14  main    5433 online                postgres /var/lib/postgresql/14/main /var/log/postgresql/postgresql-14-main.log

This explains how it got into this mess on my system. The default port is 5432, but after I upgraded from version 12 to 14, the server was setup to listen to 5432, presumably because it considered 5432 as already taken. Two alternatives here, get the server to listen on 5432 which is the client's default, or get the client to use 5433.

Let's try it by changing the client's parameters:

$ sudo su postgres -c "psql --port=5433"
psql (14.0 (Ubuntu 14.0-1.pgdg21.04 1))
Type "help" for help.

postgres=#

It worked! Now, to make it permanent I'm supposed to put this setting on a psqlrc or ~/.psqlrc file. The thin documentation on this (under "Files") was not helpful to me as I was not sure on the syntax and my attempts did not change the client's default, so I moved on.

To change the server I looked for the postgresql.conf mentioned in the documentation but could not find the file. I did however see /var/lib/postgresql/14/main/postgresql.auto.conf so I created it on the same directory with the content:

port = 5432

Restarted the server: sudo systemctl restart postgresql

But the error persisted because, as the logs confirmed, the port did not change:

$ tail /var/log/postgresql/postgresql-14-main.log
...
2021-10-29 16:36:12.195 UTC [25236] LOG:  listening on IPv4 address "127.0.0.1", port 5433
2021-10-29 16:36:12.198 UTC [25236] LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5433"
2021-10-29 16:36:12.204 UTC [25237] LOG:  database system was shut down at 2021-10-29 16:36:12 UTC
2021-10-29 16:36:12.210 UTC [25236] LOG:  database system is ready to accept connections

After other attempts did not succeed, I eventually decided to use a workaround: to redirect the client's requests on 5432 to 5433:

ln -s /var/run/postgresql/.s.PGSQL.5433 /var/run/postgresql/.s.PGSQL.5432

This is what I have now:

$ ls -lah /var/run/postgresql/
total 8.0K
drwxrwsr-x  4 postgres postgres  160 Oct 29 16:40 .
drwxr-xr-x 36 root     root     1.1K Oct 29 14:08 ..
drwxr-s---  2 postgres postgres   40 Oct 29 14:33 12-main.pg_stat_tmp
drwxr-s---  2 postgres postgres  120 Oct 29 16:59 14-main.pg_stat_tmp
-rw-r--r--  1 postgres postgres    6 Oct 29 16:36 14-main.pid
lrwxrwxrwx  1 postgres postgres   33 Oct 29 16:40 .s.PGSQL.5432 -> /var/run/postgresql/.s.PGSQL.5433
srwxrwxrwx  1 postgres postgres    0 Oct 29 16:36 .s.PGSQL.5433
-rw-------  1 postgres postgres   70 Oct 29 16:36 .s.PGSQL.5433.lock

This means I can now just run psql without having to explicitly set the port to 5433. Now, this is a hack and I would not recommend it. But in my development system I am happy with it for now, because I don't have more time to spend on this. This is why I shared the steps and the links so that you can find a proper solution for your case.