Different releases in the same server

## marco.ptz@tiscali.it (marco.ptz@tiscali.it):

we have recently started to manage a production server
running a 9.6 postgres.

Which is EOL for nearly two years now:
https://www.postgresql.org/support/versioning/

We
have to upgrade to postgres 12.x

Which is going EOL in little over one year's time.

You should look into https://yum.postgresql.org/packages/ where you
can get packages with some real production life time.

At present in /usr/bin there are not links as
aspected for use with alternatives, but there are files belonging to
9.2
version except for pg_basebackup:

That sounds severly broken, as if you installed CentOS original
"postgresql" package (CentOS 7 ships with the really ancient 9.2).
Check rpm and yum where that came from and then it's time for some
cleanup. In the mean time, make sure to always call all PostgreSQL
utilities with full path.

Could
we have trouble having 9.6 and 12 running in the same time (we will
upgrade with pg_upgrade) in such server?

For pg_upgrade, you will need both the old and new binaries installed.

HOW can we to fix the presence
of release 9.2 files in /usr/bin?

Maybe best use the upgrade opportunity to move everything to a clean
installation on a new VM?

Will the simbolic links in /usr/bin be
created by alternatives once the 9.2 release will be
dropped/deinstalled/deleted?

The symlinks would be created by registering the repective versions
with the alternatives system - usually that happens in the post-install
scripts of the RPMs. You could do that manually (after removing 9.2)
or maybe by re-installing your current (ancient) packages. Cleanest
solution would be a new server - who knows what other surprises are
hidden on that machine?

Regards,
Christoph

--
Spare Space.

Hi Christoph,

thank you very much for your time.
We know the 2
releases are old, unfortunately we cannot change requirement,
we got
this server under our management 3 weeks ago, and customer asked to
upgrade urgently to 12.
It's a governement agency, and the limit is due
to certification matrix with application running
against the
database.

We totaly agree with you about to switch to a new VM built
from scratch, but probabiy this wont be possible,
so we need to
understand in deep what we could face in the migration step having this
server and (we hope) a clone to test on.

thanks again
regards
Marco

Il
02.08.2023 16:57 Christoph Moench-Tegeder ha scritto:

##

marco.ptz@tiscali.it [1] (marco.ptz@tiscali.it [2]):

we have

recently started to manage a production server running a 9.6 postgres.

Which is EOL for nearly two years now:

https://www.postgresql.org/support/versioning/ [3]

We have to

upgrade to postgres 12.x

Which is going EOL in little over one

year's time.

You should look into

https://yum.postgresql.org/packages/ [4]where you

can get packages

with some real production life time.

ks in /usr/bin be created by

alternatives once the 9.2 release w

d/deinstalled/deleted?

The

symlinks would be created by registering the repective versions

with

the alternatives system - usually that happens in the post-install

scripts of the RPMs. You could do that manually (after removing 9.2)

or maybe by re-installing your current (ancient) packages. Cleanest

so> toph -- Spare Space.

When I want to run a specific version of psql, pg_dump. pg_basebackup, etc I
explicitly use fully-qualified file names. Environment variables make that
easy.  Ditto with having a different port number for each instance.

(And even though it's too late for you regarding that, I *always* install
from the postgresql.org repository.)

On 8/2/23 09:31, marco.ptz@tiscali.it wrote:

Hi all,
we have recently started to manage a production server running a 9.6 postgres.

[root@xx ~]# cat /etc/os-release
NAME="CentOS Linux"
VERSION="7 (Core)"
ID="centos"
ID_LIKE="rhel fedora"
VERSION_ID="7"
PRETTY_NAME="CentOS Linux 7 (Core)"
ANSI_COLOR="0;31"
CPE_NAME="cpe:/o:centos:centos:7"
HOME_URL="https://www.centos.org/"
BUG_REPORT_URL="https://bugs.centos.org/"

CENTOS_MANTISBT_PROJECT="CentOS-7"
CENTOS_MANTISBT_PROJECT_VERSION="7"
REDHAT_SUPPORT_PRODUCT="centos"
REDHAT_SUPPORT_PRODUCT_VERSION="7"

We have to upgrade to postgres 12.x but we have a few questions due the configuration found in the server.
Basically there are 3 different release present, 9.2, 9.6 ( running ), 12 (future upgrade target release):

[root@xx ~]# yum list installed | grep postgres
Repodata is over 2 weeks old. Install yum-cron? Or run: yum makecache fast
postgresql.x86_64 9.2.24-4.el7_8 @base/7.9.2009
postgresql-libs.x86_64 9.2.24-4.el7_8 @base/7.9.2009
postgresql12.x86_64 12.14-1PGDG.rhel7 @pgdg12
postgresql12-libs.x86_64 12.14-1PGDG.rhel7 @pgdg12
postgresql12-server.x86_64 12.14-1PGDG.rhel7 @pgdg12
postgresql96.x86_64 9.6.11-1PGDG.rhel7 @pgdg96
postgresql96-contrib.x86_64 9.6.11-1PGDG.rhel7 @pgdg96
postgresql96-libs.x86_64 9.6.11-1PGDG.rhel7 @pgdg96
postgresql96-server.x86_64 9.6.11-1PGDG.rhel7 @pgdg96

the running version is 9.6

[root@xx ~]# ps -ef| grep D
UID PID PPID C STIME TTY TIME CMD
postgres 1436 1 0 Apr18 ? 06:56:14 /usr/pgsql-9.6/bin/postmaster -D /var/lib/pgsql/9.6/data/

in the server is also installed the alternatives packages which is configured that way:

[root@xx ~]# alternatives --list
libnssckbi.so.x86_64 auto /usr/lib64/pkcs11/p11-kit-trust.so
ld auto /usr/bin/ld.bfd
mta manual /usr/sbin/sendmail.sendmail
pgsql-ld-conf auto /usr/pgsql-12/share/postgresql-12-libs.conf
pgsql-psql auto /usr/pgsql-12/bin/psql
pgsql-clusterdb auto /usr/pgsql-12/bin/clusterdb
pgsql-createdb auto /usr/pgsql-12/bin/createdb
pgsql-createlang auto /usr/pgsql-9.6/bin/createlang
pgsql-createuser auto /usr/pgsql-12/bin/createuser
pgsql-dropdb auto /usr/pgsql-12/bin/dropdb
pgsql-droplang auto /usr/pgsql-9.6/bin/droplang
pgsql-dropuser auto /usr/pgsql-12/bin/dropuser
pgsql-pg_basebackup auto /usr/pgsql-12/bin/pg_basebackup
pgsql-pg_dump auto /usr/pgsql-12/bin/pg_dump
pgsql-pg_dumpall auto /usr/pgsql-12/bin/pg_dumpall
pgsql-pg_restore auto /usr/pgsql-12/bin/pg_restore
pgsql-reindexdb auto /usr/pgsql-12/bin/reindexdb
pgsql-vacuumdb auto /usr/pgsql-12/bin/vacuumdb
pgsql-clusterdbman auto /usr/pgsql-12/share/man/man1/clusterdb.1
pgsql-createdbman auto /usr/pgsql-12/share/man/man1/createdb.1
pgsql-createlangman auto /usr/pgsql-9.6/share/man/man1/createlang.1
pgsql-createuserman auto /usr/pgsql-12/share/man/man1/createuser.1
pgsql-dropdbman auto /usr/pgsql-12/share/man/man1/dropdb.1
pgsql-droplangman auto /usr/pgsql-9.6/share/man/man1/droplang.1
pgsql-dropuserman auto /usr/pgsql-12/share/man/man1/dropuser.1
pgsql-pg_basebackupman auto /usr/pgsql-12/share/man/man1/pg_basebackup.1
pgsql-pg_dumpman auto /usr/pgsql-12/share/man/man1/pg_dump.1
pgsql-pg_dumpallman auto /usr/pgsql-12/share/man/man1/pg_dumpall.1
pgsql-pg_restoreman auto /usr/pgsql-12/share/man/man1/pg_restore.1
pgsql-psqlman auto /usr/pgsql-12/share/man/man1/psql.1
pgsql-reindexdbman auto /usr/pgsql-12/share/man/man1/reindexdb.1
pgsql-vacuumdbman auto /usr/pgsql-12/share/man/man1/vacuumdb.1
postgis-pgsql2shp auto /usr/pgsql-9.6/bin/pgsql2shp
postgis-shp2pgsql auto /usr/pgsql-9.6/bin/shp2pgsql

As far as I know, alternatives package uses path /etc/alternatives/ and create links from /usr/bin to /etc/alternatives
which in turn links to the binaries to the correct selected version (/usr/pgsql-9.6/bin OR /usr/pgsql-12/bin).
We have checked the current alternatives configuration, and it knows only about 9.6 and 12 relases:

[root@xx ~]# alternatives --display pgsql-psql
pgsql-psql - status is auto.
link currently points to /usr/pgsql-12/bin/psql
/usr/pgsql-9.6/bin/psql - priority 960
/usr/pgsql-12/bin/psql - priority 1200
Current `best' version is /usr/pgsql-12/bin/psql.

At present in /usr/bin there are not links as aspected for use with alternatives, but there are files belonging to
9.2 version except for pg_basebackup:

[root@xx ~]# ls -l /usr/bin/p*
lrwxrwxrwx. 1 root root 37 Apr 30 08:05 /usr/bin/pg_basebackup -> /etc/alternatives/pgsql-pg_basebackup
-rwxr-xr-x. 1 root root 28672 May 12 2020 /usr/bin/pg_config
-rwxr-xr-x. 1 root root 313120 May 12 2020 /usr/bin/pg_dump
-rwxr-xr-x. 1 root root 75672 May 12 2020 /usr/bin/pg_dumpall
-rwxr-xr-x. 1 root root 129872 May 12 2020 /usr/bin/pg_restore
-rwxr-xr-x. 1 root root 449272 May 12 2020 /usr/bin/psql

[root@xx ~]# /usr/bin/psql --version
psql (PostgreSQL) 9.2.24
[root@xx ~]# /usr/bin/pg_dump --version
pg_dump (PostgreSQL) 9.2.24

we have checked whit rpm and it seems that the 9.2 package is the only one that put files in /usr/bin:

relase 9.2
[root@xx local]# rpm -ql postgresql.x86_64
/usr/bin/clusterdb
/usr/bin/createdb
/usr/bin/createlang
/usr/bin/createuser
/usr/bin/dropdb
/usr/bin/droplang
/usr/bin/dropuser
/usr/bin/pg_config
/usr/bin/pg_dump
/usr/bin/pg_dumpall
/usr/bin/pg_restore
/usr/bin/psql
/usr/bin/reindexdb
/usr/bin/vacuumdb
/usr/lib64/pgsql

release 9.6
[root@xx local]# rpm -ql postgresql96-server.x86_64
/etc/pam.d/postgresql
/etc/sysconfig/pgsql
/usr/lib/systemd/system/postgresql-9.6.service
/usr/lib/tmpfiles.d/postgresql-9.6.conf
/usr/pgsql-9.6/bin/initdb
/usr/pgsql-9.6/bin/pg_controldata
/usr/pgsql-9.6/bin/pg_ctl
/usr/pgsql-9.6/bin/pg_resetxlog
/usr/pgsql-9.6/bin/postgres
/usr/pgsql-9.6/bin/postgresql96-check-db-dir
/usr/pgsql-9.6/bin/postgresql96-setup
/usr/pgsql-9.6/bin/postmaster

release 12
[root@xx local]# rpm -ql postgresql12-server.x86_64
/etc/pam.d/postgresql
/etc/sysconfig/pgsql
/usr/bin/postgresql-12-setup
/usr/lib/systemd/system/postgresql-12.service
/usr/lib/tmpfiles.d/postgresql-12.conf
/usr/pgsql-12/bin/initdb
/usr/pgsql-12/bin/pg_checksums
/usr/pgsql-12/bin/pg_controldata
/usr/pgsql-12/bin/pg_ctl
/usr/pgsql-12/bin/pg_resetwal
/usr/pgsql-12/bin/postgres
/usr/pgsql-12/bin/postgresql-12-check-db-dir
/usr/pgsql-12/bin/postgresql-12-setup
/usr/pgsql-12/bin/postmaster

Of course we will try to test in a VM created as a clone of the producion to verify any step (it is not our own server).
the quesions are:

Could we have trouble having 9.6 and 12 running in the same time (we will upgrade with pg_upgrade) in such server?
HOW can we to fix the presence of release 9.2 files in /usr/bin?
Will the simbolic links in /usr/bin be created by alternatives once the 9.2 release will be dropped/deinstalled/deleted?

Any suggestion is welcome
Thanks in advance

regards
Marco

--
Born in Arizona, moved to Babylonia.