DISTRIBUTION: How to upgrade bundled PostgreSQL database when upgrading Distribution

DISTRIBUTION: How to upgrade bundled PostgreSQL database when upgrading Distribution
Products
JFrog_Insight
Content Type
Use_Case
AuthorFullName__c
Ino Choi
articleNumber
000006660
FirstPublishedDate
2025-10-16T09:00:06Z
lastModifiedDate
2025-10-16
Introduction 

This article provides step-by-step instructions for manually upgrading the PostgreSQL database bundled with JFrog Distribution. When upgrading the Distribution application across major versions (e.g., from 2.22.x, which uses PostgreSQL 13, to 2.33.x, which uses PostgreSQL 16), the underlying database is not automatically upgraded. A manual migration is required to complete the process.

Problem

Running the installer for a new version of JFrog Distribution upgrades the application binaries but does not touch the existing PostgreSQL database. This leaves the old database version in place, which may not be optimized for the new application version. To ensure stability, leverage new database features, and apply the latest security patches, the database upgrade is recommended.

Resolution 

The solution is to perform a manual data migration. This guide provides two distinct methods based on your installation type: one for bare-metal (RHEL/Debian) environments using a full backup and restore, and another for Docker Compose environments.

CRITICAL: Before attempting either method, you must perform a full backup of your existing Distribution database to prevent data loss.

Note: 

The instructions in this article use the upgrade from Distribution 2.22.x (PostgreSQL 13) to 2.33.x (PostgreSQL 16) as an example.


Step-by-Step Instructions

Method 1: Bare-Metal Installation (RHEL/Debian)
1. Stop Distribution and Back Up the Database
First, stop the Distribution service to ensure data consistency. Then, create a full backup of your existing database.
# Stop the Distribution service
systemctl stop distribution

# Create a full backup of the database
# For RHEL/CentOS:
sudo -u postgres /usr/pgsql-13/bin/pg_dumpall -U postgres > /path/to/ds_database_backup.sql

# For Debian/Ubuntu (uncomment the line below):
# sudo -u postgres /usr/lib/postgresql/13/bin/pg_dumpall -U postgres > /path/to/ds_database_backup.sql


Troubleshooting: If the backup command fails with a password authentication failed error, temporarily edit pg_hba.conf to allow trust-based local connections.
a. Find the correct pg_hba.conf file. The location can vary. Find the active data directory by running:
ps -ef | grep postgres | grep -- -D
The path shown after the -D flag is the data directory. The pg_hba.conf file is located inside it.

b. Open the file for editing (path may vary slightly based on distribution):
# For RHEL/CentOS (standard path):
sudo vi /var/lib/pgsql/13/data/pg_hba.conf

# For Debian/Ubuntu (standard path):
# sudo vi /etc/postgresql/13/main/pg_hba.conf

# For non-standard installations (use the -D path from the command above):
# sudo vi /var/opt/postgres/data/pg_hba.conf
c. Change the local connection method from peer or md5 to trust:
local   all             all                                     trust

 

d. Reload the PostgreSQL configuration:
# For RHEL/CentOS systems (use the specific version number):
sudo systemctl reload postgresql-13

# For Debian/Ubuntu systems (use the generic service name):
# sudo systemctl reload postgresql

 

e. Re-run the pg_dumpall command.
Important: After the backup is complete, revert the change in pg_hba.conf to restore your security configuration and reload the service again.

2. Uninstall Old PostgreSQL and Install New Version

Remove the old version of PostgreSQL. Then, install the new version from the packages bundled with the new Distribution installer.
# Uninstall PostgreSQL 13
# For RHEL/CentOS:
sudo dnf remove -y 'postgresql13*'

# For Debian/Ubuntu (uncomment the line below):
# sudo apt-get purge -y 'postgresql-13*'

# Navigate to the new installer's third-party directory
# For RHEL/CentOS:
cd /path/to/jfrog-distribution-2.33.x-rpm/third-party/postgresql/
# For Debian/Ubuntu (uncomment the line below):
# cd /path/to/jfrog-distribution-2.33.x-deb/third-party/postgresql/

# Install PostgreSQL 16
# For RHEL 8/9 systems (use the appropriate file):
sudo dnf install -y postgresql16*rhel[8|9]*.rpm

# For Debian/Ubuntu systems, use apt to handle dependencies automatically
# (select the file matching your OS version):
# Example for Ubuntu 22.04:
sudo apt install ./postgresql-16_*.pgdg22.04+*.deb

3. Initialize and Start the New PostgreSQL 16 Cluster
# --- Initialize and Start PostgreSQL 16 ---

# For RHEL/CentOS systems:
sudo /usr/pgsql-16/bin/postgresql-16-setup initdb
sudo systemctl start postgresql-16
sudo systemctl enable postgresql-16

# For Debian/Ubuntu systems (uncomment and use the commands below as needed):
# The cluster is usually created automatically. First, check its status:
# pg_lsclusters
#
# If the cluster status is 'down', start it:
# sudo systemctl start postgresql
#
# If no version 16 cluster exists, create it:
# sudo pg_createcluster 16 main --start
#
# Finally, ensure the service is enabled to start on boot:
# sudo systemctl enable postgresql

Adjust Authentication for Debian/Ubuntu Systems

New PostgreSQL 16 installations default to a modern authentication method (scram-sha-256) that may not be compatible with the Distribution application. You must change this to md5 to ensure connectivity.
a. Open the new PG16 configuration file:
sudo vim /etc/postgresql/16/main/pg_hba.conf

 

b. Change the Authentication Method.
Look for the line host all all 127.0.0.1/32 scram-sha-256 and change scram-sha-256 to md5.
# IPv4 local connections:
host    all             all             127.0.0.1/32            md5

 

c. Restart PostgreSQL to Apply Changes.
sudo systemctl restart postgresql

4. Create Database and Restore Data
# Create the role and database for Distribution
sudo -u postgres psql -c "CREATE ROLE distribution WITH LOGIN PASSWORD 'YOUR_PASSWORD';"
sudo -u postgres psql -c "CREATE DATABASE distribution OWNER distribution;"

# Restore the data from your backup file
sudo -u postgres psql -f /path/to/ds_database_backup.sql

5. Grant Privileges
sudo -u postgres psql -d distribution -c "
  GRANT ALL ON SCHEMA public TO distribution;
  GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO distribution;
  GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO distribution;
  GRANT ALL PRIVILEGES ON ALL FUNCTIONS IN SCHEMA public TO distribution;
  ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL PRIVILEGES ON TABLES TO distribution;
  ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL PRIVILEGES ON SEQUENCES TO distribution;
  ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL PRIVILEGES ON FUNCTIONS TO distribution;
"

6. Upgrade and Start JFrog Distribution
# Navigate to the new installer directory for your OS
# For RHEL/CentOS:
cd /path/to/jfrog-distribution-2.33.x-rpm/
# For Debian/Ubuntu (uncomment the line below):
# cd /path/to/jfrog-distribution-2.33.x-deb/

# Run the installer to upgrade the application files
./install.sh

# Start the service
systemctl start distribution

Method 2: Docker Compose Installation

1. Back Up Database and Master Key

Before making any changes, back up the database from your running PostgreSQL 13 environment.
docker exec <your_postgres_13_container_name> pg_dumpall -U distribution > db_backup.sql

2. Shut Down all services

Stop the current environment and completely remove the old PostgreSQL data directory from your host machine.
a. Shut down all services:
docker compose -p distribution down
docker compose -p distribution-redis -f docker-compose-redis.yaml down
docker compose -p distribution-postgres -f docker-compose-postgres.yaml down
b. Move the old database directory to a temporary location. Verify your db_backup.sql file is secure before proceeding.
sudo mv ${ROOT_DATA_DIR}/var/data/postgres/data /tmp/postgres_data_backup

3. Prepare the New Docker Compose Configuration

Prepare the new Distribution version's Docker Compose files by copying the latest templates and then merging your custom settings.
a. Extract the new JFrog Distribution archive for Docker Compose and navigate into the extracted folder.
b. Copy the new template files from the templates directory to the root of this folder, as they may contain important updates.
cp -f templates/docker-compose.yaml .
cp -f templates/docker-compose-postgres.yaml .
cp -f templates/docker-compose-redis.yaml .
c. Copy your old .env file's contents to the new .env file, but do not copy the version variables. The .env file is located in the root of the extracted folder.
## This file contains environment variables used by the docker-compose yaml files

## IMPORTANT: During installation, this file may be updated based on user choices or existing configuration

## Docker registry to fetch images from
DOCKER_REGISTRY=releases-docker.jfrog.io

## The Installation directory for Distribution. IF not entered, the script will prompt you for this input. Default [$HOME/.jfrog/distribution]
ROOT_DATA_DIR=/opt/jfrog/distribution

## The distribution user and group user by the Operating System
DISTRIBUTION_USER=1020

## Versions to install
DOCKER_VERSION_ROUTER=7.179.1
DISTRIBUTION_VERSION=2.33.2
OBSERVABILITY_VERSION=2.13.0

# Port on which the router should communicate. (router.entrypoints.externalPort)
JF_ROUTER_ENTRYPOINTS_EXTERNALPORT=8082

# Timeout in secs to validate jfrogUrl
JF_SHARED_JFROGURLTIMEOUT=10

 

d. Merge customizations from your old docker-compose.yaml into the new docker-compose.yaml file.
e. Modify the docker-compose-postgres.yaml file with the required compatibility settings:
  • Ensure the image tag points to the new PostgreSQL 16 version (e.g., releases-docker.jfrog.io/postgres:16.6-alpine).
  • Set the POSTGRES_PASSWORD environment variable to the correct plain-text password.
  • Add a command directive to force the server to use md5 password encryption, which is compatible with the restored user's password hash from the old database.
    Your final postgres service definition in the docker-compose-postgres.yaml file should look like this:
services:
  postgres:
    image: "releases-docker.jfrog.io/postgres:16.6-alpine"
    # ...
    environment:
      - POSTGRES_PASSWORD="your_plain_text_password"
    # ...
    # ADD THIS COMMAND SECTION TO FORCE MD5
    command:
      - "postgres"
      - "-c"
      - "password_encryption=md5"


4. Restore Data and Complete the Upgrade

We can now start the new Postgres 16 database and restore the data. After the data is restored, start the remaining services in the correct order.
a. Start ONLY the database container.
docker compose -p distribution-postgres -f docker-compose-postgres.yaml up -d
b. Restore the database backup.
cat db_backup.sql | docker exec -i distribution_postgres psql -U distribution
c. Update the Installer State File. Before running the configuration script, you must update the ${ROOT_DATA_DIR}/var/etc/installerState.yaml file to inform the installer of the new database version.
  • Change From:
    postgresql_version: 13-10v

  • Change To:
    postgresql_version: 16-6v
d. Run the interactive installer script. This script prepares the environment and finalizes the application configuration before startup.
./config.sh
e. Start the remaining services.
docker compose -p distribution-redis -f docker-compose-redis.yaml up -d
docker compose -p distribution up -d
Your upgraded JFrog Distribution instance should now start successfully with the upgraded PostgreSQL 16 database.

5. Clean Up

After you have confirmed the upgrade is successful, you can safely delete the database backup directory
sudo rm -rf /tmp/postgres_data_backup