Installing PostgreSQL 18

Note: Follow this instruction only if you want to install PostgreSQL on an external box, that is, on a separate host from OpenIAM. On the OpenIAM box, PostgreSQL is installed and configured automatically by the init script during the RPM installation, so there is nothing to do here.

PostgreSQL 18 is one of the most popular open-source relational database systems. It is possible to install it both online (using internet-based package repositories) and offline (manual or air-gapped) installation methods. This steps below will guide you through Postgres 18 installation on Enterprise Linux 9.

Internet-based installation

Add PostgreSQL yum repository with the following commands.

sudo dnf install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-9-x86_64/pgdg-redhat-repo-latest.noarch.rpm

Disable built-in PostgreSQL module as follows.

sudo dnf -qy module disable postgresql

Install PostgreSQL 18 server.

sudo dnf install -y postgresql18-server

Initialize database cluster.

sudo /usr/pgsql-18/bin/postgresql-18-setup initdb

Enable and start PostgreSQL service.

sudo systemctl enable --now postgresql-18
systemctl status postgresql-18

Configure database and roles.

sudo su - postgres
chown postgres:postgres /tmp/setup.sql
psql -f /tmp/setup.sql

Note: Provide all contents in setup.sql from initialization AQL script of OpenIAM and Activiti database to create necessary schema and users.

Next, update role settings

psql -d openiam -c "ALTER ROLE idmuser SET search_path TO public; GRANT USAGE ON SCHEMA public TO idmuser; GRANT CREATE ON SCHEMA public TO idmuser;"
psql -d activiti -c "ALTER ROLE activiti SET search_path TO public; GRANT USAGE ON SCHEMA public TO activiti; GRANT CREATE ON SCHEMA public TO activiti;

Verify service status.

systemctl status postgresql-18.service

Non-internet-based installation

Non-internet-based installation is similar to that of internet-based except the packages need to be downloaded manually.

Download offline package at https://download.openiam.com/release/enterprise/infra/postgres-18.tar.gz
Install PostgreSQL packages with the following command.

sudo dnf install -y --disablerepo="*" *.rpm --skip-broken

Initialize database cluster.

sudo /usr/pgsql-18/bin/postgresql-18-setup initdb

Enable and start PostgreSQL service.

sudo systemctl enable --now postgresql-18
systemctl status postgresql-18

Configure database and roles

sudo su - postgres
chown postgres:postgres /tmp/setup.sql
psql -f /tmp/setup.sql

Note: Provide all contents in setup.sql from initialization AQL script of OpenIAM and Activiti database to create necessary schema and users.

Next, update role settings.

psql -d openiam -c "ALTER ROLE idmuser SET search_path TO public; GRANT USAGE ON SCHEMA public TO idmuser; GRANT CREATE ON SCHEMA public TO idmuser;"
psql -d activiti -c "ALTER ROLE activiti SET search_path TO public; GRANT USAGE ON SCHEMA public TO activiti; GRANT CREATE ON SCHEMA public TO activiti;

Verify service status with the following command.

systemctl status postgresql-18.service

Connecting to locally installed PostgreSQL

First, access PostgreSQL CLI with

sudo su - postgres
psql

... and list all databases as follows.

\l

Compatibility matrix

Compatibility Matrix