Prepare Environment

Preflight tasks ..

Prepare Environment

This section will guide you through the preflight tasks:

• create a 'installation user' with sudo privileges.

• install supported version of OpenJDK 11

• set PENTAHO_JAVA_HOME

• install certified version of Postgres 15

• create 'superadmin' user

• install pgAdmin4

The training environment is: OS Zorin 17.3 Pro which is based on Ubuntu 22.04 LTS

1. Creating a Pentaho User

Pentaho Installation Account

Add an account that is assigned administrative privileges by performing the following steps. We'll be using this account to complete the deployment.

This section is for Reference only.

For production environments its best practice to create a specific 'installation user' account with the required role / permissions.

1. Run update & upgrade (optional).

sudo apt update -y && sudo apt upgrade -y

1. Add new user to system.

sudo adduser pentaho

1. Set password.

New password: password
Retype new password: password
passwd: password updated successfully

Follow the prompts to set the new user’s information. It is fine to accept the defaults to leave 
all this information blank:

1. Add new user to sudo group.

 sudo usermod -aG sudo pentaho

1. Test access.

su - pentaho

sudo ls -la /root

2. Open JDK & JRE 17

Java

Check the components reference ..!!

Pentaho 10.2 is certified on: Oracle OpenJDK & JRE 11 & 17

Welcome to the Hitachi Vantara Documentation Portal

Pentaho 10.2 Components Reference

1. Run update & upgrade.

sudo apt update -y && sudo apt upgrade -y

1. Check whether Java is already installed in our system.

java -version

Java versions

1. Install openjdk 11.

sudo apt install openjdk-17-jdk && sudo apt install openjdk-17-jre-headless

1. Check Java version.

java -version

openjdk 17.0.12 2024-07-16
OpenJDK Runtime Environment (build 17.0.12+7-Ubuntu-1ubuntu222.04)
OpenJDK 64-Bit Server VM (build 17.0.12+7-Ubuntu-1ubuntu222.04, mixed mode, sharing)

1. Tidy up.

sudo apt autoremove

---

Set Java Version

You can have multiple Java installations on one server. You can configure which version is the default for use on the command line by using the update-alternatives command.

1. Run the following command to set the preferred Java version.

sudo update-alternatives --config java

There is only one alternative in link group java (providing /usr/bin/java): /usr/lib/jvm/java-17-openjdk-amd64/bin/java
Nothing to configure.

3. PENTAHO_JAVA_HOME

Set PENTAHO_JAVA_HOME

Perform the following steps to set the PENTAHO_JAVA_HOME environment variable. This will ensure that if there are multiple versions of Java the correct version is associated with PENTAHO_JAVA_HOME.

1. Run update & upgrade.

sudo apt update -y && sudo apt upgrade -y

1. Verify java version.

java -version

1. Determine path to OpenJDK.

update-alternatives --list java

---

Set PENTAHO_JAVA_HOME - Global

As Pentaho is being installed under root to /opt/ then set PENTAHO_JAVA_HOME for all users.

1. Edit /etc/environment.

sudo nano /etc/environment

1. Add the following.

# set PENTAHO_JAVA_HOME
PENTAHO_JAVA_HOME=/usr/lib/jvm/java-17-openjdk-amd64

1. Save.

CTRL + o
Enter
CTRL + x

1. Check path.

echo $PENTAHO_JAVA_HOME

1. Restart the server.

reboot

---

Set PENTAHO_JAVA_HOME - Local

1. Set the path to OpenJDK.

sudo nano .bashrc

1. Add the following to the bottom.

# set PENTAHO_JAVA_HOME
export PENTAHO_JAVA_HOME=/usr/lib/jvm/java-17-openjdk-amd64

1. Save.

CTRL + o
Enter
CTRL + x

1. Reload .bashrc.

. ~/.bashrc

1. Check path.

echo $PENTAHO_JAVA_HOME

1. Restart the server.

reboot

4. PostgreSQL 14 & 15

Pentaho Repository

The following databases are supported types as your Pentaho Repository 10.2.

Certified	Supported
PostgreSQL 15	PostgreSQL 14 & 15
MySQL 8.026	MySQL 8.026
Oracle 23c	Oracle 19c & 23c (including patched versions)
MS SQL Server 2019	Microsoft SQL Server 2017 & 2019 (including patched versions)
Maria DB 11.1.2	Maria DB 11.1.2

4.1 Supported Version

Supported Pentaho Database - PostgreSQL

Ensure you have a 'clean' PostgeSQL environment to avoid any potential conflicts - usually port.

1. Run update & upgrade.

sudo apt update -y && sudo apt upgrade -y

1. Check Postgresql version.

apt show postgresql -a

Package: postgresql
Version: 14+238
Priority: optional
Section: database
Source: postgresql-common (238)
Origin: Ubuntu

---

If PostgreSQL is installed, check its a supported version:

PDI v10.2 == Postgres 14 & 15

1. If not purge current Postgresql instance.

Execute each command separately ..

sudo apt autoremove to tidy up ..

sudo apt-get --purge remove postgresql
sudo apt-get purge postgresql*
sudo apt-get --purge remove postgresql postgresql-doc postgresql-common

1. Check for packages.

dpkg -l |grep postgres;PostgreSQL

4.2 Install PostgreSQL 14 & 15

Install PostgreSQL 14

PostgreSQL 14 is a default in the Unbuntu 22.04 apt repository.

1. Update Repository list.

sudo apt update -y && sudo apt upgrade -y

1. Install PostgreSQL 14.

sudo apt-get -y install postgresql-14 

Reading package lists... Done
Building dependency tree... Done
Reading state information... Done
The following additional packages will be installed:
  libcommon-sense-perl libjson-perl libjson-xs-perl libllvm14 libpq5
  libtypes-serialiser-perl postgresql-client-14 postgresql-client-common
  postgresql-common sysstat
Suggested packages:
  postgresql-doc-14 isag
The following NEW packages will be installed
  libcommon-sense-perl libjson-perl libjson-xs-perl libllvm14 libpq5
  libtypes-serialiser-perl postgresql-14 postgresql-client-14
  postgresql-client-common postgresql-common sysstat
0 to upgrade, 11 to newly install, 0 to remove and 0 not to upgrade.
Need to get 42.4 MB of archives.
...

1. Check the PostgreSQL version.

psql --version

psql (PostgreSQL) 14.12 (Ubuntu 14.12-0ubuntu0.22.04.1)

---

PostgreSQL 15 - Add APT Repository

If you decide to go the PostgreSQL 15, the package is not available in the default package repository, so needs to be added to the official package repository using following commands.

1. Add APT Repository.

sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'

1. Import GPG key.

wget -qO- https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo tee /etc/apt/trusted.gpg.d/pgdg.asc &>/dev/null

1. Update Repository list.

sudo apt update -y && sudo apt upgrade -y

1. Reboot server.

reboot

---

Install PostgreSQL 15

Once package repository is updated, then ..

1. Install postgresql 15.

sudo apt install postgresql-client-15 postgresql-15

Reading package lists... Done
Building dependency tree... Done
Reading state information... Done
The following additional packages will be installed:
  libcommon-sense-perl libjson-perl libjson-xs-perl libpq5
  libtypes-serialiser-perl postgresql-client-common postgresql-common sysstat
Suggested packages:
  postgresql-doc-15 isag
The following NEW packages will be installed
  libcommon-sense-perl libjson-perl libjson-xs-perl libpq5
  libtypes-serialiser-perl postgresql-15 postgresql-client-15
  postgresql-client-common postgresql-common sysstat
0 to upgrade, 10 to newly install, 0 to remove and 0 not to upgrade.
Need to get 20.0 MB of archives.
After this operation, 66.6 MB of additional disk space will be used.
Do you want to continue? [Y/n] y
...

1. Check postgresql 15 status.

sudo systemctl status postgresql

pentaho@pentaho:~$ sudo systemctl status postgresql
● postgresql.service - PostgreSQL RDBMS
     Loaded: loaded (/lib/systemd/system/postgresql.service; enabled; vendor pr>
     Active: active (exited) since Fri 2024-08-16 00:38:15 BST; 2min 27s ago
   Main PID: 5687 (code=exited, status=0/SUCCESS)
        CPU: 1ms

Aug 16 00:38:15 pentaho systemd[1]: Starting PostgreSQL RDBMS...
Aug 16 00:38:15 pentaho systemd[1]: Finished PostgreSQL RDBMS.

sudo service postgresql stop // Stop the service
sudo service postgresql start // Start the service
sudo service postgresql restart // Stop and restart the service
sudo service postgresql reload // Reload the configuration without 
stopping the service

1. Check the PostgreSQL version.

psql --version

1. Tidy up installation.

sudo apt autoremove

4.3 Superuser

Grant privileges to a SuperUser

During installation, a 'postgres' user is created automatically. This user has full superadmin access to your entire PostgreSQL instance. Before you switch to this account, your logged in system user should have sudo privileges.

1. Log in as 'postgres' user.

sudo su -l postgres

1. Change password.

psql -c "ALTER USER postgres WITH PASSWORD 'Welcome123'";

1. Exit

CTRL + d

---

Pentaho Superuser

Grant access & privileges to Pentaho superuser.

1. Switch to 'postgres' user.

sudo -u postgres psql

1. Create 'pentaho' user.

CREATE USER pentaho WITH PASSWORD 'Welcome123';

1. Upgrade 'pentaho' to superuser.

ALTER USER pentaho WITH SUPERUSER;

1. Exit.

CTRL + d

1. Switch to 'pentaho' user:

 sudo -u pentaho psql postgres

1. Check connection details.

\conninfo

postgres=# \conninfo
You are connected to database "postgres" as user "pentaho" via socket in "/var/run/postgresql" at port "5432".

1. Exit.

CTRL + d

---

Allow Remote Connections

For reference only as connecting to Postgresql via localhost

By default, PostgreSQL accepts connections from the localhost only. However, we can easily modify the configuration to allow connection from remote clients.

PostgreSQL reads its configuration from the postgresql.conf file which is located: /etc/postgresql/<version>/main/ directory

1. Edit the postgresql.conf file.

cd
cd /etc/postgresql/15/main/
sudo nano postgresql.conf

1. Uncomment the line that starts with the listen_addresses, and replace ‘localhost’ with ‘*’.

Alternatively, you can specify a specific IP address or a range of IP addresses that are allowed to connect to the server.

1. Modify the pg_hba.conf file.

sudo nano /etc/postgresql/15/main/pg_hba.conf

1. Locate the following section and modify

# IPv4 local connections: 
host    all             all             127.0.0.1/32         md5 

# IPv4 local connections:
host    all             all             0.0.0.0/0            md5 

1. Allow port 5432 through the firewall.

sudo ufw allow 5432/tcp

1. Restart Postgres.

sudo service postgresql restart

4.4 pgAdmin

pgAdmin4 Desktop

The pgadmin4 package contains both:

• pgadmin4-desktop – Provides desktop application for Ubuntu system.

• pgadmin4-web – Provides the web interface accessible in a web browser

1. Update Repository list.

sudo apt update -y && sudo apt upgrade -y

1. Install Public Key.

curl -fsS https://www.pgadmin.org/static/packages_pgadmin_org.pub | sudo gpg --dearmor -o /usr/share/keyrings/packages-pgadmin-org.gpg

1. Create Repository config file.

sudo sh -c 'echo "deb [signed-by=/usr/share/keyrings/packages-pgadmin-org.gpg] https://ftp.postgresql.org/pub/pgadmin/pgadmin4/apt/$(lsb_release -cs) pgadmin4 main" > /etc/apt/sources.list.d/pgadmin4.list && apt update'

1. Install pgAdmin4 for both desktop - see below for Web version.

sudo apt install pgadmin4-desktop

1. Check contents APT Repository.

cat /etc/apt/sources.list.d/pgadmin4.list

---

Pentaho Server Group

In pgAdmin, a server group is a way to organize and categorize your PostgreSQL server connections. It's essentially a logical grouping mechanism that helps you manage multiple database server connections more efficiently.

1. Click on: 'Add New Server'.

create server group

1. Enter: Pentaho for server name.

server group

1. Click on 'Connection'.

Connection details

Welcome123

Don't save the password in Production environments.

1. Save.

pgAdmin4 UI