How to Migrate PostgreSQL to Oracle Database in 2 Methods?

The primary difference between PostgreSQL and Oracle is that PostgreSQL is an open-source, free-to-use object-relational database management system, while Oracle is a commercial database system. But besides that, other factors like performance, support, and business requirements are deciding which one to use.

PostgreSQL has a free large developer community to solve issues but it takes some time. It offers great scalability like clustered-based storage, and security features such as server communication encryption, or role-based permissions. And it is compatible with many operating systems including Linux, Windows, FreeBSD, macOS, etc.

But Oracle database dwarfs PostgreSQL in advanced security, processing speed, and technical superiority, so it outperforms the latter in large-scale environments when handling a massive amount of data.

The database also provides cross-platform integration on Windows, Linux, macOS, and others. Applications running on several platforms can easily communicate with one another due to the native networking stack of Oracle. Additionally, Oracle advanced analytics performs quick analytical calculations on company data.

Some PostgreSQL users may be looking for a way to convert PostgreSQL to Oracle. In this blog, you’ll learn the 2 migration methods in detail.

Migrate PostgreSQL to Oracle Database with Oracle SQL Developer

Prerequisites:

Download the latest SQL Developer from the Oracle website.

Download the PostgreSQL JDBC driver from here.

Grant required privileges to the Oracle user account.

1.     Launch the Oracle SQL Developer, go to Tools> Preferences> Data Modeler Third Party JDBC Drivers.

2.     Connect to the PostgreSQL database.

a.     Navigate to Connections> right-click the root node> New Connection.

b.   Click the PostgreSQL tab and input connection information, click Test. Then click Save.

3.     Connect to the new Oracle database.

a.      Navigate to Connections> right-click the root node> New Connection.

b.     Click the Oracle tab and input connection information, and click Test. Then click Connect.

The connection can be seen in the Connections tree view.

4.     In the Oracle SQL Developer, navigate to Tools> Migration> Migrate.

5.     The Migration Wizard will appear. On the Repository page, specify the connection for the migration repository, Test it, and click Save> Next. Create the migration repository and connect it to the database as follows if you don’t have one.

a.     Create an Oracle user (RESOURCE role) that contains default tablespace USERS and temporary tablespace TEMP. Grant it privileges: CREATE SESSION, CREATE VIEW, and CREATE MATERIALIZED VIEW.

b.     Establish a database connection with the database user called Migration Repository.

c.     Right-click the connection> Migration Repository> Associate Migration Repository.

6.     On the Project page, specify the migration project and click Next.

7.     On the Source Database page, set the Mode as Online, add (+) the PostgreSQL connection, and click Next.

8.     On the Capture page, select the database to be migrated and click Next.

9.     In the Convert tab, examine and modify the data type conversion rules, and click Next.

10.  In the Translate tab, select the desired items to be translated under Available SQL Objects and click Next.

11.  In the Target Database tab, select the Connection as Online, add (+) the Oracle connection, and enable Drop Target Objects to ensure that existing database objects in the target schema are cleared before migration.

12.  In the Move Data tab, specify table data migration options, if not, choose Offline. Click Next.

13.  On the Summary page, review all settings and click Finish.

14.  Validate the migrated data.

Compare the number of rows in the source database with the Oracle database;

Calculate the Oracle database's numerical columns' sum and compare them to those of the source database.

Migrate PostgreSQL to Oracle Database Manually

1.     Export data from the PostgreSQL table to a CSV file.

COPY table TO 'destination_path.csv' DELIMITER ',' CSV HEADER;

Or export data from columns of a table to the CSV file.

COPY table(column1,column2)

TO 'destination_path.csv' DELIMITER ',' CSV HEADER;

Exclude the header that includes the table column names.

COPY table(column)

TO 'destination_path.csv' DELIMITER ',' CSV;

Note: the CSV file must be located on the database server computer and allow the user of the PostgreSQL server to write it.

2.     Migration on a new table: Database> Import Data.

Migration on an existing table: Database Explorer> right-click the target table> Import Data.

3.     The Data Import Wizard will open up. Choose a CSV import format, specify the Source data location, and click Next.

4.     Go to Destination> mention the connection to Oracle> select the destination database and table> click Next.

5.     On the Options page, set the options for the file. Choose the line number for the table header and skipping, and specify a column delimiter, the result of which will be shown in the Preview section. Click Next.

6.     In the Data Formats tab, specify the formats of the Source data. Click Next.

7.     In the Mapping tab, map the columns to the columns of the Target Table, click Clear Mappings to cancel the mapping, and click Fill Mapping to restore them.

To import data to a new table, double-click the top grid to change the Target column, then check the Key box for a column with a primary key included and click Next.

8.     In the Modes tab, choose an import mode and click Next.

9.     In the Output tab, choose one to manage the import script and click Next.

Launch the internal editor and select the data import script.

The data import script can then be saved as a file.

Lastly, you can directly import data into the database.

10.  In the Errors Handling tab, select a strategy for handling import errors and decide to obtain a log file that contains information about the import session or not.

11.  Click Import to start PostgreSQL to Oracle migration and click Finish to close the wizard.

Database Backup Before the Data Migration

It is advised that create a complete backup of the source database before beginning the PostgreSQL to Oracle conversion to prevent data loss.

Vinchin Backup & Recovery delivers efficient and hassle-free database backup and recovery for Oracle Database, MySQL, SQL Server, PostgreSQL, Postgres Pro, and MariaDB in physical and virtual machines.

It supports various database versions for both PostgreSQL (12, 13, 14) and Oracle (11g 12c 18c 19c 21c) with multiple backup types including full, differential, incremental, and log backup (vary with databases).

Vinchin Backup & Recovery provides a plethora of strategies for simplified backup and recovery, like 50% of data reduction, data retention policy, delete archivelog, multithread transmission, and more.

The solution also prevents ransomware attacks with real-time IO monitoring that whitelists visitors to shut malicious actors out.

Wanna Vinchin Backup & Recovery for a spin? Click the 60-day free trial for the Enterprise version below.

 Take the PostgreSQL backup as an example.

1.     Go to Database Backup> Backup, and select the data source from the licensed database list.

2.     Select the target node and storage for the job.

3.     Set up desired backup strategies.

4.     View settings and click Submit.

Conclusion

Oracle database beats PostgreSQL in readily accessible support, data processing speed, and security mechanisms in enterprises under large environments. Some may consider converting PostgreSQL to Oracle for improvement.

This blog introduces two ways to migrate PostgreSQL to the Oracle database (see how to migrate Oracle to PostgreSQL) without any third-party conversion tools.

It is important to back up the source database before migration and the target after the process to ensure data integrity.