PostgreSQL Migration #28

Type: Infrastructure / Database Migration
Priority: Critical
Parent: PostgreSQL & Vault Migration
Depends on: Phase 1

Objective

Replace Oracle persistence infrastructure with PostgreSQL across every applicable PayGuard business service without weakening domain correctness or data integrity.

Tasks

Build and Configuration

• Replace Oracle JDBC dependencies with PostgreSQL JDBC.
• Replace Oracle-specific Flyway dependencies.
• Update datasource configurations and JDBC URLs.
• Configure appropriate Hibernate/JPA settings.
• Configure the PostgreSQL dialect for existing jOOQ usage.
• Preserve existing persistence approaches unless changes are technically required.
• Update shared dependency management.
• Update Docker Compose and database container definitions.
• Update health checks and startup dependencies.
• Update CI configuration.

Database Design

• Maintain separate database ownership per business service.
• Preserve Identity's internal module ownership boundaries.
• Create least-privilege runtime database roles.
• Separate schema migration privileges from runtime privileges.
• Validate connection, schema, table, sequence, and default privileges.
• Configure TLS and hostname verification for non-disposable environments.

SQL and Flyway

• Convert Oracle-specific SQL to PostgreSQL-compatible SQL.
• Review RAW, VARCHAR2, NUMBER, UUID, boolean, and timestamp mappings.
• Review functions, defaults, generated IDs, sequences, constraints, and indexes.
• Convert PL/SQL logic where needed.
• Preserve case-sensitive prefixed identifiers.
• Preserve audit relationships and foreign keys.
• Preserve exact monetary representations.
• Create and validate PostgreSQL Flyway migration history.
• Keep historical Oracle migrations separate when necessary.
• Verify ORM mappings against the real PostgreSQL schema.

Existing Data

• Define a repeatable migration strategy for populated databases.
• Preserve historical identifiers without regeneration.
• Resolve legacy identifier mappings explicitly.
• Validate row counts, foreign keys, and representative records.
• Reconcile financial records where applicable.
• Define rollback limitations and recovery procedures.
• Rehearse migration using representative fixtures.

Acceptance Criteria

• Every applicable service starts with PostgreSQL.
• Database migrations execute successfully.
• Runtime roles have only the required permissions.
• Domain identifiers, timestamps, relationships, and amounts remain correct.
• No unapproved destructive operation is required.
• Remaining Oracle references are documented and justified.

Definition of Done

The PostgreSQL migration is implemented, supported by relevant tests, and ready for concurrency and Vault integration verification.