Обсуждение: [ADMIN] psql can't connect to old DB after installing new binaries

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

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

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