Using Native PostgreSQL Repository

Last modified 22 Oct 2021 11:36 +02:00
Since 4.4
This functionality is available since version 4.4.

Changes from old to new repository

What is similar and what is different when configuring midPoint for the new repository?

  • New repository supports only PostgreSQL database of version 13 and later.

  • The database must be installed and prepared as described below. The setup for Postgres is fairly similar to the old repo, just different schema files are used.

  • Most of the repository configuration in config.xml works as before. New repo doesn’t use Hibernate, so any Hibernate related configuration is ignored. See this Repository Configuration document for details.

  • Main difference in config.xml is to use <type>generic</type> instead of <repositoryServiceFactoryClass> element (which does not work with new repo). Definitely don’t try to use both <repositoryServiceFactoryClass> and <type> simultaneously!

  • For SQL audit replace old repository value in auditServiceFactoryClass with com.evolveum.midpoint.repo.sqale.audit.SqaleAuditServiceFactory. Audit factory class for SQL auditing must match the used repository.

Database setup

Installation

This guide does not cover the installation process as there are many possible combinations. To install PG 13 on Ubuntu 20.04 one can use these steps for an inspiration. Adjust the setup in pg_hba.conf to the real IP address of the server. Setup can be different if PG is used only on localhost, but we assume host-to-host communication which is typical for production setup.

The short checklist:

  • Install PostgreSQL 13 on your OS or server or VM.

  • Setup listen_addresses = '*' in postgresql.conf.

  • While in postgresql.conf it’s also good to add statements statistics extension and query logging for better visibility (the latter may not be a good option for production and small disks). See the snippet lower.

  • Setup host…​md5 line in pg_hba.conf, otherwise you (and JDBC driver) will not be able to connect to the database remotely.

  • Restart PostgreSQL.

In short, this can all be added to the end postgresql.conf (then restart the server):

listen_addresses = '*'

# this is necessary for pg_stat_statements extension
shared_preload_libraries = 'pg_stat_statements'

# this is to log all the queries, just be aware of the free disk space
log_directory = 'pg_log'
log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'
log_statement = 'all'
logging_collector = on
Sizing the database server and adjust PostgreSQL configuration parameters is not part of this document at this moment - but we plan to add it. In the meantime rely on other online resources. Be aware that default Postgres server sizing is rather small.

Database preparation

First command is to be executed in bash on the server with Postgres, the rest is executed inside psql:

sudo -i -u postgres psql

CREATE USER midpoint WITH PASSWORD 'password' LOGIN SUPERUSER;
CREATE DATABASE midpoint WITH OWNER = midpoint ENCODING = 'UTF8' TABLESPACE = pg_default LC_COLLATE = 'en_US.UTF-8' LC_CTYPE = 'en_US.UTF-8' CONNECTION LIMIT = -1;

To initialize the database connect to midpoint database as midpoint user and execute the content of the following schema files provided with the distribution package under the doc/config/sql/native-new directory:

  • postgres-new.sql is the content of the main repository schema without audit tables. This file also creates public schema if missing - dropping the whole public schema is often the fastest way to replace any previous schema (obviously, not recommended for production).

  • postgres-new-audit.sql is the content of audit schema. It can be applied on top of the main repository schema or separately to another database. If both schemas are to be applied, first apply main repository schema followed by audit schema.

See Audit configuration for details how to set it up in a database separate from the main repository. Be aware that with new repository both databases have to be PostgreSQL databases. It is possible to use different versions for each database as long as they are supported (12 or higher).

The location of the files will likely change, and it will be packed in the distribution as well.

You can use any client to do this, or we can download the file on the VM and use it like this:

wget -q https://raw.githubusercontent.com/Evolveum/midpoint/master/config/sql/native-new/postgres-new.sql
wget -q https://raw.githubusercontent.com/Evolveum/midpoint/master/config/sql/native-new/postgres-new-audit.sql
# without this export psql will prompt for the password
export PGPASSWORD=password
# If you want to replace any previous tables in the schema, uncomment this drop too:
#psql -h localhost midpoint midpoint -c "drop schema public cascade"
psql -h localhost midpoint midpoint -f postgres-new.sql
psql -h localhost midpoint midpoint -f postgres-new-audit.sql

If you plan to use statement statistics extension (not discussed here), initialize it like this:

psql -h localhost midpoint midpoint -c "create extension pg_stat_statements"

Quartz tables

Quartz scheduler in midPoint can be configured to use a database with taskManager/jdbcJobStore option in config.xml set to true. This is also the default if clustered is set to true. See Task Manager Configuration for further details.

In that case Quartz requires that its tables are ready in the database. By default, the same database is used for repository and for Quartz, which can be changed by using jdbcUrl and other options inside taskManager section (see the link above).

Let’s simplify things and assume that we need the tables, and the same database is used. Connect to the midpoint database with midpoint user and execute the commands stored in doc/config/sql/native-new/postgres-new-quartz.sql in the distribution package.

The following commands can be used in the bash on the database VM:

wget -q https://raw.githubusercontent.com/Evolveum/midpoint/master/config/sql/native-new/postgres-new-quartz.sql
psql -h localhost midpoint midpoint -f postgres-new-quartz.sql

Example config.xml

Example config.xml is here. The main difference is using the type element instead of repositoryServiceFactoryClass which does not work for new repository anymore. Set the value of type element to native, values sqale or scale are also supported. Do not use sql which indicates old repo!

Native repository comes with native SQL audit, so we need to change the audit factory class in auditServiceFactoryClass element from old repository value containing …​SqlAuditServiceFactory to com.evolveum.midpoint.repo.sqale.audit.SqaleAuditServiceFactory.

With this config.xml you can start midPoint as usual. Consult Repository Configuration article for more details.

The setup for the new repo is also available in the config.xml automatically generated when the midPoint starts for the first time - it’s just commented out. Stop the midPoint, remove the created H2 database files, adjust the config file and start the midPoint again.

Of course, you still need the installed and prepared PostgreSQL database!

Versioning and upgrading

Long story short, just run the provided postgres-new-upgrade.sql anytime, it should be safe. It always runs only the missing parts of the upgrade process.

See also