How to fix PostgreSQL error: connection to server on socket failed

How to fix PostgreSQL error: connection to server on socket failed?

Abdallah Yashir
4 min readDec 18, 2023

Introduction

Photo by 🇸🇮 Janko Ferlič / Unsplash

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

Photo by Lukas / Unsplash

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 start

You 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_lsclusters

You 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.log

This 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 restart

Now 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/main

You 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 started

You can also test if your database is working fine by running pg_isready.

Bonus

3D Render of a Cyl. stacks
Photo by Sunder Muthukumaran / Unsplash

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.

--

--

Abdallah Yashir
Abdallah Yashir

Written by Abdallah Yashir

Senior Software Developer, Writer, Amateur Photographer, Reader

Responses (1)