pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedpsql can't connect to old DB after installing new binaries
7+ messages / 5 participants
[nested] [flat]
* psql can't connect to old DB after installing new binaries
@ 2017-10-04 19:26 Don Seiler <don@seiler.us>
0 siblings, 2 replies; 7+ messages in thread
From: Don Seiler @ 2017-10-04 19:26 UTC (permalink / raw)
To: pgsql-admin
CentOS 6. I have a box with 9.2 installed and running, planning to upgrade
to 9.6. All packages are from the postgresql yum repos, not the CentOS base
repos.
However, after installing the 9.6 binaries, psql complains about the socket
file:
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"?
My postgres 9.2 database has a socket file in /tmp/.s.PGSQL.5432. What's
interesting is that I get this error even when running the 9.2 psql as well:
$ /usr/pgsql-9.2/bin/psql
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"?
$ /usr/pgsql-9.2/bin/psql --version
psql (PostgreSQL) 9.2.22
However I can connect if I specify "-h /tmp":
$ /usr/pgsql-9.2/bin/psql -h /tmp
psql (9.2.22)
Type "help" for help.
postgres=#
Also when if I remove the 9.6 binaries (yum remove postgresql96*), then
psql works as expected as well.
I can sort of understand the socket thing from the 9.6 psql binary, but not
the 9.2 binary. Either way it would break scripts for the existing DB,
maybe some other backwards compatibility issues. Is there something else to
change (other than unix_socket_directory, which would require a restart as
well) so that installing the 9.6 binaries wouldn't affect the 9.2
environment?
--
Don Seiler
www.seiler.us
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: psql can't connect to old DB after installing new binaries
@ 2017-10-04 20:03 Tom Lane <tgl@sss.pgh.pa.us>
parent: Don Seiler <don@seiler.us>
1 sibling, 1 reply; 7+ messages in thread
From: Tom Lane @ 2017-10-04 20:03 UTC (permalink / raw)
To: Don Seiler <don@seiler.us>; +Cc: pgsql-admin
Don Seiler <don@seiler.us> writes:
> However, after installing the 9.6 binaries, psql complains about the socket
> file:
> 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"?
> My postgres 9.2 database has a socket file in /tmp/.s.PGSQL.5432.
Yeah, I think Devrim switched the default socket location from /tmp
to /var/run/postgresql to conform to what the Red Hat build does.
> What's interesting is that I get this error even when running the 9.2
> psql as well:
I believe that the client-side default for this is embedded in libpq.so.
Probably the way things are set up, the 9.2 psql uses the 9.6 libpq.so
if it's installed. That would tend to happen if the libpq.so is being
picked up from /usr/lib(64) rather than a version-specific directory
("ldd" would help you check that).
Since at least 9.3, it's been possible to configure the server to create
more than one socket file (see unix_socket_directories). Recommended
practice when you're dealing with multiple client builds is to create
sockets in both places.
regards, tom lane
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: psql can't connect to old DB after installing new binaries
@ 2017-10-04 20:09 Jerry Sievers <gsievers19@comcast.net>
parent: Don Seiler <don@seiler.us>
1 sibling, 0 replies; 7+ messages in thread
From: Jerry Sievers @ 2017-10-04 20:09 UTC (permalink / raw)
To: Don Seiler <don@seiler.us>; +Cc: pgsql-admin
Don Seiler <don@seiler.us> writes:
> CentOS 6. I have a box with 9.2 installed and running, planning to
> upgrade to 9.6. All packages are from the postgresql yum repos, not
> the CentOS base repos.
>
> However, after installing the 9.6 binaries, psql complains about the
> socket file:
>
> 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"?
Try PGHOST=/tmp psql ...
Or psql -h /tmp -p $port...
ISTM your newer libpq is now being used and as seen above it's built
with a different default socket file location.
>
> My postgres 9.2 database has a socket file in /tmp/.s.PGSQL.5432.
> What's interesting is that I get this error even when running the 9.2
> psql as well:
>
> $ /usr/pgsql-9.2/bin/psql
> 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"?
>
> $ /usr/pgsql-9.2/bin/psql --version
> psql (PostgreSQL) 9.2.22
>
> However I can connect if I specify "-h /tmp":
>
> $ /usr/pgsql-9.2/bin/psql -h /tmp
> psql (9.2.22)
> Type "help" for help.
>
> postgres=#
>
> Also when if I remove the 9.6 binaries (yum remove postgresql96*),
> then psql works as expected as well.
>
> I can sort of understand the socket thing from the 9.6 psql binary,
> but not the 9.2 binary. Either way it would break scripts for the
> existing DB, maybe some other backwards compatibility issues. Is
> there something else to change (other than unix_socket_directory,
> which would require a restart as well) so that installing the 9.6
> binaries wouldn't affect the 9.2 environment?
>
> --
> Don Seiler
> www.seiler.us
>
>
--
Jerry Sievers
Postgres DBA/Development Consulting
e: postgres.consulting@comcast.net
p: 312.241.7800
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: psql can't connect to old DB after installing new binaries
@ 2017-10-04 20:36 Don Seiler <don@seiler.us>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 7+ messages in thread
From: Don Seiler @ 2017-10-04 20:36 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-admin
On Wed, Oct 4, 2017 at 3:03 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
> I believe that the client-side default for this is embedded in libpq.so.
> Probably the way things are set up, the 9.2 psql uses the 9.6 libpq.so
> if it's installed. That would tend to happen if the libpq.so is being
> picked up from /usr/lib(64) rather than a version-specific directory
> ("ldd" would help you check that).
>
Looks like that is what happened here:
/usr/pgsql-9.2/bin
postgres@dts-clone$ ldd psql | grep libpq
libpq.so.5 => /usr/pgsql-9.6/lib/libpq.so.5 (0x00007f9f0b2bf000)
It's pointing to the newly installed version instead of the version it the
9.2 lib directory. There isn't any such file under /usr/lib64. So how is it
even aware of that /usr/pgsql-9.6 version?
Since at least 9.3, it's been possible to configure the server to create
> more than one socket file (see unix_socket_directories). Recommended
> practice when you're dealing with multiple client builds is to create
> sockets in both places.
Yep that sounds good for later, although can't really help me in this case.
I guess I can set unix_socket_directory in 9.2 to use the new location but
that requires a restart anyway, not ideal.
Would creating a symlink be a workaround? Would that cause issues with
server restarts?
Mind that this is only to prepare for the upgrade. We aren't planning to
run both 9.2 and 9.6, and eventually 9.2 would be removed.
--
Don Seiler
www.seiler.us
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: psql can't connect to old DB after installing new binaries
@ 2017-10-04 21:32 Devrim Gündüz <devrim@gunduz.org>
parent: Don Seiler <don@seiler.us>
0 siblings, 2 replies; 7+ messages in thread
From: Devrim Gündüz @ 2017-10-04 21:32 UTC (permalink / raw)
To: Don Seiler <don@seiler.us>; Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-admin
Hi,
On Wed, 2017-10-04 at 15:36 -0500, Don Seiler wrote:
>
> It's pointing to the newly installed version instead of the version it the
> 9.2 lib directory. There isn't any such file under /usr/lib64. So how is it
> even aware of that /usr/pgsql-9.6 version?
There is a file under /etc/ld.so.conf.d directory, which does this magic.
>
> Yep that sounds good for later, although can't really help me in this case.
> I guess I can set unix_socket_directory in 9.2 to use the new location but
> that requires a restart anyway, not ideal.
>
> Would creating a symlink be a workaround? Would that cause issues with
> server restarts?
>
> Mind that this is only to prepare for the upgrade. We aren't planning to
> run both 9.2 and 9.6, and eventually 9.2 would be removed.
What happens when you use 9.2's psql?
/usr/pgsql-9.2/bin/psql
Regards,
--
Devrim Gündüz
EnterpriseDB: https://www.enterprisedb.com
PostgreSQL Consultant, Red Hat Certified Engineer
Twitter: @DevrimGunduz , @DevrimGunduzTR
Attachments:
signature.asc (application/pgp-signature, 832B) download
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: psql can't connect to old DB after installing new binaries
@ 2017-10-04 21:38 David G. Johnston <david.g.johnston@gmail.com>
parent: Devrim Gündüz <devrim@gunduz.org>
1 sibling, 0 replies; 7+ messages in thread
From: David G. Johnston @ 2017-10-04 21:38 UTC (permalink / raw)
To: Devrim Gündüz <devrim@gunduz.org>; +Cc: Don Seiler <don@seiler.us>; Tom Lane <tgl@sss.pgh.pa.us>; pgsql-admin
On Wed, Oct 4, 2017 at 2:32 PM, Devrim Gündüz <devrim@gunduz.org> wrote:
> >
> > Mind that this is only to prepare for the upgrade. We aren't planning to
> > run both 9.2 and 9.6, and eventually 9.2 would be removed.
>
> What happens when you use 9.2's psql?
>
> /usr/pgsql-9.2/bin/psql
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: psql can't connect to old DB after installing new binaries
@ 2017-10-05 14:56 Don Seiler <don@seiler.us>
parent: Devrim Gündüz <devrim@gunduz.org>
1 sibling, 0 replies; 7+ messages in thread
From: Don Seiler @ 2017-10-05 14:56 UTC (permalink / raw)
To: Devrim Gündüz <devrim@gunduz.org>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-admin
On Wed, Oct 4, 2017 at 4:32 PM, Devrim Gündüz <devrim@gunduz.org> wrote:
>
>
> There is a file under /etc/ld.so.conf.d directory, which does this magic.
>
Yes I can see this in action now. Before installing 9.6:
# cat /etc/ld.so.conf.d/postgresql-pgdg-libs.conf
/usr/pgsql-9.2/lib/
After installing 9.6 (just installing the RPMs, no init or config):
# cat /etc/ld.so.conf.d/postgresql-pgdg-libs.conf
/usr/pgsql-9.6/lib/
Very good to know, thanks.
What happens when you use 9.2's psql?
>
> /usr/pgsql-9.2/bin/psql
As David shared, I got the same socket file error when directly using that
binary file, and ldd shows that it is linked to the 9.6 libpq.so.5 file.
Thanks,
Don.
--
Don Seiler
www.seiler.us
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2017-10-05 14:56 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2017-10-04 19:26 psql can't connect to old DB after installing new binaries Don Seiler <don@seiler.us>
2017-10-04 20:03 ` Tom Lane <tgl@sss.pgh.pa.us>
2017-10-04 20:36 ` Don Seiler <don@seiler.us>
2017-10-04 21:32 ` Devrim Gündüz <devrim@gunduz.org>
2017-10-04 21:38 ` David G. Johnston <david.g.johnston@gmail.com>
2017-10-05 14:56 ` Don Seiler <don@seiler.us>
2017-10-04 20:09 ` Jerry Sievers <gsievers19@comcast.net>
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