# PostgreSQL Migration Options

Pipeline build artifacts include the version number (from the latest release in `CHANGELOG`). Filenames follow:

- **Combined package:** `postgresql-migration-tools-<version>.zip` (e.g. `postgresql-migration-tools-0.5.0.zip`) — contains all three tools
- **Individual tools:** `hs1-jmeter-<version>.zip`, `hs1-hammerdb-win-<version>.zip`, `hs1-port-<version>.zip`

Download the latest from the [GitLab pipeline](https://gitlab.com/scheinone/exan/axium/postgresql-migration) job artifacts.

---

## Prerequisites

The script requires:

- **Administrator privileges** — it checks on startup and exits if not elevated
- **Oracle client tools** — for Oracle connectivity
- **Npgsql PowerShell module** — for PostgreSQL connectivity
- **EDB Migration Toolkit (MTK)** — hardcoded path `C:\Program Files\edb\mtk\bin`

On every run, the script first prompts for Oracle and PostgreSQL connection credentials (saved to `axiummigration.properties`). Passwords are encrypted at rest; any plaintext passwords found in the file are automatically converted.

---

## Option 1: Full Migration

Use this option to start a migration. This will create the PostgreSQL database, migrate all customizations, and migrate all the data.

It will carry out the following steps, in order:

### 1. Validates connections & Oracle version

- **a.** Tests the PostgreSQL admin connection
- **b.** Tests the Oracle user connection
- **c.** Verifies the Oracle `PRACTICE` version is `20260100` or greater

### 2. Checks to see if the axiUm Postgres database exists

- **a.** If it exists, confirm with the user if they would like to recreate the base axiUm PostgreSQL database, user, schema, extensions
  - **i.** If the user would like to recreate the base,
    - **1.** Drops the selected PostgreSQL database and user, if they exist
    - **2.** Creates the base axiUm database (derived from NAGOLD_BP, stripped of all customizable and obsolete code)
  - **ii.** If the user would like to keep the existing database, creation is skipped
- **b.** If the database does not exist, creates the database structure automatically

### 3. Optional: Apply SQL files prior to DDL comparison

- **a.** You are prompted whether to apply any SQL files before proceeding
- **b.** If yes, scans and executes files in `C:\AxiUmDatabaseMigration\SQLFiles`

### 4. Runs axiUm Database Upgrade on Oracle (optional)

- **a.** By default the Oracle upgrade is skipped. Pass `-SkipOracleDbUpgrade $false` when starting the script to run the Oracle upgrade on the client database
  - **i.** `.\migrate.ps1 -SkipOracleDbUpgrade $false`
- **b.** Execution stops if there are critical errors. Database Check errors are ignored
  - **i.** View Database Upgrade errors at `C:\AxiUmDatabaseMigration\migrate\helper\AxiumDatabaseUpgrade\[timestamp]_db_upgrade_err`
- **c.** Resolve critical errors in Oracle, then run the script again and choose Option 3 (Oracle upgrade)
  - **i.** This will typically be handled by engineering

### 5. Migrates core Oracle objects: sequences, types, and tables

- **a.** Object names listed in `C:\AxiUmDatabaseMigration\ignoreoracleobjects.txt` are excluded
- **b.** Execution stops if any objects cannot be migrated (auto-fix is offered when applicable)
- **c.** Review `C:\AxiUmDatabaseMigration\output\[ORACLE_USER]_[timestamp]_failed_objects` to see the SQL for the failed objects
- **d.** Fix issues in SQL files
  - **i.** This will typically be handled by engineering or support
- **e.** Place the fixed SQL files in `C:\AxiUmDatabaseMigration\SQLFiles`
- **f.** Run the script again and choose Option 4 to load the custom scripts and to continue to the next steps

### 6. Migrates all data from all tables

- **a.** Currently chunking tables to 100 at a time. This is customizable in the script
  - **i.** Table names that are in `C:\AxiUmDatabaseMigration\ignoreoracleobjects.txt` will be excluded
- **b.** Any tables that fail will automatically be retried using a different connection method
- **c.** Any tables that fail a second time will be logged but will not halt execution
- **d.** After every batch, the tables that succeeded will be saved to `C:\AxiUmDatabaseMigration\ignoreoracleobjects_[schema]_[ts].txt`

### 7. Reimport sequences (errors logged but script will continue)

### 8. Applies custom indexes and constraints

- **a.** Runs `SQL-PostgreSQL-Indexes-Constraints.sql` if present in the helper directory
- **b.** Failures are logged as warnings (low priority) and do not halt execution

### 9. Migrates advanced Oracle objects: global temporary tables, views, packages, procedures, functions, triggers, materialized views, indexes, constraints, and NOT NULL constraints

- **a.** Object names listed in `C:\AxiUmDatabaseMigration\ignoreoracleobjects.txt` are excluded
- **b.** Auto-fix is offered when applicable for failed objects
- **c.** Any remaining failed objects will go to `C:\AxiUmDatabaseMigration\output\[ORACLE_USER]_[timestamp]_failed_objects`
- **d.** Fix issues in SQL files
- **e.** Place the fixed SQL files in `C:\AxiUmDatabaseMigration\SQLFiles`
- **f.** Re-run the script and choose Option 4 to apply SQL files, then continue as needed

### 10. Runs axiUm Database Upgrade on PostgreSQL (with practice downgrade)

- **a.** Will downgrade practice version to 2025.03.00 to ensure all PostgreSQL specific code in 2025.2 onwards is applied correctly
- **b.** Similar to step 4, will stop execution if there are critical errors
- **c.** Resolve critical errors in Postgres, then run the script again and choose Option 3 (PostgreSQL upgrade or Postgres with practice downgrade)
  - **i.** This will typically be handled by engineering

### 11. Optional: Oracle/PostgreSQL table row count report

- **a.** After a successful migration you are prompted whether to run a row count report (default: yes)
- **b.** Same as running Option 7 standalone

---

## Option 2: Schema Comparison Report + Migrate Differences

Use this option to compare Oracle and PostgreSQL schema objects and migrate differences.

1. Compares Oracle and PostgreSQL schemas across all object types (sequences, types, tables, views, packages, procedures, functions, triggers, global temporary tables, materialized views, indexes, constraints, NOT NULL constraints)
2. Migrates objects that differ, when applicable (auto-fix is offered for failures)
3. Output: `C:\AxiUmDatabaseMigration\output\[ORACLE_USER]_[timestamp]_reports\oracle_postgres_differences.csv`

---

## Option 3: axiUm Database Upgrade

Use this option to upgrade existing axiUm PostgreSQL or Oracle databases.

1. You choose which database to upgrade:
   - **a.** Oracle
   - **b.** PostgreSQL
   - **c.** PostgreSQL with practice downgrade to 2025.03.00 (then upgrade)
2. Checks version and runs axiUm Database Upgrade when applicable

---

## Option 4: Apply SQL Files to PostgreSQL

Use this option to apply custom SQL scripts, typically for failed customizations, against the PostgreSQL database.

1. Scans `C:\AxiUmDatabaseMigration\SQLFiles`
2. Executes all SQL files found

---
## Option 5: Migrate Data + Reimport Sequences

Use this option to migrate data and reimport the sequences.

1. Asks for a timestamp string for this run (for output naming; you can align with dated folders under `C:\AxiUmDatabaseMigration\output`)
2. Same as Option 1, Steps 6 and 7

---

## Option 6: Attempt Auto Processing of Failed or Custom Files

Use this option to auto fix the known common issues when migrating structures from Oracle to Postgres.

1. Scans and displays failed objects folders for the schema under `C:\AxiUmDatabaseMigration\output` (matching `[schema]_[timestamp]_failed_objects`)
2. Choose the correct failed objects folder (or enter a full path)
3. If `SQLFiles` is not empty, it is renamed to `_[timestamp]_SQLFiles` before new fixes are written
4. SQL files under `FailedDbObjects` (or the selected folder) are processed; auto-fixed files are written to `C:\AxiUmDatabaseMigration\SQLFiles` as `*_AUTOFIX.sql`, and each processed source file in the failed-objects tree is renamed with a `DONE_` prefix
5. You are prompted to apply the fixes to PostgreSQL (default: yes)

---
## Option 7: Oracle/PostgreSQL Table Row Count Report

Use this option to re-extract Oracle objects (tables), compare Oracle and PostgreSQL row counts, and save CSV reports.

1. Prompts whether to respect `ignoreoracleobjects.txt` when extracting Oracle objects (default: no)
2. Runs Oracle schema extraction, then compares row counts table by table
3. Output under `C:\AxiUmDatabaseMigration\output\[ORACLE_USER]_[timestamp]_reports\`: `oracle_table_counts.csv`, `postgres_table_counts.csv`, `row_count_comparison.csv`, and `ignoreoracleobjects_[ORACLE_USER].txt`
4. Produces `[ORACLE_USER]_livecompare.ini` file without passwords (please insert manually) to feed into the `livecompare` tool. More information here https://exansoftware.atlassian.net/wiki/spaces/PD/pages/4285661185/LiveCompare+Usages+and+Guidelines#How-to-Run-LiveCompare

---

## Option 8: Run SPL Check on PostgreSQL Routines

Use this option to run EDB SPL Check on every `edbspl` function, procedure, and package in the PostgreSQL database.

1. Validates each routine for SPL compatibility
2. Writes a CSV report and prints findings as warnings
3. Non-zero findings result in a warning exit but do not block further work

---

## Option 9: Exit

Exits the migration script.

---

## Appendix 1: File Locations

- **Migration Directory:** `C:\AxiUmDatabaseMigration`
- **Logs:** `C:\AxiUmDatabaseMigration\logs`
- **Failed Objects:** `C:\AxiUmDatabaseMigration\output\[ORACLE_USER]_[timestamp]_failed_objects`
- **Reports:** `C:\AxiUmDatabaseMigration\output\[ORACLE_USER]_[timestamp]_reports`
- **EDB Logs:** `C:\Users\[username]\.enterprisedb\migration-toolkit\logs`
- **SQL Files:** `C:\AxiUmDatabaseMigration\SQLFiles`
- **Properties File:** `C:\AxiUmDatabaseMigration\axiummigration.properties`

---

## Appendix 2: Troubleshooting

1. Check log files for errors (shared log file path is printed at script start)
2. Review failed objects directory
3. Contact axiUm support for application issues
