DEV Community: jerry80409

Ora2pg - a free tool for Oracle migrate to Postgres

jerry80409 — Tue, 22 Oct 2019 08:02:38 +0000

Ora2pg

I'm trying to help my team migration Oracle to Postgres and I found a very useful tool that can help me to solve migrate problems. It not only supports Postgres but also Mysql, Ora2pg can work with Orafce, if you have procedures or functions, its easy to integrate to work.

Ora2Pg is a free tool used to migrate an Oracle or MySQL database to a PostgreSQL compatible schema. It connects your Oracle database, scan it automatically and extracts its structure or data, it then generates SQL scripts that you can load into your PostgreSQL database.

The "orafce" project implements some functions from the Oracle database. The functionality was verified on Oracle 10g, and the module is useful for production work.

Initiate a project for work

Ora2pg will help you to create a project, it helps you to configure the setting.

# it will create a folder 
ora2pg --init_project your_project

Configuration

The ora2pg.conf in the /config folder. This directives are demonstration.

# Set Oracle database connection (datasource, user, password)
ORACLE_DSN      dbi:Oracle:host=localhost;sid=EE;port=1521
ORACLE_USER     user
ORACLE_PWD      pwd

# setting database objects to export
TYPE            TABLE,PACKAGE,VIEW,SEQUENCE,PROCEDURE,TYPE,MVIEW,TRIGGER,SEQUENCE,COPY

# you can exclude tables like logs table
EXCLUDE        LOG_FOO_TABLES

# Oracle data type mapping
DATA_TYPE       NUMBER(*\,0):bigint;NVARCHAR2(*):varchar(255)

# disable or enable Oracle NUMBER(p,s) type to Postgres numeric(p,s)
PG_NUMERIC_TYPE    1

# disable or enable Oracle NUMBER(p) type to Postgres int or bigint
PG_INTEGER_TYPE    1

# this setting should work with PG_INTEGER_TYPE
DEFAULT_NUMERIC    bigint

# setting specific pk fields to bigserial
MODIFY_TYPE  FOO_TABLE:ID_NUM:BIGSERIAL,BAR_TABLE:ID_NUM:BIGSERIAL

# Enable PLSQL to PLPSQL conversion
PLSQL_PGSQL 1

# if you installed Orafce, you can enable it
USE_ORAFCE  0

# setting specific Postgres version
PG_VERSION  11

Export and check schema

Ora2pg project provides a script, just execute the export_schema.sh, it easy to help you export Oracle schemas.

./export_schema.sh

Load schemas to Postgres

Another script is import_all.sh, its aims to help you import .sql files to Postgres.

# if you won't answer PG_PASSWORD prompt, you can setting PGPASSWORD var 
PGPASSWORD='password' ./import_all.sh -h postgres \
  -p 5432 \
  -U username \
  -o dbowner \
  -d database_name

Import data to Postgres

# Import data to Postgres with `-a`.
PGPASSWORD='password' ./import_all.sh -h postgres \
  -a \        # import data only
  -p 5432 \
  -U username \
  -o dbowner \
  -d database_name

# Directly import data to Postgres
PGPASSWORD='password' ora2pg -c config/ora2pg.conf \
  -t COPY \
  --pg_dsn "dbi:Pg:dbname=database_name;host=postgres;port=5432" \
  --pg_user username \
  -e 'TABLE[LOG_.*];TABLE[*._LOG]'  # exclude LOG tables

A simple script for Postgres database export and import

jerry80409 — Thu, 17 Oct 2019 18:13:05 +0000

I write a simple script that aims to help me work.

Usage

You can put this script to your work directory and export the Postgres data in the same directory. Consider your situation and change these options.

You can check Postgres docs to see Postgres environment variables and check pg_dum docs to understand.

# go to your work directory
cd your-work-dir

# export Postgres database
./pg_helper.sh -o

# import to other Postgres database
./pg_helper.sh -i

pg_helper.sh

#!/usr/bin/env bash
#==============================
# An script help postgres database impoort and exoprt
#==============================

# an error occurred will stop script.
set -e

# an undefined parameter will throw error message.
set -u

# an pipeline failed will stop script.
set -o pipefail

#==============================
# Parameters
# You should change parameters for your Postgres connection.
#==============================
HOST='localhost'
DATABASE='database'
USER='database user'
PASSWD='database password'
TAR_FILE='tar file name'

#==============================
# Ansi colors
#==============================
RED='\033[0;31m'
BLUE='\033[0;34m'
YELLOW='\033[0;33m'
NCOLOR='\033[0m'

#==============================
# Message functions
#==============================
die() {
    echo -e "${RED}ERROR:${NCOLOR} $1" 1>&2
    exit 1
}

usage() {
    echo -e "An script help postgres database impoort and exoprt"
    echo -e "usage: `basename $0` [options]"
    echo ""
    echo -e "options:"
    echo -e "   ${YELLOW}-i${NCOLOR}     import .tar file;    usage: ${YELLOW}./`basename $0` -i${NCOLOR}"
    echo -e "   ${YELLOW}-o${NCOLOR}     export to .tar;      usage: ${YELLOW}./`basename $0` -o${NCOLOR}"
    echo -e "   ${YELLOW}-h${NCOLOR}     help;                usage: ${YELLOW}./`basename $0` -h${NCOLOR}"
    echo ""
    exit $1
}

confirm() {
    local msg
    if [[ -z $1 ]]; then
        msg="Are you sure?"
    else
        msg=$1
    fi

    # call with a prompt string or use a default
    read -r -p "${msg} [y/N/q] " response
    case ${response} in
        [yY][eE][sS]|[yY])
            true
            ;;
        [qQ][uU][iI][tT]|[qQ])
            exit
            ;;
        *)
            false
            ;;
    esac
}

import_data() {
    local file=${TAR_FILE}
    if [[ ! -f ${file} ]]; then
        die "The file ${file} not existed."
    fi

    echo -e "${RED}This option will clean your database ${DATABSE}.${NCOLOR}"
    if confirm ""; then
        pg_restore -d${DATABASE} -U${USER} -c --if-exists -vvv -e ${file}
    fi
}

export_data() {
    local file=${TAR_FILE}
    if [[ -f ${file} ]]; then
        echo -e "rm -f ${file}"
        rm -f ${file}
        pg_dump -U${USER} -Ft ${DATABASE} -vvv > ${file}
    fi
}

#==============================
# Handled user options
#==============================
while getopts "ioh?" opt; do
    case "$opt" in
        "i") import_data ;;
        "o") export_data ;;
        "h") usage 1;;
        "?") usage 1;;
        *) die "Unknown options.";;
    esac
done

should provide an executive privilege

chmod +x pg_helper.sh

Hope it will help you :)

Yum Install Postgres 11 client

jerry80409 — Thu, 17 Oct 2019 10:17:59 +0000

CentOS 7

# -U Upgrade 
# -v Show verbose stdout
# -h Show hash marks
rpm -Uvh https://yum.postgresql.org/11/redhat/rhel-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm

Install PostgreSQL Client

# -y Always answer yes for all questions
# and clean cached package
yum -y install postgresql11 && yum clean

Check psql command

psql --version
psql (PostgreSQL) 11.5

Connect your Postgres database

# $PG_HOST: Hostname of Postgres
# $PG_PORT: Default is 5432
# $PG_USER: User of Postgres
psql -h $PG_HOST -p $PG_PORT -U $PG_USER
Password for user wms: # enter the password