OpenProject PostgreSQL 17 upgrade

A PostgreSQL major upgrade is a different kind of work from a minor update, and the difference determines how OpenProject is taken offline. Minor PostgreSQL releases never change the internal storage format, so they only replace executables and restart the server. A major release changes the data format, so the existing data must be rewritten or moved into a new cluster. PostgreSQL documents three methods for this: dump and restore with pg_dumpall, pg_upgrade, or logical replication. Each method trades setup effort against the length of the window in which OpenProject cannot accept writes. Logical replication works between major versions, and the PostgreSQL upgrading documentation describes it as allowing only several seconds of downtime, but it needs careful setup and verification before cutover.

Where the database lives depends on how OpenProject was installed, so the first task is to identify the install type. A packaged .deb or .rpm install runs PostgreSQL as a host service next to the application, and the cluster's data directory and service name are managed by the operating system packages. A Docker Compose install runs the database as a separate service whose data sits in a named or bind-mounted volume defined in the Compose file. An all-in-one container keeps the database inside the same container as the application, with its data in a volume mounted into that container. The exact data directory, service names and volume layout differ by install type and must be confirmed against the installed OpenProject version before any step is planned.

The OpenProject official install documentation is the reference for stopping and starting the application. Writes must stop before the database is dumped, replicated or switched, or the copy will not match the live data. Follow the documented order for the install type, and confirm that the application services have fully stopped before the database work begins. Check the target version as well. OpenProject documents support for PostgreSQL 16 or later and requires the pg_trgm, btree_gist and unaccent extensions, so confirm that PostgreSQL 17 is listed for your release and that the extensions are available in the new cluster before scheduling the window.

Before the window is booked, decide the method, the rollback path and who runs each step. Keep the original cluster or a verified backup until the upgraded installation has passed functional checks, because reverting a major upgrade is far harder than repeating a backup. PostgreSQL 17 is supported until November 8, 2029, while PostgreSQL 14 support ends on November 12, 2026, so the upgrade also serves as a supportability step. Ask your delivery team to document the chosen method, the stop and start sequence from the official documentation, the extension checks and the rollback steps, and confirm each item against your own installed version.

The choice among the three PostgreSQL methods depends on data size, install type and how much downtime the business will accept. Dump and restore is the simplest method to reason about. The source cluster is exported with pg_dumpall or pg_dump, the output is loaded into a PostgreSQL 17 server, and the result can be checked against the source before users return. The trade-off is time. Every row is written out and read back in, so downtime grows with the database size, and the window must cover the dump, the restore, index rebuilds and post-restore checks. For a small OpenProject instance, that window may be acceptable. For a large one, measure a test restore on a copy of production data before committing to it, and ask for the restore duration and the disk space the dump needs.

pg_upgrade converts the existing data directory in place. It avoids copying most of the data, which usually shortens the window compared with dump and restore, but it depends on two conditions. The old and new PostgreSQL binaries must be installed on the same host, and the data directory layout must match what the new binaries expect. Packaged installs on supported Linux distributions usually meet this because the old and new versions can be installed side by side. Docker Compose and all-in-one container installs complicate it, because the old binaries, the new binaries and the data volume must be available to the tool together. Confirm in your OpenProject install guide how your image stores its database files before you choose this path, and confirm that pg_trgm, btree_gist and unaccent are available on the new server.

Logical replication works between different major versions. The PostgreSQL documentation states that it can allow a major upgrade with only several seconds of downtime, but the method requires more setup than the other two. You create a publication on the source, a subscription on the PostgreSQL 17 server, let the initial copy complete, and then cut over the application. Two areas need specific planning. Logical replication does not carry sequence values in the same way it carries table rows, so the sequences must be set on the new server before cutover. Schema changes made during replication are not carried automatically, so freeze OpenProject migrations for the replication period. Plan a verification step that compares row counts and checksums for critical tables, and keep the source running as a rollback path until the new server is verified.

The right method is the one that fits your database size, install type and approved outage. A short window may favor logical replication if the team can run it reliably. A large database on a packaged install may favor pg_upgrade. A small instance with an overnight window may be well served by dump and restore. Before rehearsal, record the database size, the install type in production and the outage the business has approved in writing. Then confirm the steps against the OpenProject install guide for your release, since that guide defines the supported procedure for your install type and the settings it expects after the move.

PostgreSQL 18 pg_upgrade adds parallel jobs and a --swap option, and it retains planner statistics across major upgrades. These features matter only if the team later targets PostgreSQL 18. This project targets PostgreSQL 17, which is supported until November 8, 2029, so do not plan around the PostgreSQL 18 options now. If a move to PostgreSQL 18 is already on the roadmap, record the method and timings used for this upgrade. That record gives the team a baseline for comparing the newer pg_upgrade options when that later project is scoped.

The acceptance tests decide whether the upgrade is finished, so run them in the rehearsal against a restored copy of production data on the PostgreSQL 17 target. Compare row counts for projects, work packages and users between the source and target databases, then spot-check specific records: a project with a deep hierarchy, a work package with relations and attachments, and a user with several group memberships. Confirm that the pg_trgm, btree_gist and unaccent extensions that OpenProject requires are present in the target database. Record each result in a checklist that the project owner signs, so the go or no-go decision rests on recorded evidence from the rehearsal.

Authentication needs its own tests because a database move can break sign-in without changing any data. Log in through every identity path you have configured: a local account, an LDAP-backed account and, if you run it, an SSO account. For each one, confirm that roles and group memberships resolve as they did before the upgrade. If you use LDAP group synchronization or SSO, which are Enterprise features, test a user added to a group after the last sync. Upload, open and download a file attachment, confirm that background jobs such as notifications complete on the new database, and, if the Nextcloud link is in use, open a linked file from a work package to confirm it still resolves.

Rollback begins before cutover. Keep the original PostgreSQL data directory and the pre-upgrade backup intact until acceptance is signed off, and schedule their deletion as a separate later change. Define the conditions that trigger rollback in advance, such as a failed count comparison, a failed login path or a missing extension. If a check fails, stop the cutover, point OpenProject back at the original database, restart the service and rerun the same checks against the restored state. Confirm which backup method you used and restore it at least once during the rehearsal, because a backup that has never been restored is still an assumption.

Two infrastructure items are easy to miss. PostgreSQL does not provide software that detects a failed primary and promotes a standby, so any replica you run needs a documented promotion procedure, a plan for rejoining the old primary and a named owner who can carry out both steps. Practice promotion during the rehearsal so the team has done it before an incident forces the decision. Check PgBouncer or any other pooling layer after cutover as well: confirm it points at the new server, that its authentication settings and connection limits still match the environment, and that no connections to the old cluster remain before you declare the upgrade complete.