Driver for this database is bundled in MidPoint WAR archive.
Tips and Tricks
Create Database User
Delete Database User
Run databse script
Working with explicit schema
Development installation on Windows
(Tested in March 2020 with PostgreSQL 12.2.)
- Unpack the archive, e.g. to c:\work\tools\pgsql (pgsql is the name of zipped directory).
- Optional: Add C:\work\tools\pgsql\bin to your PATH environment variable for convenience.
- Optional: Decide where you want your postgres data and set PGDATA environment variable. This is useful for initdb and pg_ctl commands, but can be also provided with switches. I strongly recommend to set this one.
- Run (and provide admin password and remember it): initdb.exe -E utf8 --auth-host=scram-sha-256 -U admin -W
- If you don't have PGDATA set, provide the expected directory name with -D switch.
- By default superuser is named postgres, you can use that, if you want. This is useful on Linux where the name matches system user name, less so on Windows.
If we try to mess with locales (e.g. LC_CTYPE is set) initdb command may fail mysteriously with:
This is the start of the locale related problems on Windows, but not the end of it.
- Start the database with: pg_ctl start
- Again, use -D switch if you don't have PGDATA set.
- Log in: psql -W postgres admin
- -W forces the password prompt and is not necessary, unless you already set some psql environment variables; postgres is name of the default database, admin is the user name
Creating user and database
Now we can create user and the database as indicated at the start of this page - but Windows uses different names for locales, so we simply leave this out and hope it will not matter for our development purposes.
If we try the locales provided above, it will very likely complain about it and fallback to something like CPUTF-8.
Now the database is up and running, operated manually with pg_ctl start/stop, fully under our control, no services, no access rights problems (provided the directories you used are available to you).
Then we want to populate the database, e.g. from the root of Midpoint repository: psql midpoint midpoint < config\sql\postgresql-4.0-all.sql
- djust the Midpoint configuration. If you use multiple databases I recommend to add new midpoint.home, e.g.: c:\work\tmp\midpoint-home-postgres
- Add config.xml there with repository configured to use Postgres (the same as at the start of this page).
- Start your Midpoint application with switch: -Dmidpoint.home=c:\work\tmp\midpoint-home-postgres
To make psql easier to use you can set environment variables PGHOST=localhost (probably default), PGPORT=5432 (probably default), PGDATABASE=midpoint, PGUSER=midpoint, PGPASSWORD=password (normally obviously unsafe).
Installation up to this point does not allow connection from other machines and we don't need it for Midpoint and PostgreSQL running on the same machine. In the next experiment, with PostgreSQL on virtual machine, we have to solve this issue as well.
Vagrantbox with PostgreSQL on Linux
(Tested in March 2020, based on bento/ubuntu-19.10 box, PostgreSQL is 11.7)
Virtual machine provisioning
Create empty directory, which will be the working directory for this Vagrant environment, in our case representing a single Linux virtual machine we want to provision with PostgreSQL - we will call it vagrant directory. Create a single file called Vagrantfile there with the following content:
Now inside the vagrant directory (the one with Vagrantfile) run: vagrant up
No PostgreSQL can run on the host taking the 5432 port. We either have to stop the Postgres on the host, or use different port for host. This is perfectly fine, but we have to change it later in JDBC URL as it will not be default anymore.
Using the VM
This will provision the virtual machine and starts it up for us. Next vagrant up is faster, first provisioning always lasts longer. Now we want to log into the machine: vagrant ssh
We can check that everthing works just running psql, checking the list of databases there with \l - but we still don't have midpoint database populated, \d shows nothing. Let's get out with: \q
To halt the VM later we need to get out of it (exit) and run vagrant halt on our host machine, still in our vagrant directory. To restart it later just run vagrant up again. VM remembers everything when used like this.
Populating the DB
There are two ways how to run the initial script:
- We can copy postgresql-4.0-all.sql to the vagrant directory, which is shared with the virtual machine as its /vagrant directory. Than we can run from inside the box (as vagrant user): psql < /vagrant/postgresql-4.0-all.sql
- Or we can use psql from the host machine, if PostgreSQL is available there. Database port 5432 is forwarded from guest to host again as 5432, so it seems to be running on host directly.
The first way has an advantage that we don't need to install anything additional on the host - Midpoint and driver is obvious prerequisite that we need anyway. From here on it's again about setting the configuration, preferably in newly created midpoint.home that we provide to Midpoint using -D switch.
One big advantage of using Vagrant is repeatability. When we're done with our VM or we want to recreated it from scratch (e.g. something went wrong beyond repair) we just destroy it with: vagrant destroy -f
After that we just start it again with: vagrant up
This time it does not need to download the base box, but it still needs to provision it - that is to run update, PG install and other commands in the Vagrantfile, but this is much faster than manual work.
Vagrant, or better said VirtualBox used under the hood, sometimes has its own problems. If provisioning doesn't work as expected, first upgrade both tools and then restart the computer before trying again. This helps most of the time.
You can also run the VM directly from VirtualBox, but this is not recommended for Vagrant managed boxes. It may be needed to use VirtualBox to really get rid of some stuck VM.
Despite these possible annoying problems, I highly recommend Vagrant for provisioning of development environments.