kandi background
kandi background
Explore Kits
kandi background
Explore Kits
Explore all SQL Database open source software, libraries, packages, source code, cloud functions and APIs.

Popular New Releases in SQL Database

22.0.3

v7.0.0-alpha.11

1.0.7

Version 1.6.0

0.39.1

dbeaver

22.0.3

sequelize

v7.0.0-alpha.11

knex

1.0.7

mysql

Version 1.6.0

dolt

0.39.1

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

csharp-sql-database

10 best C# SQL Database

C# is a general-purpose programming language developed by Microsoft. C# was designed to be simple, object-oriented and type-safe. It has been widely used for enterprise applications and has many other uses, including video games, desktop applications and embedded systems. C# SQL Database libraries are important to any development project. They provide a clean and simple API for working with data from a SQL database. SQL Database is a managed, relational database service that makes it easy to set up, manage and scale your database. It does not require a separate server process and interacts directly with the filesystem. C# SQL Database libraries are used for interacting with a database in C#. These libraries provide an easier way to connect to a database, create tables and store data. Some of the most popular C# SQL Database Open Source libraries among developers are: querybuilder - SQL query builder; DbUp - NET library that helps you to deploy changes; chinook-database - Sample database for SQL Server, Oracle, MySQL, PostgreSQL, SQLite, DB2.

java-sql-database

13 best Java SQL Database

Java is a programming language that is used to create applications for various platforms, including Windows, Mac OS, UNIX and Linux. SQL databases are the most common data storage systems for modern web applications. They are the backbone of any web application. Java SQL Database is a language-independent, vendor-neutral, technology-neutral specification intended to standardize the interaction between a database management system and a programming language. Java Database Connectivity (JDBC) API provides the ability to interact with databases that support the SQL standard. Java-JDBC is a pure Java package that provides both a JDBC driver and a rich set of classes for building and manipulating SQL statements, as well as for accessing data from relational database management systems. There are several popular Java SQL Database open source libraries available for developers: dbeaver - Free universal database tool and SQL client; questdb - open source SQL database designed to process time series data; zeppelin - Webbased notebook that enables datadriven.

php-sql-database

13 best PHP SQL Database

PHP is a powerful, flexible programming language for web development. It’s easy to learn and use, making it one of the most popular languages on the web. PHP has a number of built-in functions for working with SQL databases. A major part of any web application is the database. PHP is one of the most popular server-side scripting languages. It has a lot of features and libraries that make it easy for developers to create complex websites and applications. PHP allows developers to work with databases either directly or through an abstraction layer called PDO (PHP Data Objects). Database libraries are packages that provide additional functionality to PDO, making it easier for developers to work with relational databases like MySQL or PostgreSQL. Some of the most widely used open source libraries for PHP SQL Database among developers include: php-crud-api - Single file PHP script that adds a REST API to a SQL database; countries-states-cities-database - World countries; php-my-sql-pdo-database-class - A database class for PHP MySQL which utilizes PDO.

python-sql-database

13 best Python SQL Database

Python is a widely used general-purpose, high-level programming language. Its design philosophy emphasizes code readability, and its syntax allows programmers to express concepts in fewer lines of code. The language provides constructs intended to enable clear programs on both a small and large scale. Python supports multiple programming paradigms, including object-oriented, imperative and functional programming or procedural styles. It features a dynamic type system and automatic memory management and has a large and comprehensive standard library. A database is an organized collection of data that's stored on a computer so that it can be accessed easily. Databases are used for many different purposes, but they're often used to store large amounts of information and retrieve it quickly when needed. Many developers depend on the following Python SQL Database open source libraries: sqlmap - Automatic SQL injection and database takeover tool; q - q Run SQL directly on CSV or TSV files; sqlmodel - SQL databases in Python, designed for simplicity, compatibility, and robustness.

go-sql-database

18 best Go SQL Database

Go programming language has been around for a number of years and has gained popularity in recent years. It is an open source, general-purpose programming language that makes it easy to build simple, reliable, and efficient software. Go SQL Database is a database driver for Go. It supports the popular databases MySQL, MariaDB, PostgreSQL, SQLite and MSSQL. Go is a compiled language which means that it produces machine code on the fly. Go's core features include: Concurrency without data races, Rich standard library, Fast compilation, clean syntax, and fast execution. The Go language is simple, but powerful enough to run a huge amount of existing production code and create new applications that are lightweight, flexible and scalable. Some of the most popular Go SQL Database Open Source libraries among developers are: tidb - open source distributed HTAP database compatible; cockroach - open source, cloudnative distributed SQL database; sqlx - general purpose extensions to golang's database/sql.

cpp-sql-database

6 best C++ SQL Database

C++ is a general-purpose programming language that supports procedural, object-oriented and generic programming. It was developed by Bjarne Stroustrup starting in 1979 at Bell Labs as an enhancement to the C programming language. C++ is one of the most popular programming languages in use today, primarily for its efficiency and flexibility. A database is a program that stores, organizes and provides access to data. Database management systems (DBMS) are software programs designed to manage databases. A database management system is a software system that provides data definition, data manipulation, and data control services for a database. Some of the most widely used open source libraries for C++ SQL Database among developers include: mysql-server - MySQL Server, the world's most popular open source database; oceanbase - OceanBase is an enterprise distributed relational database with high availability, high performance; mysql-5.6 - Facebook's branch of the Oracle MySQL v5.6 database.

Trending Discussions on SQL Database

    psql: error: connection to server on socket "/tmp/.s.PGSQL.5432" failed: No such file or directory
    How to obtain MongoDB version using Golang library?
    Issue while trying to set enum data type in MySQL database
    Unable to resolve service for type Microsoft.EntityFrameworkCore.Diagnostics.IDiagnosticsLogger
    Should I close an RDS Proxy connection inside an AWS Lambda function?
    PostgreSQL conditional select throwing error
    How to programmatically detect auto failover on AWS mysql aurora?
    System.NotSupportedException: Character set 'utf8mb3' is not supported by .Net Framework
    Debugging a Google Dataflow Streaming Job that does not work expected
    move Odoo large database (1.2TB)

QUESTION

psql: error: connection to server on socket "/tmp/.s.PGSQL.5432" failed: No such file or directory

Asked 2022-Apr-04 at 15:46

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!

ANSWER

Answered 2022-Jan-13 at 15:19

Resetting 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:

copy icondownload icon

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

Now I have:

copy icondownload icon

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

And 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:

copy icondownload icon

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

I checked what unix sockets I had in that directory:

copy icondownload icon

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

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

copy icondownload icon

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

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 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:

copy icondownload icon

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=#
30

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:

copy icondownload icon

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

Restarted the server: sudo systemctl restart postgresql

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

copy icondownload icon

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
37

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

copy icondownload icon

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

This is what I have now:

copy icondownload icon

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
48

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.

Source https://stackoverflow.com/questions/69754628

Community Discussions contain sources that include Stack Exchange Network

    psql: error: connection to server on socket "/tmp/.s.PGSQL.5432" failed: No such file or directory
    How to obtain MongoDB version using Golang library?
    Issue while trying to set enum data type in MySQL database
    Unable to resolve service for type Microsoft.EntityFrameworkCore.Diagnostics.IDiagnosticsLogger
    Should I close an RDS Proxy connection inside an AWS Lambda function?
    PostgreSQL conditional select throwing error
    How to programmatically detect auto failover on AWS mysql aurora?
    System.NotSupportedException: Character set 'utf8mb3' is not supported by .Net Framework
    Debugging a Google Dataflow Streaming Job that does not work expected
    move Odoo large database (1.2TB)

QUESTION

psql: error: connection to server on socket "/tmp/.s.PGSQL.5432" failed: No such file or directory

Asked 2022-Apr-04 at 15:46

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!

ANSWER

Answered 2022-Jan-13 at 15:19

Resetting 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:

copy icondownload icon

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

Now I have:

copy icondownload icon

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

And 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:

copy icondownload icon

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

I checked what unix sockets I had in that directory:

copy icondownload icon

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

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

copy icondownload icon

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

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 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:

copy icondownload icon

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=#
30

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:

copy icondownload icon

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

Restarted the server: sudo systemctl restart postgresql

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

copy icondownload icon

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
37

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

copy icondownload icon

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

This is what I have now:

copy icondownload icon

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
48

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.

Source https://stackoverflow.com/questions/69754628

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

share link

Get latest updates on SQL Database

  • © 2022 Open Weaver Inc.