Обсуждение: 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

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
forthe socket in one place, which is not either of the two places the server is listening.  You could explicitly tell
itwhere to connect with either `-h /tmp`, or `-h 127.0.0.1`.  Or you could find the correct "psql" to run (the one that
camewith the running server) so that it just knows where to look, possibly uninstalling the wrong psql to minimize
futureconfusion. 
>

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

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