IDempiere/Importing DB Seed Manually

⇐ Table of Contents | Setting up Eclipse | Importing DB Seed Manually | Running iDempiere within Eclipse ⇒

Please note this procedure is intended for technical people wanting to configure iDempiere to run in Eclipse, there is an easier mechanism on the installers to create the database.

Let's check the steps to create an idempiere postgres database:

Create adempiere user

sudo su - postgres
psql -U postgres -c "CREATE ROLE adempiere SUPERUSER LOGIN PASSWORD 'adempiere'"
logout

WARNING: Of course this is an insecure password, it is recommended that you set up a proper password for this user account

Create the idempiere database

createdb  --template=template0 -E UNICODE -O adempiere -U adempiere idempiere
psql -d idempiere -U adempiere -c "ALTER ROLE adempiere SET search_path TO adempiere, pg_catalog"

When asked the password please provide the adempiere password that you set up in the previous step.

NOTE: It is possible that you will confront problems related to pg_hba.conf configuration, it's beyond of the scope of this tutorial to solve those problems at this moment, please check and configure proper permissions. Also, this tutorial is intended to work on a postgres installed locally, if your postgres is elsewhere you must use corresponding -h (host) and -p (port) parameters for createdb and psql.

Installing UUID (required to apply some migration scripts)

Some migration scripts references the function generate_uuid, unfortunately the way to install this is different on different postgres versions, so we were not able to include it by default on the DB seed, next are the instructions per version:

For postgresql versions 8.X, 9.0

• Firstly you must install the postgresql-contrib package on your ubuntu
  • sudo apt-get install postgresql-contrib
• Then you must run via psql the script provided at db/ddlutils/postgresql/uuid-ossp.sql
  • psql -d idempiere -U adempiere -f db/ddlutils/postgresql/uuid-ossp.sql

For postgresql 9.1 and above

• Run the command CREATE EXTENSION "uuid-ossp" using psql
  • psql -d idempiere -U adempiere -c 'CREATE EXTENSION "uuid-ossp"'

PostgreSQL 8.4 or 9.0

This tutorial uses postgres 8.4 - please note the following instructions are applicable if you use postgresql 8.4 or 9.0, for 9.1 or higher see below.

cd /tmp
jar xvf $IDEMPIERE_REPOSITORY/org.adempiere.server-feature/data/seed/Adempiere_pg84.jar
psql -d idempiere -U adempiere -f Adempiere_pg84.dmp

You must replace $IDEMPIERE_REPOSITORY with the folder of your cloned repository.

PostgreSQL 9.1 or higher

cd /tmp
jar xvf $IDEMPIERE_REPOSITORY/org.adempiere.server-feature/data/seed/Adempiere_pg.jar
psql -d idempiere -U adempiere -f Adempiere_pg.dmp

Applying additional migration scripts

Manual way

Now, you must check if there are new migration scripts after the last applied, you can check the migration scripts this way:

ls $IDEMPIERE_REPOSITORY/migration/i1.0b-release/postgresql

And compare the list of migration scripts with the contents of your AD_MigrationScript table, execute this in postgres:

select name from ad_migrationscript order by 1

Please note that you must do this check every time you upgrade your version.

If you find there are unapplied migration scripts then you can apply them with:

psql -d idempiere -U adempiere -f $IDEMPIERE_REPOSITORY/migration/i1.0b-release/postgresql/20130425011956_ProbableFutureMigrationScript.sql

Please check carefully the output of the migration scripts, if you find errors please ask for help on the support forums

You must repeat the previous for each unapplied migration scripts, it's recommended also to take note of the last applied for future reference.

At the end you can run the post-migration processes like this:

cat $IDEMPIERE_REPOSITORY/migration/processes_post_migration/postgresql/*.sql | psql -d idempiere -U adempiere

Automated way (linux)

There are two scripts contributed that help with this task:

• syncApplied.sh for PostgreSQL
• syncApplied_ora.sh for Oracle

You can download the corresponding file, edit the variables on top to fit your configuration and then you can run periodically the script every time you update source code.