Oracle to PostgreSQL Data Migration Using CSV

Oracle to PostgreSQL Data Migration Using CSV

1. Overview

CSV-based migration is one of the simplest and most transparent ways to move table data from Oracle to PostgreSQL. It is useful when the source of data volume is manageable, when a dependency-free method is preferred, or when teams need a repeatable runbook for controlled data movement.

This document explains the end-to-end flow in a blog-friendly format. The sample object names are generic, so the steps can be reused safely without exposing internal or client-specific details.

2. High-Level Migration Flow

Step	Activity	Purpose	Main Output
1	Export from Oracle	Extract selected rows into a CSV file	CSV file
2	Transfer file	Move the export file to the PostgreSQL server securely	CSV or compressed CSV
3	Prepare PostgreSQL table	Create a matching target structure	Target table
4	Import and validate	Load data using COPY and verify results	Loaded and validated data

3. Prerequisites

• Oracle database access with read permission on the source table.

• PostgreSQL database access with permission to create tables and load data.

• Sufficient disk space on both source and target servers for the CSV file.

• Network connectivity between source and target servers for secure file transfer.

• Agreement on date filters, column mapping, data types, and expected row count.

• A rollback or cleanup plan in case validation fails.

4. Step 1: Export Data from Oracle

Start by exporting only the required columns and date range. Keeping the export focused reduces file size, improves load performance, and makes validation easier.

In SQL*Plus, CSV formatting can be enabled so that the result is written directly to a structured CSV file.

sqlplus <source_user>/<password>@<source_service_name>

SET HEADING ON

SET FEEDBACK OFF

SET TERMOUT OFF

SET MARKUP CSV ON DELIMITER ',' QUOTE ON

SPOOL /path/to/export/source_table_month.csv

SELECT column_1,

       column_2,

       event_timestamp,

       numeric_value,

       created_timestamp,

       status_code,

       group_id

FROM <source_schema>.<source_table>

WHERE event_timestamp >= TO_DATE('<start_date>', 'YYYY-MM-DD')

  AND event_timestamp <  TO_DATE('<end_date>',   'YYYY-MM-DD');

 

SPOOL OFF

EXIT;

Why this step matters

• Column-level selection avoids exporting unnecessary data.

• A date range filter makes the extract repeatable and easier to reconcile.

• CSV quoting helps preserve values that contain commas or special characters.

• The spool output becomes the migration input for PostgreSQL.

5. Optional Step: Compress the Export File

If the CSV file is large, compress it before transferring. Compression reduces transfer time and lowers the chance of network interruption during file movement.

gzip /path/to/export/source_table_month.csv

After compression, the file name will usually become source_table_month.csv.gz.

6. Step 2: Transfer the File Securely

Move the CSV or compressed CSV file from the Oracle server to the PostgreSQL server using an approved secure transfer method such as SCP, SFTP, or an enterprise file transfer tool.

scp /path/to/export/source_table_month.csv.gz <target_user>@<target_host>:/path/to/target/folder/

Recommended checks after transfer

• Confirm that the file exists on the target server.

• Compare file size between source and target.

• If checksum validation is available, compare checksum values.

• Unzip the file before loading if PostgreSQL COPY is reading the plain CSV file.

gunzip /path/to/target/folder/source_table_month.csv.gz

7. Step 3: Prepare the Target Table in PostgreSQL

Before loading the CSV file, create the target table with data types that match the source data. This step is important because Oracle and PostgreSQL data types are not always one-to-one matches.

psql -h <target_host> -U <target_user> -d <target_database>

CREATE TABLE <target_schema>.<target_table> (

    column_1           VARCHAR(100),

    column_2           VARCHAR(100),

    event_timestamp    TIMESTAMP,

    numeric_value      NUMERIC,

    created_timestamp  TIMESTAMP,

    status_code        VARCHAR(50),

    group_id           INTEGER

);

Data type mapping guidance

Oracle Type	PostgreSQL Type	Comment
VARCHAR2	VARCHAR or TEXT	Use VARCHAR when a known limit is required.
NUMBER	NUMERIC, INTEGER, BIGINT	Choose based on precision, scale, and expected values.
DATE	TIMESTAMP or DATE	Oracle DATE can include time, so validate carefully.
TIMESTAMP	TIMESTAMP	Confirm timezone handling requirements separately.

8. Step 4: Import CSV Data into PostgreSQL

PostgreSQL COPY is a fast and commonly used method to load CSV data into a table. The file path must be accessible to the PostgreSQL server process.

COPY <target_schema>.<target_table> (

    column_1,

    column_2,

    event_timestamp,

    numeric_value,

    created_timestamp,

    status_code,

    group_id

)

FROM '/path/to/target/folder/source_table_month.csv'

WITH (

    FORMAT csv,

    HEADER true,

    DELIMITER ',',

    QUOTE '"',

    ESCAPE ''''

);

Common import issues to check

• File access issue: PostgreSQL must be able to read the file path.

• Timestamp format mismatch: confirm exported timestamps match PostgreSQL expectations.

• Delimiter or quote issue: validate the CSV configuration if rows fail to load.

• Numeric conversion issue: check blank values, special characters, and precision requirements.

9. Step 5: Validate the Data Load

Validation confirms whether the migration was completed correctly. At minimum, compare row counts, sample records, timestamp boundaries, and numeric values.

-- Check total row count

SELECT COUNT(*) AS target_row_count

FROM <target_schema>.<target_table>;

-- Check timestamp range

SELECT MIN(event_timestamp) AS min_event_time,

       MAX(event_timestamp) AS max_event_time

FROM <target_schema>.<target_table>;

-- Review sample records

SELECT *

FROM <target_schema>.<target_table>

ORDER BY event_timestamp

LIMIT 10;

Validation	Expected Result	Action if Failed
Row count	Target count matches source count	Review filters, rejected rows, and duplicate load attempts.
Date range	Minimum and maximum timestamp are within expected range	Check Oracle WHERE clause and timestamp formatting.
Data sampling	Sample values match source records	Compare source and target for selected keys.
Numeric accuracy	Decimal and integer values are preserved	Review NUMERIC precision and CSV formatting.

10. Step 6: Post-Migration Cleanup

Once validation is successful, complete the housekeeping activities so that the environment remains clean and secure.

• Archive the CSV file only if required by the retention policy.

• Remove temporary files from shared paths after approval.

• Document row counts, validation of queries, and completion time.

• Apply indexes or constraints after the bulk load if that was part of the migration plan.

• Notify application or reporting teams that the dataset is ready for use.

11. Summary

This blog provides a simple and repeatable approach for moving Oracle table data to PostgreSQL using CSV files. The key success factors are a controlled export query, secure file transfer, correct target table design, reliable COPY import, and strong post-load validation.