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 andenables Xray to take advantage of the latest abilities provided by the new versions, such as better performance, etc.
Note
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:
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 databasebeforebeginning the upgrade.
Stop Xray services.
Backup and upgrade the database.
Upgrade an External PostgreSQL and select the link for your external PostgreSQL version.
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.
Note
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.