Oracle to PostgreSQL: How to Migrate Your Data
Migrate Oracle to PostgreSQL with Airbyte. What the pipeline moves and what it does not, LogMiner CDC limits, and the Enterprise connector requirement.
Moving Oracle to PostgreSQL is one of the most common cost-driven migrations in enterprise data, and one of the most frequently underestimated. The data movement is the easy half. What surrounds it, the PL/SQL, the packages, the sequences, is where the timeline actually goes.
Before anything else, one commercial fact: Airbyte's Oracle source is an Enterprise connector, available at additional cost and not offered on the Core, Standard, or Plus plans. If you are evaluating this on open source, that is the first thing to establish.
What does Airbyte actually migrate?
This is the expectation to set before you plan anything, because an Oracle to Postgres migration involves far more than rows.
Airbyte replicates table contents, not schema objects. That makes it the right tool for keeping Postgres current while you cut over, and the wrong tool for converting a schema. Dedicated schema conversion tooling handles that part, and the two approaches complement each other rather than competing.
How do you configure the Oracle source?
The Oracle source supports full refresh and incremental syncs, replicates incremental deletes, and operates read-only. It requires Oracle 23ai, 21c, or 19c, plus a dedicated read-only user with access to the tables you want.
For a migration you almost certainly want CDC, since it captures the deletions that happen while the old system is still live. CDC runs on Debezium over Oracle LogMiner, which means enabling ARCHIVELOG mode, setting an archive log retention period long enough to cover your sync interval, and enabling supplemental logging both at database level and on each table with ALTER TABLE ... ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;
The CDC user needs broad privileges including FLASHBACK ANY TABLE, SELECT ANY TABLE, SELECT_CATALOG_ROLE, EXECUTE_CATALOG_ROLE, SELECT ANY TRANSACTION, LOGMINING, CREATE TABLE, LOCK ANY TABLE, and CREATE SEQUENCE. On a multitenant CDB instance these need CONTAINER=ALL. Budget time for that conversation with the database owner, because it is more access than a reporting user normally gets.
Which LogMiner limits will bite you?
- Names over 30 characters are silently ignored. LogMiner skips tables and columns whose names exceed 30 characters, without raising an error. Query your data dictionary for long names before building anything, because a table that simply never appears is harder to diagnose than one that fails.
- Several data types cannot use CDC. BLOB, CLOB, NCLOB, RAW, LONG, ROWID, BFILE, and JSON columns are replicable in full refresh but not through CDC, as are user-defined types such as VARRAY. Legacy Oracle schemas often lean on these heavily, so audit early.
- Oracle DATE becomes a timestamp. The connector maps Oracle DATE to a timestamp rather than a Postgres date. Anything downstream assuming day granularity needs checking.
- Idle databases abort the sync. A heartbeat ends the sync if no change events arrive inside the waiting window, controlled by Initial Waiting Time in Seconds. It defaults to 1200 with a range of 120 to 3600.
There is one piece of good news on naming. Oracle's own 30-character identifier limit on older schemas sits comfortably inside PostgreSQL's 63-byte limit, so unlike migrations from systems with generous naming, you are unlikely to hit truncation collisions on the destination side.
Configuring the PostgreSQL destination
Add a Postgres destination with host, port, database, default schema, and a user with write permission. Airbyte Cloud connects over SSL or TLS by default. From version 3.0.0 the destination uses Direct Load, writing straight into final tables with no raw tables, and each table carries your columns plus _airbyte_raw_id, _airbyte_extracted_at, and an _airbyte_meta JSONB column.
On the Oracle side, choose an encryption scheme: unencrypted TCP, Native Network Encryption, or TLS with certificate verification. Airbyte Cloud only supports secured connections, so use one of the latter two or an SSH tunnel through a bastion.
One sizing note worth taking seriously. Airbyte's documentation recommends Postgres destinations for roughly 10GB or less. Oracle databases are frequently far larger than that, so if you are migrating a substantial estate, plan for a properly provisioned Postgres instance and watch memory, CPU, and IOPS during the initial load. If the goal is analytics rather than an application cutover, a warehouse destination is the better target.
Frequently asked questions
Is the Oracle source available on open source?
No. It is a premium Enterprise connector available at additional cost on Pro and Enterprise Flex, rather than Core, Standard, or Plus. Confirm availability for your plan before designing around it.
Will Airbyte convert my PL/SQL?
No. Airbyte moves table data. Packages, procedures, triggers, sequences, views, and constraints are a separate workstream, usually the largest one in an Oracle to Postgres migration. Use dedicated schema conversion tooling alongside the pipeline.
Why are some tables missing from my CDC sync?
Check name lengths first. LogMiner silently ignores tables and columns with names longer than 30 characters. Then confirm supplemental logging is enabled on each table you selected.
Can I replicate CLOB and BLOB columns?
In full refresh yes, with binary types arriving base-64 encoded. Through CDC no, because LogMiner does not support those types. Plan a separate full refresh strategy for large object columns.
Is Postgres a suitable destination for a large Oracle database?
For an application migration, yes, with a properly sized instance. Airbyte's own guidance suggests Postgres destinations suit around 10GB or less, so for larger estates provision carefully and monitor the initial load. For analytics, use a warehouse instead.
Get your Oracle data into PostgreSQL
Confirm the Enterprise connector is available to you, audit your schema for long names and unsupported types, and treat schema conversion as its own project. Sending Oracle data to an analytical store instead? See our guide to Oracle to ClickHouse. Migrating from a different database? See MySQL to PostgreSQL.
Integrate with 600+ apps using Airbyte
Move data from 600+ sources into warehouses, lakes, and beyond. Set up pipelines in minutes with pre-built connectors and the Connector Builder.