.::: Upgrade PostgreSQL from 14 to 15 on CentOS 7 :::.

1. chek version before upgrade
SELECT version();
select pg_read_file('/etc/hostname') as hostname, version();
 

2. backup all
su - postgres -c "pg_dumpall -p 5432 > /data/edb/backupupgrade/all_14posg.sql"

3. install postgres15 as
detail http://teguhth.blogspot.com/2021/07/install-postgresql-linux-using.html

wget http://mirror.centos.org/centos/8-stream/BaseOS/x86_64/os/Packages/libzstd-1.4.4-1.el8.x86_64.rpm
rpm -ivh libzstd-1.4.4-1.el8.x86_64.rpm

yum -y install postgresql15-server

[root@teguhth data]# wget http://mirror.centos.org/centos/8-stream/BaseOS/x86_64/os/Packages/libzstd-1.4.4-1.el8.x86_64.rpm
[root@teguhth data]# rpm -ivh libzstd-1.4.4-1.el8.x86_64.rpm
[root@teguhth data]# yum -y install postgresql15-server
[root@teguhth data]#

4.  Inisialize the DB

[root@teguhth data]# /usr/pgsql-15/bin/postgresql-15-setup initdb
Initializing database ... OK

[root@teguhth data]#

5. stop old progres 14

systemctl stop postgresql-14
[root@teguhth data]# systemctl stop postgresql-14
[root@teguhth data]# systemctl status postgresql-14
? postgresql-14.service - PostgreSQL 14 database server
   Loaded: loaded (/usr/lib/systemd/system/postgresql-14.service; enabled; vendor preset: disabled)
   Active: inactive (dead) since Wed 2023-08-30 13:20:16 WIB; 5s ago
     Docs: https://www.postgresql.org/docs/14/static/
  Process: 953 ExecStart=/usr/pgsql-14/bin/postmaster -D ${PGDATA} (code=exited, status=0/SUCCESS)
 Main PID: 953 (code=exited, status=0/SUCCESS)

Aug 30 13:10:25 teguhth systemd[1]: Starting PostgreSQL 14 database server...
Aug 30 13:10:25 teguhth postmaster[953]: 2023-08-30 13:10:25.995 WIB [953] LOG:  redirecting log output to logging collector process
Aug 30 13:10:25 teguhth postmaster[953]: 2023-08-30 13:10:25.995 WIB [953] HINT:  Future log output will appear in directory "log".
Aug 30 13:10:26 teguhth systemd[1]: Started PostgreSQL 14 database server.
Aug 30 13:20:16 teguhth systemd[1]: Stopping PostgreSQL 14 database server...
Aug 30 13:20:16 teguhth systemd[1]: Stopped PostgreSQL 14 database server.
[root@teguhth data]#


6. Check that everything is fine before upgrade

/usr/pgsql-15/bin/pg_upgrade --old-datadir=/var/lib/pgsql/14/data --new-datadir=/var/lib/pgsql/15/data --old-bindir=/usr/pgsql-14/bin --new-bindir=/usr/pgsql-15/bin --old-options '-c config_file=/var/lib/pgsql/14/data/postgresql.conf' --new-options '-c config_file=/var/lib/pgsql/15/data/postgresql.conf' --check

[root@teguhth data]# su - postgres
Last login: Wed Aug 30 11:28:54 WIB 2023 on pts/0
-bash-4.2$ /usr/pgsql-15/bin/pg_upgrade --old-datadir=/var/lib/pgsql/14/data --new-datadir=/var/lib/pgsql/15/data --old-bindir=/usr/pgsql-14/bin --new-bindir=/usr/pgsql-15/bin --old-options '-c config_file=/var/lib/pgsql/14/data/postgresql.conf' --new-options '-c config_file=/var/lib/pgsql/15/data/postgresql.conf' --check
Performing Consistency Checks
-----------------------------
Checking cluster versions                                   ok
Checking database user is the install user                  ok
Checking database connection settings                       ok
Checking for prepared transactions                          ok
Checking for system-defined composite types in user tables  ok
Checking for reg* data types in user tables                 ok
Checking for contrib/isn with bigint-passing mismatch       ok
Checking for presence of required libraries                 ok
Checking database user is the install user                  ok
Checking for prepared transactions                          ok
Checking for new cluster tablespace directories             ok

*Clusters are compatible*
-bash-4.2$

7. upgrade postgresql

/usr/pgsql-15/bin/pg_upgrade --old-datadir=/var/lib/pgsql/14/data --new-datadir=/var/lib/pgsql/15/data --old-bindir=/usr/pgsql-14/bin --new-bindir=/usr/pgsql-15/bin --old-options '-c config_file=/var/lib/pgsql/14/data/postgresql.conf' --new-options '-c config_file=/var/lib/pgsql/15/data/postgresql.conf'

-bash-4.2$ /usr/pgsql-15/bin/pg_upgrade --old-datadir=/var/lib/pgsql/14/data --new-datadir=/var/lib/pgsql/15/data --old-bindir=/usr/pgsql-14/bin --new-bindir=/usr/pgsql-15/bin --old-options '-c config_file=/var/lib/pgsql/14/data/postgresql.conf' --new-options '-c config_file=/var/lib/pgsql/15/data/postgresql.conf'
Performing Consistency Checks
-----------------------------
Checking cluster versions                                   ok
Checking database user is the install user                  ok
Checking database connection settings                       ok
Checking for prepared transactions                          ok
Checking for system-defined composite types in user tables  ok
Checking for reg* data types in user tables                 ok
Checking for contrib/isn with bigint-passing mismatch       ok
Creating dump of global objects                             ok
Creating dump of database schemas                           ok
Checking for presence of required libraries                 ok
Checking database user is the install user                  ok
Checking for prepared transactions                          ok
Checking for new cluster tablespace directories             ok

If pg_upgrade fails after this point, you must re-initdb the
new cluster before continuing.

Performing Upgrade
------------------
Analyzing all rows in the new cluster                       ok
Freezing all rows in the new cluster                        ok
Deleting files from new pg_xact                             ok
Copying old pg_xact to new server                           ok
Setting oldest XID for new cluster                          ok
Setting next transaction ID and epoch for new cluster       ok
Deleting files from new pg_multixact/offsets                ok
Copying old pg_multixact/offsets to new server              ok
Deleting files from new pg_multixact/members                ok
Copying old pg_multixact/members to new server              ok
Setting next multixact ID and offset for new cluster        ok
Resetting WAL archives                                      ok
Setting frozenxid and minmxid counters in new cluster       ok
Restoring global objects in the new cluster                 ok
Restoring database schemas in the new cluster
                                                            ok
Copying user relation files
                                                            ok
Setting next OID for new cluster                            ok
Sync data directory to disk                                 ok
Creating script to delete old cluster                       ok
Checking for extension updates                              ok

Upgrade Complete
----------------
Optimizer statistics are not transferred by pg_upgrade.
Once you start the new server, consider running:
    /usr/pgsql-15/bin/vacuumdb --all --analyze-in-stages

Running this script will delete the old cluster's data files:
    ./delete_old_cluster.sh
-bash-4.2$

8. start new postgresql
[root@teguhth pgsql]# systemctl start postgresql-15
[root@teguhth pgsql]# systemctl enable postgresql-15
Created symlink from /etc/systemd/system/multi-user.target.wants/postgresql-15.service to /usr/lib/systemd/system/postgresql-15.service.
[root@teguhth pgsql]#

9. check new version
-bash-4.2$ psql -c "select pg_read_file('/etc/hostname') as hostname, version();"
 hostname |                                                 version
----------+---------------------------------------------------------------------------------------------------------
 teguhth +| PostgreSQL 15.4 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-44), 64-bit
          |
(1 row)

-bash-4.2$
 

 

10. copy all old config to new config
su - postgres

cp /var/lib/pgsql/14/data/postgresql.conf /var/lib/pgsql/15/data/postgresql.conf
cp /var/lib/pgsql/14/data/pg_hba.conf /var/lib/pgsql/15/data/pg_hba.conf

[root@teguhth ~]# su - postgres
Last login: Wed Aug 30 15:19:46 WIB 2023 on pts/0
-bash-4.2$
-bash-4.2$ cp /var/lib/pgsql/14/data/postgresql.conf /var/lib/pgsql/15/data/postgresql.conf
-bash-4.2$ cp /var/lib/pgsql/14/data/pg_hba.conf /var/lib/pgsql/15/data/pg_hba.conf
-bash-4.2$

11. restart new postgresql-15 again
systemctl restart  postgresql-15
[root@teguhth ~]# systemctl restart  postgresql-15
[root@teguhth ~]#


12. check new version again
-bash-4.2$ psql -c "select pg_read_file('/etc/hostname') as hostname, version();"
 hostname |                                                 version
----------+---------------------------------------------------------------------------------------------------------
 teguhth +| PostgreSQL 15.4 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-44), 64-bit
          |
(1 row)


13. check rpm for posgres
rpm -qa | grep -i postgres

[root@teguhth ~]# rpm -qa | grep -i postgres
postgresql15-libs-15.4-1PGDG.rhel7.x86_64
postgresql14-libs-14.9-2PGDG.rhel7.x86_64
postgresql14-server-14.9-2PGDG.rhel7.x86_64
postgresql15-15.4-1PGDG.rhel7.x86_64
postgresql14-14.9-2PGDG.rhel7.x86_64
postgresql15-server-15.4-1PGDG.rhel7.x86_64
[root@teguhth ~]#

14. remove & delete old directory

yum remove postgresql14* -y

[root@teguhth ~]# yum -y remove postgresql14* -y
[root@teguhth ~]# rm -rf /var/lib/pgsql/14

15. check packet

[root@teguhth ~]# rpm -qa | grep -i postgres
postgresql15-libs-15.4-1PGDG.rhel7.x86_64
postgresql15-15.4-1PGDG.rhel7.x86_64
postgresql15-server-15.4-1PGDG.rhel7.x86_64
[root@teguhth ~]#

16. login n cek database
\c teguhth
select * from pembelian;
 

17. vacum for first time

 /usr/pgsql-15/bin/vacuumdb --all --analyze-in-stages

-bash-4.2$     /usr/pgsql-15/bin/vacuumdb --all --analyze-in-stages
vacuumdb: processing database "postgres": Generating minimal optimizer statistics (1 target)
vacuumdb: processing database "secretdb": Generating minimal optimizer statistics (1 target)
vacuumdb: processing database "teguhth": Generating minimal optimizer statistics (1 target)
vacuumdb: processing database "template1": Generating minimal optimizer statistics (1 target)
vacuumdb: processing database "postgres": Generating medium optimizer statistics (10 targets)
vacuumdb: processing database "secretdb": Generating medium optimizer statistics (10 targets)
vacuumdb: processing database "teguhth": Generating medium optimizer statistics (10 targets)
vacuumdb: processing database "template1": Generating medium optimizer statistics (10 targets)
vacuumdb: processing database "postgres": Generating default (full) optimizer statistics
vacuumdb: processing database "secretdb": Generating default (full) optimizer statistics
vacuumdb: processing database "teguhth": Generating default (full) optimizer statistics
vacuumdb: processing database "template1": Generating default (full) optimizer statistics
-bash-4.2$