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.