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

Поиск
Список
Период
Сортировка

Postgres v9.5.3 and v9.5.4 Unix Socket Issue

От
Murthy Nunna
Дата:

Hi All,

 

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"?

 The socket is in /tmp and that is where it is supposed to be.

srwxrwxrwx   1 postgres postgres       0 Aug 13 08:16 .s.PGSQL.5434

-rw-------   1 postgres postgres      49 Aug 13 08:16 .s.PGSQL.5434.lock

 

Following line is from postgresql.conf:

unix_socket_directories = ‘/tmp’

Do you know why the new build (9.5.3 psql and 9.5.4 as well) is looking in /var/run/postgresql? This seems to be some inconsistency in new build. BTW, I can connect successfully to this 9.5 database using 9.3 psql executable.

Thanks for reading my post.

Murthy Nunna

Database Services Group

Enterprise Server Operations Dept.

Fermi National Accelerator Laboratory

 

Re: Postgres v9.5.3 and v9.5.4 Unix Socket Issue

От
Robert Burgholzer
Дата:
Off the top of my head I would say check to see if pg_hba.conf syntax must be updated for the newer version, or if it was overwritten during upgrade.

Hth,
Robert

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

Hi All,

 

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"?

 The socket is in /tmp and that is where it is supposed to be.

srwxrwxrwx   1 postgres postgres       0 Aug 13 08:16 .s.PGSQL.5434

-rw-------   1 postgres postgres      49 Aug 13 08:16 .s.PGSQL.5434.lock

 

Following line is from postgresql.conf:

unix_socket_directories = ‘/tmp’

Do you know why the new build (9.5.3 psql and 9.5.4 as well) is looking in /var/run/postgresql? This seems to be some inconsistency in new build. BTW, I can connect successfully to this 9.5 database using 9.3 psql executable.

Thanks for reading my post.

Murthy Nunna

Database Services Group

Enterprise Server Operations Dept.

Fermi National Accelerator Laboratory

 



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

Re: Postgres v9.5.3 and v9.5.4 Unix Socket Issue

От
Murthy Nunna
Дата:

Hi Robert,

 

I restored my 9.3 version of pg_hba after upgrading the cluster.

Besides, 9.3 version of psql is able to connect to the database using the same syntax.

To me it looks like the build defaults of psql (client rpm in 9.5) is not consistent with build default of 9.5 server rpm. Is that a possibility????

 

Thanks,

Murthy

 

From: Robert Burgholzer [mailto:rburghol@vt.edu]
Sent: Saturday, August 13, 2016 8:47 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

 

Off the top of my head I would say check to see if pg_hba.conf syntax must be updated for the newer version, or if it was overwritten during upgrade.

 

Hth,

Robert

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

Hi All,

 

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"?

 The socket is in /tmp and that is where it is supposed to be.

srwxrwxrwx   1 postgres postgres       0 Aug 13 08:16 .s.PGSQL.5434

-rw-------   1 postgres postgres      49 Aug 13 08:16 .s.PGSQL.5434.lock

 

Following line is from postgresql.conf:

unix_socket_directories = ‘/tmp’

Do you know why the new build (9.5.3 psql and 9.5.4 as well) is looking in /var/run/postgresql? This seems to be some inconsistency in new build. BTW, I can connect successfully to this 9.5 database using 9.3 psql executable.

Thanks for reading my post.

Murthy Nunna

Database Services Group

Enterprise Server Operations Dept.

Fermi National Accelerator Laboratory

 



--

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

 

Postgres v9.5.3 and v9.5.4 Unix Socket Issue

От
Robert Burgholzer
Дата:
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:

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

Re: Postgres v9.5.3 and v9.5.4 Unix Socket Issue

От
Murthy Nunna
Дата:

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:

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

 

Re: Postgres v9.5.3 and v9.5.4 Unix Socket Issue

От
Robert Burgholzer
Дата:
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:

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

 



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

Re: Postgres v9.5.3 and v9.5.4 Unix Socket Issue

От
Robert Burgholzer
Дата:
So it looks like it wants -h /tmp

On Saturday, August 13, 2016, Robert Burgholzer <rburghol@vt.edu> wrote:
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:

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

 



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



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

Re: Postgres v9.5.3 and v9.5.4 Unix Socket Issue

От
Murthy Nunna
Дата:

Hi Robert,

 

I tried with –h /tmp and IT WORKED (in 9.5).

 

I looked at 9.3 and 9.5 psql command documentation. There is no change.

 

In 9.3 psql local connection worked without –h but in 9.5 psql it seems it is mandatory. If anybody could kindly point me to this change in behavior I really appreciate it.

 

Thanks,

Murthy

 

 

From: Robert Burgholzer [mailto:rburghol@vt.edu]
Sent: Saturday, August 13, 2016 9:47 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

 

So it looks like it wants -h /tmp

On Saturday, August 13, 2016, Robert Burgholzer <rburghol@vt.edu> wrote:

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:

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

 



--

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

 



--

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

 

Re: Postgres v9.5.3 and v9.5.4 Unix Socket Issue

От
Tom Lane
Дата:
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


Re: Postgres v9.5.3 and v9.5.4 Unix Socket Issue

От
Devrim Gündüz
Дата:
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

Вложения

Re: Postgres v9.5.3 and v9.5.4 Unix Socket Issue

От
Murthy Nunna
Дата:
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

Re: Postgres v9.5.3 and v9.5.4 Unix Socket Issue

От
Murthy Nunna
Дата:
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


Re: Postgres v9.5.3 and v9.5.4 Unix Socket Issue

От
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


Re: Postgres v9.5.3 and v9.5.4 Unix Socket Issue

От
Murthy Nunna
Дата:
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