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.
/messages/by-id/21044.1326496507@sss.pgh.pa.us
/messages/by-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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

Thanks everyone for the replies. Adrian is right--I did try this with the
9.2 binaries, with the same problem. But to address Tom's question (and if
I'm using ldd properly), the 9.2 psql binary is using the 9.6 libpq.

[root@centos-new postgresql]# ldd /usr/bin/psql | grep libpq
libpq.so.5 => /usr/pgsql-9.6/lib/libpq.so.5 (0x00007f2e6c99a000)
[root@centos-new postgresql]# ldd /usr/pgsql-9.2/bin/psql | grep libpq
libpq.so.5 => /usr/pgsql-9.6/lib/libpq.so.5 (0x00007f52f9c67000)

Devrim--the -h /tmp option works great.

I still wanted this to just "work" though, for scripts and such. I
specified the socket directory in the 9.2 postgresql.conf, and it seems to
be working "normally" now.

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! ;)

Cheers,
Ken

--
AGENCY Software
A Free Software data system
By and for non-profits
*http://agency-software.org/ <http://agency-software.org/&gt;*
*https://agency-software.org/demo/client
<https://agency-software.org/demo/client&gt;*
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.

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On Mon, May 15, 2017 at 4:45 PM, Adrian Klaver <adrian.klaver@aklaver.com>
wrote:

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.

Done, and thanks for pointing me to the tracker.

https://redmine.postgresql.org/issues/2409

Cheers,
Ken

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 Tue, May 16, 2017 at 10:00 AM, Devrim Gündüz <devrim@gunduz.org> 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.

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.

But it should perhaps be more clearly documented somewhere.

--
Magnus Hagander
Me: https://www.hagander.net/ <http://www.hagander.net/&gt;
Work: https://www.redpill-linpro.com/ <http://www.redpill-linpro.com/&gt;

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general