Using OpenMRS with PostgreSQL

disturbedoctopusData Management

Nov 27, 2012 (4 years and 8 months ago)


Using OpenMRS with PostgreSQL

Daniel Futerman
October 1,2008
1 Porting OpenMRS to PostgreSQL
To allowOpenMRS to run using a PostgreSQL database,the following lines need to be added/changed in the file:
connection.username = <postgres user name>
connection.password = <postgres user password>
connection.url = jdbc:postgresql://localhost:<postgres port num e.g.5432>/openmrs
connection.driver_class = org.postgresql.Driver
The PostgreSQL JDBC driver
is also needed and should be added to the lib folder of OpenMRS.
2 Installation Options
Please note that both installation options require the use of Administrator Privileges,and there should be no existing
installations of PostgreSQL on the PC.
2.1 Postgres Silent Installer
The PostgreSQL installer was developed to provide a simple installation procedure for PostgreSQL,with minimal
user interaction and a way to run PostgreSQL without installation as a Windows service.The installer is packaged as
a compressed zip file,and should be unzipped before attempting to install.
To install,run the install.bat script,which automates the installation and initialization of PostgreSQL and the
pgAdmin database management system.The application is installed to the default directory C:\Postgres_OMRS,
which can be changed in the ROOT_DIR field in install.bat.The installation first checks whether an existing
installation occurs at ROOT_DIR,and if none exist,installs the PostgreSQL application.
Once the installation of PostgreSQL has completed,the database cluster is initialised,which uses the psexec
tool to
revoke Administrator privileges and run the initdb procedure.The database server is then started,and a newuser
is created,at which point the user must specify a password for the postgres user.
The next step of installation is the creation of the OpenMRS database.The OpenMRS database dump file,
omrs_dump.sql is used to populate the database.This file can be changed for another dump file,and can be re-
named in the install script under the DB_DUMP_FILE field.Certain functions in PostgreSQL are not avaliable or
behave differently to those in MySQL,and the compatibility script compat.sql is run to overcome these problems.
In the final step of installation,start and stop scripts are copied to ROOT_DIR.
2.2 Bitnami WAPP Stack
The Bitnami
WAPP Stack provides a simple installation package that includes Apache Web Server 2.2.8,PostgreSQL
8.3.3,PHP 5.2.6 and phpPgAdmin 4.2.To run OpenMRS,Apache Tomcat is still required,however the WAPP Stack
provides a simple,minimal installation of PostgreSQLandcan be usedas an alternative to the Postgres Silent Installer.

For Windows users only.
Similarly to the silent installer,the WAPP Stack does not install PostgreSQL as a Windows service,but rather provides
scripts to start and stop the service.Once the WAPP Stack has been installed,it may be useful to install the pgAdmin
tool for PostgreSQL.This can be added by running the PostgreSQL installer postgresql-8.3-int located in the
postgresql folder in the WAPPStack installation directory.The choice to modify the installation is chosen,and
fromthere one has the option to install pgAdmin to the local hard drive.Once installed,pgAdmin can be accessed
fromthe\postgresql\bin directory.
Starting and stopping the PostgreSQL server can be done via the command line interface.Once in the
\postgresql\scripts\directory,simply run the servicerun script with the paramater option START or STOP.
For example:
C:\Program Files\Bitnami WAPPStack\postgresql\scripts> servicerun START
The first step to running OpenMRS with PostgreSQL is to create the openmrs database.This can be done from
the command line or from pgAdmin.Database creation is done through the createdb application,located in the
\postgresql\bin directory.Creating the database,using the postgres user,is done as follows:
C:\Program Files\Bitnami WAPPStack\postgresql\bin> createdb -U postgres openmrs
Note that the database server must be running for this to take effect.
The next step is to populate the database and run the compatibility script against the database.To do this via the
command line,we use the psql application.
To populate the database,using the dump file omrs_dump.sql:
C:\Program Files\Bitnami WAPPStack\postgresql\bin> psql -U postgres openmrs <"%DIR%\omrs_dump.sql"
Note that %DIR% refers to the directory that the the sql file is situated,e.g.C:\myscripts\openmrs\.To run the
compatability script compat.sql:
C:\Program Files\Bitnami WAPPStack\postgresql\bin> psql -U postgres openmrs <"%DIR%\compat.sql"
Again,%DIR% refers to the directory that the the sql file is situated.
The PostgreSQL database is nowready to use.
Note that since PostgreSQL is not run as a service,when the systemis (re)booted the database server must be manu-
ally started again.