How to fix PostgreSQL error: connection to server on socket failed
How to fix PostgreSQL error: connection to server on socket failed?
Introduction
Imagine on a Monday morning, after a hot cup of coffee, you are ready to start your day. You check if you’re web application is working fine. Alas, You see a 500 error and an ugly error page.
You immediately make a quick mental note: “I need to fix this error page.” You open your terminal and type psql -U postgres to check if your database is working fine.
You see the following error message:
psql: error: connection to server on socket "/var/run/postgresql/.s.PGSQL.5432" failed: No such file or directory
You’re confused. You didn’t do anything, yet your database is down.
Welcome to one of the most common problems we encounter as developers.
In this article, we will learn how to fix this error, both the easy way and the hard way.
Buckle up.
Context
I’m using Ubuntu 20.04 and PostgreSQL 14.
I have this issue whenever I try to restore a lightened production database to my local machine. I don’t have enough space on my machine when doing so.
This often prompts my system to behave weirdly; sometimes Ubuntu won’t even boot normally. I have to boot in recovery mode and run fsck to fix the issue.
Error message
This is the complete error message.
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?Easy Solution
Here we can assume the postgreSQL service isn’t working. So normally, just by starting it again, we can fix the issue.
sudo service postgresql startYou run this command but the error persists when you try to connect to the database. What do you do now?
Clearly, the problem isn’t directly linked to this service. We need to dig deeper.
Hard Solution — Clusters
A reminder of what is a cluster?
A Postgres cluster can be thought of as a collection of databases with their configurations. For example, you have a cluster with two databases that utilize Postgres v9. And all databases use the same cluster settings, such as buffer size, number of connections allowed, connection pool size, etc [https://hevodata.com/learn/postgresql-cluster]
Let’s check the status of our clusters.
pg_lsclustersYou will see the following output:
Ver Cluster Port Status Owner Data directory Log file
14 main 5432 down postgres /var/lib/postgresql/14/main /var/log/postgresql/postgresql-14-main.logThis indicates that the cluster is down and possibly why we can't connect to the database.
We need to restart the cluster.
pg_ctlcluster 14 main restartNow if you run the command but the error persists, next is to restart the cluster inside of the data directory but first we need to change the permissions.
700 means that only the owner can read, write, and execute the file. The owner is the postgres user.
sudo chmod 700 -R /var/lib/postgresql/14/main
sudo -i -u postgres
/usr/lib/postgresql/14/bin/pg_ctl restart -D /var/lib/postgresql/14/mainYou will see the following message:
postgres@klanik:~$ /usr/lib/postgresql/14/bin/pg_ctl restart -D /var/lib/postgresql/14/main
pg_ctl: PID file "/var/lib/postgresql/14/main/postmaster.pid" does not exist
Is server running?
trying to start server anyway
waiting for server to start....2023-12-18 14:24:08.675 +04 [54384] LOG: starting PostgreSQL 14.8 (Ubuntu 14.8-0ubuntu0.22.10.1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 12.2.0-3ubuntu1) 12.2.0, 64-bit
2023-12-18 14:24:08.675 +04 [54384] LOG: listening on IPv4 address "127.0.0.1", port 5432
2023-12-18 14:24:08.677 +04 [54384] LOG: listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
2023-12-18 14:24:08.691 +04 [54385] LOG: database system was interrupted while in recovery at 2023-12-18 14:21:44 +04
2023-12-18 14:24:08.691 +04 [54385] HINT: This probably means that some data is corrupted and you will have to use the last backup for recovery.
2023-12-18 14:24:09.040 +04 [54385] LOG: database system was not properly shut down; automatic recovery in progress
2023-12-18 14:24:09.042 +04 [54385] LOG: redo starts at BA/6DB8D548
.2023-12-18 14:24:10.534 +04 [54385] LOG: redo done at BA/B0FFDE00 system usage: CPU: user: 0.55 s, system: 0.83 s, elapsed: 1.49 s
...2023-12-18 14:24:12.909 +04 [54384] LOG: database system is ready to accept connections
done
server startedYou can also test if your database is working fine by running pg_isready.
Bonus
Run this command to connect to the database as the postgres user.
sudo -u postgres psql
Enter your password.
You can then run \l to list all the databases.
From this, select the one you want to connect to by running \c <database_name>.
If you want to delete a database, run DROP DATABASE <database_name>;.
You can also run this dropdb <database_name>.
In my case, the database takes too much space on my system. I have to delete it and restore a much older but lighter version.
