Thread

1. Different releases in the same server

   marco.ptz@tiscali.it — 2023-08-02T14:31:12Z

     
   
   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
     
   

2. Re: Different releases in the same server

   Christoph Moench-Tegeder <cmt@burggraben.net> — 2023-08-02T14:57:59Z

   ## 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.
   
   
   
   

3. Re: Different releases in the same server

   marco.ptz@tiscali.it — 2023-08-02T15:27:35Z

     
   
   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.
     
   

4. Re: Different releases in the same server

   Ron <ronljohnsonjr@gmail.com> — 2023-08-02T15:51:01Z

   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.