Database schema upgrade

Last modified 08 Sep 2026 07:41 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.

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.

See also

Compliance

This feature is related to the following compliance frameworks:

Was this page helpful?
YES NO
Thanks for your feedback