Skip to main content

​
Overview

iDempiere supports both PostgreSQL and Oracle databases. This guide covers installation, configuration, and optimization for both database systems.

​
Supported Databases

  • PostgreSQL: Recommended open-source option (version 12+)
  • Oracle: Enterprise option (version 11g+, including Oracle XE)
PostgreSQL is the recommended database for most installations due to its open-source nature, excellent performance, and lower cost.

​
PostgreSQL Setup

​
Installation

1

Install PostgreSQL

Ubuntu/Debian:
sudo apt update
sudo apt install postgresql postgresql-contrib
RHEL/CentOS:
sudo dnf install postgresql-server postgresql-contrib
sudo postgresql-setup --initdb
sudo systemctl start postgresql
sudo systemctl enable postgresql
2

Create database user

Switch to postgres user and create the iDempiere database user:
sudo -u postgres psql
Then execute:
CREATE USER adempiere WITH CREATEDB CREATEUSER PASSWORD 'adempiere';
3

Create database

CREATE DATABASE idempiere
  WITH ENCODING='UTF8'
       OWNER=adempiere;
Exit psql:
\q

​
PostgreSQL Configuration

​
Configure Authentication

Edit pg_hba.conf to allow iDempiere connections:
/etc/postgresql/14/main/pg_hba.conf
# Allow local connections
local   all             adempiere                               md5
host    all             adempiere       127.0.0.1/32            md5
host    all             adempiere       ::1/128                 md5

# Allow remote connections (if needed)
host    idempiere       adempiere       0.0.0.0/0               md5
Allowing remote connections from all IPs (0.0.0.0/0) is a security risk. Use specific IP ranges in production.

​
Optimize PostgreSQL Settings

Edit postgresql.conf for better performance:
/etc/postgresql/14/main/postgresql.conf
# Connection settings
max_connections = 200
shared_buffers = 256MB

# Memory settings
effective_cache_size = 1GB
maintenance_work_mem = 128MB
work_mem = 8MB

# Write-ahead log
wal_buffers = 16MB
max_wal_size = 2GB
min_wal_size = 1GB

# Query planning
random_page_cost = 1.1
effective_io_concurrency = 200

# Logging
log_min_duration_statement = 1000
log_line_prefix = '%t [%p]: [%l-1] user=%u,db=%d,app=%a,client=%h '
log_checkpoints = on
log_connections = on
log_disconnections = on
log_lock_waits = on
Restart PostgreSQL: 
sudo systemctl restart postgresql

​
PostgreSQL Database Creation Script

iDempiere provides scripts for database initialization. The CreateUser.sql script:
CreateUser.sql (PostgreSQL)
/*
 * Drop and re-create database user
 * Parameters: UserID UserPwd
 * Run as postgres superuser
 */
DROP DATABASE IF EXISTS idempiere;
DROP USER IF EXISTS adempiere;

CREATE USER adempiere WITH CREATEDB CREATEUSER PASSWORD 'adempiere';

CREATE DATABASE idempiere
  WITH ENCODING='UNICODE'
       OWNER=adempiere;

​
Oracle Setup

​
Installation Prerequisites

1

Install Oracle Database

Follow Oracle’s installation guide for your platform. Oracle XE (Express Edition) is suitable for smaller installations.
2

Set environment variables

export ORACLE_HOME=/u01/app/oracle/product/19.0.0/dbhome_1
export ORACLE_SID=idempiere
export PATH=$ORACLE_HOME/bin:$PATH

​
Create Oracle User

iDempiere provides a user creation script:
CreateUser.sql (Oracle)
/*
 * Drop and re-create Oracle user
 * Parameters: UserID UserPwd
 * Run as SYSTEM user
 */
DROP USER adempiere CASCADE
/

CREATE USER adempiere IDENTIFIED BY adempiere
    DEFAULT TABLESPACE USERS
    TEMPORARY TABLESPACE TEMP
    PROFILE DEFAULT
    ACCOUNT UNLOCK
/

GRANT CONNECT TO adempiere
/

GRANT DBA TO adempiere
/

GRANT RESOURCE TO adempiere
/

GRANT UNLIMITED TABLESPACE TO adempiere
/

ALTER USER adempiere DEFAULT ROLE CONNECT, RESOURCE, DBA
/

GRANT CREATE TABLE TO adempiere
/

​
Run the Creation Script

sqlplus system/password @CreateUser.sql adempiere adempiere

​
Oracle Configuration

​
Set Oracle V8Compatible

iDempiere requires Oracle V8 compatible mode for date/time handling: 
export ORACLE_V8_COMPATIBLE=true
Or set in Java system properties: 
-Doracle.jdbc.V8Compatible=true

​
Connection String Format

For Oracle, the connection string uses TNS format or service name: 
# Using service name
ADEMPIERE_DB_SERVER=localhost
ADEMPIERE_DB_PORT=1521
ADEMPIERE_DB_NAME=idempiere

# Or using TNS
ADEMPIERE_DB_URL=jdbc:oracle:thin:@//localhost:1521/idempiere

​
Database Functions

iDempiere requires custom database functions for both PostgreSQL and Oracle.

​
PostgreSQL Functions

Functions are stored in db/postgresql/functions/. Key functions include:
  • C_Currency_Convert: Currency conversion
  • BOM_Qty_OnHand: Bill of materials quantity calculations
  • C_Invoice_Open: Calculate open invoice amounts
  • Acct_Balance: Account balance calculations
These are automatically installed during initial setup.

​
Oracle Functions

Functions are stored in db/oracle/functions/. Oracle-specific functions include:
  • C_Currency_Convert: Currency conversion
  • Generate_UUID: UUID generation
  • DBA_DisplayType: Display type helpers
These are automatically installed during initial setup.

​
Database Initialization

​
Import Initial Data

1

Navigate to iDempiere directory

cd $IDEMPIERE_HOME/utils
2

Source environment

source ./myEnvironment.sh
3

Run import script

PostgreSQL:
./RUN_ImportIdempiere.sh
Oracle:
./RUN_ImportIdempiere.sh
The import script will:
  • Create database schema
  • Install database functions
  • Import seed data
  • Create default client (GardenWorld demo)
Initial import can take 30-60 minutes depending on hardware. Do not interrupt the process.

​
Database Maintenance

​
PostgreSQL Maintenance

​
Vacuum and Analyze

Regularly vacuum the database: 
# As postgres user
vacuumdb -z -d idempiere -U adempiere
Configure autovacuum in postgresql.conf: 
autovacuum = on
autovacuum_max_workers = 3
autovacuum_naptime = 1min

​
Reindex

Periodically reindex for optimal performance: 
reindexdb -d idempiere -U adempiere

​
Oracle Maintenance

​
Gather Statistics

EXEC DBMS_STATS.GATHER_SCHEMA_STATS('ADEMPIERE');

​
Rebuild Indexes

SELECT 'ALTER INDEX ' || index_name || ' REBUILD;'
FROM user_indexes
WHERE status = 'UNUSABLE';

​
Database Synchronization

Keep database schema in sync with migration scripts: 
cd $IDEMPIERE_HOME
./RUN_SyncDB.sh
Or with a specific properties file: 
./RUN_SyncDB.sh /path/to/idempiere.properties
The sync process:
  1. Checks AD_MigrationScript table for applied scripts
  2. Identifies pending migration files
  3. Applies scripts in chronological order
  4. Runs post-migration processes
  5. Updates migration tracking

​
Connection Pooling

iDempiere uses HikariCP for connection pooling. The implementation is in:
  • org.compiere.db.DB_PostgreSQL (PostgreSQL driver)
  • org.compiere.db.DB_Oracle (Oracle driver)

​
PostgreSQL Driver Configuration

// Default port
public static final int DEFAULT_PORT = 5432;

// Driver class
public static final String DRIVER = "org.postgresql.Driver";

​
Oracle Driver Configuration

// Default port
public static final int DEFAULT_PORT = 1521;

// Driver class
public static final String DRIVER = "oracle.jdbc.OracleDriver";

​
Troubleshooting

​
Connection Refused

PostgreSQL: 
# Check if PostgreSQL is running
sudo systemctl status postgresql

# Check listening ports
sudo netstat -plnt | grep postgres

# Review logs
sudo tail -f /var/log/postgresql/postgresql-14-main.log
Oracle: 
# Check listener status
lsnrctl status

# Start listener if needed
lsnrctl start

​
Authentication Failures

Verify credentials and pg_hba.conf (PostgreSQL) or tnsnames.ora (Oracle).

​
Performance Issues

  1. Check connection pool settings
  2. Review slow query logs
  3. Analyze query execution plans
  4. Update database statistics

​
Migration Script Errors

If migration fails:
  1. Check SyncDB_out_* files in /tmp
  2. Fix errors manually
  3. Run sync again
Never manually mark migration scripts as applied without actually running them. This will cause schema inconsistencies.

​
See Also

Build docs developers (and LLMs) love

Get started for freeTalk to us