PostgreSQL Migration Using MTK

-

Enterprisedb Migration Toolkit:

EDB Migration Toolkit is a powerful command-line tool that offers granular control of the migration process.
Migration Toolkit facilitates migration of database objects and data to an EDB Postgres Advanced Server (Advanced Server) or PostgreSQL database from:

  1. Oracle
  2. MySQL
  3. Microsoft SQL Server
  4. Sybase

which objects migrate to postgres

 Oracle Objects:

  1. Schemas  
  2. Tables   
  3. List-Partitioned Tables        
  4. Range-Partitioned Table        
  5. Constraints  
  6. Indexes  
  7. Triggers        
  8. Table Data  
  9. Views    
  10. Materialized Views        
  11. Packages        
  12. Procedures  
  13. Functions      
  14. Sequences      
  15. Users/Roles  
  16. Profiles  
  17. Object Types  
  18. Object Type Methods  
  19. Database Links    
  20. Queues      

MySQL Objects:

  1. Schemas
  2. Tables
  3. Constraints
  4. Indexes
  5. Table Data

Microsoft SQL Server Objects:
  1. Schemas
  2. Tables
  3. Constraints
  4. Indexes
  5. Table Data
Sybase Objects:
  1. Schemas
  2. Tables
  3. Constraints
  4. Indexes
  5. Table Data
  6. Views

Install EDB-Migration Toolkit

Install Repository file and change repository username and password

yum -y install https://yum.enterprisedb.com/edbrepos/edb-repo-latest.noarch.rpm

vi /etc/yum.repos.d/edb.repo
[edb]
name=EnterpriseDB RPMs $releasever - $basearch
baseurl=https://ravirajdba92:KnjF6PGtfjrFmT2f@yum.enterprisedb.com/edb/redhat/rhel-$releasever-$basearch
enabled=1
gpgcheck=1
gpgkey=file:///etc/pki/rpm-gpg/ENTERPRISEDB-GPG-KEY

yum install edb-migrationtoolkit

MTK Tool Bin path =/usr/edb/migrationtoolkit/bin
MTK Tool configuration file path=/usr/edb/migrationtoolkit/etc

edit MTK configuration file for Oracle to Postgres Migration:

vi toolkit.properties 
SRC_DB_URL=jdbc:oracle:thin:@192.168.1.201:1521:pearl
SRC_DB_USER=mms
SRC_DB_PASSWORD=mms

TARGET_DB_URL=jdbc:edb://192.168.1.201:5444/edb
TARGET_DB_USER=enterprisedb
TARGET_DB_PASSWORD=post

edit MTK configuration file for MySQL to Postgres Migration:
change SRC_DB_URL only
jdbc:mysql://<host_name>[:<port>]/<database_id>

 edit MTK configuration file for Sybase to Postgres Migration:
change SRC_DB_URL only
jdbc:jtds:sybase://<host_name>[:<port>]/<database_id>

 edit MTK configuration file for  SQL Server to Postgres Migration:
change SRC_DB_URL only
jdbc:jtds:sqlserver:\<server_name>\[:<port>]\<database_id>

 Download and paste the ojdbc file
[root@server1 lib]# pwd
/usr/edb/migrationtoolkit/lib
[root@server1 lib]# ll
-rwxr-xr-x. 1 root root  315292 Mar 10 03:29 commons-lang3-3.1.jar
-rwxr-xr-x. 1 root root  223779 Mar 10 03:29 edb-commons.jar
lrwxrwxrwx. 1 root root      48 Jul 23 10:06 edb-jdbc17.jar -> /usr/edb/migrationtoolkit/../jdbc/edb-jdbc17.jar
-rwxr-xr-x. 1 root root  483663 Mar 10 03:29 log4j-1.2.17.jar
-rwxrwxrwx. 1 root root 2739616 Jul 23 10:21 ojdbc6.jar

authentication and authorization for both server
Start Oracle listener and pg_hba.conf ipv4 entery for postgresql

Run EDB-Migration Toolkit

EDB-Migration Toolkit provide two types of migration

  • online Migration
  • offline Migration

online Migration: 

 cd /usr/edb/migrationtoolkit/bin
 ./runMTK.sh -allSchemas

offline Migration:

 ./runMTK.sh -offlineMigration /home/oracle/muthu_migration.sql -allSchemas

import offline Migration:

 su - enterprisedb
cd /opt/edb/as10/bin
./psql
edb=#\i /home/oracle/muthu_migration.sql

Comments

Post a Comment

Popular posts from this blog

PostgreSQL pg_pool-II Installation and Configuration

-
Image
Pgpool-II Pgpool-II is a proxy software that sits between PostgreSQL servers and a PostgreSQL database client.  It provides the following features: 1.Connection Pooling:     Pgpool-II maintains established connections to the PostgreSQL servers, and reuses them whenever a new connection with the same properties (i.e. user name, database, protocol version, and other connection parameters if any) comes in. It reduces the connection overhead, and improves system's overall throughput.   2.Load Balancing:     If a database is replicated (because running in either replication mode or master/slave mode), performing a SELECT query on any server will return the same result. Pgpool-II takes advantage of the replication feature in order to reduce the load on each PostgreSQL server.  It does that by distributing SELECT queries among available servers, improving the system's overall throughput. In an ideal scenario, read performance could improve proportionally to the number of PostgreSQ
Read more

PostgreSQL Pages and Tuples

-
Image
INTERNAL LAYOUT: a tuple or an item is a synonym for a row a relation is a synonym for a table a filenode is an id which represent a reference to a table or an index. a block and page are equals and they represent a 8kb segment information the file storing the table. a heap refer to heap file . Heap files are lists of unordered records of variable size. Although sharing a similar name, heap files are different from heap data structure. CTID represent the physical location of the row version within its table. CTID is also a special column available for every tables but not visible unless specifically mentioned. It consists of a page number and the index of an item identifier. OID stands for Object Identifier. database cluster , we call a database cluster the storage area on disk. A database cluster is a collection of databases that is managed by a single instance of a running database server. Page:            Inside the data file (heap table and index, as well as the free space ma
Read more