Cloud customer?
Start for Free >
Upgrade in MyJFrog >
What's New in Cloud >

Search





Overview

The purpose of this article is to address the fact that PostgreSQL is ending its support for some of its older versions, including 9.5 in February 2021 and 9.6 in November 2021.

PostgreSQL is supported for JFrog Xray in two ways:

  • Bundled together with the Xray installation (herein referred to as internal)
  • External PostgreSQL database (herein referred to as external)

Supported PostgreSQL Versions

  • Xray 2.x supports the PostgreSQL versions listed in Installing Xray - Using External Databases (the earliest version of Xray 2.x that supports PostgreSQL 10-12 is Xray 2.16). 
  • Xray 3.x supports the PostgreSQL versions listed in the System Requirements. From Xray version 3.18 PostgreSQL 13 is also supported.

Although it is possible to upgrade incrementally from 9.5 to 9.6, JFrog recommends upgrading to the latest version supported by Xray. This will extend the time until the next PostgreSQL end of support and enables Xray to take advantage of the latest abilities provided by the new versions, such as better performance, etc.

Although PostgreSQL is ending its support for the 9.x versions, JFrog will continue to support Xray customers using these versions as much as possible until the customers upgrade to a supported version.

The following workflow provides you with directions on how to upgrade the Xray database to a supported PostgreSQL version.

Important

Before upgrading PostgreSQL (using any method), we recommend reading the PostgreSQL upgrade instructions for the relevant upgrade version:

Page Contents


Upgrading PostgreSQL Workflow

The following steps apply to both internal and external PostgreSQL and are the same for both Xray 2.x and 3.x.

Important Rules and Guidelines

  • Upgrading the PostgreSQL for the Xray database requires downtime of Xray.
  • Remember to back up your database before beginning the upgrade. 
  1. Stop Xray services.
  2. Backup and upgrade the database.
    1. Upgrade an Internal PostgreSQL
    1. Upgrade an External PostgreSQL and select the link for your external PostgreSQL version.
  3. Start Xray services.


Upgrading an Internal PostgreSQL

If you are running the PostgreSQL bundled with the Xray installer, follow the steps below according to your installation type. There are several upgrade methods you can use for upgrading to PostgreSQL 13. Which method to use depends on your preferences.

The PostgreSQL upgrade steps below assume that the user is trying the upgrade from a PostgreSQL 9.x version to any higher version.

Docker Compose Installers

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 Installers

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 Installers

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

Upgrading an External PostgreSQL

If you are using an external PostgreSQL, you will need to upgrade according to the distribution of PostgreSQL you are using, and the official PostgreSQL documentation.


  • No labels
Copyright © 2021 JFrog Ltd.