For years Oracle database server is known as highly advanced, reliable and powerful DBMS suitable for any size of data warehouses. Of course, it is costly to obtain the license, especially for large corporate infrastructure including multiple database servers. This is a primary reason for some organizations to consider Oracle to Postgres migration.
Why PostgreSQL?
Database executive or another individual accountable for database development should anticipate that new framework that offers a proportionate arrangement of administrations when contrasted with the essential DBMS. When talking about Oracle database, it is clear that none of the other social database administration frameworks is so stuffed with effective properties, for example,
- extensive reinforcement
- multi-level pressure
- flexible capacity customization
As a strong and standard-affirmed RDBMS that coordinates question arranged and social database highlights, PostgreSQL gets a ton nearer to Oracle than different projects. Here is the short rundown of its propelled highlights:
- asynchronous replication
- multi-form simultaneousness control
- nested exchanges
- point-in-time recuperation
- sophisticated bolting instrument
Those points of interest make PostgreSQL an extraordinary answer for complex ventures requiring high reliability and information respectability i.e. the best choice to Oracle database.
Steps of Migration
The strategy of Oracle to Postgres migration consists of the following stages:
- export Oracle table definitions into “CREATE TABLE” proclamations
- make these SQL-guidelines accommodated with PostgreSQL configuration and load to the objective server
- export Oracle information into moderate stockpiling, for example, CSV records
- convert it into the objective configuration (if fundamental) and import in PostgreSQL database
- export Oracle sees, triggers, put away systems and capacities into SQL proclamations and plain content source code
- transform these announcements and code as indicated by PostgreSQL sentence structure and load to the objective server
Migration of Schemas
Underneath this classification, we consider the fundamental method for Oracle to Postgres migration of. schemas Anyplace beneath SQL*Plus is utilized as default Oracle customer application. Open the source database using SQL*Plus as follows:
sqlplus username/password@database
Let us start with extracting all table names:
SQL> select table_name from user_tables;
Next step is to get definition (DDL statement) of the specific Oracle table providing there is enough screen buffer size to fit it:
- SQL> set long 1000
- SQL> set pagesize 0
- SQL> select DBMS_METADATA.GET_DDL(‘TABLE’,'<TABLE NAME>'[,’SCHEMA’]) from DUAL
The extracted DDL statement must be transformed to comply with PostgreSQL format as follows:
- Remove Oracle particular proclamations toward the finish of table DDL (beginning from “Utilizing INDEX PCTFREE…”)
- convert all information sorts into PostgreSQL counterparts as per this table
Data Migration
The subsequent stage is to send out Oracle information into CSV design. It should be possible by means of the accompanying grouping of orders:
- SQL> set taking off
- SQL> spool filename.CSV
- SQL> select column1 || “,” || column2 || … from my table;
- SQL> set colsep “,”
- SQL> select * from my_table;
- SQL> spool off;
The resulting CSV records can be loaded into existing PostgreSQL table through “COPY” operator:
COPY <table name> FROM <path to CSV file> DELIMITER “,” CSV;
When there is “Authorization denied” blunder subsequent to running this announcement, attempt to utilize “\COPY” charge.
Migration of Indexes
How about we get a rundown of all files that have a place with table “mytable”:
- SQL> select * from all_indexes where table_name = ‘<TABLE NAME>’;
It is important to realize that Oracle stores all database objects names in capitalized manner by default. To use lower case names, it requires to enclose them in quotes inside “CREATE TABLE” statements.
Also, this is the manner by which meaning of specific files can be extricated:
- SQL> set long 1000
- SQL> set pagesize 0
- SQL> select DBMS_METADATA.GET_DDL(‘INDEX’,'<INDEX NAME>’) from DUAL;
- Oracle to PostgreSQL Transformation Tools
The strategies clarified above exhibit that Oracle to Postgres migration is a perplexing procedure. Manual migration requires a great deal of work and it can likewise cause loss of information or defilement because of the human factor. However, it is reasonable to consider some extraordinary instruments that can automate the migration with just a couple of snaps of the mouse. Intelligent Converters software company has made the related tool for Oracle to Postgres migration. It can mechanize development of the accompanying database objects:
- Table definitions
- Data
- Indexes and imperatives
- Foreign keys
- Views
