Installation of the Postgres Database on WindowsOpenEMPI 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.Under Ubuntu you You can download the software using the apt utility. In the example below we assume that you will be using the 9.1 version of Postgres although, depending on which version of Ubuntu you are deploying OpenEMPI, the exact version of Postgres available may vary. We have been using various versions of Postgres over the years and we haven't had any issues with any of the versions we have tried. If you run into any issues or experience any problems, please let us know through the forums. | Code Block |
|---|
$ sudo apt-get update
$ sudo apt-get install postgresql postgresql-client pgadmin3 |
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. We must first create an initial password for the postgres user that is created when the Postgres software is installed . Then we will use the postgres account that was created during the installation to create the one 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. | Code Block |
|---|
$ sudo -u postgres psql postgres
postgres=# \password postgres
Enter new password: ******
Enter it again: ******
postgres=# CREATE ROLE openempi LOGIN PASSWORD 'openempi'
SUPERUSER
VALID UNTIL 'infinity';
postgres=# CREATE DATABASE openempi
WITH ENCODING='UTF8'
OWNER=openempi
CONNECTION LIMIT=-1;
postgres=# \q. 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.  Image Added
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.  Image Added
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.  Image Added
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. | Code Block |
|---|
$ cd $OPENEMPI_HOME/conf
$ psql --username=openempi --host=localhost openempi
postgres=# \i 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. | Code Block |
|---|
create_new_database_schema-3.0.0.sql
postgres=# \i
create_person_entity_model_definition.sql
postgres=# \i
create_person_reference_tables.sql
postgres=# \q |
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. | Code Block |
|---|
$ psql --username=openempi --host=localhost openempi
postgres=# \i update_database_schema-3.1.0.sql
update_database_schema-3.x2.y0.sql
postgres=# \q... |
There may be some warnings but you can ignore them. This completes the installation steps for the database server. |