Обсуждение: Postgres v9.5.3 and v9.5.4 Unix Socket Issue
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
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
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
Athletics: http://athleticalgorithm.wordpress.com/
Science: http://robertwb.wordpress.com/
psql (client rpm in 9.5) is not consistent with build default of 9.5 server rpm. Is that a possibility????
h hostname
--host=hostnameSpecifies 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
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/
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=hostnameSpecifies 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 MingusAthletics: http://
athleticalgorithm.wordpress. com/ Science: http://robertwb.
wordpress.com/
--
Robert W. Burgholzer
'Making the simple complicated is commonplace; making the complicated simple, awesomely simple, that's creativity.' - Charles Mingus
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:
h hostname
--host=hostnameSpecifies 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 MingusAthletics: http://athleticalgo
rithm.wordpress.com/ Science: http://robertwb.wordp
ress.com/
----
Robert W. Burgholzer
'Making the simple complicated is commonplace; making the complicated simple, awesomely simple, that's creativity.' - Charles MingusAthletics: http://athleticalgorithm.wordpress. com/ Science: http://robertwb.wordpress.com/
--
Robert W. Burgholzer
'Making the simple complicated is commonplace; making the complicated simple, awesomely simple, that's creativity.' - Charles Mingus
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:
h hostname
--host=hostnameSpecifies 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 MingusAthletics: http://athleticalgorithm.wordpress.com/
Science: http://robertwb.wordpress.com/
----
Robert W. Burgholzer
'Making the simple complicated is commonplace; making the complicated simple, awesomely simple, that's creativity.' - Charles MingusAthletics: http://athleticalgorithm.wordpress.com/
Science: http://robertwb.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/
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