Thread: Files overwritten when installing v9.6 in machine with v8.4previously running

Hi again,

I previously asked in another list about what version would be the best 
to make a pg_upgrade from v8.4. I've finally chosen 9.6 to make version 
jump less drastic, though I've been testing pg_upgrade whole process 
with both 9.6 and 10. But after reinstalling my test machine several 
times now I have 9.6 so that's what I'll be talking about.

The problem I have is that when I install PG9.6 in a machine with CentOS 
and PG8.4 (version that comes with OS) then v8.4 becomes unavailable. 
Finally I've found that psql binary changes after v9.6 installation. But 
checking files that are installed with this version, I can't see what 
package causes the problem. And as I'm not used to *nix systems, I'm not 
sure how can I solve this. The process I've done:

- Installed a new machine with CentOS installation CD, which installs 
CentOS and PG8.4

- Made InitDB, started service (with "service postgresql start" 
instruction).

- Without changing pg_hba.conf (nor postgresql.conf) file, I've 
connected with psql, created some roles and some empty databases. 
Disconnected from database ("\q") and restored some backups with psql 
command.

- After all this, I can connect to v8.4 with root user and postgres 
user. Finally I've stopped postgresql (service stop).

- Tried to start postgres again but using pg_ctl. Tested connection with 
psql again (ok) and stopped.

- v8.4 is installed in /usr/bin and /var/lib/pgsql folders, that seemed 
quite generic for me, so I made a copy of both (with "cp -pr" command):
/usr/bin ==> /usr/bin_8.4
/var/lib/pgsl ==> /var/lib/pgsl_8.4

- I've tested copy: started with pg_ctl (the one that was copied to 
/usr/bin_8.4) pointing to copied data folder (/var/lib/pgsl_8.4/data). 
Started ok. Tried to connect with psql (ok) and stopped again.

- At this point, I've followed 
https://www.postgresql.org/download/linux/rdhat/ instructions to install 
v9.6: download repo, install client and install server.

- Made InitDB in v9.6 and changed listening port in postgresql.conf 
(v9.6 config file)

- After this installation, if I make "service postgresql start" I can 
see "something" running (I can list processes with ps -ef) but when 
trying to connect with psql it won't connect exiting with a message like 
(sorry, it's my translation as original message is not in english):
psql: could not connect to server: file or directory does not exist.
     Is server locally running and listening at Unix domain socket 
«/var/run/postgresql/.s.PGSQL.5432»?
Also "/usr/bin/psql" file is different than it's copy at bin_8.4 (some 
Kbytes bigger than the original file).


Older PG version is now unavailable, and I can't run pg_upgrade to 
migrate to a newer version. I've managed to make the whole process in 
Ubuntu 18, but Production server is CentOS 6.8. Any clue about why is 
this happening and how can I deal with this situation? During CentOS 
installation I can't (or haven't seen) change installation directory for 
Postgres. I now about data_directory in postgresql.conf, but not sure 
how to manage binaries dir.

I have recorded all the commands I've run, just in case anyone need more 
detailed info.


Thanks in advance,

Ekaterina

The  9.6 binaries should be installed in their own directories, so no need to move anything around.

Hi again,

I previously asked in another list about what version would be the best to make a pg_upgrade from v8.4. I've finally chosen 9.6 to make version jump less drastic, though I've been testing pg_upgrade whole process with both 9.6 and 10. But after reinstalling my test machine several times now I have 9.6 so that's what I'll be talking about.

The problem I have is that when I install PG9.6 in a machine with CentOS and PG8.4 (version that comes with OS) then v8.4 becomes unavailable. Finally I've found that psql binary changes after v9.6 installation. But checking files that are installed with this version, I can't see what package causes the problem. And as I'm not used to *nix systems, I'm not sure how can I solve this. The process I've done:

- Installed a new machine with CentOS installation CD, which installs CentOS and PG8.4

- Made InitDB, started service (with "service postgresql start" instruction).

- Without changing pg_hba.conf (nor postgresql.conf) file, I've connected with psql, created some roles and some empty databases. Disconnected from database ("\q") and restored some backups with psql command.

- After all this, I can connect to v8.4 with root user and postgres user. Finally I've stopped postgresql (service stop).

- Tried to start postgres again but using pg_ctl. Tested connection with psql again (ok) and stopped.

- v8.4 is installed in /usr/bin and /var/lib/pgsql folders, that seemed quite generic for me, so I made a copy of both (with "cp -pr" command):
/usr/bin ==> /usr/bin_8.4
/var/lib/pgsl ==> /var/lib/pgsl_8.4

- I've tested copy: started with pg_ctl (the one that was copied to /usr/bin_8.4) pointing to copied data folder (/var/lib/pgsl_8.4/data). Started ok. Tried to connect with psql (ok) and stopped again.

- At this point, I've followed https://www.postgresql.org/download/linux/rdhat/ instructions to install v9.6: download repo, install client and install server.

- Made InitDB in v9.6 and changed listening port in postgresql.conf (v9.6 config file)

- After this installation, if I make "service postgresql start" I can see "something" running (I can list processes with ps -ef) but when trying to connect with psql it won't connect exiting with a message like (sorry, it's my translation as original message is not in english):
psql: could not connect to server: file or directory does not exist.
    Is server locally running and listening at Unix domain socket «/var/run/postgresql/.s.PGSQL.5432»?
Also "/usr/bin/psql" file is different than it's copy at bin_8.4 (some Kbytes bigger than the original file).


Older PG version is now unavailable, and I can't run pg_upgrade to migrate to a newer version. I've managed to make the whole process in Ubuntu 18, but Production server is CentOS 6.8. Any clue about why is this happening and how can I deal with this situation? During CentOS installation I can't (or haven't seen) change installation directory for Postgres. I now about data_directory in postgresql.conf, but not sure how to manage binaries dir.

I have recorded all the commands I've run, just in case anyone need more detailed info.


Thanks in advance,

Ekaterina

Hi Ron,

I first thought they were going to be installed in their own directories, but I'm starting to think that I'm missing something during instalation process or maybe I'm missing something at deeper level (probably lack of knowledge about linux and postgres). The thing is after installing v9.6, having both versions running, I can't connect to the older one and I'm having the message:  "no such file or directory. Is the server running locally and accepting connections on Unix domain socket  "/var/run/postgresql/.s.PGSQL.5432"?".

I've checked ports and everything seems to be fine:

[root@test-ekaterina6 data]# sudo lsof -i -P -n | grep LISTEN
rpcbind    1796      rpc    8u  IPv4  14941      0t0  TCP *:111 (LISTEN)
rpcbind    1796      rpc   11u  IPv6  14946      0t0  TCP *:111 (LISTEN)
rpc.statd  1818  rpcuser    9u  IPv4  15042      0t0  TCP *:39832 (LISTEN)
rpc.statd  1818  rpcuser   11u  IPv6  15050      0t0  TCP *:36471 (LISTEN)
cupsd      1875     root    6u  IPv6  15240      0t0  TCP [::1]:631 (LISTEN)
cupsd      1875     root    7u  IPv4  15241      0t0  TCP 127.0.0.1:631 (LISTEN)
sshd       2129     root    3u  IPv4  15948      0t0  TCP *:22 (LISTEN)
sshd       2129     root    4u  IPv6  15960      0t0  TCP *:22 (LISTEN)
master     2208     root   12u  IPv4  16140      0t0  TCP 127.0.0.1:25 (LISTEN)
master     2208     root   13u  IPv6  16147      0t0  TCP [::1]:25 (LISTEN)
postmaste 31600 postgres    3u  IPv6  55497      0t0  TCP [::1]:5433 (LISTEN) ==> v9.6
postmaste 31600 postgres    4u  IPv4  55498      0t0  TCP 127.0.0.1:5433 (LISTEN) ==> v9.6
postmaste 31674 postgres    3u  IPv6  55863      0t0  TCP [::1]:5432 (LISTEN) ==> v8.4
postmaste 31674 postgres    4u  IPv4  55864      0t0  TCP 127.0.0.1:5432 (LISTEN) ==> v8.4

I've also tried to change v8.4 pg_hba.conf setting local connections to trusted but this also doesn't work.

The  9.6 binaries should be installed in their own directories, so no need to move anything around.

On 9/27/19 9:23 AM, Ekaterina Amez wrote:

Hi again,

I previously asked in another list about what version would be the best to make a pg_upgrade from v8.4. I've finally chosen 9.6 to make version jump less drastic, though I've been testing pg_upgrade whole process with both 9.6 and 10. But after reinstalling my test machine several times now I have 9.6 so that's what I'll be talking about.

The problem I have is that when I install PG9.6 in a machine with CentOS and PG8.4 (version that comes with OS) then v8.4 becomes unavailable. Finally I've found that psql binary changes after v9.6 installation. But checking files that are installed with this version, I can't see what package causes the problem. And as I'm not used to *nix systems, I'm not sure how can I solve this. The process I've done:

- Installed a new machine with CentOS installation CD, which installs CentOS and PG8.4

- Made InitDB, started service (with "service postgresql start" instruction).

- Without changing pg_hba.conf (nor postgresql.conf) file, I've connected with psql, created some roles and some empty databases. Disconnected from database ("\q") and restored some backups with psql command.

- After all this, I can connect to v8.4 with root user and postgres user. Finally I've stopped postgresql (service stop).

- Tried to start postgres again but using pg_ctl. Tested connection with psql again (ok) and stopped.

- v8.4 is installed in /usr/bin and /var/lib/pgsql folders, that seemed quite generic for me, so I made a copy of both (with "cp -pr" command):
/usr/bin ==> /usr/bin_8.4
/var/lib/pgsl ==> /var/lib/pgsl_8.4

- I've tested copy: started with pg_ctl (the one that was copied to /usr/bin_8.4) pointing to copied data folder (/var/lib/pgsl_8.4/data). Started ok. Tried to connect with psql (ok) and stopped again.

- At this point, I've followed https://www.postgresql.org/download/linux/rdhat/ instructions to install v9.6: download repo, install client and install server.

- Made InitDB in v9.6 and changed listening port in postgresql.conf (v9.6 config file)

- After this installation, if I make "service postgresql start" I can see "something" running (I can list processes with ps -ef) but when trying to connect with psql it won't connect exiting with a message like (sorry, it's my translation as original message is not in english):
psql: could not connect to server: file or directory does not exist.
    Is server locally running and listening at Unix domain socket «/var/run/postgresql/.s.PGSQL.5432»?
Also "/usr/bin/psql" file is different than it's copy at bin_8.4 (some Kbytes bigger than the original file).


Older PG version is now unavailable, and I can't run pg_upgrade to migrate to a newer version. I've managed to make the whole process in Ubuntu 18, but Production server is CentOS 6.8. Any clue about why is this happening and how can I deal with this situation? During CentOS installation I can't (or haven't seen) change installation directory for Postgres. I now about data_directory in postgresql.conf, but not sure how to manage binaries dir.

I have recorded all the commands I've run, just in case anyone need more detailed info.


Thanks in advance,

Ekaterina

--
Angular momentum makes the world go 'round.

Ekaterina Amez <ekaterina.amez@zunibal.com> writes:
> I first thought they were going to be installed in their own 
> directories, but I'm starting to think that I'm missing something during 
> instalation process or maybe I'm missing something at deeper level 
> (probably lack of knowledge about linux and postgres). The thing is 
> after installing v9.6, having both versions running, I can't connect to 
> the older one and I'm having the message:  "no such file or directory. 
> Is the server running locally and accepting connections on Unix domain 
> socket "/var/run/postgresql/.s.PGSQL.5432"?".

This is probably a case of confusion about where the Unix socket file is.
There are competing standards about that: as we ship it, that file will
be in /tmp, but some Linux vendors make it be /var/run/postgresql because
of filesystem layout rules.  You are evidently using a libpq that was
built to default to /var/run/postgresql --- and since we can see that
you have a postmaster running on port 5432, it must have put its socket
file somewhere else, which I'm going to jump to the conclusion is /tmp.

You can override the client's default socket directory via something
like

    psql -h /tmp ...

A better long-term fix might be to change the postmaster's
unix_socket_directory(ies) setting to agree with what the
updated client library expects.

            regards, tom lane

Tom, you've pointed me the right way: setting unix_socket_directory was 
the answer. Having a look at v9.6 config, this parameter is set as:

     unix_socket_directories = '/var/run/postgresql, /tmp'

I've finally pg_upgrade my test server from v8.4 to v9.6.

Thank you all for your suggestions.


El 30/9/19 a las 16:47, Tom Lane escribió:
> Ekaterina Amez <ekaterina.amez@zunibal.com> writes:
>> I first thought they were going to be installed in their own
>> directories, but I'm starting to think that I'm missing something during
>> instalation process or maybe I'm missing something at deeper level
>> (probably lack of knowledge about linux and postgres). The thing is
>> after installing v9.6, having both versions running, I can't connect to
>> the older one and I'm having the message:  "no such file or directory.
>> Is the server running locally and accepting connections on Unix domain
>> socket "/var/run/postgresql/.s.PGSQL.5432"?".
> This is probably a case of confusion about where the Unix socket file is.
> There are competing standards about that: as we ship it, that file will
> be in /tmp, but some Linux vendors make it be /var/run/postgresql because
> of filesystem layout rules.  You are evidently using a libpq that was
> built to default to /var/run/postgresql --- and since we can see that
> you have a postmaster running on port 5432, it must have put its socket
> file somewhere else, which I'm going to jump to the conclusion is /tmp.
>
> You can override the client's default socket directory via something
> like
>
>     psql -h /tmp ...
>
> A better long-term fix might be to change the postmaster's
> unix_socket_directory(ies) setting to agree with what the
> updated client library expects.
>
>             regards, tom lane