search

visulate / visulate-ora2pg

Docker image and single page web application for Ora2Pg
Apache License 2.0
30 stars 10 forks source link
ora2pg oracle postgres postgresql

readme

Visulate Ora2Pg

A docker image and single page web application for Ora2Pg

Overview

Ora2Pg is an open source Perl script used to migrate an Oracle database to a PostgreSQL compatible schema. It connects an Oracle database, scans it to extract its structure or data, then generates SQL scripts for loading into a PostgreSQL database. It is a popular tool with users who are converting Oracle databases to PostgreSQL as part of a GCP or Azure migration (AWS has its own proprietary conversion tool).

Ora2Pg is a CLI application. It is a powerful tool in the hands of an experienced user but has usability issues for the first time user. The installation procedure is cumbersome with a number of prerequisite dependencies that must be installed manually. Once installed, the initial user experience is intimidating. Most of the functionality is controlled via a configuration file. This file is over 1100 lines long with many configuration parameters. It is not immediately apparent which parameters require user input.

This project aims to address some of the usability issues. It creates a Docker image to simplify the installation and adds a single page web application UI to control the configuration and execution of Ora2Pg.

The UI provides an HTML form to edit configuration parameters. This form allows the user to toggle between basic and advanced settings. Alt text

Once the configuration parameters have been set the user can run Ora2Pg via the UI and view its output. Alt text

Output files are written to a Docker volume which can be bound to the host filesystem. There's also a UI to download output files.

Quickstart

Build the docker image or pull the latest version from Docker Hub.

Create a directory on your host computer to hold the output from Ora2Pg:

mkdir ora2pg-projects

Run Visulate-Ora2Pg with a port and volume binding - example:

docker run -d -p 3000:3000 -v "$(pwd)/ora2pg-projects":/project visulate/visulate-ora2pg:latest

Open your browser at http://localhost:3000 and follow the onscreen instructions

Additional usage instructions are available in Migrating Oracle to PostgreSQL

Apple Silicon

To run on Apple silicon, you will need to specify the platform using the --platform=linux/amd64 flag. E.g:

docker run -d --platform=linux/amd64 -v "$(pwd)/ora2pg-projects":/project visulate/visulate-ora2pg:latest

or set the DOCKER_DEFAULT_PLATFORM=linux/amd64 environment variable before running docker-compose up

Technical Description

The Dockerfile is in the project root. You can build it using:

docker build -t visulate-ora2pg:local .

This builds an image that includes Ora2Pg and the UI which can be run using:

docker run -d -p 3000:3000 -v "$(pwd)/ora2pg-projects":/project visulate-ora2pg:local

The UI is implemented using a combination of Node/Express API and a Vue UI. The API handles interaction with the Ora2Pg CLI. The Vue UI handles presentation to the user.

The UI and API communicate using a JSON representation of the ora2pg.conf file. The JSON document stores the ora2pg.conf data and metadata in a structured format suitable for use in the UI. For example, it records valid values for a parameter in an array where the ora2pg.conf file documents these using comments. A handlebars template is used to convert the JSON document to a ora2pg.conf file at runtime.

The Vue UI is served from Express. The entry point for this is the express/ui/dist/index.html file. The distribution can be built from the express/ui directory by running npm run build.

The Vue UI makes REST API calls to the API. These are handled by the express/api/ora2pg.js router.

The REST API is organized around projects. Each project is a filesystem directory with an ora2pg-conf.json.enc file and any files that have been generated by running Ora2Pg. The ora2pg-conf.json.enc file is an aes-256-ctr encrypted JSON document. The UI creates a default project on initial startup and can be used to add or remove projects as required. A menu in the UI allows users to navigate between projects.

When the user selects a project the UI gets the corresponding ora2pg-conf.json file from the API and displays its data in a form. This form can be used to edit its values and save or run the result.

Submitting a run from the UI initiates a server sent event stream. The UI issues an API call which performs the following actions:

  1. Saves the ora2pg-conf.json.enc file
  2. Translates the ora2pg-conf JSON object into a temporary ora2pg.conf file
  3. Initiates a "Content-Type": "text/event-stream"
  4. Calls ora2pg using the temporary ora2pg.conf file
  5. Pipes stdout and stderr from the ora2pg process to the event-stream
  6. Writes ora2pg output files to the project directory
  7. Waits for ora2pg to complete
  8. Deletes the temporary ora2pg.conf file
  9. Closes the event-stream

Project directories are created as subdirectories in the /project volume of the docker container. Ora2Pg output files can be accessed directly from the host computer if this volume was bound during docker run. They can also be downloaded from the 'Project Review' screen in the UI. This lists project files. It also provides an option to delete the project.

Security Considerations

Measures should be taken to prevent unauthorized access to the project directory. Ora2Pg accepts a plain text configuration file as input. This file may include sensitive information like the SYSTEM password of the source database. This plain text file is accessible for the duration of the run and may persist after it if the run fails.

The file is created at runtime using credentials supplied from the UI. The process it uses is described in the auth design doc. The configuration file is deleted at the end of the run.

HTTPS support

Visulate Ora2Pg can be configured to use self signed X.509 certificates. The docker container exposes a certs volume that can be mapped to a directory with a certificate and key file. On startup the express server checks for an environment variable called ORA2PG_TLS_CERT_PASSPHRASE to determine whether to start an http or https server. If the variable is set an https server is started:

docker run -d -p 3000:3000 \
-v "$(pwd)/ora2pg-projects":/project \
-v "$(pwd)/ora2pg-certs":/certs \
-e ORA2PG_TLS_CERT_PASSPHRASE='my-passphrase' \
visulate-ora2pg:local

The code looks for a certificate file called ora2pg-cert.pem and keyfile called ora2pg-key.pem. These can be generated using openssl as shown in the example below.

mkdir ora2pg-certs
cd ora2pg-certs/
openssl req -x509 -newkey rsa:4096 -keyout ora2pg-key.pem -out ora2pg-cert.pem -sha256 -days 365

Generating a RSA private key
.++++
.....................................................................................................................................................................++++
writing new private key to 'ora2pg-key.pem'
Enter PEM pass phrase:
Verifying - Enter PEM pass phrase:
-----
You are about to be asked to enter information that will be incorporated
into your certificate request.
What you are about to enter is what is called a Distinguished Name or a DN.
There are quite a few fields but you can leave some blank
For some fields there will be a default value,
If you enter '.', the field will be left blank.
-----
Country Name (2 letter code) [AU]:US
State or Province Name (full name) [Some-State]:Florida
Locality Name (eg, city) []:Orlando
Organization Name (eg, company) [Internet Widgits Pty Ltd]:Visulate
Organizational Unit Name (eg, section) []:Ora2Pg
Common Name (e.g. server FQDN or YOUR name) []:ora2pg.us-east1-b.c.my-gcp-project.internal
Email Address []:

Tests

There are separate tests for the APIs and Vue UI. Initiate the API tests by running npm test from the express directory. The Vue UI tests are initiated by running npm run test:unit from the express/ui directory.