migration.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Oracle to Postgres\n",
    "\n",
    "This notebook demonstrates how to connect to use the `oracle2postgres` package to migrate data between databases. Warning: Running `drop_database` will drop databases. Running `check_for_nulls` with the `remove=True` will remove null characters that appear in strings in the source data."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Make a backup\n",
    "\n",
    "We recommend backing up your source and target databases before beginning the migration process."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Import libraries"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Import libraries\n",
    "import sys\n",
    "import oracle2postgres"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Create logfile"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# create the logfile\n",
    "oracle2postgres.create_logfile()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Source database settings"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# get source database settings\n",
    "source_config = oracle2postgres.get_source_config()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Target database settings"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# get target database settings\n",
    "target_config = oracle2postgres.get_target_config()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Migration settings"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# get settings for migration\n",
    "migration_config = oracle2postgres.get_migration_config()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Check the target schema exists"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# check the schema exist on the source database\n",
    "source_engine = oracle2postgres.connect_to_source(source_config)\n",
    "oracle2postgres.check_schema_exist(source_engine,source_config['schema_list'])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Check for null characters in the source database\n",
    "\n",
    "If null characters exist and they are not removed, your migration will fail"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# check and remove null characters in strings\n",
    "oracle2postgres.check_for_nulls(source_engine,source_config['schema_list'])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Remove null characters in the source database\n",
    "\n",
    "The following command will remove null characters in the source database. Use at your own risk."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# WARNING: uncomment the following line to remove null characters in the source database\n",
    "# oracle2postgres.check_for_nulls(source_engine,source_config['schema_list'],remove=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Create the target database"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# connect to the target database\n",
    "target_engine = oracle2postgres.connect_to_target(target_config)\n",
    "\n",
    "# WARNING: uncomment the following line to delete target database before creation!\n",
    "# oracle2postgres.drop_connections(target_config['database'],target_engine)\n",
    "# oracle2postgres.drop_database(target_config['database'],target_engine)\n",
    "\n",
    "# create a new database on the target\n",
    "oracle2postgres.create_database(target_config['database'],target_engine)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Create the schema on the target database"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# create the schema on the target database\n",
    "target_engine = oracle2postgres.connect_to_target(target_config,target_config['database'])\n",
    "oracle2postgres.create_target_schema(source_config['schema_list'],source_engine,target_engine)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Migrate the data!"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# run the migration\n",
    "oracle2postgres.migrate(source_config,target_config,migration_config)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Check the migration"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# run some integrity checks\n",
    "source_engine = oracle2postgres.connect_to_source(source_config)\n",
    "target_engine = oracle2postgres.connect_to_target(target_config,target_config['database'])\n",
    "oracle2postgres.check_migration(source_engine,target_engine,source_config)"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.6.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}