Migrating Fineract from MariaDB/MySQL to PostgreSQL

Understanding the Two-Database Architecture

Fineract uses two separate databases that must both be migrated:

Database	Purpose	Controlled by
`fineract_tenants`	Tenant store — connection metadata, tenant registry	`FINERACT_HIKARI_JDBC_URL`
`fineract_default` (and each tenant DB)	Actual financial data — loans, clients, accounting	`FINERACT_DEFAULT_TENANTDB_*` env vars

Both must be migrated before switching the application to PostgreSQL. In a multi-tenant setup, each tenant has its own database; all of them need to go through this process.

Prerequisites

PostgreSQL 15 or later (Fineract's CI uses 18.3; 15+ is required for the public schema privilege changes)
pgloader installed (pgloader.io) — recommended migration tool for live production data
Fineract 1.6 or later (earlier versions used Flyway; see the Liquibase migration guide first)
A maintenance window — the migration requires stopping the application
Full database backups

Step 1: Back Up Your Existing Databases

Always take backups before proceeding. These are your only rollback path.

bash

# MariaDB / MySQL
mysqldump -u root -p fineract_tenants > fineract_tenants_backup.sql
mysqldump -u root -p fineract_default > fineract_default_backup.sql

# Repeat for each additional tenant database
mysqldump -u root -p fineract_<tenant_name> > fineract_<tenant_name>_backup.sql

Store backups on a separate host or object storage before continuing.

Step 2: Provision and Initialise the PostgreSQL Server

Create the databases and grant the required privileges. The commands below mirror the 01-init.sh script in the Fineract Docker PostgreSQL setup:

sql

-- Connect as a superuser (e.g. postgres)
CREATE USER fineract WITH PASSWORD 'your_secure_password';

CREATE DATABASE fineract_tenants ENCODING 'UTF8' LC_COLLATE 'en_US.UTF-8' LC_CTYPE 'en_US.UTF-8';
CREATE DATABASE fineract_default ENCODING 'UTF8' LC_COLLATE 'en_US.UTF-8' LC_CTYPE 'en_US.UTF-8';

GRANT ALL PRIVILEGES ON DATABASE fineract_tenants TO fineract;
GRANT ALL PRIVILEGES ON DATABASE fineract_default TO fineract;

-- Required on PostgreSQL 15+ (public schema privileges changed in PG15)
\c fineract_tenants
GRANT ALL ON SCHEMA public TO fineract;

\c fineract_default
GRANT ALL ON SCHEMA public TO fineract;

PostgreSQL 15+ schema privilege change

PostgreSQL 15 removed the default privilege that allowed all users to create objects in the public schema. The explicit GRANT ALL ON SCHEMA public lines above are required. Without them, Liquibase cannot create tables on startup.

Step 3: Migrate the Data with pgloader

pgloader handles the data transfer, data-type conversions, and index recreation in a single pass. It converts MySQL's TINYINT columns, datetime types, and utf8mb4 charset automatically.

Tenant store migration

Create a fineract_tenants.load file:

LOAD DATABASE
     FROM   mysql://root:yourpassword@localhost/fineract_tenants
     INTO   postgresql://fineract:your_secure_password@localhost/fineract_tenants

WITH include drop, create tables, create indexes, reset sequences,
     workers = 8, concurrency = 1,
     multiple readers per thread, rows per range = 50000

SET PostgreSQL PARAMETERS
    work_mem to '128MB',
    maintenance_work_mem to '512MB'

CAST type datetime to timestamptz drop default drop not null using zero-dates-to-null,
     type date    to date         drop default drop not null using zero-dates-to-null,
     type tinyint to boolean      using tinyint-to-boolean
;

Run the migration:

bash

pgloader fineract_tenants.load

Tenant database migration

Create a fineract_default.load file (repeat for each additional tenant DB):

LOAD DATABASE
     FROM   mysql://root:yourpassword@localhost/fineract_default
     INTO   postgresql://fineract:your_secure_password@localhost/fineract_default

WITH include drop, create tables, create indexes, reset sequences,
     workers = 8, concurrency = 1,
     multiple readers per thread, rows per range = 50000

SET PostgreSQL PARAMETERS
    work_mem to '128MB',
    maintenance_work_mem to '512MB'

CAST type datetime to timestamptz drop default drop not null using zero-dates-to-null,
     type date    to date         drop default drop not null using zero-dates-to-null,
     type tinyint to boolean      using tinyint-to-boolean
;

bash

pgloader fineract_default.load

pgloader outputs a summary table on completion showing rows migrated, errors, and timing per table. Review it before proceeding — any table with errors needs investigation.

Large databases

For databases above a few hundred GB, add PREFETCH ROWS = 10000 and consider running pgloader from a host co-located with both databases to reduce network latency. pgloader supports --verbose for detailed per-table progress.

Step 4: Reset PostgreSQL Sequences

PostgreSQL uses sequences for auto-increment columns. After a bulk data import, sequences are unaware of the maximum IDs already in the tables and will try to insert from 1 again, causing primary key conflicts.

Fineract's own Liquibase changelogs reset sequences for the tenant store (0003_reset_postgresql_sequences.xml). For the tenant database, run this after pgloader completes:

sql

-- Connect to fineract_tenants and run:
SELECT SETVAL('tenant_server_connections_id_seq', COALESCE(MAX(id), 0)+1, false) FROM tenant_server_connections;
SELECT SETVAL('tenants_id_seq',                   COALESCE(MAX(id), 0)+1, false) FROM tenants;
SELECT SETVAL('timezones_id_seq',                 COALESCE(MAX(id), 0)+1, false) FROM timezones;

For fineract_default (and each tenant DB), the quickest approach is to reset all sequences automatically:

sql

-- Connect to fineract_default and run this block:
DO $$
DECLARE
    r RECORD;
BEGIN
    FOR r IN
        SELECT
            s.sequencename,
            t.tablename,
            c.attname AS colname
        FROM pg_sequences s
        JOIN information_schema.columns c
            ON c.column_default LIKE '%' || s.sequencename || '%'
        JOIN information_schema.tables t
            ON t.table_name = c.table_name
            AND t.table_schema = 'public'
        WHERE s.schemaname = 'public'
    LOOP
        EXECUTE format(
            'SELECT SETVAL(%L, COALESCE((SELECT MAX(%I) FROM %I), 0)+1, false)',
            'public.' || r.sequencename,
            r.colname,
            r.tablename
        );
    END LOOP;
END;
$$;

Step 5: Handle the Liquibase Migration History

The Liquibase DATABASECHANGELOG table was migrated from MySQL by pgloader. However, because the Liquibase context during the original MySQL setup was mysql, the changelog history contains changesets that were conditionally applied for MySQL — and some PostgreSQL-only changesets that were not applied (because they were gated with context="postgresql").

The safest approach is to let Fineract's built-in Liquibase startup handle this. Fineract uses the initial_switch mechanism to bootstrap a PostgreSQL database that has the schema but no Liquibase history:

Option A — Trust Liquibase's initial switch (recommended for most migrations):

Drop the migrated Liquibase tables and let Fineract rebuild them against the existing schema:

sql

-- On fineract_tenants:
DROP TABLE IF EXISTS DATABASECHANGELOG;
DROP TABLE IF EXISTS DATABASECHANGELOGLOCK;

-- On fineract_default:
DROP TABLE IF EXISTS DATABASECHANGELOG;
DROP TABLE IF EXISTS DATABASECHANGELOGLOCK;

When Fineract starts with context=postgresql, it will detect the absence of DATABASECHANGELOG, recognise the existing schema via the initial_switch changelog, mark all initial changesets as applied, and proceed to apply any pending changesets from the current changelog.

Option B — Keep the history (advanced):

If you want to preserve the exact changeset history, you can keep the migrated DATABASECHANGELOG table but manually update the CONTEXTS column on rows that should now be marked as applied for PostgreSQL. This is error-prone and only recommended if you have deep Liquibase experience.

Step 6: Update Environment Variables

Stop the running Fineract instance and update the configuration to point at PostgreSQL:

Variable	Old value (MySQL example)	New value (PostgreSQL)
`FINERACT_HIKARI_DRIVER_SOURCE_CLASS_NAME`	`com.mysql.cj.jdbc.Driver` or `org.mariadb.jdbc.Driver`	`org.postgresql.Driver`
`FINERACT_HIKARI_JDBC_URL`	`jdbc:mysql://db:3306/fineract_tenants`	`jdbc:postgresql://db:5432/fineract_tenants`
`FINERACT_HIKARI_USERNAME`	`root`	`fineract` (or your PG user)
`FINERACT_HIKARI_PASSWORD`	(mysql password)	(postgresql password)
`FINERACT_DEFAULT_TENANTDB_PORT`	`3306`	`5432`
`FINERACT_DEFAULT_TENANTDB_UID`	`root`	`fineract`
`FINERACT_DEFAULT_TENANTDB_PWD`	(mysql password)	(postgresql password)
`SPRING_LIQUIBASE_CONTEXTS`	`mysql`	`postgresql`
`SPRING_DATASOURCE_PLATFORM`	`mysql`	`postgresql`

For Docker Compose, replace your DB service definition:

yaml

# Remove:
# mysql or mariadb service definition

# Add:
  postgresql:
    image: postgres:18.3
    environment:
      POSTGRES_USER: fineract
      POSTGRES_PASSWORD: your_secure_password
      POSTGRES_DB: fineract_tenants
    volumes:
      - pgdata:/var/lib/postgresql/data

volumes:
  pgdata:

Step 7: Start Fineract and Verify

Start Fineract against PostgreSQL. Watch the startup logs for Liquibase activity:

bash

docker logs -f fineract

Expected log sequence:

INFO  TenantDatabaseUpgradeService - Upgrading tenant store DB at db:5432
INFO  LiquibaseChangeLog - Running Liquibase against postgresql context
INFO  TenantDatabaseUpgradeService - Tenant store upgrade finished
INFO  TenantDatabaseUpgradeService - Upgrade for tenant default has finished

Verification checks

1. No failed changesets:

sql

-- PostgreSQL
SELECT COUNT(*) FROM databasechangelog WHERE exectype = 'FAILED';
-- Must return 0

2. Lock is released:

sql

SELECT locked FROM databasechangeloglock WHERE id = 1;
-- Must return false

3. Health endpoint is up:

bash

curl -k https://localhost:8443/fineract-provider/actuator/health
# Expected: {"status":"UP"}

4. Tenant API responds correctly:

bash

curl -k -X GET \
  https://localhost:8443/fineract-provider/api/v1/loanproducts \
  -H "Fineract-Platform-TenantId: default" \
  -H "Authorization: Basic $(echo -n 'mifos:password' | base64)"

5. No sequence errors on first write: Create a test client or loan product through the API. A primary key conflict at this point means the sequence reset in Step 4 was incomplete.

Known Differences to Be Aware Of

`TINYINT` columns become `BOOLEAN`

pgloader's tinyint-to-boolean cast (used above) converts TINYINT columns to PostgreSQL BOOLEAN. This is the correct mapping, but some native SQL queries in custom integrations or Pentaho reports that check = 1 or = 0 instead of = TRUE / = FALSE will break on PostgreSQL. Review any custom native SQL after migration.

If you prefer to keep them as SMALLINT (safer for compatibility with existing custom SQL), remove the tinyint to boolean line from the pgloader config — but note that Fineract's JPA layer expects BOOLEAN on PostgreSQL in newer versions.

Pentaho report SQL (FINERACT-2436)

The built-in Pentaho reports use SQL written for MySQL/MariaDB — backtick identifiers, IFNULL(), CONCAT(), and other MySQL-specific functions. These reports will not work on PostgreSQL without rewriting. See JIRA FINERACT-2436. This is a known open issue in the community.

`CONCAT()` in custom native queries

PostgreSQL uses the || operator for string concatenation. CONCAT() is valid on PostgreSQL 9.1+ and generally works, but edge cases around NULL handling differ: in PostgreSQL NULL || 'string' returns NULL, while MySQL's CONCAT() with NULL returns NULL too. Only CONCAT_WS() differs significantly.

`serverTimezone` JDBC parameter

MySQL/MariaDB JDBC URLs often include ?serverTimezone=UTC&.... This parameter is not recognised by the PostgreSQL JDBC driver. Remove it from any connection strings. Instead, ensure your PostgreSQL server is configured with timezone = 'UTC' in postgresql.conf.

Character set and collation

The ALTER DATABASE CHARACTER SET utf8mb4 changeset is MySQL/MariaDB-only and is silently skipped on PostgreSQL. Your PostgreSQL databases should be created with ENCODING='UTF8' (as shown in Step 2), which is the equivalent setting.

Rollback Procedure

The only reliable rollback is restoring from the backups taken in Step 1.

bash

# Restore MariaDB / MySQL
mysql -u root -p fineract_tenants < fineract_tenants_backup.sql
mysql -u root -p fineract_default < fineract_default_backup.sql

Then revert the environment variables to the MySQL/MariaDB values and restart Fineract.

WARNING

Do not attempt to run Liquibase rollback for this migration. Most Fineract changesets do not define rollback instructions, and a partial rollback will leave the schema in an inconsistent state.

Business Events

Migrating Fineract from MariaDB/MySQL to PostgreSQL

Understanding the Two-Database Architecture

Prerequisites

Step 1: Back Up Your Existing Databases

Step 2: Provision and Initialise the PostgreSQL Server

Step 3: Migrate the Data with pgloader

Tenant store migration

Tenant database migration

Step 4: Reset PostgreSQL Sequences

Step 5: Handle the Liquibase Migration History

Step 6: Update Environment Variables

Step 7: Start Fineract and Verify

Verification checks

Known Differences to Be Aware Of

`TINYINT` columns become `BOOLEAN`

Pentaho report SQL (FINERACT-2436)

`CONCAT()` in custom native queries

`serverTimezone` JDBC parameter

Character set and collation

Rollback Procedure

Further Reading

Migrating Fineract from MariaDB/MySQL to PostgreSQL ​

Understanding the Two-Database Architecture ​

Prerequisites ​

Step 1: Back Up Your Existing Databases ​

Step 2: Provision and Initialise the PostgreSQL Server ​

Step 3: Migrate the Data with pgloader ​

Tenant store migration ​

Tenant database migration ​

Step 4: Reset PostgreSQL Sequences ​

Step 5: Handle the Liquibase Migration History ​

Step 6: Update Environment Variables ​

Step 7: Start Fineract and Verify ​

Verification checks ​

Known Differences to Be Aware Of ​

TINYINT columns become BOOLEAN ​

Pentaho report SQL (FINERACT-2436) ​

CONCAT() in custom native queries ​

serverTimezone JDBC parameter ​

Character set and collation ​

Rollback Procedure ​

Further Reading ​

Migrating Fineract from MariaDB/MySQL to PostgreSQL

Understanding the Two-Database Architecture

Prerequisites

Step 1: Back Up Your Existing Databases

Step 2: Provision and Initialise the PostgreSQL Server

Step 3: Migrate the Data with pgloader

Tenant store migration

Tenant database migration

Step 4: Reset PostgreSQL Sequences

Step 5: Handle the Liquibase Migration History

Step 6: Update Environment Variables

Step 7: Start Fineract and Verify

Verification checks

Known Differences to Be Aware Of

`TINYINT` columns become `BOOLEAN`

Pentaho report SQL (FINERACT-2436)

`CONCAT()` in custom native queries

`serverTimezone` JDBC parameter

Character set and collation

Rollback Procedure

Further Reading