pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedpostgresql unix socket connections
11+ messages / 4 participants
[nested] [flat]
* postgresql unix socket connections
@ 2019-01-09 08:35 Mariel Cherkassky <mariel.cherkassky@gmail.com>
0 siblings, 3 replies; 11+ messages in thread
From: Mariel Cherkassky @ 2019-01-09 08:35 UTC (permalink / raw)
To: pgsql-performance@lists.postgresql.org
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 ?
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: postgresql unix socket connections
@ 2019-01-09 08:46 Mariel Cherkassky <mariel.cherkassky@gmail.com>
parent: Mariel Cherkassky <mariel.cherkassky@gmail.com>
2 siblings, 0 replies; 11+ messages in thread
From: Mariel Cherkassky @ 2019-01-09 08:46 UTC (permalink / raw)
To: pgsql-performance@lists.postgresql.org
>
> 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
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: postgresql unix socket connections
@ 2019-01-09 14:55 Tom Lane <tgl@sss.pgh.pa.us>
parent: Mariel Cherkassky <mariel.cherkassky@gmail.com>
2 siblings, 1 reply; 11+ messages in thread
From: Tom Lane @ 2019-01-09 14:55 UTC (permalink / raw)
To: Mariel Cherkassky <mariel.cherkassky@gmail.com>; +Cc: pgsql-performance@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: postgresql unix socket connections
@ 2019-01-09 15:08 Mariel Cherkassky <mariel.cherkassky@gmail.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 2 replies; 11+ messages in thread
From: Mariel Cherkassky @ 2019-01-09 15:08 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-performance@lists.postgresql.org
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
>
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: postgresql unix socket connections
@ 2019-01-09 15:09 Jeff Janes <jeff.janes@gmail.com>
parent: Mariel Cherkassky <mariel.cherkassky@gmail.com>
2 siblings, 0 replies; 11+ messages in thread
From: Jeff Janes @ 2019-01-09 15:09 UTC (permalink / raw)
To: Mariel Cherkassky <mariel.cherkassky@gmail.com>; +Cc: pgsql-performance@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: postgresql unix socket connections
@ 2019-01-09 15:13 Jeff Janes <jeff.janes@gmail.com>
parent: Mariel Cherkassky <mariel.cherkassky@gmail.com>
1 sibling, 1 reply; 11+ messages in thread
From: Jeff Janes @ 2019-01-09 15:13 UTC (permalink / raw)
To: Mariel Cherkassky <mariel.cherkassky@gmail.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-performance@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: postgresql unix socket connections
@ 2019-01-09 15:17 Mariel Cherkassky <mariel.cherkassky@gmail.com>
parent: Jeff Janes <jeff.janes@gmail.com>
0 siblings, 1 reply; 11+ messages in thread
From: Mariel Cherkassky @ 2019-01-09 15:17 UTC (permalink / raw)
To: Jeff Janes <jeff.janes@gmail.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-performance@lists.postgresql.org
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
>
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: postgresql unix socket connections
@ 2019-01-09 16:11 Tom Lane <tgl@sss.pgh.pa.us>
parent: Mariel Cherkassky <mariel.cherkassky@gmail.com>
0 siblings, 1 reply; 11+ messages in thread
From: Tom Lane @ 2019-01-09 16:11 UTC (permalink / raw)
To: Mariel Cherkassky <mariel.cherkassky@gmail.com>; +Cc: Jeff Janes <jeff.janes@gmail.com>; pgsql-performance@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: postgresql unix socket connections
@ 2019-01-10 08:22 Mariel Cherkassky <mariel.cherkassky@gmail.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 11+ messages in thread
From: Mariel Cherkassky @ 2019-01-10 08:22 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Jeff Janes <jeff.janes@gmail.com>; pgsql-performance@lists.postgresql.org
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
>
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: postgresql unix socket connections
@ 2019-01-10 09:41 Ken Tanzer <ken.tanzer@gmail.com>
parent: Mariel Cherkassky <mariel.cherkassky@gmail.com>
1 sibling, 1 reply; 11+ messages in thread
From: Ken Tanzer @ 2019-01-10 09:41 UTC (permalink / raw)
To: Mariel Cherkassky <mariel.cherkassky@gmail.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-performance@lists.postgresql.org
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.
https://www.postgresql.org/message-id/flat/CAD3a31XLfN0hgEVJPzfKj9JzVqEOpLrn6eE06PGNMq5JsFngPA%40mai...
Cheers,
Ken
--
AGENCY Software
A Free Software data system
By and for non-profits
*http://agency-software.org/ <http://agency-software.org/>*
*https://demo.agency-software.org/client
<https://demo.agency-software.org/client>*
ken.tanzer@agency-software.org
(253) 245-3801
Subscribe to the mailing list
<agency-general-request@lists.sourceforge.net?body=subscribe> to
learn more about AGENCY or
follow the discussion.
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: postgresql unix socket connections
@ 2019-01-10 10:36 Mariel Cherkassky <mariel.cherkassky@gmail.com>
parent: Ken Tanzer <ken.tanzer@gmail.com>
0 siblings, 0 replies; 11+ messages in thread
From: Mariel Cherkassky @ 2019-01-10 10:36 UTC (permalink / raw)
To: Ken Tanzer <ken.tanzer@gmail.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-performance@lists.postgresql.org
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.
>
>
> https://www.postgresql.org/message-id/flat/CAD3a31XLfN0hgEVJPzfKj9JzVqEOpLrn6eE06PGNMq5JsFngPA%40mai...
>
> Cheers,
> Ken
>
>
> --
> AGENCY Software
> A Free Software data system
> By and for non-profits
> *http://agency-software.org/ <http://agency-software.org/>*
> *https://demo.agency-software.org/client
> <https://demo.agency-software.org/client>*
> ken.tanzer@agency-software.org
> (253) 245-3801
>
> Subscribe to the mailing list
> <agency-general-request@lists.sourceforge.net?body=subscribe> to
> learn more about AGENCY or
> follow the discussion.
>
^ permalink raw reply [nested|flat] 11+ messages in thread
end of thread, other threads:[~2019-01-10 10:36 UTC | newest]
Thread overview: 11+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2019-01-09 08:35 postgresql unix socket connections Mariel Cherkassky <mariel.cherkassky@gmail.com>
2019-01-09 08:46 ` Mariel Cherkassky <mariel.cherkassky@gmail.com>
2019-01-09 14:55 ` Tom Lane <tgl@sss.pgh.pa.us>
2019-01-09 15:08 ` Mariel Cherkassky <mariel.cherkassky@gmail.com>
2019-01-09 15:13 ` Jeff Janes <jeff.janes@gmail.com>
2019-01-09 15:17 ` Mariel Cherkassky <mariel.cherkassky@gmail.com>
2019-01-09 16:11 ` Tom Lane <tgl@sss.pgh.pa.us>
2019-01-10 08:22 ` Mariel Cherkassky <mariel.cherkassky@gmail.com>
2019-01-10 09:41 ` Ken Tanzer <ken.tanzer@gmail.com>
2019-01-10 10:36 ` Mariel Cherkassky <mariel.cherkassky@gmail.com>
2019-01-09 15:09 ` Jeff Janes <jeff.janes@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox