Enabling PostgreSQL database for Developer Toolkit

Configure the Developer Toolkit to use a compatible PostgreSQL database version.

Before you begin

Ensure that you configure the Developer Toolkit environment and the docker_default network is created.
Download the PostgreSQL database JDBC driver (postgresql-42.2.24.jar) from PostgreSQL Database JDBC driver and Companion Jars Downloads. The driver is required to connect to your database.

Procedure

Create a PostgreSQL DB (version 14) container where your existing Developer Toolkit is configured. Set the container network to docker_default to connect other containers with the PostgreSQL container.
```
docker run -dt --rm --name postgres --net=docker_default -p 5432:5432 -e POSTGRES_USER=omdb -e POSTGRES_PASSWORD=omdb -e POSTGRES_DB=omdb docker.io/postgres:14
```

Run the following query to create and configure PostgreSQL schemas:

docker exec -i postgres psql -h localhost -p 5432 -U omdb omdb << EOF
ALTER USER omdb VALID UNTIL 'infinity';
CREATE SCHEMA omdb;
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA omdb TO omdb;
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA omdb TO omdb;
GRANT ALL PRIVILEGES ON ALL FUNCTIONS IN SCHEMA omdb TO omdb;
EOF

Note: If you want to use multischema mode, create additional database schemas.

Edit the compose/docker/omruntime/safestart.properties.defaults properties to replace db2Pool value with postgresqlPool as following.

jdbcService.postgresqlPool.url=jdbc:postgresql://<PostgreSQL_Container_IP>:5432/omdb
jdbcService.postgresqlPool.catalog=omdb
jdbcService.postgresqlPool.dbname=omdb
jdbcService.postgresqlPool.user=omdb
jdbcService.postgresqlPool.password=omdb
jdbcService.postgresqlPool.schema=omdb
yfs.yfs.install.localecode=REPLACE_LOCALE_CODE

Run the following command from the compose folder.
```
om-compose.sh setup-upg
```

Copy the downloaded PostgreSQL database JDBC driver (postgresql-42.2.24.jar) to your om-runtime container. If you did not download the driver, see PostgreSQL database JDBC driver download.
```
docker cp <path_to_postgresql-42.2.24.jar> om-runtime:/opt/ssfs/
```

Install postgresql-42.2.24.jar from <INSTALL_DIR>/bin by running the following commands:

./install3rdParty.sh postgres 42_2_24 -d /opt/ssfs/postgresql-42.2.24.jar -targetJVM EVERY

./install3rdParty.sh postgres 42_2_24 -j /opt/ssfs/postgresql-42.2.24.jar -targetJVM EVERY

Enter the om-runtime container by running the following command:
```
docker exec -it om-runtime /bin/bash
```
Verify PostgreSQL DB_JAR entries are added to the APPDynamicclasspath.cfg.in and AGENTDynamicclasspath.cfg.in files.

Create system_overrides.properties with the following properties.

jdbcService.postgresqlPool.dbvendor=postgresql
jdbcService.postgresqlPool.systemPool=true
si_config.DB_SCHEMA_OWNER=omdb
jdbcService.postgresqlPool.datasource=jdbc/OMDS

Note: <PostgreSQL_Container_IP> is the IP address of the PostgreSQL container. Run the following command to identify the PostgreSQL container IP:

docker inspect postgres --format '{{ .NetworkSettings.Networks.docker_default.IPAddress }}

Edit sandbox.cfg.
1. Replace DB2=TRUE with POSTGRESQL=TRUE.
2. Change the DB_POOL and UI_DB_POOL properties to postgresqlPool.
3. Replace DB2 property values with PostgresSQL values from system_overrides.properties in step #8.
```
DB_USER 
UI_DB_POOL 
DB_HOST 
DB_SCHEMA_OWNER 
DB_DATASOURCE 
DB_POOL 
DB_VENDOR 
DB_DATA 
JDBC_VENDOR 
YANTRA_DB_PORT 
YANTRA_DB_PORT 
YANTRA_DB_PASS 
DBINIT_DIR 
DB_DRIVERS 
YANTRA_DB_HOST 
YANTRA_DB_DATA 
DB_PORT 
DB_PASS
```
4. Disable the following DB2 specific properties from sandbox.cfg:
```
DB2
DB2_USER
DB2_PASS
DB2_DATA
```
Run setup files from <INSTALL_DIR>/bin by running the following command:
```
./setupfiles.sh
```
Create database entities and run db verify from <INSTALL_DIR>/bin by running the following command:
```
./dbverify.sh
```
Run the entity deployer from <INSTALL_DIR>/bin to activate the changes, by running the following command:
```
./deployer.sh -t entitydeployer
```
Run load custom db from <INSTALL_DIR>/bin to activate customizations, by running the following command:
```
./loadCustomDB.sh
```
Add the factory setup data from <INSTALL_DIR>/bin to the newly created tables, by running the following command:
```
./loadFactoryDefaults.sh
```
Run the following command to reset the password of admin user as password:
```
om-compose.sh resetpsw
```
Update extensions from the compose folder to restart and reflect the applied changes in the application server, by running the following command:
```
om-compose.sh update-extn
```