Installation of the Postgres Database (Windows Edition)

Owned by Odysseas Pentakalos
Last updated: Jun 24, 2016
4 min read

Installation of the Postgres Database on Windows

OpenEMPI is tested and supported to run under the Postgres database although it has been successfully deployed on other database servers. In this section we describe the installation of the Postgres database. We describe this briefly mainly because the Postgres Database site has detailed instructions for installing the database server under different environments.

You can download the Windows installer for Postgres from the download site. This graphical installer for PostgreSQL includes the PostgreSQL server, pgAdmin III; a graphical tool for managing and developing your databases, and StackBuilder; a package manager that can be used to download and install additional PostgreSQL applications and drivers. The installer is straightforward to use. You simply need to select the installation location for the executables and data files, choose a password for the postgres user and select a port for the server. OpenEMPI does not require any additional Postgres components so you can skip the launching of the StackBuilder component of the installer.

The next step is to create a role that will be used by the OpenEMPI software for connecting to the database. We use the account name openempi although any account name will work. . Then we will use the postgres account that was created during the installation to create the account for the OpenEMPI user. For the sake of simplicity we use the password postgres for the postgres user and openempi for the openempi user. You must change this for production deployment and use strong passwords instead of these trivial ones. We will accomplish the next few tasks using the pgAdmin tool. When you start the pgAdmin tool the first time, there will be an entry for connecting to the local server on the control on the left side. Click on the entry with the red 'X' to the left of it and you will be asked to enter the password for the psotgres user that you choose during the installation.

Once you are connected to the database, right click on the Login Roles entry on the left hand side and select the New Login Role entry. Use 'openempi' as the new login role name and under the Role privileges tab, check the Superuser option.

Now right click on the Databases entry and select the New Database option. Use ;openempi' as the name of the database and select 'openempi' as the owner of the database. If the openempi role does not show up under the Owner drop down list, go back to the previous step and make sure that the 'openempi' role exists.

After creating a database account for the OpenEMPI application and creating a database schema the next step is to actually create the database objects that comprise the OpenEMPI application. For every major release, there is a create script that creates the schema for that release. Then for each minor release there may be a update script that upgrades the database from the prior minor release to the next one. The database scripts reside under the configuration directory of the distribution.

Right click on the openempi database entry under the Databases tree node on the left side and select the Create Script option. Select each of the following scripts in the sequence shown below and execute them.

 

create_new_database_schema-3.0.0.sql
create_person_entity_model_definition.sql
create_person_reference_tables.sql

The first step creates the database schema that is used to support the OpenEMPI installation and the second step creates the definition of the person entity that is needed to get the system up and running. As you become more familiar with OpenEMPI, you can customize that entity definition or drop it altogether and create another entity.

If there are any update scripts that are available as part of the release to match the minor release number, then you can install those at this time as well. Note that there may be more than one update scripts so make sure you execute them in the order indicated by their minor version numbering.

update_database_schema-3.1.0.sql
update_database_schema-3.2.0.sql
...

There may be some warnings but you can ignore them. This completes the installation steps for the database server.