# PostgreSQL Migration Options

## 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. 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.** Base axiUm database was created from NAGOLD_BP and stripped of all customizable and obsolete code
  - **ii.**if the user would like to keep the existing db
      - **1.** Asks the user if they would like to apply any sql files prior to continuing to step 3
        - **a.** If yes, scans `C:\AxiUmDatabaseMigration\SQLFiles`
        - **b.** Confirm with the user would like to execute all the SQL files found 


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

- **a.** Use the -SkipOracleDbUpgrade Flag to upgrade the Oracle instance 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 2
  - **i.** This will typically be handled by engineering

### 3. Migrates custom Oracle essential structures: tables, sequences, and types 

- **a.** Structure names that are in `C:\AxiUmDatabaseMigration\ignoreoracleobjects.txt` will be not be included
- **b.** Execution stops if any objects cannot be migrated
- **c.** Review `C:\AxiUmDatabaseMigration\output\[schema_name]_failed_objects_[timestamp]` 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 3 to load the custom scripts and to continue to the next steps

### 4. 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`


### 5. Migrate sequences (errors logged but script will continue)

### 6. Runs axiUm Database Upgrade on PostgreSQL

- **a.** Will downgrade practice version to 2025.01 to ensure all PostgreSQL specific code in 2025.2 onwards is applied correctly
- **b.** Similar to step 2, will stop execution if there are critical errors
- **c.** Resolve critical errors in Postgres, then run the script again and choose Option 4
  - **i.** question: who will fix the critical errors in axiUm database upgrade?

### 7. Migrates custom Oracle views, packages, procedures, functions, triggers, global temporary tables and materialized views

- **a.** Structure names that are in `C:\AxiUmDatabaseMigration\ignoreoracleobjects.txt` will be not be included
- **b.** Any failed objects will go to `C:\AxiUmDatabaseMigration\FailedDbObject`
- **c.** Fix issues in SQL files
- **d.** Place the fixed SQL files in `C:\AxiUmDatabaseMigration\SQLFiles`
- **e.** Re-run the script and choose Option 7

---

## Option 2: Schema Comparison Report

Use this option at the end of a full migration to generate a report to compare objects in Oracle and PostgreSQL.

1. Compares Oracle and PostgreSQL schemas
   - **a.** Checks all object types
2. Read-only - no migration
3. Output: `C:\AxiUmDatabaseMigration\[ORACLE_SCHEMA]_differences[timestamp].csv`

---

## Option 3: axiUm Database Upgrade

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

1. Checks version and will update if the axiUm database version <= axiUm Database Upgrade
2. Runs axiUm upgrade

---

## 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. Will ask for the timestamp for the comparison report.
  - **i.** timestamp found in `C:\AxiUmDatabaseMigration\output`
2. Same as Option 1, Step 4 and Step 5

---

## Option 6: Attempt Auto Processing of Failed Files

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

1. Scans and display all failed sql objects for the schema in `C:\AxiUmDatabaseMigration\output\[schema]_[timestamp]_failed_objects\FailedDbObjects`
2. Choose the correct failed objects folder.
3. Copies the SQL files to `C:\AxiUmDatabaseMigration\SQLFiles`
4. Both the folders in Steps 1 and 3 will be renamed `DONE_[filename]`

---
## Appendix 1: File Locations

- **Migration Directory:** `C:\AxiUmDatabaseMigration`
- **Logs:** `C:\AxiUmDatabaseMigration\logs`
- **Failed Objects:** `C:\AxiUmDatabaseMigration\FailedDbObjects`
- **EDB Logs:** `C:\Users\[username]\.enterprisedb\migration-toolkit\logs`
- **SQL Files:** `C:\AxiUmDatabaseMigration\SQLFiles`

---

## Appendix 2: Troubleshooting

1. Check log files for errors
2. Review failed objects directory
3. Contact axiUm support for application issues

