cancel
Showing results for 
Search instead for 
Did you mean: 

MTK Quickstart Postgres to Postgres

EDB Team Member

Objective

Quickly (less than five minutes) install and run Migration Toolkit to migrate from one schema to another on the same Postgres database on RHEL 6/7. Why might you do this? Maybe you want to clone a schema. Maybe you used MTK to create your initial schema and you want to use the same mechanism to take occasional snapshots of your new schema. Maybe you just want to practice using MTK options in the most simple case possible (a single Postgres database). Whatever the reason, this quickstart will get you started using MTK offline/online with data and without data in a few minutes.

 

Prerequisites

Running instance of RHEL 6/7 with root access

 

Components

  • EPAS 9.5 with a single default cluster (/var/lib/ppas/9.5/data) and default database (edb)
    • Manually created "source_schema" with a single table
    • MTK created "target_schema" with the same table from "source_schema"
  • Migration Toolkit (plus Java as a pre-requisite to run MTK)
    • Binary in /usr/ppas/migrationtoolkit/bin
    • Configuration properties file in /usr/ppas/migrationtoolkit/etc/toolkit.properties
  • /tmp/migration_offline
    • Location for offline scripts if MTK is run in offline mode (meaning extract source to files so that they can be loaded to target later, not at the same time as the extract)

 

Steps (as root user):

#!/bin/bash

#
# Setup YUM repository for installing EPAS
#
rpm -Uvh http://yum.enterprisedb.com/edbrepos/edb-repo-9.6-4.noarch.rpm

#
# Set YUM username/password in edb.repo
#
export YUM_USER=<yum user>
export YUM_PASSWORD=<yum password>
sed -i "s/<username>:<password>/$YUM_USER:$YUM_PASSWORD/g" /etc/yum.repos.d/edb.repo

#
# Enable the EPAS 9.5 repo in edb.repo
#
sed -i "\/ppas95/,/gpgcheck/ s/enabled=0/enabled=1/" /etc/yum.repos.d/edb.repo

#
# Install EPAS 9.5
#
yum -y install ppas95-server

#
# Start the EPAS server and initialize a new Postgres
# data directory using the appropriate mechanism
# for the operating system
#
if [ `cat /etc/redhat-release | grep "release 7" | wc -l` = 1 ]
  then
    /usr/lib/systemd/system/ppas-9.5.sh initdb
    systemctl start ppas-9.5.service
elif [ `cat /etc/redhat-release | grep "release 6" | wc -l` = 1 ]
  then
    service ppas-9.5 initdb
    service ppas-9.5 start
else
  su - enterprisedb -c "/usr/ppas-9.5/bin/initdb -D /var/lib/ppas/9.5/data"
  su - enterprisedb -c "/usr/ppas-9.5/bin/pg_ctl -D /var/lib/ppas/9.5/data start"
fi

#
# Set the password for the enterprisedb user and setup pg_hba.conf for md5 authentication
#
su - enterprisedb -c "psql -d edb -c \"ALTER USER enterprisedb IDENTIFIED BY enterprisedb\""
sed -i "s/ident/trust/g" /var/lib/ppas/9.5/data/pg_hba.conf
su - enterprisedb -c "/usr/ppas-9.5/bin/pg_ctl reload"

#
# Enabled the EnterpriseDB Tool Repository
#
sed -i "\/enterprisedb-tools/,/gpgcheck/ s/enabled=0/enabled=1/" /etc/yum.repos.d/edb.repo

#
# Install Migration Toolkit and Java (which is required by Migration Toolkit
#
yum -y install ppas-migrationtoolkit java

#
# Create a simple source schema and table to be migrated
#
su - enterprisedb -c "psql -d edb -c \"CREATE SCHEMA source_schema\""
su - enterprisedb -c "psql -d edb -c \"CREATE TABLE source_schema.source_table (my_col text)\""
su - enterprisedb -c "psql -d edb -c \"INSERT INTO source_schema.source_table values ('source value')\""

#
# Setup the MTK configuration file for the migration source and target
#
echo "SRC_DB_URL=jdbc:edb://localhost:5444/edb"     > /usr/ppas/migrationtoolkit/etc/toolkit.properties
echo "SRC_DB_USER=enterprisedb"                    >> /usr/ppas/migrationtoolkit/etc/toolkit.properties
echo "SRC_DB_PASSWORD=enterprisedb"                >> /usr/ppas/migrationtoolkit/etc/toolkit.properties
echo ""                                            >> /usr/ppas/migrationtoolkit/etc/toolkit.properties
echo "TARGET_DB_URL=jdbc:edb://localhost:5444/edb" >> /usr/ppas/migrationtoolkit/etc/toolkit.properties
echo "TARGET_DB_USER=enterprisedb"                 >> /usr/ppas/migrationtoolkit/etc/toolkit.properties
echo "TARGET_DB_PASSWORD=enterprisedb"             >> /usr/ppas/migrationtoolkit/etc/toolkit.properties

#
# Show that the source_table only exists in the source_schema
#
su - enterprisedb -c "psql -d edb -c \"\dt *.source_table \""

#
# Run the Migration Toolkit in offline mode (which dumps the source to scripts to be run later)
#
su - enterprisedb -c "mkdir /tmp/migration_offline"
su - enterprisedb -c "/usr/ppas/migrationtoolkit/bin/runMTK.sh -sourcedbtype enterprisedb -schemaOnly -targetSchema target_schema -offlineMigration /tmp/migration_offline -logDir /tmp/migration_offline source_schema"

#
# Show the generated offline script includes SQL to create the schema and table
#
cat /tmp/migration_offline/mtk_target_schema_ddl.sql

#
# Run the Migration Toolkit in online mode (which extracts the source and creates the target in one step)
#
su - enterprisedb -c "mkdir /tmp/migration_online"
su - enterprisedb -c "/usr/ppas/migrationtoolkit/bin/runMTK.sh -sourcedbtype enterprisedb -schemaOnly -targetSchema target_schema -logDir /tmp/migration_online source_schema"

#
# Show that the target schema now exists and the source_table is in it as well as in the source schema,
# but also show that the row of data was not migrated, just the schema
#
su - enterprisedb -c "psql -d edb -c \"\dt *.source_table\""
su - enterprisedb -c "psql -d edb -c \"table target_schema.source_table\""

#
# Lastly, drop the target_schema and run migration toolkit once more online without
# the "schema_only" flag so that the table data will be migrated as well.
#
su - enterprisedb -c "psql -d edb -c 'drop schema target_schema cascade'"
su - enterprisedb -c "/usr/ppas/migrationtoolkit/bin/runMTK.sh -sourcedbtype enterprisedb -targetSchema target_schema -logDir /tmp/migration_online source_schema"
su - enterprisedb -c "psql -d edb -c \"table target_schema.source_table\""

#
# Congratulations! You have used migration toolkit to migrate a Postgres schema to another Postgres schema both online/offline
# as well as schema_only and with data!
#
 
 

Tips

 

Summary

This guide is intended to get you up and started with a simple use of Migration Toolkit for migrating one Postgres schema to another Postgres schema. There is much more that Migration Toolkit can do and more related quickstarts will be coming.