docs/installation-and-operations/misc/migration-to-postgresql17/README.md
OpenProject version 16+ will default to PostgreSQL 17. If you have an existing OpenProject installation, please follow the guide below to upgrade your PostgreSQL version.
For the time being, using an older Postgres version is still possible, but not recommended.
This documentation shows how to upgrade PostgreSQL via a SQL dump.
If you prefer doing the upgrade using the in-place method, you are free to do so.
[!IMPORTANT] Please follow this section only if you have installed OpenProject using this procedure. Before attempting the upgrade, please ensure you have performed a backup of your installation by following the backup guide.
Create a backup of your current PostgreSQL database. Run the following command from your OpenProject project directory:
docker compose exec -it -u postgres db pg_dump -d openproject -x -O > openproject.sql
This creates a backup named openproject.sql.
Shut down all running containers:
docker compose down
To upgrade to PostgreSQL 17, you need to override the default database image.
docker-compose.override.ymlCreate a file named docker-compose.override.yml in the docker compose directory.
Replace the default PostgreSQL version with 17.
Also, define a new volume for the upgraded database to avoid overwriting the existing one.
Here’s an example configuration:
volumes:
pgdata17:
services:
db:
image: postgres:17
volumes:
- pgdata17:/var/lib/postgresql/data
With your override file in place, start only the database container:
docker compose up db -d
This starts a clean PostgreSQL 17 container with an empty data directory.
Now that the new database is running, restore your backup:
docker compose exec -T -u postgres db psql -d openproject < openproject.sql
This will import your data into the new PostgreSQL 17 container.
With your data restored, bring up the rest of the OpenProject services:
docker compose up -d
You now have OpenProject running with PostgreSQL 17. Verify everything works correctly by visiting your OpenProject instance in the browser.
[!IMPORTANT] Please follow this section only if you have installed OpenProject using this procedure. Before attempting the upgrade, please ensure you have performed a backup of your installation by following the backup guide.
This only works if you are using OpenProject >= 16.2 because older versions have PostgreSQL 13 as the default database.
Create a PostgreSQL dump using:
docker exec -it $OP_CONTAINER_NAME su - postgres -c 'pg_dump -d openproject -x -O' > openproject.sql
This command connects to the running container and exports the database into a SQL file on your host machine.
docker stop $OP_CONTAINER_NAME
Run a fresh PostgreSQL 17 container using a new volume:
docker run --rm -d --name postgres \
-e POSTGRES_PASSWORD=postgres \
-e LANG=C.UTF-8 \
-e LC_ALL=C.UTF-8 \
-v /var/lib/openproject/pgdata17:/var/lib/postgresql/data \
postgres:17
Connect to the new PostgreSQL 17 container and drop the openproject database:
echo "CREATE USER openproject WITH PASSWORD 'openproject';" | docker exec -i postgres psql -U postgres
Now create a fresh openproject database:
echo "CREATE DATABASE openproject OWNER openproject;" | docker exec -i postgres psql -U postgres
Restore your data:
docker exec -i postgres psql -U openproject -d openproject < openproject.sql
This imports your backup into the newly created database.
docker stop postgres
You can now run a new OpenProject container connected to your upgraded PostgreSQL 17 data volume:
docker run -d -p 8080:80 --name openproject \
-e OPENPROJECT_HOST__NAME=openproject.example.com \
-e SECRET_KEY_BASE=secret \
-v /var/lib/openproject/pgdata17:/var/openproject/pgdata \
-v /var/lib/openproject/assets:/var/openproject/assets \
openproject/openproject:17
Make sure the environment variables and version match your setup.
Visit your OpenProject instance to confirm everything works as expected.
[!IMPORTANT] Please follow this section only if you have installed OpenProject using this procedure. Before attempting the upgrade, please ensure you have performed a backup of your installation by following the backup guide.
sudo service openproject stop
pg_dump $(sudo openproject config:get DATABASE_URL) -x -O > openproject.sql
sudo pg_ctlcluster 13 main stop
sudo systemctl stop postgresql-13
sudo apt update
sudo apt install postgresql-17
sudo pg_createcluster 17 main --start
sudo dnf install -y postgresql17-server postgresql17-contrib
sudo /usr/pgsql-17/bin/postgresql-17-setup initdb
sudo systemctl enable --now postgresql-17
TODO: sudo zypper addrepo https://download.postgresql.org/pub/repos/zypp/17/suse/sles-15.5-x86_64/ openSUSE-PostgreSQL-17
TODO: sudo zypper install --repo openSUSE-PostgreSQL-17 postgresql17 postgresql17-server postgresql17-libs postgresql17-contrib
#TODO:? sudo su - postgres -c '/usr/lib/postgresql17/bin/initdb -D /var/lib/pgsql/17/data'
sudo systemctl enable postgresql
sudo systemctl start postgresql
sudo su - postgres -c "cp /etc/postgresql/13/main/pg_hba.conf /etc/postgresql/17/main/pg_hba.conf"
sudo su - postgres -c "cp /etc/postgresql/13/main/conf.d/custom.conf /etc/postgresql/17/main/conf.d/custom.conf"
sudo pg_ctlcluster 17 main restart
sudo su - postgres -c "cp /var/lib/pgsql/13/data/pg_hba.conf /var/lib/pgsql/17/data/pg_hba.conf"
sudo su - postgres -c "cp -r /var/lib/pgsql/13/data/conf.d /var/lib/pgsql/17/data/"
sudo su - postgres -c "cp -r /var/lib/pgsql/13/data/postgresql.conf /var/lib/pgsql/17/data/postgresql.conf"
sudo service postgresql-17 restart
sudo su - postgres -c "cp /var/lib/pgsql/13/data/pg_hba.conf /var/lib/pgsql/data/pg_hba.conf"
sudo su - postgres -c "cp -r /var/lib/pgsql/13/data/conf.d /var/lib/pgsql/data/"
sudo su - postgres -c "cp -r /var/lib/pgsql/13/data/postgresql.conf /var/lib/pgsql/data/postgresql.conf"
sudo systemctl restart postgresql
sudo apt remove --purge postgresql-13
sudo dnf remove postgresql13-server
sudo zypper remove postgresql13-server
sudo su - postgres -c "psql -p 45432 -c \"create user openproject with password '$(sudo openproject config:get DATABASE_URL | sed -n 's|.*://[^:]*:\([^@]*\)@.*|\1|p')'\""
sudo su - postgres -c "psql -p 45432 -c 'create database openproject owner openproject'"
psql $(sudo openproject config:get DATABASE_URL) < openproject.sql
sudo openproject restart
Visit your OpenProject instance in the browser to confirm everything works as expected.
Please follow this section only if you have installed OpenProject using this procedure. Before attempting the upgrade, please ensure you have performed a backup of your installation by following the backup guide.
Stop your frontend or scale it down to 0 to prevent frontend changes.
Backup your database by entering the shell of the existing PostgreSQL pod:
kubectl exec -it <postgresql-pod-name> -- bash
Create a PostgreSQL dump of the database and save it to the persistent directory:
PGPASSWORD=$(cat "${POSTGRES_POSTGRES_PASSWORD_FILE:-/dev/null}" && echo "$POSTGRES_POSTGRES_PASSWORD") pg_dumpall -U postgres > /bitnami/postgresql/backup.sql
mv /bitnami/postgresql/data /bitnami/postgresql/data-old
postgresql:
image:
tag: 17.5.0-debian-12-r16
After upgrading the Helm chart, enter the shell of the newly upgraded PostgreSQL pod:
kubectl exec -it <new-postgresql-pod-name> -- bash
Restore the backup by running the following command:
PGPASSWORD=$(cat "${POSTGRES_POSTGRES_PASSWORD_FILE:-/dev/null}" && echo "$POSTGRES_POSTGRES_PASSWORD") psql -U postgres -h localhost -f /bitnami/postgresql/backup.sql
Restore Frontend Availability by starting the frontend or scaling it up again.
Verify the Upgrade by ensuring everything is working as expected by checking that the PostgreSQL instance is running correctly and the frontend is accessible.
Remove Backup Files:
Once verified, enter the shell of the PostgreSQL pod again and remove the backup files to clean up:
rm /bitnami/postgresql/backup.sql
rm -r /bitnami/postgresql/data-old
After an upgrade of PostgreSQL, we strongly recommend running the following SQL command to ensure query plans are regenerated as this doesn't necessarily happen automatically.
For that, open a database console. On a packaged installation, this is the way to do it:
psql $(openproject config:get DATABASE_URL)
Please change the command appropriately for other installation methods. Once connected, run the following command
ANALYZE VERBOSE;
User "openproject" does not have a valid SCRAM secret - psql: error: FATAL: password authentication failed for user "openproject"
Check /var/lib/pgsql/17/data/pg_hba.conf for any appearance of scram-sha-256 and replace with md5
Check /var/lib/pgsql/17/data/postgresql.conf for any appearance of scram-sha-256 and replace with md5 (search for encryption)
Reload Configuration of PostgreSQL server with systemctl reload postgresql-17