BUG #16086: Cannot connect using psql, however I can connect using pgadmin
The following bug has been logged on the website:
Bug reference: 16086
Logged by: Muziwandile Zwane
Email address: mzwai99@outlook.com
PostgreSQL version: 12.0
Operating system: Linux x86-64
Description:
[root@zardplpsmasdev01 ~]# systemctl status postgresql-12 -l
● postgresql-12.service - PostgreSQL 12 database server
Loaded: loaded (/usr/lib/systemd/system/postgresql-12.service; enabled;
vendor preset: disabled)
Active: active (running) since Tue 2019-10-29 10:59:59 SAST; 45s ago
Docs: https://www.postgresql.org/docs/12/static/
Process: 98621 ExecStartPre=/usr/pgsql-12/bin/postgresql-12-check-db-dir
${PGDATA} (code=exited, status=0/SUCCESS)
Main PID: 98628 (postmaster)
Tasks: 8
Memory: 18.1M
CGroup: /system.slice/postgresql-12.service
├─98628 /usr/pgsql-12/bin/postmaster -D
/u01/postgress/pgsql/12/data/
├─98629 postgres: logger
├─98631 postgres: checkpointer
├─98632 postgres: background writer
├─98633 postgres: walwriter
├─98634 postgres: autovacuum launcher
├─98635 postgres: stats collector
└─98636 postgres: logical replication launcher
Oct 29 10:59:59 zardplpsmasdev01 systemd[1]: Starting PostgreSQL 12 database
server...
Oct 29 10:59:59 zardplpsmasdev01 postmaster[98628]: 2019-10-29 10:59:59.483
SAST [98628] LOG: starting PostgreSQL 12.0 on x86_64-pc-linux-gnu, compiled
by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-39), 64-bit
Oct 29 10:59:59 zardplpsmasdev01 postmaster[98628]: 2019-10-29 10:59:59.486
SAST [98628] LOG: listening on IPv4 address "0.0.0.0", port 5437
Oct 29 10:59:59 zardplpsmasdev01 postmaster[98628]: 2019-10-29 10:59:59.486
SAST [98628] LOG: listening on IPv6 address "::", port 5437
Oct 29 10:59:59 zardplpsmasdev01 postmaster[98628]: 2019-10-29 10:59:59.495
SAST [98628] LOG: listening on Unix socket
"/u01/postgresql/.s.PGSQL.5437"
Oct 29 10:59:59 zardplpsmasdev01 postmaster[98628]: 2019-10-29 10:59:59.505
SAST [98628] LOG: listening on Unix socket "/tmp/.s.PGSQL.5437"
Oct 29 10:59:59 zardplpsmasdev01 postmaster[98628]: 2019-10-29 10:59:59.522
SAST [98628] LOG: redirecting log output to logging collector process
Oct 29 10:59:59 zardplpsmasdev01 postmaster[98628]: 2019-10-29 10:59:59.522
SAST [98628] HINT: Future log output will appear in directory "log".
Oct 29 10:59:59 zardplpsmasdev01 systemd[1]: Started PostgreSQL 12 database
server.
[root@zardplpsmasdev01 ~]# su - postgres -c "psql"
psql: error: could not connect to server: could not connect to server: No
such file or directory
Is the server running locally and accepting
connections on Unix domain socket
"/var/run/postgresql/.s.PGSQL.5432"?
[root@zardplpsmasdev01 ~]#
Hello
SAST [98628] LOG: listening on Unix socket
"/u01/postgresql/.s.PGSQL.5437"
Oct 29 10:59:59 zardplpsmasdev01 postmaster[98628]: 2019-10-29 10:59:59.505
SAST [98628] LOG: listening on Unix socket "/tmp/.s.PGSQL.5437"
Seems you have modified unix_socket_directories setting. Your psql tries another path (also was changed from /tmp to /var/run/postgresql/ during compilation).
So this is not bug, just misconfiguration. Possible you want add /var/run/postgresql/ to unix_socket_directories
regards, Sergei
LOG: listening on Unix socket "/u01/postgresql/.s.PGSQL.5437"
LOG: listening on Unix socket "/tmp/.s.PGSQL.5437"
...
[root@zardplpsmasdev01 ~]# su - postgres -c "psql"
psql: error: could not connect to server: could not connect to server: No
such file or directory
Is the server running locally and accepting
connections on Unix domain socket
"/var/run/postgresql/.s.PGSQL.5432"?
You probably have multiple binaries installed coming from different
packages or package managers. psql is searching for the socket in one
place, which is not either of the two places the server is listening. You
could explicitly tell it where to connect with either `-h /tmp`, or `-h
127.0.0.1`. Or you could find the correct "psql" to run (the one that came
with the running server) so that it just knows where to look, possibly
uninstalling the wrong psql to minimize future confusion.
Cheers,
Jeff
On Tue, Oct 29, 2019 at 4:57 PM Jeff Janes <jeff.janes@gmail.com> wrote:
LOG: listening on Unix socket "/u01/postgresql/.s.PGSQL.5437"
LOG: listening on Unix socket "/tmp/.s.PGSQL.5437"...
[root@zardplpsmasdev01 ~]# su - postgres -c "psql"
psql: error: could not connect to server: could not connect to server: No
such file or directory
Is the server running locally and accepting
connections on Unix domain socket
"/var/run/postgresql/.s.PGSQL.5432"?You probably have multiple binaries installed coming from different packages or package managers. psql is searching for the socket in one place, which is not either of the two places the server is listening. You could explicitly tell it where to connect with either `-h /tmp`, or `-h 127.0.0.1`. Or you could find the correct "psql" to run (the one that came with the running server) so that it just knows where to look, possibly uninstalling the wrong psql to minimize future confusion.
Alternatively, you could also create a soft link to /tmp/.s.PGSQL.5432
ln -s /tmp/.s.PGSQL.5432 /var/run/postgresql/
Thanks,
--
With Regards,
Ashutosh Sharma
EnterpriseDB:http://www.enterprisedb.com
Hi Jeff, thanks for the reply, I have found where I went wrong.
I am very new in the PSQL world, I am an oracle dba by profession.
I wanted to change the port from default to 5437, initially it worked, but suddendly I had issues connecting.
I changed the port postgresql.conf back to 5432.
[root@zardplpsmasdev01 data]# su - postgres -c "psql"
psql (12.0)
Type "help" for help.
postgres=#
________________________________
From: Jeff Janes <jeff.janes@gmail.com>
Sent: Tuesday, 29 October 2019 13:27
To: PG Bug reporting form <noreply@postgresql.org>
Cc: pgsql-bugs <pgsql-bugs@lists.postgresql.org>; mzwai99@outlook.com <mzwai99@outlook.com>
Subject: Re: BUG #16086: Cannot connect using psql, however I can connect using pgadmin
LOG: listening on Unix socket "/u01/postgresql/.s.PGSQL.5437"
LOG: listening on Unix socket "/tmp/.s.PGSQL.5437"
...
[root@zardplpsmasdev01 ~]# su - postgres -c "psql"
psql: error: could not connect to server: could not connect to server: No
such file or directory
Is the server running locally and accepting
connections on Unix domain socket
"/var/run/postgresql/.s.PGSQL.5432"?
You probably have multiple binaries installed coming from different packages or package managers. psql is searching for the socket in one place, which is not either of the two places the server is listening. You could explicitly tell it where to connect with either `-h /tmp`, or `-h 127.0.0.1`. Or you could find the correct "psql" to run (the one that came with the running server) so that it just knows where to look, possibly uninstalling the wrong psql to minimize future confusion.
Cheers,
Jeff
Em ter, 29 de out de 2019 às 08:54, Muziwandile Zwane
<mzwai99@outlook.com> escreveu:
I wanted to change the port from default to 5437, initially it worked, but suddendly I had issues connecting.
I changed the port postgresql.conf back to 5432.
... if you change the port, you should specify it in psql command-line.
[root@zardplpsmasdev01 data]# su - postgres -c "psql"
psql (12.0)
Type "help" for help.
su - postgres -c "psql -p 5437"
The default port (5432 or what it was compiled with) is hardcoded in
all postgres binaries. If you change the port to something else, you
should specify --port (-p) parameter or set PGPORT variable.
--
Euler Taveira Timbira -
http://www.timbira.com.br/
PostgreSQL: Consultoria, Desenvolvimento, Suporte 24x7 e Treinamento