Skip to main content

Migrate eRS database to a new version of PostgreSQL (17) on Linux Based Systems

  • Updated

 

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>
....