Upgrading PostgreSQL from Management Console 19.05 - 22.01¶

Upgrading from Management Console 22.01 or older requires PostgreSQL version 14.1

Upgrading from Management Console 22.01 or older requires PostgreSQL version 14.1 installed.

Proper permissions and backups

CentOS/RHEL logged in user performing these change must have sudo privileges

It is recommended to take a DB backup from the Management Console UI before performing an upgrade to PostgreSQL 14.1.

This topic describes the steps to update PostgreSQL to a newer version on the Management Console host VM operating system.

Login to the Management Console operating system and create new folder named as postgresql14_dependencies under the logged in users directory.
```
cd ~
mkdir postgresql14_dependencies
```
Copy teradicimc-postgresql14-offline-dependencies.tgz to the <logged in user directory>/postgresql14_dependencies directory.

You can use a third party tool such as WinSCP to copy and move files into the CentOS/RHEL host operating system.
From the users logged in directory, create a file called post14_update.sh.
```
cd ~
vi post14_update.sh
```

Copy the following script to the post14_update.sh file.

#!/usr/bin/env bash

#"""File for uninstall existed PostgreSQL and install PostgreSQL14.1 before RPM upgrade"""

#Backup file path
db_backup_file_path='/tmp/pgsqlmcbackup'
#PostgreSQL configuration file
postgresql_config_file='/tmp/postgres-install.sh'
#Declaring PostgreSQL version as 14
postgresql_version=14
#Declaring PostgreSQL minor version as 14.1
postgresql_minor_version=14.1
#Assigning first command line argument value to postgresql_dependencies_tarfile variable
postgresql_dependencies_tarfile=$1
#Fetching the current directory path
current_directory_path=$(getent passwd "$SUDO_USER" | cut -d: -f6)

#Delete postgres-install.sh and pgsqlmcbackup from tmp folder, if files are existed after script execution
trap "rm -rf $postgresql_config_file" EXIT SIGINT SIGQUIT
trap "rm -rf $db_backup_file_path" EXIT SIGINT SIGQUIT 

#Validating number of arguments are equal to one and it is not null
if [ -z "$postgresql_dependencies_tarfile" ] || [ "$#" -ne 1 ];  then
    echo -e "Usage: sudo sh ./post14_update.sh <postgresql_dependencies_tarfile>\nExample: sudo sh ./post14_update.sh teradicimc-postgresql14-offline-dependencies.tgz"
    exit 0
else
    echo 'INFO: PostgreSQL tarball file name is' "$postgresql_dependencies_tarfile"
fi

#Validating postgresql_dependencies_tarfile is having PostgreSQL rpm
tar -tvf "$current_directory_path"/postgresql14_dependencies/"${postgresql_dependencies_tarfile}" | grep -i postgresql > /dev/null

if [ $? -ne 0 ]; then
    echo 'ERROR: Invalid archive, PostgreSQL rpm is missing'
    exit 0
fi

#Validating postgresql_dependencies_tarfile existing in the specific path
if [ ! -f "$current_directory_path"/postgresql14_dependencies/"${postgresql_dependencies_tarfile}" ]; then
    echo 'ERROR: '"$postgresql_dependencies_tarfile"' file is missing'
    exit 0
fi

#Validating postgres-install.sh file existing in scripts folder
if [ ! -f /opt/teradici/scripts/postgres-install.sh ]; then
    echo 'ERROR: postgres-install.sh file is missing in scripts folder'
    exit 0
fi

#Checking if system has PostgreSQL version 14.1 or below
#If PostgreSQL14.1 exists, skip the installation
#If PostgreSQL version below 14.1 exists, take the database backup, uninstall the existing PostgreSQL version and install PostgreSQL14.1
echo 'INFO: Searching for the existing PostgreSQL version installed'
is_postgresql14_exist=$(rpm -qa | grep -i postgresql14-server)
is_postgresql9_exist=$(rpm -qa | grep -e ^postgresql-server-[0-9]\\. -e ^postgresql-server-[1][0-3] -e ^postgresql-[0-9]\\. -e ^postgresql-[1][0-3] -e ^postgresql[0-9]- -e ^postgresql[1][0-3])

if [ ! -z "$is_postgresql9_exist" ]; then
    echo 'INFO: Found PostgreSQL version below 14.1'
    echo 'INFO: Stopping mcconsole and mcdaemon'
    systemctl stop mcconsole && systemctl stop mcdaemon
    echo 'INFO: Checking status of PostgreSQL service'
    is_postgresql9_status=$(systemctl is-active postgresql | grep -w 'inactive')
    if [ ! -z "$is_postgresql9_status" ];then
        echo 'INFO: Starting PostgreSQL service'
        systemctl enable postgresql --now
    fi
    echo 'INFO: Dropping the index of name column in schedule table'
    psql -U postgres -t -d pcoip_mc_db -c "ALTER TABLE schedule DROP CONSTRAINT IF EXISTS uq_schedule_name"
    echo 'INFO: Creating folder as pgsqlmcbackup under /tmp/ folder to store database backup file'
    mkdir "$db_backup_file_path" && chown -R postgres:postgres "$db_backup_file_path"
    mkdir "$current_directory_path"/pgsqlmcbackup

    echo 'INFO: Taking database backup and stopping PostgreSQL service'
    su - postgres -c "pg_dumpall > $db_backup_file_path/backup" && systemctl stop postgresql
    echo "INFO: Copying database backup file from $db_backup_file_path to $current_directory_path/pgsqlmcbackup"
    cp "$db_backup_file_path"/backup "$current_directory_path"/pgsqlmcbackup/backup
else
    if [ ! -z "$is_postgresql14_exist" ]; then
        echo 'INFO: Found PostgreSQL version 14.1 installed. Upgrade not required'
        exit 0
    else
        echo 'INFO: PostgreSQL version below 14.1 is not found. Nothing to upgrade'
        exit 0
    fi
fi

#Checking the availability of database backup file
#Checking the availability of postgresql_dependencies_tarfile
#Uninstall PostgreSQL, if all required files are available
if [ -z "$is_postgresql14_exist" ]; then
    if [ -f "$db_backup_file_path"/backup ] && [ -s "$db_backup_file_path"/backup ]; then
        echo 'INFO: Database backup is successful'
        if [ -f "$current_directory_path"/postgresql14_dependencies/"${postgresql_dependencies_tarfile}" ]; then
            echo 'INFO: Uninstalling the existing PostgreSQL version'
            rpm -qa | grep -e ^postgresql-libs-[0-9]\\. -e ^postgresql-contrib-[0-9]\\. -e ^postgresql-server-[0-9]\\. -e ^postgresql-[0-9]\\. -e ^postgresql-libs-[1][0-3] -e ^postgresql-contrib-[1][0-3] -e ^postgresql-server-[1][0-3] -e ^postgresql-[1][0-3] -e ^postgresql[0-9]- -e ^postgresql[1][0-3] | xargs sudo rpm -e --nodeps
            is_postgresql9_exist=$(rpm -qa | grep -e ^postgresql-server-[0-9]\\. -e ^postgresql-server-[1][0-3] -e ^postgresql-[0-9]\\. -e ^postgresql-[1][0-3] -e ^postgresql[0-9]- -e ^postgresql[1][0-3])
            if [ -z "$is_postgresql9_exist" ]; then
                echo 'INFO: Installing PostgreSQL14.1 from' "$postgresql_dependencies_tarfile"
                cd "$current_directory_path"/postgresql14_dependencies && \
                tar xvf "$postgresql_dependencies_tarfile" && \
                yum -y install ./*.rpm
                cd
            else
                echo 'ERROR: Failed to uninstall the existing PostgreSQL version'
                exit 0
            fi   
        else
        echo 'ERROR:' "$postgresql_dependencies_tarfile"' file is missing'
        exit 0
        fi
    else
        echo 'ERROR: Backup file does not exist or content is missing in the backup file'
        exit 0
    fi
else
    echo 'INFO: Found PostgreSQL version 14.1 installed. Upgrade not required'
    exit 0
fi

#Checking the PostgreSQL version   
echo 'INFO: Verifying the PostgreSQL version is' "$postgresql_minor_version"
psql -V | awk {'print $3'} | awk {'print $1'} | grep $postgresql_minor_version

if [ $? -eq 0 ]; then
    echo 'INFO: Checking PostgreSQL path is exists in /etc/profile'
    if [[ $(grep -i /usr/pgsql-$postgresql_version/bin /etc/profile) ]]; then
        echo 'INFO: PostgreSQL path is present in /etc/profile'
    else
        echo 'INFO: PostgreSQL path is not present in /etc/profile'
        echo 'INFO: Exporting PostgreSQL path to /etc/profile'
        echo export 'PATH=$PATH:'/usr/pgsql-${postgresql_version}/bin | sudo tee -a /etc/profile
        echo 'INFO: PostgreSQL14.1 installation is completed'
    fi
else
    echo 'ERROR: PostgreSQL14.1 installation is failed'
    exit 0
fi

#Updating PostgreSQL Configuration file and initializing the PostgreSQL service   
printf '#!/usr/bin/env bash\n

#Installing postgres\n
#echo "*** Patching Centos_Base.repo.patch"\n
PG_VER=14\n
POST_CONFIG=/var/lib/pgsql/$PG_VER/data/postgresql.conf\n
PG_HBA_CONFIG=/var/lib/pgsql/$PG_VER/data/pg_hba.conf\n

# initializing postgres DB\n
postgresql-$PG_VER-setup initdb\n

rm -f $PG_HBA_CONFIG\n

# create pg_hba.conf\n
cat > $PG_HBA_CONFIG << EOF\n
# Added by bootstrap.sh\n
local   all             postgres                                trust\n
local   all             all                                     peer\n
host    all             all             127.0.0.1/32            md5\n
host    all             all             ::1/128                 md5\n
local   all             all                                     md5\n
EOF\n

echo "*** Update postgresql.conf"\n
cat >> $POST_CONFIG << EOF\n
# Added by bootstrap.sh\n
listen_addresses = 'localhost'\n
max_connections = 100\n
EOF\n

sed --in-place "s/^shared_buffers =.*/shared_buffers = 2GB/" $POST_CONFIG\n
sed --in-place "s/^#effective_cache_size =.*/effective_cache_size = 4GB/" $POST_CONFIG\n
sed --in-place "s/^#work_mem =.*/work_mem = 512MB/" $POST_CONFIG\n
# for 9.5 and later\n
sed --in-place "s/^#max_wal_size =.*/max_wal_size = 2GB/" $POST_CONFIG\n
# before 9.5\n
sed --in-place "s/^#checkpoint_segments =.*/checkpoint_segments = 32/" $POST_CONFIG\n


# intializing postgres\n
chown postgres:postgres $POST_CONFIG\n
chown postgres:postgres $PG_HBA_CONFIG\n

systemctl start postgresql-$PG_VER\n
systemctl enable postgresql-$PG_VER\n

sleep 10\n' > $postgresql_config_file

if [ -f "$postgresql_config_file" ] && [ -s "$postgresql_config_file" ]; then
    awk '/"*** Setting db user/,/chmod 0600\/root/' /opt/teradici/scripts/postgres-install.sh >> $postgresql_config_file
    echo 'INFO: Updating the PostgreSQL configuration file'
    sudo bash "$postgresql_config_file"
else
    echo 'ERROR: postgres-install.sh file does not exist'
    exit 0
fi

#Checking PostgreSQL14.1 is running
is_postgresql14_status=$(systemctl is-active postgresql-${postgresql_version} | grep -w 'inactive')

if [ ! -z "$is_postgresql14_status" ]; then
    systemctl enable postgresql-${postgresql_version} --now
else
    echo 'INFO: PostgreSQL14.1 is running'
fi

#Restoring database backup
#After restoring the database backup, add index to name column in schedule table
#Delete backup folder once restore is completed
if [ -f "$db_backup_file_path"/backup ] && [ -s "$db_backup_file_path"/backup ]; then
    echo 'INFO: Found database backup and started database restore'
    su - postgres -c 'psql -d postgres -f '"$db_backup_file_path"'/backup'
    psql -U postgres -t -d pcoip_mc_db -c "CREATE UNiQUE INDEX IF NOT EXISTS uq_schedule_name ON schedule (md5(name))"
    echo 'INFO: INDEX created on name column in schedule table using md5'
    db_name=pcoip_mc_db
    psql -U postgres ${db_name} --command="SELECT version();" >/dev/null 2>&1
    db_result=$?
    if [ "$db_result" -eq 0 ]; then
        echo 'INFO: Database restore is completed'
    else
        echo 'ERROR: Database restore is failed'
    fi
    rm -rf "$postgresql_config_file"
    rm -rf "$db_backup_file_path"
    rm -rf "$current_directory_path"/postgresql14_dependencies
    echo 'INFO: Deleting backup folder,' "$postgresql_config_file"' file and postgresql14_dependencies folder'
    systemctl start mcconsole
    systemctl start mcdaemon
    echo 'INFO: Starting mcconsole and mcdaemon'

else
    echo 'ERROR: Database backup does not exist or content is missing in the backup file'
    exit 0
fi

Save the script file.

Esc:wq

DB backup generated

Before upgrading to PostgreSQL 14.1, the script generates a Management Console database backup and stores it in /pgsqlmcbackup. This backup file can be deleted if not required as it is not deleted by the post14_update script.
Give permissions to the post14_update.sh script file to allow the script to execute.

chmod +x ./post14_update.sh
Run the script file.

sudo sh ./post14_update.sh teradicimc-postgresql14-offline-dependencies.tgz

Verify install

Verify the installed PostgreSQL version is 14.1.

psql -V

Sample output