How to handle the required PostgreSQL upgrade from 11.5 to 14.3 for Clarity 16.0.3
Article ID: 249341
Updated On:
Products
Issue/Introduction
You are planning to upgrade Clarity to 16.0.3. With the requirements, you will also need to also upgrade your PostgreSQL from 11.5 to 14.3.
Environment
Release : 16.0.3
Component : Clarity Install / Upgrade
Cause
With Clarity 16.0.3, an upgrade of Postgres is also required
Resolution
These steps are provided as an example and as a high level summary. Please plan your upgrade to consider the needed hardware, software and infrastructure.
You may perform these steps AFTER the Clarity upgrade
Backup database on Postgres 11
As the postgres user:
bash-4.2$ /usr/pgsql-11/bin/pg_dump clarity --file=clarity.bak --format=t
Copy the "clarity.back" to the postgres 14 server.
Install postgres 14 on the new server
sudo yum -y install https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm
sudo yum repolist -y
sudo yum update -y
sudo reboot now
sudo yum install -y postgresql14-server postgresql14
sudo rpm -qi postgresql14-server postgresql14
sudo yum install -y postgresql-contrib
As an alternative to using yum, you can use rpm to install the postgres packages
on the Clarity 16.0.3 3rd party ISO download:
postgresql14-14.3-1PGDG.rhel7.x86_64.rpm
postgresql14-contrib-14.3-1PGDG.rhel7.x86_64.rpm
postgresql14-libs-14.3-1PGDG.rhel7.x86_64.rpm
postgresql14-server-14.3-1PGDG.rhel7.x86_64.rpm
sudo /usr/pgsql-14/bin/postgresql-14-setup initdb
sudo systemctl enable --now postgresql-14
sudo systemctl status postgresql-14
sudo su - postgres
and as the postgres user:
psql
postgres=# alter user postgres with password 'PostgresStrongPassword#';
\q
The final step you need to perform to set up the database is to change the Host authentication from ident to md 5.
This enables password-based authentication from the Clarity server to the PostgreSQL database.
To update the authentication, navigate to the /var/lib/pgsql/14/data/pg_hba.conf file and change ident parameter to md5.
Original Value
host all all 127.0.0.1/32 ident
Updated Value
host all all 127.0.0.1/32 md5
Add one more line at the bottom of the file to enable remote connections.
host all all 0.0.0.0/0 md5
If you have installed Clarity and PostgreSQL on the same server, please update the following line in the pg_hba.conf file.
Before
host all all ::1/128 ident
After
host all all ::1/128 md5
Modify the /var/lib/pgsql/11/data/postgresql.conf file and change the listen_addresses = 'localhost' to listen_addresses = '*'.
For example:
Restore database from the Postgres 11 server on to the Postgres 14 server
On the Postgres 14 server
Bash
su - postgres
mkdir /var/lib/pgsql/14/tablespaces/yourtablespacename
psql
--in this example, my database name is my user is 'clarity' and my database name is 'clarity'
create role clarity password 'niku' login nosuperuser nocreatedb nocreaterole connection limit -1;
create tablespace clarity_data owner claritylocation '/var/lib/pgsql/14/tablespaces/customername';
create database clarity owner clarity tablespace clarity_data connection limit -1;
\q
psql -d clarity
|--> create schema clarity authorization clarity;
pg_restore -F t /var/lib/pgsql/14/backups/clarity.bak -U postgres -d clarity --role clarity --no-owner --verbose --no-tablespaces --schema "clarity"
Repeat the steps above for the data warehouse
Update the NSA in clarity for the Database and Data Warehouse database information.
Update the jaspersoft Data Sources, from the clarity\bin\ folder:
./admin update jasperParameters
Restart all Clarity Services:
/opt/clarity/bin/service stop start all
Create the loopback link needed for stored procedures
/opt/clarity/bin/admin db create-db-link
Additional Information
Feedback
