Обсуждение: postgresql unix socket connections

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

postgresql unix socket connections

От
Mariel Cherkassky
Дата:
Hi,
I'm trying to understand some issues that I'm having with the unix_socket settings and pgsql.
I have 2 machines with pg v9.2.5 with the same next settings :
#listen_addresses = 'localhost'
#unix_socket_directory = ''

in both of the machines I run netstat to check on what socket the postgres listens for connections and I got the same output : 
machine 1
netstat -nlp | grep postgres
tcp        0      0 127.0.0.1:5432              0.0.0.0:*                   LISTEN      2049/postgres
unix  2      [ ACC ]     STREAM     LISTENING     12086  2049/postgres       /tmp/.s.PGSQL.5432

machine 2
tcp        0      0 127.0.0.1:5432              0.0.0.0:*                   LISTEN      3729/postgres
unix  2      [ ACC ]     STREAM     LISTENING     51587140 3729/postgres       /tmp/.s.PGSQL.5432


In both of the machines I tried to check if there are some PG environment variables but nothing was set : 
env | grep PG

The pg_hba in both cases is the default pg_hba.

Now, In machine 1 when I run psql I get the prompt password but in machine 2 I keep getting the next error : 

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

One important thing that I didnt mention, is that I installed in machine 2 package postgresql-libs.x86_64 0:8.4.20-8.el6_9 from the postgres repository (in order to upgrade it to 9.6).

I solved it in machine 2 by setting the unix_socket_directory to /var/run/postgresql/.s.PGSQL.5432 and restarting the database.

My questions are :
1)Why in machine 1, where I dont have a soft link /var/run/postgresql/.s.PGSQL.5432 that directs to the temp dir I can connect succesfully ? (env|grep PG didnt show anything). 
2)What might explain the issue on machine 2? Or maybe machine2 works normally but machine1 has an issue ?

Re: postgresql unix socket connections

От
Mariel Cherkassky
Дата:
I installed on machine 2 the next packages and not what I mentioned on my last comment : 
---> Package postgresql96.x86_64 0:9.6.10-1PGDG.rhel6 will be installed
---> Package postgresql96-contrib.x86_64 0:9.6.10-1PGDG.rhel6 will be installed
---> Package postgresql96-libs.x86_64 0:9.6.10-1PGDG.rhel6 will be installed
---> Package postgresql96-server.x86_64 0:9.6.10-1PGDG.rhel6 will be installed
 

Re: postgresql unix socket connections

От
Tom Lane
Дата:
Mariel Cherkassky <mariel.cherkassky@gmail.com> writes:
> I'm trying to understand some issues that I'm having with the unix_socket
> settings and pgsql.
> I have 2 machines with pg v9.2.5 with the same next settings :
> #listen_addresses = 'localhost'
> #unix_socket_directory = ''

This will result in the server creating the socket in whatever it thinks
is the default socket directory.  Traditionally PG uses /tmp as the
default socket directory, and your netstat result is consistent with that:

> unix  2      [ ACC ]     STREAM     LISTENING     51587140 3729/postgres
>    /tmp/.s.PGSQL.5432

However, this:

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

shows that your psql is using a libpq that thinks the default socket
directory is /var/run/postgresql.  That's a build-time option, and
I recall that Red Hat builds their postgresql package that way.
I'm not 100% sure which way the PGDG RPMs do it.

You could override libpq's default, for instance via "psql -h /tmp".
But probably you'd be better off removing any packages that provide
libpq versions that don't match your server.

Alternatively, you could configure the server to create socket
files in both places.

            regards, tom lane

Re: postgresql unix socket connections

От
Mariel Cherkassky
Дата:
Hey Tom,
I'm aware of how I can solve it. I wanted to understand why after installing the pg 9.6 packages suddenly psql tries to access the socket on /var/run/postgresql. Does the libpq default unix socket is changed between those two versions ? (9.6,9.2)

‫בתאריך יום ד׳, 9 בינו׳ 2019 ב-16:55 מאת ‪Tom Lane‬‏ <‪tgl@sss.pgh.pa.us‬‏>:‬
Mariel Cherkassky <mariel.cherkassky@gmail.com> writes:
> I'm trying to understand some issues that I'm having with the unix_socket
> settings and pgsql.
> I have 2 machines with pg v9.2.5 with the same next settings :
> #listen_addresses = 'localhost'
> #unix_socket_directory = ''

This will result in the server creating the socket in whatever it thinks
is the default socket directory.  Traditionally PG uses /tmp as the
default socket directory, and your netstat result is consistent with that:

> unix  2      [ ACC ]     STREAM     LISTENING     51587140 3729/postgres
>    /tmp/.s.PGSQL.5432

However, this:

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

shows that your psql is using a libpq that thinks the default socket
directory is /var/run/postgresql.  That's a build-time option, and
I recall that Red Hat builds their postgresql package that way.
I'm not 100% sure which way the PGDG RPMs do it.

You could override libpq's default, for instance via "psql -h /tmp".
But probably you'd be better off removing any packages that provide
libpq versions that don't match your server.

Alternatively, you could configure the server to create socket
files in both places.

                        regards, tom lane

Re: postgresql unix socket connections

От
Jeff Janes
Дата:
On Wed, Jan 9, 2019 at 3:35 AM Mariel Cherkassky <mariel.cherkassky@gmail.com> wrote:

Now, In machine 1 when I run psql I get the prompt password but in machine 2 I keep getting the next error : 

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

One important thing that I didnt mention, is that I installed in machine 2 package postgresql-libs.x86_64 0:8.4.20-8.el6_9 from the postgres repository (in order to upgrade it to 9.6).

The front end and the backend have compiled-in defaults for the socket directory.  If you installed them from different sources, they may have different compiled-in defaults.  Which means they may not be able to rendezvous using the default settings for both of them.  

You can override the default using unix_socket_directory on the server (as you discovered).  On the client you can override it by using -h (or PGHOST or host= or whatever mechanism), with an argument that looks like a directory, starting with a '/'.

Cheers,

Jeff

Re: postgresql unix socket connections

От
Jeff Janes
Дата:
On Wed, Jan 9, 2019 at 10:09 AM Mariel Cherkassky <mariel.cherkassky@gmail.com> wrote:
Hey Tom,
I'm aware of how I can solve it. I wanted to understand why after installing the pg 9.6 packages suddenly psql tries to access the socket on /var/run/postgresql. Does the libpq default unix socket is changed between those two versions ? (9.6,9.2)

It is not a version issue, but a packaging issue.  Different systems have different conventions on where sockets should go, and the packager imposes their opinion on the things they package.

Cheers,

Jeff

Re: postgresql unix socket connections

От
Mariel Cherkassky
Дата:
But in both of the machines I have the same os and I used the same repository - postgresql rpm repository. The only difference is that in machine 2 I also installed all pg 9.6 packages. Even When I try to use /usr/pgsql-9.2/bin/psql the psql still tries to access the /var/run/run/postgresql dir as the socket dir. Does those packages include a different libpq ?  What postgres package change the libpq ?

‫בתאריך יום ד׳, 9 בינו׳ 2019 ב-17:13 מאת ‪Jeff Janes‬‏ <‪jeff.janes@gmail.com‬‏>:‬
On Wed, Jan 9, 2019 at 10:09 AM Mariel Cherkassky <mariel.cherkassky@gmail.com> wrote:
Hey Tom,
I'm aware of how I can solve it. I wanted to understand why after installing the pg 9.6 packages suddenly psql tries to access the socket on /var/run/postgresql. Does the libpq default unix socket is changed between those two versions ? (9.6,9.2)

It is not a version issue, but a packaging issue.  Different systems have different conventions on where sockets should go, and the packager imposes their opinion on the things they package.

Cheers,

Jeff

Re: postgresql unix socket connections

От
Tom Lane
Дата:
Mariel Cherkassky <mariel.cherkassky@gmail.com> writes:
> But in both of the machines I have the same os and I used the same
> repository - postgresql rpm repository. The only difference is that in
> machine 2 I also installed all pg 9.6 packages. Even When I try to use
> /usr/pgsql-9.2/bin/psql the psql still tries to access the
> /var/run/run/postgresql dir as the socket dir. Does those packages include
> a different libpq ?  What postgres package change the libpq ?

"rpm -ql" would tell you about which packages supply what.

Assuming there's more than one libpq.so on your machine, which it sounds
like there is, which one gets used depends on the dynamic linker's
configuration -- see /etc/ld.so.conf and "man ldconfig".

            regards, tom lane

Re: postgresql unix socket connections

От
Mariel Cherkassky
Дата:
In machine 2 : 
I found 4 libpq.so files : 
[root@~]# locate libpq.so
/usr/lib64/libpq.so.5
/usr/lib64/libpq.so.5.2
/usr/pgsql-9.6/lib/libpq.so.5
/usr/pgsql-9.6/lib/libpq.so.5.9

 cat /etc/ld.so.conf
include ld.so.conf.d/*.conf
 ld.so.conf.d]# cat /etc/ld.so.conf.d/postgresql-pgdg-libs.conf
/usr/pgsql-9.6/lib/


In machine 1 :
locate libpq.so
/usr/lib64/libpq.so.5
/usr/lib64/libpq.so.5.2
/usr/pgsql-9.2/lib/libpq.so.5
/usr/pgsql-9.2/lib/libpq.so.5.5



I checked with rpm -ql the packge postgresql96-libs.x86_64 0:9.6.10-1PGDG.rhel6 and it seems that it indeed put the new libpq.so in the system. My question is, is it possible that it also deleted the 9.2 libpq file ?


‫בתאריך יום ד׳, 9 בינו׳ 2019 ב-18:11 מאת ‪Tom Lane‬‏ <‪tgl@sss.pgh.pa.us‬‏>:‬
Mariel Cherkassky <mariel.cherkassky@gmail.com> writes:
> But in both of the machines I have the same os and I used the same
> repository - postgresql rpm repository. The only difference is that in
> machine 2 I also installed all pg 9.6 packages. Even When I try to use
> /usr/pgsql-9.2/bin/psql the psql still tries to access the
> /var/run/run/postgresql dir as the socket dir. Does those packages include
> a different libpq ?  What postgres package change the libpq ?

"rpm -ql" would tell you about which packages supply what.

Assuming there's more than one libpq.so on your machine, which it sounds
like there is, which one gets used depends on the dynamic linker's
configuration -- see /etc/ld.so.conf and "man ldconfig".

                        regards, tom lane

Re: postgresql unix socket connections

От
Ken Tanzer
Дата:
On Wed, Jan 9, 2019 at 7:09 AM Mariel Cherkassky <mariel.cherkassky@gmail.com> wrote:
Hey Tom,
I'm aware of how I can solve it. I wanted to understand why after installing the pg 9.6 packages suddenly psql tries to access the socket on /var/run/postgresql. Does the libpq default unix socket is changed between those two versions ? (9.6,9.2)

I hit this kind of problem too.  Per Devrim in this thread, the default socket location changed in v. 9.4.


Cheers,
Ken


-- 
AGENCY Software  
A Free Software data system
By and for non-profits
(253) 245-3801

learn more about AGENCY or
follow the discussion.

Re: postgresql unix socket connections

От
Mariel Cherkassky
Дата:
Thanks Ken. I just wanted to make sure that it happened because of 9.6 packages installation and not because of any other reason.

‫בתאריך יום ה׳, 10 בינו׳ 2019 ב-11:42 מאת ‪Ken Tanzer‬‏ <‪ken.tanzer@gmail.com‬‏>:‬
On Wed, Jan 9, 2019 at 7:09 AM Mariel Cherkassky <mariel.cherkassky@gmail.com> wrote:
Hey Tom,
I'm aware of how I can solve it. I wanted to understand why after installing the pg 9.6 packages suddenly psql tries to access the socket on /var/run/postgresql. Does the libpq default unix socket is changed between those two versions ? (9.6,9.2)

I hit this kind of problem too.  Per Devrim in this thread, the default socket location changed in v. 9.4.


Cheers,
Ken


-- 
AGENCY Software  
A Free Software data system
By and for non-profits
(253) 245-3801

learn more about AGENCY or
follow the discussion.