Обсуждение: [GENERAL] Help: Installing 9.6 breaks local connections to 9.2 on Centos 6.9

On Mon, May 15, 2017 at 12:55:48PM -0700, Ken Tanzer wrote:
> Hi.  On a Centos 6.9 server (in the cloud with Rackspace), I'm wanting to
> install PGDG 9.6 alongside the already-running 9.2.  After installing the
> 9.6 packages (and even before doing an initdb), I am no
> longer able to make a local connection to the 9.2 server.  Instead I get
> the message:

See eg.
https://www.postgresql.org/message-id/21044.1326496507@sss.pgh.pa.us
https://www.postgresql.org/message-id/0A21BC93-7B9C-476E-AAF4-0FF71708E87F@elevated-dev.com

I'm guessing you upgraded the client libraries, which probably change the
(default) socket path.

Your options are to specify path to the socket (maybe in /tmp for running
PG92?), change to TCP connection, or specify server option
unix_socket_directories.

Justin

Ken Tanzer <ken.tanzer@gmail.com> writes:
> Hi.  On a Centos 6.9 server (in the cloud with Rackspace), I'm wanting to
> install PGDG 9.6 alongside the already-running 9.2.  After installing the
> 9.6 packages (and even before doing an initdb), I am no
> longer able to make a local connection to the 9.2 server.  Instead I get
> the message:

> psql: could not connect to server: Connection refused
> Is the server running locally and accepting
> connections on Unix domain socket "/var/run/postgresql/.s.PGSQL.5432"?

Where is the 9.2 server making its socket ... /tmp ?

What it looks like is that you've started to use a libpq.so that is
following the Red Hat convention of putting the socket file in
/var/run/postgresql, rather than /tmp.  I do not know exactly where
the PGDG packages stand on that theological issue, or whether they
changed between 9.2 and 9.6.  But the first step would be to use
"ldd" to see which libpq your invoked psql is pulling in.

            regards, tom lane

Hi,

On Mon, 2017-05-15 at 12:55 -0700, Ken Tanzer wrote:
> Hi.  On a Centos 6.9 server (in the cloud with Rackspace), I'm wanting to
> install PGDG 9.6 alongside the already-running 9.2.  After installing the
> 9.6 packages (and even before doing an initdb), I am no
> longer able to make a local connection to the 9.2 server.  Instead I get
> the message:
>
> psql: could not connect to server: Connection refused
> Is the server running locally and accepting
> connections on Unix domain socket "/var/run/postgresql/.s.PGSQL.5432"?
>
> That socket file does not exist on the server. (And in fact, the
> /var/run/postgresql directory didn't exist before installing 9.6).  When I
> configure 9.6 to use port 5433 and run it, it does create that socket for
> 5433.  I tried creating such a socket manually for 5432, but that didn't
> seem to change anything.
>
> Any help in getting this working and/or pointing out what I'm missing would
> be great.  I'm also confused conceptually about what is happening here.
> What is it that the installation (but not execution) of 9.6 does that's
> blocking the local 9.2 access?  I'm guessing it's gotta be something in the
> RPM install scripts.

PGDG RPMs use alternatives method, to replace some binaries that can be used
across multiple PostgreSQL versions, and psql is one of them. When you install
9.6, 9.6's psql has higher priority than 9.2, so that one is used -- and 9.4+
are complied with a patch that changes default socket directory from /tmp to
/var/run/postgresql, and 9.2 is not aware of that.


Workarounds:

* You can connect to 9.2 using /usr/pgsql-9.2/bin/psql command. It knows the
old socket directory.

* Pass -h /tmp to 9.6's psql, so that it connects to 9.2 instance.

-HTH

Regards,

--
Devrim Gündüz
EnterpriseDB: http://www.enterprisedb.com
PostgreSQL Danışmanı/Consultant, Red Hat Certified Engineer
Twitter: @DevrimGunduz , @DevrimGunduzTR

On 05/15/2017 01:10 PM, Devrim Gündüz wrote:
>
> Hi,
>
> On Mon, 2017-05-15 at 12:55 -0700, Ken Tanzer wrote:
>> Hi.  On a Centos 6.9 server (in the cloud with Rackspace), I'm wanting to
>> install PGDG 9.6 alongside the already-running 9.2.  After installing the
>> 9.6 packages (and even before doing an initdb), I am no
>> longer able to make a local connection to the 9.2 server.  Instead I get
>> the message:
>>
>> psql: could not connect to server: Connection refused
>> Is the server running locally and accepting
>> connections on Unix domain socket "/var/run/postgresql/.s.PGSQL.5432"?
>>
>> That socket file does not exist on the server. (And in fact, the
>> /var/run/postgresql directory didn't exist before installing 9.6).  When I
>> configure 9.6 to use port 5433 and run it, it does create that socket for
>> 5433.  I tried creating such a socket manually for 5432, but that didn't
>> seem to change anything.
>>
>> Any help in getting this working and/or pointing out what I'm missing would
>> be great.  I'm also confused conceptually about what is happening here.
>> What is it that the installation (but not execution) of 9.6 does that's
>> blocking the local 9.2 access?  I'm guessing it's gotta be something in the
>> RPM install scripts.
>
> PGDG RPMs use alternatives method, to replace some binaries that can be used
> across multiple PostgreSQL versions, and psql is one of them. When you install
> 9.6, 9.6's psql has higher priority than 9.2, so that one is used -- and 9.4+
> are complied with a patch that changes default socket directory from /tmp to
> /var/run/postgresql, and 9.2 is not aware of that.
>
>
> Workarounds:
>
> * You can connect to 9.2 using /usr/pgsql-9.2/bin/psql command. It knows the
> old socket directory.

That was where I was going until I saw this in the OP:

bash-4.1$ /usr/pgsql-9.2/bin/psql -p 5432
psql: could not connect to server: Connection refused
    Is the server running locally and accepting
    connections on Unix domain socket "/var/run/postgresql/.s.PGSQL.5432"?


>
> * Pass -h /tmp to 9.6's psql, so that it connects to 9.2 instance.
>
> -HTH
>
> Regards,
>


--
Adrian Klaver
adrian.klaver@aklaver.com

Adrian Klaver <adrian.klaver@aklaver.com> writes:
> On 05/15/2017 01:10 PM, Devrim Gündüz wrote:
>> * You can connect to 9.2 using /usr/pgsql-9.2/bin/psql command. It knows the
>> old socket directory.

> That was where I was going until I saw this in the OP:

> bash-4.1$ /usr/pgsql-9.2/bin/psql -p 5432
> psql: could not connect to server: Connection refused
>     Is the server running locally and accepting
>     connections on Unix domain socket "/var/run/postgresql/.s.PGSQL.5432"?

The default is actually compiled into libpq.so, not psql itself.
So I'm thinking what's happening here is the 9.2 psql is picking
up a libpq.so supplied by 9.6.

            regards, tom lane

Hi,

On Mon, 2017-05-15 at 16:34 -0400, Tom Lane wrote:
> > bash-4.1$ /usr/pgsql-9.2/bin/psql -p 5432
> > psql: could not connect to server: Connection refused
> >        Is the server running locally and accepting
> >        connections on Unix domain socket
> > "/var/run/postgresql/.s.PGSQL.5432"?
>
> The default is actually compiled into libpq.so, not psql itself.
> So I'm thinking what's happening here is the 9.2 psql is picking
> up a libpq.so supplied by 9.6.

Yeah, sorry, my bad. I forgot that the RPMs also put a file under
/etc/ld.so.conf.d, so that the latest libpq is picked up.

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

On 05/15/2017 01:40 PM, Ken Tanzer wrote:


>
> But let me ask, is there a big warning about this somewhere I missed?
> Can the 9.2 updates do something to fix this, or at least create a
> warning or an RPMNEW file?  I'm happy this is a cloud server and that I
> worked on a copy.  However, in different circumstances I might well have
> reasoned "well, installing the 9.6 packages really should be safe for
> 9.2, since they're clearly meant to exist side-by-side."  And then have
> a setup that no longer worked as it once did.  With an RHEL clone and
> PGDG packages straight from the horses mouth, I'd have higher
> expectations than that.  Only because of the great work y'all do! ;)

Might want to file an issue here:

https://redmine.postgresql.org/projects/pgrpms/

You will need a Postgres community account, which you can sign up for on
the same page.

>
> Cheers,
> Ken
>
>
>


--
Adrian Klaver
adrian.klaver@aklaver.com

Hi,

On Mon, 2017-05-15 at 22:35 -0700, Ken Tanzer wrote:
> https://redmine.postgresql.org/issues/2409

Not sure whether we should *fix* this or not on RPM side. This may break some
of the existing installations, right?

I'm not objecting, just asking for opinions.

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

On 05/16/2017 01:00 AM, Devrim Gündüz wrote:
>
> Hi,
>
> On Mon, 2017-05-15 at 22:35 -0700, Ken Tanzer wrote:
>> https://redmine.postgresql.org/issues/2409
>
> Not sure whether we should *fix* this or not on RPM side. This may break some
> of the existing installations, right?
>
> I'm not objecting, just asking for opinions.

To me the principle of least surprise says that it should be fixed. At
this point a pre-9.4 server is putting its socket where the primary
client library(libpq) to said server cannot find it if a 9.4+ server is
installed. The options seem to be:

1) Use the libpq appropriate for each Postgres version.

2) Modify the postgresql.conf to point at the socket directory that the
controlling libpq is looking for. I could see this being messy.

3) Document the change in behavior. Possibly here:

https://www.postgresql.org/download/linux/redhat/

PostgreSQL Yum Repository

>
> Regards,
>


--
Adrian Klaver
adrian.klaver@aklaver.com

Magnus Hagander <magnus@hagander.net> writes:
> On Tue, May 16, 2017 at 10:00 AM, Devrim Gündüz <devrim@gunduz.org> wrote:
>> Not sure whether we should *fix* this or not on RPM side. This may break
>> some of the existing installations, right?

> Changing that in a minor version seems like a *really* bad idea, because
> things *will* break. The way it is now it only breaks in case of a major
> version upgrade, and there is an easy enough workaround present.

Yeah, you don't have a lot of room in a minor release to make changes
that would affect this.

What Red Hat did about this, when I worked there, was to back-port the
unix_socket_directories patch from 9.3 into earlier branches, and then
set up the default server configuration to create sockets in both
/var/run/postgresql and /tmp.  But even if you did that, it'd require
an upgrade of the 9.2 installation before it would play nice with a
9.6 libpq, so that might be surprising.  (It would also break existing
9.2 installations that were explicitly setting unix_socket_directory,
but we can hope there are very few of those.)

            regards, tom lane