Обсуждение: Postgres v9.5.3 and v9.5.4 Unix Socket Issue

psql (client rpm in 9.5) is not consistent with build default of 9.5 server rpm. Is that a possibility????

I get that it's a local connection, but the documentation says  that you should specify the directory path to that local connection in the host name parameter (at least) in the 9.5 version

On Saturday, August 13, 2016, Murthy Nunna <mnunna@fnal.gov> wrote:

Hi Robert,

 

This is local connection from the same server where cluster is running. I don’t need hostname unless it is a change in 9.5. The same syntax (without hostname) worked in earlier versions.

 

Thanks,

Murthy

 

From: Robert Burgholzer [mailto:rburghol@vt.edu]
Sent: Saturday, August 13, 2016 9:39 AM
To: Murthy Nunna <mnunna@fnal.gov>
Cc: pgsql-admin@postgresql.org
Subject: [ADMIN] Postgres v9.5.3 and v9.5.4 Unix Socket Issue

 

psql (client rpm in 9.5) is not consistent with build default of 9.5 server rpm. Is that a possibility????

 

I suppose - but to verify it looks like your connection syntax is not really compatible with the socket style, docs say you should pass socket path with to the -h or --host - maybe pass your socket in as:

h hostname
--host=hostname

Specifies the host name of the machine on which the server is running. If the value begins with a slash, it is used as the directory for the Unix-domain socket.

 

--

--
Robert W. Burgholzer
 'Making the simple complicated is commonplace; making the complicated simple, awesomely simple, that's creativity.'  - Charles Mingus

Athletics: http://athleticalgorithm.wordpress.com/ 

Science: http://robertwb.wordpress.com/

Wine: http://reesvineyard.wordpress.com/

 

--

--
Robert W. Burgholzer
 'Making the simple complicated is commonplace; making the complicated simple, awesomely simple, that's creativity.'  - Charles Mingus

Athletics: http://athleticalgorithm.wordpress.com/ 

Science: http://robertwb.wordpress.com/

Wine: http://reesvineyard.wordpress.com/

Murthy Nunna <mnunna@fnal.gov> writes:
> We downloaded and installed postgres from rpm(s). The installation is on Linux-x64.
> We did pg_upgrade of an existing 9.5.3 cluster.
> psql -U postgres -d template1 -p 5434
> psql: 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.5434"?
>  The socket is in /tmp and that is where it is supposed to be.

It looks to me like you have a copy of libpq that was built to think that
the default socket location is in /var/run/postgresql not /tmp.  That (or
some close variant) is a change that many Linux packagers make because
they worry that /tmp isn't secure enough.  So what seems likely is that
even if you are using the copy of psql that came from the PGDG RPMs
(a fact you have not proven), it is linking to a libpq.so that came from
your Linux distro vendor.  "ldd" would help you track down which shared
libraries psql is picking up, but I'll bet it's finding libpq.so in
/usr/lib or /usr/lib64 rather than wherever the PGDG RPMs installed it.

There should be documentation material in the RPMs about how to get psql
to find the right libpq.  In the extreme you could set LD_LIBRARY_PATH
all the time, but there may be an easier way.  I've been out of the
Linux-packaging game for a little while so I don't remember all the
tricks.

Another alternative worth considering is to go ahead and make the server
create a socket in /var/run/postgresql in addition to /tmp.

            regards, tom lane

Hi,

On Sat, 2016-08-13 at 13:29 +0000, Murthy Nunna wrote:
>
> I am having an issue with version 9.5. I am unable to connect locally with
> psql using port syntax. This worked in earlier versions (for sure in 9.3).
>
> We downloaded and installed postgres from rpm(s). The installation is on
> Linux-x64.
> We did pg_upgrade of an existing 9.5.3 cluster.
> psql -U postgres -d template1 -p 5434
> psql: 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.5434"?

I think you are using 9.3's psql to connect to 9.5 instance. That won't work
for the RPMs (unless you specify -h option as stated in the thread). We changed
the default socket directory from /tmp to /var/run/postgresql. Please use 9.5's
psql.

Regards,
--
Devrim GÜNDÜZ
EnterpriseDB: http://www.enterprisedb.com
PostgreSQL Danışmanı/Consultant, Red Hat Certified Engineer
Twitter: @DevrimGunduz , @DevrimGunduzTR

Hi Devrim,

I am using 9.5 psql to connect to 9.5 instance which is not working.

You said "We changed the default socket directory from /tmp to /var/run/postgresql". Could you point this to me in
documentationplease?
 

Thanks,
Murthy

-----Original Message----
From: Devrim Gündüz [mailto:devrim@gunduz.org] 
Sent: Saturday, August 13, 2016 10:42 AM
To: Murthy Nunna <mnunna@fnal.gov>; pgsql-admin@postgresql.org
Subject: Re: [ADMIN] Postgres v9.5.3 and v9.5.4 Unix Socket Issue


Hi,

On Sat, 2016-08-13 at 13:29 +0000, Murthy Nunna wrote:
> 
> I am having an issue with version 9.5. I am unable to connect locally 
> with psql using port syntax. This worked in earlier versions (for sure in 9.3).
> 
> We downloaded and installed postgres from rpm(s). The installation is 
> on Linux-x64.
> We did pg_upgrade of an existing 9.5.3 cluster.
> psql -U postgres -d template1 -p 5434
> psql: 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.5434"?

I think you are using 9.3's psql to connect to 9.5 instance. That won't work for the RPMs (unless you specify -h option
asstated in the thread). We changed the default socket directory from /tmp to /var/run/postgresql. Please use 9.5's
psql.

Regards,
--
Devrim GÜNDÜZ
EnterpriseDB: http://www.enterprisedb.com PostgreSQL Danışmanı/Consultant, Red Hat Certified Engineer
Twitter: @DevrimGunduz , @DevrimGunduzTR

Hi Tom,

Following is output from "ldd" command:

ldd /fnal/ups/prd/postgres/v9_5_3_x64/Linux-2-6/bin/psql | grep -i libpq
        libpq.so.5 => /fnal/ups/prd/postgres/v9_5_3_x64/Linux-2-6/lib/libpq.so.5 (0x00007fbce99f5000)

It doesn't look like we are using from Linux distribution as you suspected.

Murthy


-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: Saturday, August 13, 2016 10:40 AM
To: Murthy Nunna <mnunna@fnal.gov>
Cc: pgsql-admin@postgresql.org
Subject: Re: [ADMIN] Postgres v9.5.3 and v9.5.4 Unix Socket Issue

Murthy Nunna <mnunna@fnal.gov> writes:
> We downloaded and installed postgres from rpm(s). The installation is on Linux-x64.
> We did pg_upgrade of an existing 9.5.3 cluster.
> psql -U postgres -d template1 -p 5434
> psql: 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.5434"?
>  The socket is in /tmp and that is where it is supposed to be.

It looks to me like you have a copy of libpq that was built to think that the default socket location is in
/var/run/postgresqlnot /tmp.  That (or some close variant) is a change that many Linux packagers make because they
worrythat /tmp isn't secure enough.  So what seems likely is that even if you are using the copy of psql that came from
thePGDG RPMs (a fact you have not proven), it is linking to a libpq.so that came from your Linux distro vendor.  "ldd"
wouldhelp you track down which shared libraries psql is picking up, but I'll bet it's finding libpq.so in /usr/lib or
/usr/lib64rather than wherever the PGDG RPMs installed it. 

There should be documentation material in the RPMs about how to get psql to find the right libpq.  In the extreme you
couldset LD_LIBRARY_PATH all the time, but there may be an easier way.  I've been out of the Linux-packaging game for a
littlewhile so I don't remember all the tricks. 

Another alternative worth considering is to go ahead and make the server create a socket in /var/run/postgresql in
additionto /tmp. 

            regards, tom lane

Murthy Nunna <mnunna@fnal.gov> writes:
> Following is output from "ldd" command:
> ldd /fnal/ups/prd/postgres/v9_5_3_x64/Linux-2-6/bin/psql | grep -i libpq
>         libpq.so.5 => /fnal/ups/prd/postgres/v9_5_3_x64/Linux-2-6/lib/libpq.so.5 (0x00007fbce99f5000)

And that actually is the copy of psql you're invoking?

Another theory worth considering is that something is helpfully setting
PGHOST=/var/run/postgresql in psql's environment.

            regards, tom lane

Hi Tom,

/fnal/ups/prd/postgres/v9_5_3_x64/Linux-2-6/lib/libpq.so.5  is actually symlink but it points to library that came from
rpminstall. 

ls -ltr /fnal/ups/prd/postgres/v9_5_3_x64/Linux-2-6/lib/libpq.so*

-rwxr-xr-x 1 postgres postgres 193296 May 14 17:36 /fnal/ups/prd/postgres/v9_5_3_x64/Linux-2-6/lib/libpq.so.5.8
lrwxrwxrwx 1 postgres postgres     12 Aug 11 11:39 /fnal/ups/prd/postgres/v9_5_3_x64/Linux-2-6/lib/libpq.so.5 ->
libpq.so.5.8

Well, I like the idea of adding /var/run/postgresql to unix_socket_directories. However, I would like to see this
change-in-behaviour-in-9.5document. 

If you agree with me, documented or not, that will still leave some inconsistency where psql default is /var/run
(assumingit is documented) and server default is /tmp (this is already documented in unix_socket_directories and it
worksas it says. The socket is created in /tmp by default) 

Thanks,
Murthy


-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: Saturday, August 13, 2016 11:12 AM
To: Murthy Nunna <mnunna@fnal.gov>
Cc: pgsql-admin@postgresql.org
Subject: Re: [ADMIN] Postgres v9.5.3 and v9.5.4 Unix Socket Issue

Murthy Nunna <mnunna@fnal.gov> writes:
> Following is output from "ldd" command:
> ldd /fnal/ups/prd/postgres/v9_5_3_x64/Linux-2-6/bin/psql | grep -i libpq
>         libpq.so.5 =>
> /fnal/ups/prd/postgres/v9_5_3_x64/Linux-2-6/lib/libpq.so.5
> (0x00007fbce99f5000)

And that actually is the copy of psql you're invoking?

Another theory worth considering is that something is helpfully setting PGHOST=/var/run/postgresql in psql's
environment.

            regards, tom lane