JFrog Xray PostgreSQL Upgrade-PostgreSQL 9.x EoS

Docker Compose

# Create the dump from 9.x postgresql container
docker exec -it xray_postgres_1 bash -c "pg_dumpall -U xray  > pgdump"
# Note: This is one way of taking the backup; you can use any documented way.

#copy pgdump to host
docker cp xray_postgres_1:/pgdump /home/<user>/

#Stop services
For 2.x - Stop and Remove postgresql container
   ./xray.sh stop
   docker ps -a --format '{{.Names}}' | grep ^xray_* | xargs docker rm -f
For 3.x - cd into extracted folder
   docker-compose -p xray down

#Extract new compose tar.gz and cd into extracted folder
Run ./config.sh

#Cancel installation in postgres upgrade step
#Remove old postgres directory since a backup has been taken
# WARNING: Verify the data dump before uninstalling and deleting older postgres data. 
rm -rf <MOUNT_DIR>/var/data/postgres/data

#copy compose templates: From Xray 3.8.x and later, the installer includes different templates for PostgreSQL, Xray, and RabbitMQ in the templates folder. 
cp -f <composeExtractedDIR>/templates/docker-compose.yaml   docker-compose.yaml
cp -f <composeExtractedDIR>/templates/docker-compose-postgres.yaml   docker-compose-postgres.yaml
cp -f templates/docker-compose-rabbitmq.yaml   docker-compose-rabbitmq.yaml


#Take the docker-compose-postgres.yaml template with postgresql 12.5v
Update POSTGRES_PASSWORD with actual password in docker-compose-postgres.yaml
POSTGRES_PASSWORD: <password> 

#Start Postgres
docker-compose -p xray-postgres -f docker-compose-postgres.yaml up -d


#Seed the data dump to xray_postgres container
docker exec -i xray_postgres psql -U xray -d xraydb < /home/<user>/pgdump

If the file <MOUNT_DIR>/var/etc/installerState.yaml exists
Update the postgresql latest version in this format postgresql_version: 13-2v

# Manual RabbitMQ setup steps 
mkdir -p <MOUNT_DIR>/app/third-party/rabbitmq
cp -r <composeExtractedDIR>/third-party/rabbitmq <MOUNT_DIR>/app/third-party/ 
chown -R 999:999 <MOUNT_DIR>/app/third-party/rabbitmq/

#Restart services
docker restart xray_postgres
docker-compose -p xray-rabbitmq -f docker-compose-rabbitmq.yaml up -d
docker-compose -p xray up -d

RPM

#Stop the xray service. 
/opt/jfrog/xray/scripts/xray.sh stop
#Create a data dump from postgres 
/opt/PostgreSQL/<9.x>/bin/pg_dump -U xray xraydb  > /home/<user>/pgdump

# Uninstall Postgres. 
# WARNING: Verify the data dump before uninstalling and deleting older postgres data. 
Step 1 : yum -y update
Step 2 : yum -y install mlocate
Step 3:  updatedb
# use mlocate to find postgres uninstall script
Step 4 : locate uninstall-postgresql
Step 5:  cd /opt/PostgreSQL/<9.x> && ./uninstall-postgresql
Step 6:  Data directory need to be removed manually
#WARNING: Verify the data dump before uninstalling and deleting older postgres data. 

               rm -rf  < postgres mount directory eg: /var/opt/jfrog/postgres >
               rm -rf  /var/spool/mail/postgres
               rm -rf  /opt/PostgreSQL
               rm -rf  /tmp/postgresql_installer_*
               rm -rf  /etc/selinux/targeted/active/modules/100/postgresql
# Download new xray rpm package and install postgres from the third-party folder. 
# For more information see https://www.jfrog.com/confluence/display/JFROG/Installing+Xray#InstallingXray-InstallingPostgreSQL
# Import the dumped data in to the new postgresql 
/usr/pgsql-12/bin/psql -U xray -d xraydb < /home/<user>/pgdump
# Restart postgres 
systemctl restart postgresql-12.service
# Install xray from the extracted directory
<extracted directory>/install.sh
# Start xray services 
systemctl start xray.service

Zip/Standalone/Linux

# Go to the zip extraction directory and create the dump from 9.x postgresql 
<zip extraction directory>/app/third-party/pgsql/bin/pg_dump -U xray xraydb  > /home/<user>/pgdump
#Stop the xray service. 
<zip extraction directory>/app/bin/xray.sh stop
# Stop postgres service
ps -ef | grep postgres
kill -15 <PID>
# Download and install the latest version of postgres 
#WARNING: Verify the data dump before uninstalling and deleting older postgres data. 
# Import the dumped data in to the new postgresql 
<postgres bin path>/psql -U xray -d xraydb < /home/<user>/pgdump
#restart postgres service based on instructions for the package
# Start xray services 
<zip extraction directory>/app/bin/xray.sh start