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.
- 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 12. 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
# 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 # 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
#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
# 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.
- Upgrading to PostgreSQL 12
- Upgrading to PostgreSQL 11
- Upgrading to PostgreSQL 10
- Upgrading to PostgreSQL 9.6 (not recommended)