Database schema upgrade

Last modified 08 Sep 2026 09:37 UTC
Identity repository feature
This page describes Identity repository midPoint feature. Please see the feature page for more details.
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 upgrades often introduce new database columns values of which can be derived from the existing object data. Although the required information is already present in the repository as part of the complete object representation, the newly introduced columns remain empty until the objects are reprocessed. Running the Reindex Repository Task recomputes these values and populates the new columns. It is highly recommended to run Reindex Repository Task (GUI → About → Reindex Repository Objects) after database schema upgrades.

The right process for the right repository

Since version 4.4, midPoint features two SQL repository implementations and these have different upgrade procedures. It is absolutely critical to use the right upgrade script for the right repository. While the upgrade uses upgrade scripts in both cases, there are small differences for each repository. To make it simpler, there are separate sections for each repository implementation below.

How do I know what repository I’m using?

One should know what repo is used. But just in case, there is a couple of ways to find out:

  • In GUI go to About page and check Repository panel. If Implementation name says Native, you are using the new Native repository recommended from version 4.4 on. Old Generic repo says SQL instead.

  • If in your config.xml the content of repository/type element is native (or sqale, or scale, casing does not matter), you are using the Native repository. If there is generic (or sql) instead, or there is no type element under repository, but repositoryServiceFactoryClass is used instead, you are using the old repository.

  • If you are connected to the database, there are some differences in tables. Try select * from m_uri limit 1 - if it works without any error (even if it does not return anything), you are using the new Native repository. If it doesn’t work - and select * from m_object_text_info limit 1 on the other hand does - then you are using the old repository.

Upgrading Native PostgreSQL Repository

This section describes how to upgrade the Native repository.

Upgrade script location

Upgrade scripts are included in every midPoint release in the same directories.

Depending on how you obtained midPoint, the scripts can be found in one of the following locations:

  • Binary distributions:

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

  • Source code repository:

    <source code root>/config/sql/native/

where:

  • <distribution root> is the root directory of the unpacked midPoint release package.

  • <source code root> is the root directory of the midPoint source code repository.

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 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, 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 repository schema, execute the appropriate SQL upgrade script against the PostgreSQL database. Upgrade scripts can be executed using the PostgreSQL psql client.

If you choose to execute the upgrade script using the psql command-line tool, provide the appropriate values for the following options:

The following option is required when executing an upgrade script using the psql command-line tool:

Option Description

-f

SQL script file to execute.

These options are optional.

Option Description

-v ON_ERROR_STOP=1

Terminates script execution on the first SQL error.

-h

Database host name or IP address.

-U

Database username.

-d

Database name.

-W

Prompts for the database password.

If connection options (-h, -U, and -d) are omitted, psql uses its built-in defaults.

By default, the database username is the current operating system username, and the database name is the same as the selected database user. If no host is specified, psql connects to the local PostgreSQL server.

Visit the PostgreSQL documentation to learn more about the default connection behavior.

Examples

The exact psql command depends on your deployment topology, database configuration, and credentials. The following examples illustrate common upgrade scenarios.

To upgrade the main repository database:

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

To upgrade a separate audit database:

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

If repository and audit data are stored in the same database, execute both upgrade scripts:

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

The upgrade scripts store their internal version information in the m_global_metadata table. 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. Do not use them to run upgrade scripts!

Executing upgrade scripts using Ninja

Executing upgrade scripts using the psql command-line tool may not always be straightforward. In containerized deployments, the upgrade scripts are typically located in the midPoint container, while the PostgreSQL database runs in a separate database container. In addition, the psql client may not be available in the environment from which the upgrade is being executed.

As an alternative, upgrade scripts can be executed using the Ninja tool. Ninja reads the SQL script from a file and executes it against the configured repository database using the database connection settings from the midPoint configuration.

To upgrade the database schema using Ninja, see Run SQL.

This approach avoids the need to install the PostgreSQL client or manually establish a connection to the database.

Upgrading Generic Repository

This section describes how to upgrade the Generic repository.

Upgrade Script Location

Distribution Script location

Binary

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

Source

<source code root>/config/sql/generic/

The script file name is constructed in a form:

<database>-upgrade-<from version>-<to version>.sql

e.g. oracle-upgrade-4.5-4.6.sql is an upgrade script for PostgreSQL database that upgrades midPoint 4.5 to midPoint 4.6.

Executing the script

The scripts should be executed by the usual way the SQL script is executed for any particular database. Perhaps the best way is to use command-line tools. Please refer to the documentation of your database system for the details. E.g., the upgrade script for PostgreSQL database is usually executed like this:

psql -h localhost -U midpoint -W -d midpoint -f oracle-upgrade-4.5-4.6.sql

The scripts are designed to be run only once - unlike upgrade scripts for the Native repository, they are not idempotent.

Strictly speaking, the version term above refers to the database schema version, not the midPoint version. These are the same for the majority of cases. For generic repository, the latest version of repository script is "4.6" and there were no changes afterward for generic repository. See Database schema versioning for more information.

See Also

Compliance

This feature is related to the following compliance frameworks:

Was this page helpful?
YES NO
Thanks for your feedback