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
Using Native PostgreSQL Repository
Since 4.4This 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.xmlworks 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.xmlis to use
<repositoryServiceFactoryClass>element (which does not work with new repo). Definitely don’t try to use both
For SQL audit replace old repository value in
com.evolveum.midpoint.repo.sqale.audit.SqaleAuditServiceFactory. Audit factory class for SQL auditing must match the used repository.
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.
listen_addresses = '*'in
postgresql.confit’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.
pg_hba.conf, otherwise you (and JDBC driver) will not be able to connect to the database remotely.
In short, this can all be added to the end
postgresql.conf (then restart the server):
|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.|
First command is to be executed in
bash on the server with Postgres, the rest is executed inside
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
postgres-new.sqlis the content of the main repository schema without audit tables. This file also creates
publicschema if missing - dropping the whole
publicschema is often the fastest way to replace any previous schema (obviously, not recommended for production).
postgres-new-audit.sqlis 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 scheduler in midPoint can be configured to use a database with
config.xml set to
This is also the default if
clustered is set to
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
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
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
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
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
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.