Prerequisites: The person following these steps should have good knowledge of the windows command line interface and must log in with administrator privileges.
1. Open up PowerShell with administrator privileges
2. Backup existing ers data
Move to eRS directory
cd $env:ProgramFiles\eRS\
Load path variable
$env:Path = [System.Environment]::GetEnvironmentVariable("Path","Machine") + ";" + [System.Environment]::GetEnvironmentVariable("Path","User") + ";$env:ProgramFiles\PostgreSQL\11\bin;$env:ProgramFiles\PostgreSQL\11\lib"
Backup ers database
pg_dump --username ers -Fc --no-owner --no-privileges --file "$env:TEMP\ers_backup.bak"
** Enter ers user password when prompted.
3. Install Latest PostgreSQL (skip if already installed)
Download the PostgreSQL-17 installation package and install it:
$Installer = "PostgresInstaller.exe"; (new-object System.Net.WebClient).DownloadFile('https://storage.googleapis.com/app-file-store/self-hosted/files/postgresql-17.2-3-windows-x64.exe', "$env:TEMP\$Installer"); & Start-Process "$env:TEMP\$Installer";
4. Restore eRS database
Load path variable
$env:Path = [System.Environment]::GetEnvironmentVariable("Path","Machine") + ";" + [System.Environment]::GetEnvironmentVariable("Path","User") + ";$env:ProgramFiles\PostgreSQL\17\bin;$env:ProgramFiles\PostgreSQL\17\lib"
Create Database Role: Create a role for user 'ers' with a password of your choice:
createuser -U postgres -P -I -d ers
** The command will prompt you to create a new password for the ers role and confirm it. Afterward, it will request the password for the postgres user that you set during the PostgreSQL installation process.
Create Database: Create an empty database named "ers"
createdb -U ers -O ers ers
** Use the ers password you created in the previous step.
Restore: Now restore using the backup file created in step 2.
pg_restore --username ers --no-owner --no-privileges --dbname ers "$env:TEMP\ers_backup.bak"
** Use the ers password you created in the previous step.
Verify Password: Open the configuration file and ensure you are using the correct ers password.
$env:ProgramFiles\eRS\standalone\configuration\ers-win-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>
....