ADR-035: Database Restore Architecture
Status
Accepted
Date
2025-12-08
Context
Development and staging environments need to be refreshed with production data periodically. The existing restore-prod-to-dev.sh bash script handles this but has limitations:
- Manual execution - requires SSH access to run the script
- Hardcoded paths - backup directory, database names are hardcoded
- No S3 integration - requires manual download of backups
- No dry-run mode - cannot preview what will happen
- No progress feedback - long-running operations show no status
Security Constraint
Critical: The CLI NEVER connects directly to production RDS/database. All restore operations:
- Download backups from S3 (where production backups are stored)
- Restore to local or development PostgreSQL instances only
- Production database is read-only via backup files
Decision
Implement env restore command with:
- S3-first approach - Download backups from S3 buckets
- Local file support - Also support local backup files
- Docker-first (recommended) - Use Docker PostgreSQL containers for isolation
- Version mapping - iDempiere version to PostgreSQL version mapping
- Development alterations - Auto-apply dev-safe modifications
- Dry-run mode - Preview restore plan before execution
PostgreSQL Version Mapping
| iDempiere Version | PostgreSQL Version | Notes |
|---|---|---|
| 13 (development) | 16 | Latest PostgreSQL |
| 12 | 15 | Default |
| 11 | 14 | Stable LTS |
| 10 | 13 | Legacy |
Architecture
Data Flow Diagram
AWS Cloud
+----------------------------------------+
| |
| +-------------+ +-------------+ |
| | Production | | S3 | |
| | RDS |--->| Bucket | |
| | (pg_dump) | | (backups) | |
| +-------------+ +------+------+ |
| ^ | |
| | | |
| Scheduled | |
| Backup Job | |
| | |
+----------------------------------------+
|
| HTTPS (AWS SDK)
|
+-----------------------------v----------+
| Developer Machine |
| |
| +----------------------------------+ |
| | idempiere-cli | |
| | | |
| | +------------+ +------------+ | |
| | | S3 | | Env | | |
| | | Backup |->| Restore | | |
| | | Service | | Service | | |
| | +------------+ +-----+------+ | |
| | | | |
| +------------------------|---------+ |
| | |
| | |
| +------------ OR ------------+ |
| | | |
| v v |
| +--------------+ +-------------+|
| | Docker | | OS-level ||
| | (recommended)| | (legacy) ||
| +--------------+ +-------------+|
| | | |
| v v |
| +--------------+ +-------------+|
| | PostgreSQL | | PostgreSQL ||
| | Container | | on Host ||
| | (isolated) | | (shared) ||
| +--------------+ +-------------+|
+------------------------------------------+
Docker Mode Architecture (Recommended)
+------------------------------------------------------------------+
| Developer Machine |
+------------------------------------------------------------------+
| |
| +--------------------+ |
| | idempiere-cli | |
| +--------------------+ |
| | |
| v |
| +--------------------+ +----------------------------------+ |
| | DockerPostgres | | Docker Engine | |
| | Service |---->| | |
| +--------------------+ | +----------------------------+ | |
| | | idempiere-cli-pg-{db_name} | | |
| Commands: | | | | |
| - docker run | | +------------------------+ | | |
| - docker cp | | | PostgreSQL {version} | | | |
| - docker exec | | | - uuid-ossp | | | |
| | | | - pgcrypto | | | |
| | | | - pg_trgm | | | |
| | | | - vector | | | |
| | | +------------------------+ | | |
| | | | | |
| | | Port: {docker-port}:5432 | | |
| | +----------------------------+ | |
| +----------------------------------+ |
+------------------------------------------------------------------+
Docker Container Lifecycle
env restore --docker --target dev_db
|
v
+-------------------+
| Check Docker |
| available |
+--------+----------+
|
v
+-------------------+
| Remove existing |
| container (if any)|
| docker rm -f |
+--------+----------+
|
v
+-------------------+
| Create container |
| docker run -d |
| --name ... |
| -p {port}:5432 |
| -e POSTGRES_* |
| postgres:{version}|
+--------+----------+
|
v
+-------------------+
| Wait for ready |
| pg_isready |
| (30 attempts) |
+--------+----------+
|
v
+-------------------+
| Copy backup file |
| docker cp |
| backup.gz:/tmp/ |
+--------+----------+
|
v
+-------------------+
| Restore database |
| docker exec |
| gunzip | psql |
+--------+----------+
|
v
+-------------------+
| Apply dev |
| alterations |
| docker exec psql |
+-------------------+
Restore Flow
+-------------------+
| env restore |
| --s3-bucket X |
| --target dev_db |
+--------+----------+
|
v
+--------+----------+
| Resolve Backup |
| Source |
| |
| Priority: |
| 1. --file |
| 2. --backup-dir |
| 3. --s3-bucket |
+--------+----------+
|
v
+--------+----------+ +-------------------+
| S3BackupService |---->| AWS S3 |
| | | (download backup) |
| - getLatestBackup | +-------------------+
| - download |
+--------+----------+
|
v (local .gz file)
+--------+----------+
| EnvRestoreService |
+--------+----------+
|
+---> 1. Create Roles
| (adempiere, pg1x33, clde_appserver_user)
|
+---> 2. Terminate Connections
| (pg_terminate_backend)
|
+---> 3. Drop & Create DB
| (dropdb, createdb)
|
+---> 4. Create Extensions
| (uuid-ossp, pgcrypto, pg_trgm, vector)
|
+---> 5. Restore Backup
| (gunzip | psql)
|
+---> 6. Apply Dev Alterations
(disable email, schedulers)
Component Responsibilities
+------------------------------------------------------------------+
| EnvCommand.RestoreCommand |
|------------------------------------------------------------------|
| - Parse CLI options (--s3-bucket, --file, --target, etc.) |
| - Validate inputs |
| - Prompt for missing passwords |
| - Coordinate services |
| - Display progress and results |
+------------------------------------------------------------------+
|
+--------------------+--------------------+
| |
v v
+------------------+ +---------------------+
| S3BackupService | | EnvRestoreService |
|------------------| |---------------------|
| - isAvailable() | | - restore() |
| - download() | | - createRoles() |
| - getLatestBackup| | - terminateConns() |
| - listBackups() | | - dropCreateDB() |
+------------------+ | - createExtensions()|
| | - restoreFromBackup |
v | - applyDevAlter() |
+------------------+ +---------------------+
| AWS S3 Client | |
| (Quarkus ext.) | v
+------------------+ +---------------------+
| PostgreSQL (psql) |
| - Local instance |
| - Never production! |
+---------------------+
AWS Configuration
Credential Chain (Priority Order)
1. Environment Variables
AWS_ACCESS_KEY_ID
AWS_SECRET_ACCESS_KEY
AWS_REGION
|
v (if not set)
2. AWS Credentials File
~/.aws/credentials
~/.aws/config
|
v (if not set)
3. application.properties
quarkus.s3.aws.credentials.type=static
quarkus.s3.aws.credentials.static-provider.access-key-id=XXX
Configuration Properties
# Region (required)
quarkus.s3.aws.region=${AWS_REGION:us-east-1}
# Credentials type: default, static, or profile
quarkus.s3.aws.credentials.type=default
# Disable LocalStack devservices
quarkus.s3.devservices.enabled=false
Usage Examples
# Restore latest backup from S3
idempiere-cli env restore --s3-bucket clde-backup --target dev_db
# Restore specific backup from S3
idempiere-cli env restore --s3-bucket clde-backup \
--s3-key clde_prod_db_dump_20251205.gz --target dev_db
# Restore from local file
idempiere-cli env restore --file /backups/dump.gz --target dev_db
# Dry run (preview only)
idempiere-cli env restore --s3-bucket clde-backup --target dev_db --dry-run
# Skip dev alterations (for staging)
idempiere-cli env restore --s3-bucket clde-backup --target staging \
--no-dev-alterations
Development Alterations
When --no-dev-alterations is NOT specified, the following SQL is applied:
-- Disable email sending
UPDATE ad_sysconfig SET value = 'N'
WHERE name = 'EMAIL_SEND' OR name = 'MAIL_SEND_CREDENTIALS';
-- Set development stage indicator
UPDATE ad_sysconfig SET value = 'DEV'
WHERE name = 'SYSTEM_STAGE' OR name LIKE '%STAGE%';
-- Disable schedulers
UPDATE ad_scheduler SET isactive = 'N';
-- Disable workflow email actions
UPDATE ad_wf_node SET isactive = 'N' WHERE action = 'M';
Consequences
Positive
- Safe by design - Never connects to production database
- Audit trail - S3 access logs show who downloaded what
- Reproducible - Same command works across team members
- Configurable - AWS credentials via standard mechanisms
- Preview mode - Dry-run prevents accidents
Negative
- Requires AWS credentials - Team needs S3 access configured
- Local PostgreSQL required - Must have psql installed
- Large downloads - Production backups can be several GB
Risks
| Risk | Mitigation |
|---|---|
| Accidental production restore | CLI only connects to local PostgreSQL |
| Credential exposure | Use environment variables, not config files |
| Large backup download time | Progress feedback, resume support (future) |
Related
Files
src/main/java/org/idempiere/cli/commands/EnvCommand.java- RestoreCommandsrc/main/java/org/idempiere/cli/services/EnvRestoreService.java- Restore logicsrc/main/java/org/idempiere/cli/services/S3BackupService.java- S3 operationssrc/main/resources/application.properties- AWS S3 configuration