Database Schema Upgrade

Last modified 24 Nov 2025 23:26 +01:00
Table of Contents

Introduction

New midPoint releases usually bring new features. New features usually require the extension of midPoint data model to use them. The changes of data model usually require extension of the schema of the database that stores midPoint objects. This is the usual routine for most midPoint upgrades.

MidPoint distributions come with the database upgrade scripts. These scripts contain a set of SQL commands (usually ALTER TABLE commands) that extend the schema of existing database. The scripts are designed to be non-desctructive, therefore they can safely be executed over a database that is populated with data. (Of course, the usual backup routine is strongly recommended.)

Database schema upgrade usually introduces new columns which needs to be populated from full object. It is highly recommended to run Reindex Repository Task (GUI → About → Reindex Repository Objects) after database schema upgrades.

Upgrading Native PostgreSQL Repository

This section describes how to upgrade the Native repository. For future releases, the process should be similar to previous upgrades.

Upgrade Script Location

Distribution Script location

Binary

<distribution root>/doc/config/sql/native/

Source

<source code root>/config/sql/native/
Always use the scripts from the version you want to upgrade to - either from distribution or from sources. Do not use the upgrade scripts from the master branch, e.g. downloaded directly from GitHub, as these may contain development changes already (unless you really want to try the cutting edge development version).

Executing the script

The Native repository has separate upgrade scripts for the main portion of the repository (postgres-upgrade.sql) and for the audit tables (postgres-audit-upgrade.sql). This makes the process easier for deployments with separate audit database - you simply use the right upgrade script on each database. If both repository and audit is in the same database, use both scripts on the same database.

The scripts do not contain any version number and are safe to run repeatedly - only the missing changes are applied.

If you created the schema objects as non-superuser as described here, be sure to run all the missing CREATE EXTENSION commands as a superuser first! This is also a good reason to use -v ON_ERROR_STOP=1 flag with the psql - as shown below. This stops the upgrade when the first such problem occurs, but works fine if the extension already exists.

To upgrade the main repository run this command:

psql -v ON_ERROR_STOP=1 -h localhost -U midpoint -W -d midpoint -f postgres-upgrade.sql

To upgrade the audit database (here on the same server, but different database) run:

psql -v ON_ERROR_STOP=1 -h localhost -U midaudit -W -d midaudit -f postgres-audit-upgrade.sql

To upgrade database containing both, use multiple -f options:

psql -v ON_ERROR_STOP=1 -h localhost -U midpoint -W -d midpoint \
  -f postgres-upgrade.sql -f postgres-audit-upgrade.sql

The scripts store their internal version information in the m_global_metadata table. Please, do not modify this table manually.

You can use other client than psql, but the client must send the commands to the server separately. E.g. IDEA Ultimate Edition or DataGrip from JetBrains work fine.

Some clients, notably pgAdmin, send the whole content in a single request. Please, do not use them to run upgrade scripts!

See Also

Was this page helpful?
YES NO
Thanks for your feedback