Sign in with
By continuing you indicate that you have read and agree to our Terms of service and Privacy policy
Sign in with
By continuing you indicate that you have read and agree to our Terms of service and Privacy policy
Explore all SQL Database open source software, libraries, packages, source code, cloud functions and APIs.
Popular Libraries in SQL Database
Trending New libraries in SQL Database
Top Authors in SQL Database
1
38 Libraries
16502
2
17 Libraries
1083
3
13 Libraries
23980
4
12 Libraries
1375
5
11 Libraries
225
6
10 Libraries
174
7
8 Libraries
354
8
8 Libraries
1912
9
7 Libraries
1968
10
7 Libraries
681
1
38 Libraries
16502
2
17 Libraries
1083
3
13 Libraries
23980
4
12 Libraries
1375
5
11 Libraries
225
6
10 Libraries
174
7
8 Libraries
354
8
8 Libraries
1912
9
7 Libraries
1968
10
7 Libraries
681
Trending Kits in SQL Database
Trending Discussions on SQL Database
QUESTION
psql: error: connection to server on socket "/tmp/.s.PGSQL.5432" failed: No such file or directory
Asked 2022-Apr-04 at 15:46Not 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:
- Rebooting my machine (using MBA M1 2020)
- Restarting PostgreSQL using homebrew
brew services restart postgresql - Re-installing PostgreSQL using Homebrew
- Updating PostgreSQL using Homebrew
- I also tried following this link but when I run
cd Library/Application\ Support/Postgresterminal 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!
ANSWER
Answered 2022-Jan-13 at 15:19Resetting PostgreSQL
My original answer only included the troubleshooting steps below, and a workaround. I now decided to properly fix it via brute force by removing all clusters and reinstalling, since I didn't have any data there to keep. It was something along these lines, on my Ubuntu 21.04 system:
1sudo pg_dropcluster --stop 12 main
2sudo pg_dropcluster --stop 14 main
3sudo apt remove postgresql-14
4sudo apt purge postgresql*
5sudo apt install postgresql-14
6Now I have:
1sudo pg_dropcluster --stop 12 main
2sudo pg_dropcluster --stop 14 main
3sudo apt remove postgresql-14
4sudo apt purge postgresql*
5sudo apt install postgresql-14
6$ pg_lsclusters
7Ver Cluster Port Status Owner Data directory Log file
814 main 5432 online postgres /var/lib/postgresql/14/main /var/log/postgresql/postgresql-14-main.log
9And sudo -u postgres psql works fine. The service was started automatically but it can be done manually with sudo systemctl start postgresql.
Incidentally, I can recommend the PostgreSQL docker image, which eliminates the need to bother with a local installation.
Troubleshooting
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:
1sudo pg_dropcluster --stop 12 main
2sudo pg_dropcluster --stop 14 main
3sudo apt remove postgresql-14
4sudo apt purge postgresql*
5sudo apt install postgresql-14
6$ pg_lsclusters
7Ver Cluster Port Status Owner Data directory Log file
814 main 5432 online postgres /var/lib/postgresql/14/main /var/log/postgresql/postgresql-14-main.log
9$ sudo su postgres -c "psql"
10psql: error: connection to server on socket "/var/run/postgresql/.s.PGSQL.5432" failed: No such file or directory
11 Is the server running locally and accepting connections on that socket?
12I checked what unix sockets I had in that directory:
1sudo pg_dropcluster --stop 12 main
2sudo pg_dropcluster --stop 14 main
3sudo apt remove postgresql-14
4sudo apt purge postgresql*
5sudo apt install postgresql-14
6$ pg_lsclusters
7Ver Cluster Port Status Owner Data directory Log file
814 main 5432 online postgres /var/lib/postgresql/14/main /var/log/postgresql/postgresql-14-main.log
9$ sudo su postgres -c "psql"
10psql: error: connection to server on socket "/var/run/postgresql/.s.PGSQL.5432" failed: No such file or directory
11 Is the server running locally and accepting connections on that socket?
12$ ls -lah /var/run/postgresql/
13total 8.0K
14drwxrwsr-x 4 postgres postgres 160 Oct 29 16:40 .
15drwxr-xr-x 36 root root 1.1K Oct 29 14:08 ..
16drwxr-s--- 2 postgres postgres 40 Oct 29 14:33 12-main.pg_stat_tmp
17drwxr-s--- 2 postgres postgres 120 Oct 29 16:59 14-main.pg_stat_tmp
18-rw-r--r-- 1 postgres postgres 6 Oct 29 16:36 14-main.pid
19srwxrwxrwx 1 postgres postgres 0 Oct 29 16:36 .s.PGSQL.5433
20-rw------- 1 postgres postgres 70 Oct 29 16:36 .s.PGSQL.5433.lock
21Makes sense, there is a socket for 5433 not 5432. I confirmed this by running:
1sudo pg_dropcluster --stop 12 main
2sudo pg_dropcluster --stop 14 main
3sudo apt remove postgresql-14
4sudo apt purge postgresql*
5sudo apt install postgresql-14
6$ pg_lsclusters
7Ver Cluster Port Status Owner Data directory Log file
814 main 5432 online postgres /var/lib/postgresql/14/main /var/log/postgresql/postgresql-14-main.log
9$ sudo su postgres -c "psql"
10psql: error: connection to server on socket "/var/run/postgresql/.s.PGSQL.5432" failed: No such file or directory
11 Is the server running locally and accepting connections on that socket?
12$ ls -lah /var/run/postgresql/
13total 8.0K
14drwxrwsr-x 4 postgres postgres 160 Oct 29 16:40 .
15drwxr-xr-x 36 root root 1.1K Oct 29 14:08 ..
16drwxr-s--- 2 postgres postgres 40 Oct 29 14:33 12-main.pg_stat_tmp
17drwxr-s--- 2 postgres postgres 120 Oct 29 16:59 14-main.pg_stat_tmp
18-rw-r--r-- 1 postgres postgres 6 Oct 29 16:36 14-main.pid
19srwxrwxrwx 1 postgres postgres 0 Oct 29 16:36 .s.PGSQL.5433
20-rw------- 1 postgres postgres 70 Oct 29 16:36 .s.PGSQL.5433.lock
21$ pg_lsclusters
22Ver Cluster Port Status Owner Data directory Log file
2312 main 5432 down,binaries_missing postgres /var/lib/postgresql/12/main /var/log/postgresql/postgresql-12-main.log
2414 main 5433 online postgres /var/lib/postgresql/14/main /var/log/postgresql/postgresql-14-main.log
25This 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 5433, 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:
1sudo pg_dropcluster --stop 12 main
2sudo pg_dropcluster --stop 14 main
3sudo apt remove postgresql-14
4sudo apt purge postgresql*
5sudo apt install postgresql-14
6$ pg_lsclusters
7Ver Cluster Port Status Owner Data directory Log file
814 main 5432 online postgres /var/lib/postgresql/14/main /var/log/postgresql/postgresql-14-main.log
9$ sudo su postgres -c "psql"
10psql: error: connection to server on socket "/var/run/postgresql/.s.PGSQL.5432" failed: No such file or directory
11 Is the server running locally and accepting connections on that socket?
12$ ls -lah /var/run/postgresql/
13total 8.0K
14drwxrwsr-x 4 postgres postgres 160 Oct 29 16:40 .
15drwxr-xr-x 36 root root 1.1K Oct 29 14:08 ..
16drwxr-s--- 2 postgres postgres 40 Oct 29 14:33 12-main.pg_stat_tmp
17drwxr-s--- 2 postgres postgres 120 Oct 29 16:59 14-main.pg_stat_tmp
18-rw-r--r-- 1 postgres postgres 6 Oct 29 16:36 14-main.pid
19srwxrwxrwx 1 postgres postgres 0 Oct 29 16:36 .s.PGSQL.5433
20-rw------- 1 postgres postgres 70 Oct 29 16:36 .s.PGSQL.5433.lock
21$ pg_lsclusters
22Ver Cluster Port Status Owner Data directory Log file
2312 main 5432 down,binaries_missing postgres /var/lib/postgresql/12/main /var/log/postgresql/postgresql-12-main.log
2414 main 5433 online postgres /var/lib/postgresql/14/main /var/log/postgresql/postgresql-14-main.log
25$ sudo su postgres -c "psql --port=5433"
26psql (14.0 (Ubuntu 14.0-1.pgdg21.04+1))
27Type "help" for help.
28
29postgres=#
30It 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:
1sudo pg_dropcluster --stop 12 main
2sudo pg_dropcluster --stop 14 main
3sudo apt remove postgresql-14
4sudo apt purge postgresql*
5sudo apt install postgresql-14
6$ pg_lsclusters
7Ver Cluster Port Status Owner Data directory Log file
814 main 5432 online postgres /var/lib/postgresql/14/main /var/log/postgresql/postgresql-14-main.log
9$ sudo su postgres -c "psql"
10psql: error: connection to server on socket "/var/run/postgresql/.s.PGSQL.5432" failed: No such file or directory
11 Is the server running locally and accepting connections on that socket?
12$ ls -lah /var/run/postgresql/
13total 8.0K
14drwxrwsr-x 4 postgres postgres 160 Oct 29 16:40 .
15drwxr-xr-x 36 root root 1.1K Oct 29 14:08 ..
16drwxr-s--- 2 postgres postgres 40 Oct 29 14:33 12-main.pg_stat_tmp
17drwxr-s--- 2 postgres postgres 120 Oct 29 16:59 14-main.pg_stat_tmp
18-rw-r--r-- 1 postgres postgres 6 Oct 29 16:36 14-main.pid
19srwxrwxrwx 1 postgres postgres 0 Oct 29 16:36 .s.PGSQL.5433
20-rw------- 1 postgres postgres 70 Oct 29 16:36 .s.PGSQL.5433.lock
21$ pg_lsclusters
22Ver Cluster Port Status Owner Data directory Log file
2312 main 5432 down,binaries_missing postgres /var/lib/postgresql/12/main /var/log/postgresql/postgresql-12-main.log
2414 main 5433 online postgres /var/lib/postgresql/14/main /var/log/postgresql/postgresql-14-main.log
25$ sudo su postgres -c "psql --port=5433"
26psql (14.0 (Ubuntu 14.0-1.pgdg21.04+1))
27Type "help" for help.
28
29postgres=#
30port = 5432
31Restarted the server: sudo systemctl restart postgresql
But the error persisted because, as the logs confirmed, the port did not change:
1sudo pg_dropcluster --stop 12 main
2sudo pg_dropcluster --stop 14 main
3sudo apt remove postgresql-14
4sudo apt purge postgresql*
5sudo apt install postgresql-14
6$ pg_lsclusters
7Ver Cluster Port Status Owner Data directory Log file
814 main 5432 online postgres /var/lib/postgresql/14/main /var/log/postgresql/postgresql-14-main.log
9$ sudo su postgres -c "psql"
10psql: error: connection to server on socket "/var/run/postgresql/.s.PGSQL.5432" failed: No such file or directory
11 Is the server running locally and accepting connections on that socket?
12$ ls -lah /var/run/postgresql/
13total 8.0K
14drwxrwsr-x 4 postgres postgres 160 Oct 29 16:40 .
15drwxr-xr-x 36 root root 1.1K Oct 29 14:08 ..
16drwxr-s--- 2 postgres postgres 40 Oct 29 14:33 12-main.pg_stat_tmp
17drwxr-s--- 2 postgres postgres 120 Oct 29 16:59 14-main.pg_stat_tmp
18-rw-r--r-- 1 postgres postgres 6 Oct 29 16:36 14-main.pid
19srwxrwxrwx 1 postgres postgres 0 Oct 29 16:36 .s.PGSQL.5433
20-rw------- 1 postgres postgres 70 Oct 29 16:36 .s.PGSQL.5433.lock
21$ pg_lsclusters
22Ver Cluster Port Status Owner Data directory Log file
2312 main 5432 down,binaries_missing postgres /var/lib/postgresql/12/main /var/log/postgresql/postgresql-12-main.log
2414 main 5433 online postgres /var/lib/postgresql/14/main /var/log/postgresql/postgresql-14-main.log
25$ sudo su postgres -c "psql --port=5433"
26psql (14.0 (Ubuntu 14.0-1.pgdg21.04+1))
27Type "help" for help.
28
29postgres=#
30port = 5432
31$ tail /var/log/postgresql/postgresql-14-main.log
32...
332021-10-29 16:36:12.195 UTC [25236] LOG: listening on IPv4 address "127.0.0.1", port 5433
342021-10-29 16:36:12.198 UTC [25236] LOG: listening on Unix socket "/var/run/postgresql/.s.PGSQL.5433"
352021-10-29 16:36:12.204 UTC [25237] LOG: database system was shut down at 2021-10-29 16:36:12 UTC
362021-10-29 16:36:12.210 UTC [25236] LOG: database system is ready to accept connections
37After other attempts did not succeed, I eventually decided to use a workaround: to redirect the client's requests on 5432 to 5433:
1sudo pg_dropcluster --stop 12 main
2sudo pg_dropcluster --stop 14 main
3sudo apt remove postgresql-14
4sudo apt purge postgresql*
5sudo apt install postgresql-14
6$ pg_lsclusters
7Ver Cluster Port Status Owner Data directory Log file
814 main 5432 online postgres /var/lib/postgresql/14/main /var/log/postgresql/postgresql-14-main.log
9$ sudo su postgres -c "psql"
10psql: error: connection to server on socket "/var/run/postgresql/.s.PGSQL.5432" failed: No such file or directory
11 Is the server running locally and accepting connections on that socket?
12$ ls -lah /var/run/postgresql/
13total 8.0K
14drwxrwsr-x 4 postgres postgres 160 Oct 29 16:40 .
15drwxr-xr-x 36 root root 1.1K Oct 29 14:08 ..
16drwxr-s--- 2 postgres postgres 40 Oct 29 14:33 12-main.pg_stat_tmp
17drwxr-s--- 2 postgres postgres 120 Oct 29 16:59 14-main.pg_stat_tmp
18-rw-r--r-- 1 postgres postgres 6 Oct 29 16:36 14-main.pid
19srwxrwxrwx 1 postgres postgres 0 Oct 29 16:36 .s.PGSQL.5433
20-rw------- 1 postgres postgres 70 Oct 29 16:36 .s.PGSQL.5433.lock
21$ pg_lsclusters
22Ver Cluster Port Status Owner Data directory Log file
2312 main 5432 down,binaries_missing postgres /var/lib/postgresql/12/main /var/log/postgresql/postgresql-12-main.log
2414 main 5433 online postgres /var/lib/postgresql/14/main /var/log/postgresql/postgresql-14-main.log
25$ sudo su postgres -c "psql --port=5433"
26psql (14.0 (Ubuntu 14.0-1.pgdg21.04+1))
27Type "help" for help.
28
29postgres=#
30port = 5432
31$ tail /var/log/postgresql/postgresql-14-main.log
32...
332021-10-29 16:36:12.195 UTC [25236] LOG: listening on IPv4 address "127.0.0.1", port 5433
342021-10-29 16:36:12.198 UTC [25236] LOG: listening on Unix socket "/var/run/postgresql/.s.PGSQL.5433"
352021-10-29 16:36:12.204 UTC [25237] LOG: database system was shut down at 2021-10-29 16:36:12 UTC
362021-10-29 16:36:12.210 UTC [25236] LOG: database system is ready to accept connections
37ln -s /var/run/postgresql/.s.PGSQL.5433 /var/run/postgresql/.s.PGSQL.5432
38This is what I have now:
1sudo pg_dropcluster --stop 12 main
2sudo pg_dropcluster --stop 14 main
3sudo apt remove postgresql-14
4sudo apt purge postgresql*
5sudo apt install postgresql-14
6$ pg_lsclusters
7Ver Cluster Port Status Owner Data directory Log file
814 main 5432 online postgres /var/lib/postgresql/14/main /var/log/postgresql/postgresql-14-main.log
9$ sudo su postgres -c "psql"
10psql: error: connection to server on socket "/var/run/postgresql/.s.PGSQL.5432" failed: No such file or directory
11 Is the server running locally and accepting connections on that socket?
12$ ls -lah /var/run/postgresql/
13total 8.0K
14drwxrwsr-x 4 postgres postgres 160 Oct 29 16:40 .
15drwxr-xr-x 36 root root 1.1K Oct 29 14:08 ..
16drwxr-s--- 2 postgres postgres 40 Oct 29 14:33 12-main.pg_stat_tmp
17drwxr-s--- 2 postgres postgres 120 Oct 29 16:59 14-main.pg_stat_tmp
18-rw-r--r-- 1 postgres postgres 6 Oct 29 16:36 14-main.pid
19srwxrwxrwx 1 postgres postgres 0 Oct 29 16:36 .s.PGSQL.5433
20-rw------- 1 postgres postgres 70 Oct 29 16:36 .s.PGSQL.5433.lock
21$ pg_lsclusters
22Ver Cluster Port Status Owner Data directory Log file
2312 main 5432 down,binaries_missing postgres /var/lib/postgresql/12/main /var/log/postgresql/postgresql-12-main.log
2414 main 5433 online postgres /var/lib/postgresql/14/main /var/log/postgresql/postgresql-14-main.log
25$ sudo su postgres -c "psql --port=5433"
26psql (14.0 (Ubuntu 14.0-1.pgdg21.04+1))
27Type "help" for help.
28
29postgres=#
30port = 5432
31$ tail /var/log/postgresql/postgresql-14-main.log
32...
332021-10-29 16:36:12.195 UTC [25236] LOG: listening on IPv4 address "127.0.0.1", port 5433
342021-10-29 16:36:12.198 UTC [25236] LOG: listening on Unix socket "/var/run/postgresql/.s.PGSQL.5433"
352021-10-29 16:36:12.204 UTC [25237] LOG: database system was shut down at 2021-10-29 16:36:12 UTC
362021-10-29 16:36:12.210 UTC [25236] LOG: database system is ready to accept connections
37ln -s /var/run/postgresql/.s.PGSQL.5433 /var/run/postgresql/.s.PGSQL.5432
38$ ls -lah /var/run/postgresql/
39total 8.0K
40drwxrwsr-x 4 postgres postgres 160 Oct 29 16:40 .
41drwxr-xr-x 36 root root 1.1K Oct 29 14:08 ..
42drwxr-s--- 2 postgres postgres 40 Oct 29 14:33 12-main.pg_stat_tmp
43drwxr-s--- 2 postgres postgres 120 Oct 29 16:59 14-main.pg_stat_tmp
44-rw-r--r-- 1 postgres postgres 6 Oct 29 16:36 14-main.pid
45lrwxrwxrwx 1 postgres postgres 33 Oct 29 16:40 .s.PGSQL.5432 -> /var/run/postgresql/.s.PGSQL.5433
46srwxrwxrwx 1 postgres postgres 0 Oct 29 16:36 .s.PGSQL.5433
47-rw------- 1 postgres postgres 70 Oct 29 16:36 .s.PGSQL.5433.lock
48This 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.
Community Discussions contain sources that include Stack Exchange Network
QUESTION
psql: error: connection to server on socket "/tmp/.s.PGSQL.5432" failed: No such file or directory
Asked 2022-Apr-04 at 15:46Not 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:
- Rebooting my machine (using MBA M1 2020)
- Restarting PostgreSQL using homebrew
brew services restart postgresql - Re-installing PostgreSQL using Homebrew
- Updating PostgreSQL using Homebrew
- I also tried following this link but when I run
cd Library/Application\ Support/Postgresterminal 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!
ANSWER
Answered 2022-Jan-13 at 15:19Resetting PostgreSQL
My original answer only included the troubleshooting steps below, and a workaround. I now decided to properly fix it via brute force by removing all clusters and reinstalling, since I didn't have any data there to keep. It was something along these lines, on my Ubuntu 21.04 system:
1sudo pg_dropcluster --stop 12 main
2sudo pg_dropcluster --stop 14 main
3sudo apt remove postgresql-14
4sudo apt purge postgresql*
5sudo apt install postgresql-14
6Now I have:
1sudo pg_dropcluster --stop 12 main
2sudo pg_dropcluster --stop 14 main
3sudo apt remove postgresql-14
4sudo apt purge postgresql*
5sudo apt install postgresql-14
6$ pg_lsclusters
7Ver Cluster Port Status Owner Data directory Log file
814 main 5432 online postgres /var/lib/postgresql/14/main /var/log/postgresql/postgresql-14-main.log
9And sudo -u postgres psql works fine. The service was started automatically but it can be done manually with sudo systemctl start postgresql.
Incidentally, I can recommend the PostgreSQL docker image, which eliminates the need to bother with a local installation.
Troubleshooting
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:
1sudo pg_dropcluster --stop 12 main
2sudo pg_dropcluster --stop 14 main
3sudo apt remove postgresql-14
4sudo apt purge postgresql*
5sudo apt install postgresql-14
6$ pg_lsclusters
7Ver Cluster Port Status Owner Data directory Log file
814 main 5432 online postgres /var/lib/postgresql/14/main /var/log/postgresql/postgresql-14-main.log
9$ sudo su postgres -c "psql"
10psql: error: connection to server on socket "/var/run/postgresql/.s.PGSQL.5432" failed: No such file or directory
11 Is the server running locally and accepting connections on that socket?
12I checked what unix sockets I had in that directory:
1sudo pg_dropcluster --stop 12 main
2sudo pg_dropcluster --stop 14 main
3sudo apt remove postgresql-14
4sudo apt purge postgresql*
5sudo apt install postgresql-14
6$ pg_lsclusters
7Ver Cluster Port Status Owner Data directory Log file
814 main 5432 online postgres /var/lib/postgresql/14/main /var/log/postgresql/postgresql-14-main.log
9$ sudo su postgres -c "psql"
10psql: error: connection to server on socket "/var/run/postgresql/.s.PGSQL.5432" failed: No such file or directory
11 Is the server running locally and accepting connections on that socket?
12$ ls -lah /var/run/postgresql/
13total 8.0K
14drwxrwsr-x 4 postgres postgres 160 Oct 29 16:40 .
15drwxr-xr-x 36 root root 1.1K Oct 29 14:08 ..
16drwxr-s--- 2 postgres postgres 40 Oct 29 14:33 12-main.pg_stat_tmp
17drwxr-s--- 2 postgres postgres 120 Oct 29 16:59 14-main.pg_stat_tmp
18-rw-r--r-- 1 postgres postgres 6 Oct 29 16:36 14-main.pid
19srwxrwxrwx 1 postgres postgres 0 Oct 29 16:36 .s.PGSQL.5433
20-rw------- 1 postgres postgres 70 Oct 29 16:36 .s.PGSQL.5433.lock
21Makes sense, there is a socket for 5433 not 5432. I confirmed this by running:
1sudo pg_dropcluster --stop 12 main
2sudo pg_dropcluster --stop 14 main
3sudo apt remove postgresql-14
4sudo apt purge postgresql*
5sudo apt install postgresql-14
6$ pg_lsclusters
7Ver Cluster Port Status Owner Data directory Log file
814 main 5432 online postgres /var/lib/postgresql/14/main /var/log/postgresql/postgresql-14-main.log
9$ sudo su postgres -c "psql"
10psql: error: connection to server on socket "/var/run/postgresql/.s.PGSQL.5432" failed: No such file or directory
11 Is the server running locally and accepting connections on that socket?
12$ ls -lah /var/run/postgresql/
13total 8.0K
14drwxrwsr-x 4 postgres postgres 160 Oct 29 16:40 .
15drwxr-xr-x 36 root root 1.1K Oct 29 14:08 ..
16drwxr-s--- 2 postgres postgres 40 Oct 29 14:33 12-main.pg_stat_tmp
17drwxr-s--- 2 postgres postgres 120 Oct 29 16:59 14-main.pg_stat_tmp
18-rw-r--r-- 1 postgres postgres 6 Oct 29 16:36 14-main.pid
19srwxrwxrwx 1 postgres postgres 0 Oct 29 16:36 .s.PGSQL.5433
20-rw------- 1 postgres postgres 70 Oct 29 16:36 .s.PGSQL.5433.lock
21$ pg_lsclusters
22Ver Cluster Port Status Owner Data directory Log file
2312 main 5432 down,binaries_missing postgres /var/lib/postgresql/12/main /var/log/postgresql/postgresql-12-main.log
2414 main 5433 online postgres /var/lib/postgresql/14/main /var/log/postgresql/postgresql-14-main.log
25This 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 5433, 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:
1sudo pg_dropcluster --stop 12 main
2sudo pg_dropcluster --stop 14 main
3sudo apt remove postgresql-14
4sudo apt purge postgresql*
5sudo apt install postgresql-14
6$ pg_lsclusters
7Ver Cluster Port Status Owner Data directory Log file
814 main 5432 online postgres /var/lib/postgresql/14/main /var/log/postgresql/postgresql-14-main.log
9$ sudo su postgres -c "psql"
10psql: error: connection to server on socket "/var/run/postgresql/.s.PGSQL.5432" failed: No such file or directory
11 Is the server running locally and accepting connections on that socket?
12$ ls -lah /var/run/postgresql/
13total 8.0K
14drwxrwsr-x 4 postgres postgres 160 Oct 29 16:40 .
15drwxr-xr-x 36 root root 1.1K Oct 29 14:08 ..
16drwxr-s--- 2 postgres postgres 40 Oct 29 14:33 12-main.pg_stat_tmp
17drwxr-s--- 2 postgres postgres 120 Oct 29 16:59 14-main.pg_stat_tmp
18-rw-r--r-- 1 postgres postgres 6 Oct 29 16:36 14-main.pid
19srwxrwxrwx 1 postgres postgres 0 Oct 29 16:36 .s.PGSQL.5433
20-rw------- 1 postgres postgres 70 Oct 29 16:36 .s.PGSQL.5433.lock
21$ pg_lsclusters
22Ver Cluster Port Status Owner Data directory Log file
2312 main 5432 down,binaries_missing postgres /var/lib/postgresql/12/main /var/log/postgresql/postgresql-12-main.log
2414 main 5433 online postgres /var/lib/postgresql/14/main /var/log/postgresql/postgresql-14-main.log
25$ sudo su postgres -c "psql --port=5433"
26psql (14.0 (Ubuntu 14.0-1.pgdg21.04+1))
27Type "help" for help.
28
29postgres=#
30It 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:
1sudo pg_dropcluster --stop 12 main
2sudo pg_dropcluster --stop 14 main
3sudo apt remove postgresql-14
4sudo apt purge postgresql*
5sudo apt install postgresql-14
6$ pg_lsclusters
7Ver Cluster Port Status Owner Data directory Log file
814 main 5432 online postgres /var/lib/postgresql/14/main /var/log/postgresql/postgresql-14-main.log
9$ sudo su postgres -c "psql"
10psql: error: connection to server on socket "/var/run/postgresql/.s.PGSQL.5432" failed: No such file or directory
11 Is the server running locally and accepting connections on that socket?
12$ ls -lah /var/run/postgresql/
13total 8.0K
14drwxrwsr-x 4 postgres postgres 160 Oct 29 16:40 .
15drwxr-xr-x 36 root root 1.1K Oct 29 14:08 ..
16drwxr-s--- 2 postgres postgres 40 Oct 29 14:33 12-main.pg_stat_tmp
17drwxr-s--- 2 postgres postgres 120 Oct 29 16:59 14-main.pg_stat_tmp
18-rw-r--r-- 1 postgres postgres 6 Oct 29 16:36 14-main.pid
19srwxrwxrwx 1 postgres postgres 0 Oct 29 16:36 .s.PGSQL.5433
20-rw------- 1 postgres postgres 70 Oct 29 16:36 .s.PGSQL.5433.lock
21$ pg_lsclusters
22Ver Cluster Port Status Owner Data directory Log file
2312 main 5432 down,binaries_missing postgres /var/lib/postgresql/12/main /var/log/postgresql/postgresql-12-main.log
2414 main 5433 online postgres /var/lib/postgresql/14/main /var/log/postgresql/postgresql-14-main.log
25$ sudo su postgres -c "psql --port=5433"
26psql (14.0 (Ubuntu 14.0-1.pgdg21.04+1))
27Type "help" for help.
28
29postgres=#
30port = 5432
31Restarted the server: sudo systemctl restart postgresql
But the error persisted because, as the logs confirmed, the port did not change:
1sudo pg_dropcluster --stop 12 main
2sudo pg_dropcluster --stop 14 main
3sudo apt remove postgresql-14
4sudo apt purge postgresql*
5sudo apt install postgresql-14
6$ pg_lsclusters
7Ver Cluster Port Status Owner Data directory Log file
814 main 5432 online postgres /var/lib/postgresql/14/main /var/log/postgresql/postgresql-14-main.log
9$ sudo su postgres -c "psql"
10psql: error: connection to server on socket "/var/run/postgresql/.s.PGSQL.5432" failed: No such file or directory
11 Is the server running locally and accepting connections on that socket?
12$ ls -lah /var/run/postgresql/
13total 8.0K
14drwxrwsr-x 4 postgres postgres 160 Oct 29 16:40 .
15drwxr-xr-x 36 root root 1.1K Oct 29 14:08 ..
16drwxr-s--- 2 postgres postgres 40 Oct 29 14:33 12-main.pg_stat_tmp
17drwxr-s--- 2 postgres postgres 120 Oct 29 16:59 14-main.pg_stat_tmp
18-rw-r--r-- 1 postgres postgres 6 Oct 29 16:36 14-main.pid
19srwxrwxrwx 1 postgres postgres 0 Oct 29 16:36 .s.PGSQL.5433
20-rw------- 1 postgres postgres 70 Oct 29 16:36 .s.PGSQL.5433.lock
21$ pg_lsclusters
22Ver Cluster Port Status Owner Data directory Log file
2312 main 5432 down,binaries_missing postgres /var/lib/postgresql/12/main /var/log/postgresql/postgresql-12-main.log
2414 main 5433 online postgres /var/lib/postgresql/14/main /var/log/postgresql/postgresql-14-main.log
25$ sudo su postgres -c "psql --port=5433"
26psql (14.0 (Ubuntu 14.0-1.pgdg21.04+1))
27Type "help" for help.
28
29postgres=#
30port = 5432
31$ tail /var/log/postgresql/postgresql-14-main.log
32...
332021-10-29 16:36:12.195 UTC [25236] LOG: listening on IPv4 address "127.0.0.1", port 5433
342021-10-29 16:36:12.198 UTC [25236] LOG: listening on Unix socket "/var/run/postgresql/.s.PGSQL.5433"
352021-10-29 16:36:12.204 UTC [25237] LOG: database system was shut down at 2021-10-29 16:36:12 UTC
362021-10-29 16:36:12.210 UTC [25236] LOG: database system is ready to accept connections
37After other attempts did not succeed, I eventually decided to use a workaround: to redirect the client's requests on 5432 to 5433:
1sudo pg_dropcluster --stop 12 main
2sudo pg_dropcluster --stop 14 main
3sudo apt remove postgresql-14
4sudo apt purge postgresql*
5sudo apt install postgresql-14
6$ pg_lsclusters
7Ver Cluster Port Status Owner Data directory Log file
814 main 5432 online postgres /var/lib/postgresql/14/main /var/log/postgresql/postgresql-14-main.log
9$ sudo su postgres -c "psql"
10psql: error: connection to server on socket "/var/run/postgresql/.s.PGSQL.5432" failed: No such file or directory
11 Is the server running locally and accepting connections on that socket?
12$ ls -lah /var/run/postgresql/
13total 8.0K
14drwxrwsr-x 4 postgres postgres 160 Oct 29 16:40 .
15drwxr-xr-x 36 root root 1.1K Oct 29 14:08 ..
16drwxr-s--- 2 postgres postgres 40 Oct 29 14:33 12-main.pg_stat_tmp
17drwxr-s--- 2 postgres postgres 120 Oct 29 16:59 14-main.pg_stat_tmp
18-rw-r--r-- 1 postgres postgres 6 Oct 29 16:36 14-main.pid
19srwxrwxrwx 1 postgres postgres 0 Oct 29 16:36 .s.PGSQL.5433
20-rw------- 1 postgres postgres 70 Oct 29 16:36 .s.PGSQL.5433.lock
21$ pg_lsclusters
22Ver Cluster Port Status Owner Data directory Log file
2312 main 5432 down,binaries_missing postgres /var/lib/postgresql/12/main /var/log/postgresql/postgresql-12-main.log
2414 main 5433 online postgres /var/lib/postgresql/14/main /var/log/postgresql/postgresql-14-main.log
25$ sudo su postgres -c "psql --port=5433"
26psql (14.0 (Ubuntu 14.0-1.pgdg21.04+1))
27Type "help" for help.
28
29postgres=#
30port = 5432
31$ tail /var/log/postgresql/postgresql-14-main.log
32...
332021-10-29 16:36:12.195 UTC [25236] LOG: listening on IPv4 address "127.0.0.1", port 5433
342021-10-29 16:36:12.198 UTC [25236] LOG: listening on Unix socket "/var/run/postgresql/.s.PGSQL.5433"
352021-10-29 16:36:12.204 UTC [25237] LOG: database system was shut down at 2021-10-29 16:36:12 UTC
362021-10-29 16:36:12.210 UTC [25236] LOG: database system is ready to accept connections
37ln -s /var/run/postgresql/.s.PGSQL.5433 /var/run/postgresql/.s.PGSQL.5432
38This is what I have now:
1sudo pg_dropcluster --stop 12 main
2sudo pg_dropcluster --stop 14 main
3sudo apt remove postgresql-14
4sudo apt purge postgresql*
5sudo apt install postgresql-14
6$ pg_lsclusters
7Ver Cluster Port Status Owner Data directory Log file
814 main 5432 online postgres /var/lib/postgresql/14/main /var/log/postgresql/postgresql-14-main.log
9$ sudo su postgres -c "psql"
10psql: error: connection to server on socket "/var/run/postgresql/.s.PGSQL.5432" failed: No such file or directory
11 Is the server running locally and accepting connections on that socket?
12$ ls -lah /var/run/postgresql/
13total 8.0K
14drwxrwsr-x 4 postgres postgres 160 Oct 29 16:40 .
15drwxr-xr-x 36 root root 1.1K Oct 29 14:08 ..
16drwxr-s--- 2 postgres postgres 40 Oct 29 14:33 12-main.pg_stat_tmp
17drwxr-s--- 2 postgres postgres 120 Oct 29 16:59 14-main.pg_stat_tmp
18-rw-r--r-- 1 postgres postgres 6 Oct 29 16:36 14-main.pid
19srwxrwxrwx 1 postgres postgres 0 Oct 29 16:36 .s.PGSQL.5433
20-rw------- 1 postgres postgres 70 Oct 29 16:36 .s.PGSQL.5433.lock
21$ pg_lsclusters
22Ver Cluster Port Status Owner Data directory Log file
2312 main 5432 down,binaries_missing postgres /var/lib/postgresql/12/main /var/log/postgresql/postgresql-12-main.log
2414 main 5433 online postgres /var/lib/postgresql/14/main /var/log/postgresql/postgresql-14-main.log
25$ sudo su postgres -c "psql --port=5433"
26psql (14.0 (Ubuntu 14.0-1.pgdg21.04+1))
27Type "help" for help.
28
29postgres=#
30port = 5432
31$ tail /var/log/postgresql/postgresql-14-main.log
32...
332021-10-29 16:36:12.195 UTC [25236] LOG: listening on IPv4 address "127.0.0.1", port 5433
342021-10-29 16:36:12.198 UTC [25236] LOG: listening on Unix socket "/var/run/postgresql/.s.PGSQL.5433"
352021-10-29 16:36:12.204 UTC [25237] LOG: database system was shut down at 2021-10-29 16:36:12 UTC
362021-10-29 16:36:12.210 UTC [25236] LOG: database system is ready to accept connections
37ln -s /var/run/postgresql/.s.PGSQL.5433 /var/run/postgresql/.s.PGSQL.5432
38$ ls -lah /var/run/postgresql/
39total 8.0K
40drwxrwsr-x 4 postgres postgres 160 Oct 29 16:40 .
41drwxr-xr-x 36 root root 1.1K Oct 29 14:08 ..
42drwxr-s--- 2 postgres postgres 40 Oct 29 14:33 12-main.pg_stat_tmp
43drwxr-s--- 2 postgres postgres 120 Oct 29 16:59 14-main.pg_stat_tmp
44-rw-r--r-- 1 postgres postgres 6 Oct 29 16:36 14-main.pid
45lrwxrwxrwx 1 postgres postgres 33 Oct 29 16:40 .s.PGSQL.5432 -> /var/run/postgresql/.s.PGSQL.5433
46srwxrwxrwx 1 postgres postgres 0 Oct 29 16:36 .s.PGSQL.5433
47-rw------- 1 postgres postgres 70 Oct 29 16:36 .s.PGSQL.5433.lock
48This 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.
Community Discussions contain sources that include Stack Exchange Network
Tutorials and Learning Resources in SQL Database
Tutorials and Learning Resources are not available at this moment for SQL Database
Share this Page
Get latest updates on SQL Database