Skip to main content

Documentation Index

Fetch the complete documentation index at: https://mintlify.com/idempiere/idempiere/llms.txt

Use this file to discover all available pages before exploring further.

​
Overview

iDempiere uses a systematic migration approach to evolve the database schema. Migration scripts are SQL files organized by version, tracked in the AD_MigrationScript table, and applied automatically by the synchronization tools.

​
Migration Structure

iDempiere migrations are organized in version-specific directories: 
migration/
├── i4.1/              # Version 4.1
│   ├── oracle/
│   └── postgresql/
├── i5.1/              # Version 5.1
│   ├── oracle/
│   └── postgresql/
├── i6.1/              # Version 6.1
│   ├── oracle/
│   └── postgresql/
├── i7.1/              # Version 7.1
├── i8.1/
├── i9/
├── iD10/              # Version 10 ("D" prefix for decimal)
├── iD11/              # Version 11
├── iD12/              # Version 12
├── iD13/              # Version 13 (current)
├── processes_post_migration/
└── local_sql/
iDempiere has over 2,000 migration scripts tracking every schema change since version 4.1.

​
Migration Script Format

Migration scripts follow a naming convention: 
YYYYMMDDHHMM_IDEMPIERE-NNNN.sql
  • YYYYMMDD: Date (year, month, day)
  • HHMM: Time (hour, minute)
  • IDEMPIERE-NNNN: Ticket or issue number
Examples:
  • 202302021805_IDEMPIERE-5564.sql
  • 202301191424_IDEMPIERE-5346.sql
  • 201605101800_System_Ticket_1006008.sql

​
Migration Tracking

​
AD_MigrationScript Table

Applied migrations are tracked in the database: 
CREATE TABLE AD_MigrationScript (
    AD_MigrationScript_ID NUMBER(10) NOT NULL,
    Name VARCHAR2(255) NOT NULL,
    FileName VARCHAR2(255),
    ReleaseNo VARCHAR2(20),
    Status VARCHAR2(2),
    Script CLOB,
    ...
);
Each applied script is recorded with:
  • Script name/filename
  • Application timestamp
  • Release version
  • Status (Applied/Failed)
  • Script content

​
Running Migrations

​
Automatic Synchronization

The primary method to apply migrations is using the SyncDB script.
1

Navigate to iDempiere directory

cd $IDEMPIERE_HOME
2

Run synchronization

./RUN_SyncDB.sh
Or with custom properties file:
./RUN_SyncDB.sh /path/to/idempiere.properties
3

Review output

Check console output and log files in /tmp/SyncDB_out_*

​
Manual Synchronization

For direct database synchronization: PostgreSQL: 
cd $IDEMPIERE_HOME/org.adempiere.server-feature/utils.unix/postgresql
./SyncDB.sh adempiere adempiere postgresql
Oracle: 
cd $IDEMPIERE_HOME/org.adempiere.server-feature/utils.unix/oracle
./SyncDB.sh adempiere adempiere oracle

​
How Migration Sync Works

The SyncDB.sh script performs the following:
1

Load environment

Sets database connection parameters from environment variables or properties file
2

Query applied scripts

SELECT name FROM ad_migrationscript
Creates list of already-applied scripts in /tmp/lisDB_$$.txt
3

Scan migration files

find . -type f -wholename "*/postgresql/*.sql" \
  ! -wholename "./processes_post_migration/postgresql/*" \
  | sed -e 's:.*/::' | sort > /tmp/lisFS_$$.txt
4

Identify pending scripts

comm -13 /tmp/lisDB_$$.txt /tmp/lisFS_$$.txt > /tmp/lisPENDING_$$.txt
Creates list of scripts not yet applied
5

Apply scripts in order

Executes each pending script in chronological order:
psql -h $ADEMPIERE_DB_SERVER -p $ADEMPIERE_DB_PORT \
  -d $ADEMPIERE_DB_NAME -U $ADEMPIERE_DB_USER < script.sql
6

Run post-migration processes

Executes scripts in processes_post_migration/ directory after all migrations
7

Log results

Saves output to /tmp/SyncDB_out_*/ directory

​
Error Detection

The script detects errors using pattern matching: 
ERROR_STRINGS="^(ERROR:|FEHLER:|FATAL:|ERRO:)"

if grep -E "$ERROR_STRINGS" "$OUTFILE" > /dev/null 2>&1
then
    MSGERROR="ERROR ON FILE $OUTFILE"
    break  # Stop processing
fi
If a migration script fails, the sync process stops immediately. Fix the error before running additional migrations.

​
Migration Best Practices

​
Creating Migration Scripts

Use timestamp-based naming for proper ordering
Test scripts on both PostgreSQL and Oracle
Include rollback instructions in comments
Keep scripts idempotent when possible
Document purpose and ticket reference

​
Script Template

-- Migration script: IDEMPIERE-NNNN
-- Description: Add new column to C_Invoice
-- Date: 2024-03-04
-- Ticket: https://idempiere.atlassian.net/browse/IDEMPIERE-NNNN

-- Add column
ALTER TABLE C_Invoice ADD COLUMN IsApproved CHAR(1) DEFAULT 'N';

-- Update metadata
INSERT INTO AD_Column (
    AD_Column_ID, AD_Table_ID, ColumnName, Name,
    AD_Reference_ID, FieldLength, DefaultValue,
    IsKey, IsParent, IsMandatory, IsUpdateable, IsActive,
    Created, CreatedBy, Updated, UpdatedBy
) VALUES (
    get_uuid(), get_table_id('C_Invoice'), 'IsApproved', 'Approved',
    20, 1, 'N',
    'N', 'N', 'Y', 'Y', 'Y',
    now(), 100, now(), 100
);

-- Register migration
SELECT register_migration_script('202403041200_IDEMPIERE-NNNN.sql');

​
Custom Migrations

​
Local SQL Scripts

Place custom migrations in migration/local_sql/: 
migration/local_sql/
├── postgresql/
│   └── custom_001_add_custom_table.sql
└── oracle/
    └── custom_001_add_custom_table.sql
These are applied like standard migrations but don’t conflict with upstream updates.

​
Post-Migration Processes

Scripts in processes_post_migration/ run after all migrations: 
migration/processes_post_migration/
├── postgresql/
│   ├── fix_sequences.sql
│   └── update_statistics.sql
└── oracle/
    ├── fix_sequences.sql
    └── update_statistics.sql
Use for:
  • Sequence adjustments
  • Cache synchronization
  • Statistics updates
  • Data corrections

​
Version Upgrades

​
Major Version Upgrade Process

1

Backup database

Always backup before upgrading:
./RUN_DBExport.sh
2

Stop iDempiere server

./idempiere.sh stop
3

Update iDempiere binaries

Extract new version to installation directory
4

Run migration sync

./RUN_SyncDB.sh
This applies all pending migrations from intermediate versions
5

Verify migration

# Check for errors in output
ls -la /tmp/SyncDB_out_*/
grep -i error /tmp/SyncDB_out_*/*.out
6

Start iDempiere

./idempiere.sh start
7

Test application

Verify critical functions work correctly

​
Migration from iDempiere 11 to 12

Example upgrade path: 
# Migrations applied:
migration/iD11/postgresql/*.sql  # All pending from v11
migration/iD12/postgresql/*.sql  # All scripts for v12

​
Monitoring Migrations

​
Query Migration Status

-- Count applied migrations
SELECT COUNT(*) FROM AD_MigrationScript WHERE Status = 'Applied';

-- Recent migrations
SELECT Name, Created, ReleaseNo
FROM AD_MigrationScript
ORDER BY Created DESC
LIMIT 10;

-- Failed migrations
SELECT Name, Status, ErrorMsg
FROM AD_MigrationScript
WHERE Status = 'Failed';

​
Verify Migration

Check if specific migration was applied: 
SELECT * FROM AD_MigrationScript
WHERE Name LIKE '%IDEMPIERE-5564%';

​
Rollback Strategies

​
Manual Rollback

iDempiere does not provide automatic rollback. Rollbacks must be performed manually.
1

Restore from backup

The safest approach is restoring from pre-migration backup:
./RUN_DBRestore.sh
2

Manual SQL rollback

If backup restore isn’t feasible, craft reverse SQL:
-- If migration added column
ALTER TABLE C_Invoice DROP COLUMN IsApproved;

-- Remove from metadata
DELETE FROM AD_Column WHERE ColumnName = 'IsApproved';

-- Remove migration record
DELETE FROM AD_MigrationScript
WHERE Name = '202403041200_IDEMPIERE-NNNN.sql';
3

Test thoroughly

Verify system stability after rollback

​
Troubleshooting

​
Migration Script Fails

  1. Check error output: 
    cat /tmp/SyncDB_out_*/202403041200_IDEMPIERE-NNNN.out
  2. Common issues:
    • Syntax errors (check database-specific syntax)
    • Missing dependencies (earlier migration not applied)
    • Duplicate objects (migration already partially applied)
    • Permission issues (database user lacks privileges)
  3. Fix and retry:
    • Correct the SQL script
    • Remove failed entry from AD_MigrationScript
    • Run sync again

​
Migrations Out of Sync

If migration tracking is incorrect: 
-- Remove incorrectly registered migration
DELETE FROM AD_MigrationScript
WHERE Name = 'problem_script.sql';

-- Force re-apply
-- Run SyncDB again

​
Missing Migrations

If migrations directory is incomplete:
  1. Download complete migration set from repository
  2. Place in correct version directory
  3. Run SyncDB

​
Development Workflow

​
Creating Development Migrations

1

Make schema changes in application

Use iDempiere’s dictionary to modify schema
2

Export migration

System automatically creates migration XML in 2Pack format
3

Convert to SQL

Use migration tools to convert 2Pack to SQL:
./RUN_ExportReference.sh
4

Test on clean database

Verify SQL script works on fresh installation
5

Commit to repository

Add script to appropriate migration directory

​
Advanced Topics

​
Migration Dependencies

Some migrations depend on others: 
-- Script 001: Create table
CREATE TABLE Z_Custom_Table (...);

-- Script 002: Add column (depends on 001)
ALTER TABLE Z_Custom_Table ADD COLUMN NewField VARCHAR(60);
Timestamp-based naming ensures correct order.

​
Multi-Tenant Migrations

Migrations apply to all tenants:
  • System-level changes affect all clients
  • Client-specific data can be handled with WHERE clauses
  • Use AD_Client_ID filtering when needed

​
Performance Considerations

Test migrations on production-sized datasets
Use batching for large data updates
Create indexes after bulk inserts, not before
Consider maintenance windows for long-running migrations

​
See Also