Using opentaps ERP + CRM with PostgreSQL

This guide shows how to set up opentaps ERP + CRM with PostgreSQL 8.0.4 as the backend relational database. Beginning with version 8.0, PostgreSQL can be run on both Windows and Unix/Linux operating systems. The instructions should apply to both. Earlier versions of PostgreSQL can be used on Unix/Linux operating systems only but are still compatible with opentaps ERP and can be set up in a similar way.

Installing PostgreSQL

Install PostgreSQL using the binary or RPM packages from the PostgreSQL download site or mirrors. In Windows, PostgreSQL should be installed as a service, run with a Windows user "postgres." PostgreSQL will also create a PostgreSQL user called "postgres" which can be used to set up databases and users. Write down the password you chose for this user.

Creating the PostgreSQL Databases using Command Line Tools

Once PostgreSQL is installed, you can create the PostgreSQL databases for opentaps ERP + CRM using command line tools:

$ su postgres
$ createuser -W opentaps
$ createdb opentaps

Then you will need to edit your pg_hba.conf file to allow the "opentaps" user to access the "opentaps" database by adding the following line:

host opentaps opentaps 127.0.0.1 255.255.255.255 password

If you have the following line in your pg_hba.conf, you also need to comment it out or remove it:

host all all 127.0.0.1/32 trust

Creating the PostgreSQL Databases using pgAdmin III

pgAdmin III is a graphical administration tool for PostgreSQL. The Windows version of PostgreSQL comes installed with pgAdmin III. It can also be installed separately for Unix/Linux systems.

To set up databases and users with pgAdmin III, start pgAdmin III. Then, create a new server with address "localhost", port 5432, database "template1", username "postgres", and the password you created for the PostgreSQL (not operating system) "postgres" user before.

Next, right click on this server and select "New User" and create a new user with username "opentaps", a password for the opentaps user, and "Can Create DB" turned on.

Next, right click again and select "New Database" and create a database called "opentaps" whose owner is the user "opentaps"

You can set up pgAdmin III to view this database by creating another server with address "localhost", port 5432, database "opentaps", and the password you just created for the "opentaps" user. To test it, close both servers and then try to open the one for the "opentaps" database. You will be prompted for the password. If it is correct, then you are able to access your opentaps ERP + CRM database from pgAdmin III.

Verifying the Databases

Go to the command line or Windows Terminal and try accessing your database:

$ psql -h 127.0.0.1 -p 5432 -U opentaps -W opentaps

(From Windows, you may have to be in the directory "C:\PostgreSQL\8.0\bin\" to run this command.)

If you can get into your database with your new "opentaps" user account's password, then your database has been set up successfully.

Setting Up opentaps ERP + CRM with PostgreSQL

For your convenience, a JDBC driver for PostgreSQL is included with your download in the framework/entity/lib/jdbc directory. The included driver should be compatible with current versions of PostgreSQL and work for most users. You may, however, want to verify that this is the best version to use at the PostgreSQL JDBC site. If that is not the case, download the right one and put it in the framework/entity/lib/jdbc directory.

Go to your opentaps/ directory and make these changes to a file called framework/entity/config/entityengine.xml:

1. Change the "datasource-name" attribute under the <delegator name="default"...> from the default "localderby" to "localpostgres". (Note older versions of opentaps may have a default of "localhsql" here.) Do not change "localderbyodbc".
2. Look for <datasource name="localpostgres"...> further down.
3. Change the "jdbc-uri" under "localpostgres" to "jdbc:postgresql://127.0.0.1/opentaps"
4. Change the "jdbc-username" to "opentaps"
5. Change the "jdbc-password" to the password you chose above

If you are using opentaps 1.5M2 or later, you will also need to configure your PostgreSQL database as the tenant. See opentaps 1.5 entity engine changes.

Now stop opentaps ERP + CRM. You will need to reload the seed data into the new PostgreSQL database by typing in this command from the opentaps ERP + CRM directory

$ ant run-install

Make sure you read the General Installation instructions first.

Once it is done, you can start and use opentaps ERP + CRM again using the instructions from General Installation. It should now be running with your new PostgreSQL database.

To install just the seed data and not the demo data, use

$ ant run-install-seed

instead.