Prerequisites: The person following these steps should have good knowledge of Linux shell scripting and must log in with sudo privileges.
1. Backup existing ers data
Change user context to ers
sudo -su ers
cd
Dump ers database
pg_dump -x -O -Fc ers > /path/to/bakup/ers.bak
2. Install Latest PostgreSQL (skip if already installed)
Aquire root privileges
sudo su
cd
Install required utilities to add PostgreSQL repository
apt install -y wget gnupg2
Add the PostgreSQL APT Repository: PostgreSQL provides an official APT repository for Debian-based distributions. First, import the repository signing key:
wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -
Then, add the repository to your system's source list:
sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'
Update Package Lists: Refresh your package lists to include the new repository:
apt update
Stop the Older PostgreSQL Service: In order to use the default port for new PostgreSQL service, stop the current one.
systemctl stop postgresql
Install PostgreSQL 17: Now, install PostgreSQL 17:
apt install -y postgresql-17 postgresql-client-17
Start and Enable PostgreSQL Service: Ensure that PostgreSQL starts and is enabled to start on boot:
systemctl start postgresql
systemctl enable postgresql
3. Restore eRS database
Create Database Role: Create a role for user 'ers' with a password of your choice:
sudo -i -u postgres psql -c "CREATE ROLE ers WITH LOGIN PASSWORD 'ers_user_password' NOINHERIT CREATEDB"
Create Database: Create an empty database named "ers"
sudo -i -u postgres psql -c "CREATE DATABASE ers OWNER ers;"
Restore: Now restore using the backup file created in step 1.
pg_restore -x -O -d ers /path/to/backup/ers.bak
Verify Password: Open the configuration file and ensure you are using the correct ers password.
vi /opt/ers/standalone/configuration/ers-config.xml
Find datasource directive and configure database properties. Verify the connection-url and set the ers user password that you created earlier.
....
<datasource jta="false" jndi-name="java:/postgresql/ers" pool-name="PostgresDS" enabled="true" use-ccm="false" statistics-enabled="true">
<connection-url>jdbc:postgresql://localhost:5432/ers</connection-url>
<driver-class>org.postgresql.Driver</driver-class>
<driver>postgresql</driver>
<pool>
<max-pool-size>10</max-pool-size>
</pool>
<security>
<user-name>ers</user-name>
<password>ers_user_password</password>
</security>
<validation>
....