Different releases in the same server

[marco . ptz]

  

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/cre

Re: Different releases in the same server

[marco . ptz]

  

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@tiscali.it [1] (marco@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.
  

How to Straming replication chatch up from archive

[marco . ptz]

  
Hi,
we have a doubt interpreting documentation. We have a 9.6
installation master+stand-by in streaming replication.
Master database
is in archive mode and WAL archive destination (NFS) is mounted in both
master and stand-by servers.

The question is regarding this part of
documentation:
(https://www.postgresql.org/docs/9.6/warm-standby.html)

If you use
streaming replication without file-based continuous archiving, the
server might recycle old WAL segments before the standby has received
them. 
If this occurs, the standby will need to be reinitialized from a
new base backup. You can avoid this by setting wal_keep_segments to a
value large enough to
 ensure that WAL segments are not recycled too
early, or by configuring a replication slot for the standby. IF YOU SET
UP A WAL ARCHIVE THAT'S ACCESSIBLE FROM THE STANDBY,
 THESE SOLUTIONS
ARE NOT REQUIRED, SINCE THE STANDBY CAN ALWAYS USE THE ARCHIVE TO CATCH
UP PROVIDED IT RETAINS ENOUGH SEGMENTS.

if the master overwrite the wal
BEFORE they are sent to the standby via replication, does the standby
AUTOMATICALLY start looking in WAL archive destination?
Or this step
have to be made by hand?

regards and thanks in advance
MP