comprehensive guide to postgresql to oracle migration using bash scripts

database administration Hits: 35

In database migration projects from PostgreSQL to Oracle, fundamental differences in data type behavior, JSON structures, sequence management, and DDL syntax mean that straightforward methods or generic tools do not always meet the requirements of a real-world, mission-critical scenario. This article introduces a practical toolkit based on Bash scripts that performs the extraction, schema transformation, and data loading step-by-step under the complete control of the DBA.

Background and Motivation Behind Developing This Tool

Some time ago, in a production project, I was responsible for migrating data from a PostgreSQL database to Oracle. Initially, we attempted to proceed using SQL Developer, but it did not load properly in that production environment and lacked the required performance. On the other hand, setting up heavy enterprise tools required time-consuming infrastructure prerequisites and complex configurations.

Furthermore, my main challenge was precision and flexibility in converting specific data types such as JSON, JSONB, and UUID. In PostgreSQL, these data types have their own specific structures, and on the Oracle side, selecting the optimal equivalent (such as Native JSON, a CLOB column with an IS JSON check constraint, or VARCHAR2 depending on the Oracle version) required direct DBA decision-making and supervision.

For these reasons, I decided to implement the entire process across 5 modular stages relying on lightweight Bash scripts, the native psql utility, and the high-speed SQL*Loader tool. These tools offer distinct advantages:

  • Structured and isolated extraction of PostgreSQL metadata;
  • Interactive and accurate mapping of complex types (JSON, UUID, and arrays) tailored to the target Oracle database version;
  • Automatic sequence management and initial value (Increment/Start with) adjustment aligned with the maximum values of numeric columns;
  • Generation of standard DDL scripts for creating schemas and tables in Oracle;
  • CSV data extraction and blazing-fast data loading using SQL*Loader;
  • Application of indexes and constraints after loading is complete to maintain maximum ingestion throughput;
  • Complete log transparency and rapid error tracing at every stage.

During the design and refinement of these scripts, I also utilized Artificial Intelligence (AI) as a technical assistant to optimize the Bash code, validate DDL statements, and draft documentation. This toolkit has been thoroughly and successfully tested in a production environment, with all data migrated seamlessly.

Deployment Architecture and Prerequisites

A key architectural point of this toolkit is that all scripts are executed directly on the server hosting the Oracle (target) database, eliminating the need to run any scripts on the PostgreSQL server.

The only critical prerequisite is having the psql CLI client installed on this Oracle server so it can connect over the network to the source server to read data and metadata. Subsequently, sqlplus and sqlldr commands handle the schema creation and data loading operations inside Oracle.

Commands and Prerequisites on the Oracle Server:

  1. Install PostgreSQL Client: On RHEL-based distributions such as Oracle Linux 8/9: 
    # Install PostgreSQL client
sudo dnf install -y postgresql
  2. Verify Required Tool Availability: 
    # Verify required CLI binaries
which sqlplus
which sqlldr
which psql
  3. Network Connectivity: Ensure network connectivity to the PostgreSQL database port (default 5432) from the Oracle server.

The 5 Phases of Migration Execution

1. Metadata Extraction (mig1-connect-to-postgres.sh)

Connects to the PostgreSQL server using psql and extracts all metadata related to tables, columns, constraints, indexes, sequences, and custom types.

2. Data Type Conversion and Mapping (mig2-convert_datatypes.sh)

Maps sensitive types such as UUID, JSONB, BOOLEAN, TEXT, and timestamps to their Oracle equivalents under DBA supervision and generates the type_mapping.conf configuration file.

3. Oracle DDL Generation (mig3-connect-to-oracle.sh)

Generates the DDL scripts to create schemas, tables, and sequences, making them ready for execution in the Oracle environment.

4. CSV Export and Data Loading with SQL*Loader (mig4-csv-postgres-sqldlr-oracle.sh)

Exports data to CSV format via psql and ingests it into Oracle at maximum throughput using control files (.ctl) and sqlldr.

5. Applying Indexes, Constraints, and Final Validation (mig5-postload-oracle.sh)

In the final phase, indexes and constraints (Foreign Keys and Checks) are applied, and final record counts and states are verified.

Execution Guide

# Step 1: Clone repository on the Oracle server
git clone https://github.com/vahiddb/postgres-to-oracle-migration.git
cd postgres-to-oracle-migration

# Step 2: Grant execution permissions
chmod +x *.sh

# Step 3: Run the phases sequentially
./mig1-connect-to-postgres.sh
./mig2-convert_datatypes.sh-postload-oracle.sh

Disclaimer, Scope of Responsibility, and Important Notes

The primary objective of this toolkit is strictly the accurate and secure transfer of data (Data Migration) from source to destination. Given the fundamental differences in programming strictly the accurate and secure transfer of data (Data Migration) from source to destination. Given the fundamental differences in programming logic across database platforms, the following points are vital:

  • Validation Responsibility: This toolkit is a data migration engine. The ultimate responsibility for ensuring that the migrated data conforms to application business logic, as well as final end-to-end data integrity testing, rests with the software development team and application developers, not the DBA.
  • Sequence Management: Although the tool attempts to recreate and sync sequences, you should always verify the latest record values (Last Inserted IDs) against the actual sequence states in the Oracle database post-migration.
  • Backups: It is strongly recommended to take full backups of both databases before executing any migration steps.
  • Test Environment: Always evaluate the full process in a test/staging environment identical to production prior to final production execution.
  • Execution Liability: All scripts are executed under the user's control, and full responsibility for any potential operational impacts rests entirely with the user/database administrator.

GitHub Repository

The source code for this project is available under the open-source MIT License on GitHub. Feedback, bug reports, and contributions are welcome:

https://github.com/vahiddb/postgres-to-oracle-migration