listen_addresses = '*' # this is necessary for pg_stat_statements extension shared_preload_libraries = 'pg_stat_statements' # To log all queries, uncomment the lines below. # Monitor the free disk space afterwards, there is no automatic cleanup! #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.
To use midPoint with Native PostgreSQL repository you need to:
Get the midPoint distribution and install it as described here; you can stop after unpacking the archive and setting up
Install and set up the PostgreSQL database - this is the part covered by the bulk of this document.
config.xmlto configure midPoint for the Native repository. Read Repository Configuration for more information, including a complete example of
config.xmlfor Native repository.
Finally, you can run midPoint; this is described in the rest of the installation document mentioned in the first step.
PostgreSQL versions 13 and newer are supported. We always recommend using the latest stable version, which as of writing this document is 14.x.
Installation and configuration
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 14 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
|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 the default Postgres server sizing is rather small. You can use a configuration calculator like this to get reasonable defaults if you know the DB hardware (use "Mixed type of application" as DB type).|
For more information regarding this configuration file read this document from official PostgreSQL documentation.
To make things simple, we will allow all connections to the database; password is still required.
Add the following lines at the end of the
# Allow connections from outside (with password) host all all 0.0.0.0/0 md5
For more information regarding this configuration file read the official documentation.
Restart the server
Any chanes to
pg_hba.conf requires PostgreSQL server restart.
Do so now to apply the configration changes.
|Don’t use pgAdmin for midPoint SQL scripts! Explanation is in one of the important blocks below.|
Assuming we start in
bash as a root or sudo capable user, let’s first run
psql as database admin:
sudo -i -u postgres psql
Next we will create a user and a database for midPoint.
In the following example, we create
midpoint user as database superuser.
If you require non-superuser, follow this section instead.
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.
postgres-new-quartz.sqlis the script with tables necessary for midPoint scheduler (using Quartz Schduler). These tables are optional, but it is recommended to create them in case you need them. The tables must be created in the main repository schema.
See Native PostgreSQL Audit Trail 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 (13 or higher).
|If you want to migrate your old audit records to the new database and keep event record IDs unique, set the ID sequence in the new database as described in Audit migration from other database.|
We recommend using
psql for database initialization like this:
export PGPASSWORD=password # password for psql, you can skip it and enter it interactively # If you want to replace any previous tables in the schema, uncomment this drop too: #psql -h localhost -d midpoint -U midpoint -c "drop schema public cascade" psql -h localhost -d midpoint -U midpoint -f postgres-new.sql -f postgres-new-audit.sql -f postgres-new-quartz.sql
If you want to use different schema name than
public you have to adjust the initialization commands.
Prefer psql command
You can use other client than
Some clients, notably pgAdmin, send the whole content in a single request.
This does not go well with midPoint initalization script that calls procedure with
Alternatively, it seems that wrapping the content of the file with an anonymous block
works, but we still recommend using tools that can execute files properly - that is
Quartz scheduler in midPoint can be configured to use a database with
Even if not required, it is best to create these tables in case the scheduler configuration changes later.
If you plan to use statement statistics extension (not discussed here), initialize it like this:
psql -h localhost -d midpoint -U midpoint -c "create extension pg_stat_statements"
Installing As Non-Superuser
The instruction above assume that database user
midpoint is a database superuser.
This is a very convenient method.
However, it is likely to be a security concern, especially in case that midPoint is sharing database engine with other applications.
Following steps describe the process of database initialization without granting superuser privileges to midPoint user.
psqlutility as superuser (
postgres). Execute following commands.
Create user without superuser privileges:
CREATE USER midpoint WITH PASSWORD 'password' LOGIN NOSUPERUSER NOCREATEDB NOCREATEROLE;
Or alternatively use
sudo -u postgres createuser --pwprompt --no-superuser --no-createdb --no-createrole midpoint
midpointuser as an owner:
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;
As database superuser (
postgres) connect to the new database:
Execute the commands that create database extensions. Such commands require superuser privileges:
CREATE EXTENSION IF NOT EXISTS intarray; CREATE EXTENSION IF NOT EXISTS pg_trgm; DO $$ BEGIN PERFORM pg_get_functiondef('gen_random_uuid()'::regprocedure); RAISE NOTICE 'gen_random_uuid already exists, skipping create EXTENSION pgcrypto'; EXCEPTION WHEN undefined_function THEN CREATE EXTENSION pgcrypto; END $$;
Run the rest of the SQL script as user
psql -h localhost -d midpoint -U midpoint -f doc/config/sql/native-new/postgres-new.sql
There may be errors indicating problems with creating database extensions, caused by the script attempting to execute a privileged operation. You can ignore such errors, as the extensions were created by superuser in previous step.
Continue installation process normally, which means executing the audit SQL script
postgres-new-audit.sqland script for Quartz tables
At this moment the database is prepared and it’s time to configure midPoint appropriately.
This typically preparing proper repository configuration in
Configuration options related to repository as well as an example of
config.xml file can be found
in Repository Configuration article.
The article references this page, but if you finished the steps above in this document, you can focus just on the config options.
With the database ready and midPoint configuration file referencing the Native repository,
you can finally run midPoint, e.g. by running
More details on running midPoint are mentioned in the installation document.
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.
Be sure you’re running the upgrade script provided with the version you’re actually using, see
database schema upgrade.
You can find further details in the source code documentation for
apply_change procedure at the end of the
If you find a bug or encounter performance problem with the Native repository, it is always important to gather more information before reporting the issue.
In case of error or exception, always include the relevant portion of the midpoint.log in the report.
Review Index tuning tips for performance problems, especially for extension items or shadow attributes.
If the performance problem is indeed DB related, identify the slow query, preferably using
pg_stat_statementsextension as described here.
To log issued SQL queries in midpoint.log, configure system loggers (System in main menu, then Logging) so that it contains
DEBUG(shows SQL) or
TRACE(includes parameter values).