Migrating from Oracle to PostgreSQL has become a common route for teams looking to escape license and support costs, as well as Oracle's technical constraints. The same pattern shows up with MS SQL and other commercial platforms, but Oracle-to-PostgreSQL remains the most traveled path. What follows are the lessons that surfaced while building migration tooling over several years — the errors worth avoiding before you repeat them.
Transactions at scale
Transaction length is a non-issue when you are moving one or two gigabytes. At 10+ TB, and with downtime off the table for many clients, it becomes the central design constraint. Close-to-zero downtime requires change data capture: take an initial snapshot, then replay changes as PostgreSQL catches up with the source.
That means copying enormous volumes while the source keeps changing underneath you. On the Oracle side this quickly exposes a well-known problem:
PgSQL
|
1 |
ORA-01555 Error Message “Snapshot Too Old” |
Anyone with migration experience will recognize it. Oracle has to be configured to handle real transactions, not just short read bursts. It is worth teaching the migrator to check for these showstoppers up front. A second Oracle-side issue also matters:
PgSQL
|
1 |
ORA-08177: can't serialize access for this transaction |
PostgreSQL supports high transaction isolation levels properly; Oracle does not. Firing up a SERIALIZABLE transaction and expecting it to just work will not end well. The read path needs foresight too, or Oracle's limitations become obvious fast. Reading many terabytes is simply not the same as reading a handful of rows.
The Oracle catalog is slow
PostgreSQL users are spoiled here: catalog queries are usually fast, and the vast majority have never had to think about system catalog performance at all. Building a toolchain such as the CYBERTEC Migrator is different — the Oracle catalog must be queried extensively and examined closely, and its performance is a recurring shock. The consequences for a migration tool:
- The GUI must avoid reading the Oracle catalog wherever possible
- A cached copy of the data should be kept around, in memory or in PostgreSQL
Any graphical interface that depends on Oracle system catalog performance has a doomed user experience; the application becomes unusable. This is especially visible when the number of objects to be migrated is high.
Expect INVALID objects
PostgreSQL stores views and similar objects in binary form, which pays off in ways Oracle users may not expect:
PgSQL
|
1 2 3 4 5 6 |
test=# CREATE TABLE a (aid int); CREATE TABLE test=# CREATE TABLE b (bid int); CREATE TABLE test=# CREATE VIEW v AS SELECT * FROM a, b; CREATE VIEW |
Two tables and a view joining them — all straightforward, and it works in Oracle as well. Now rename a to c:
PgSQL
|
1 2 |
test=# ALTER TABLE a RENAME TO c; ALTER TABLE |
In PostgreSQL the view's name is just a label; underneath, everything is a number. The view is not stored as a string but as a binary representation, so:
PgSQL
|
1 2 3 4 5 6 7 8 9 10 11 |
test=# d+ v View 'public.v' Column | Type | Collation | Nullable | Default | Storage | Description --------+---------+-----------+----------+---------+---------+------------- aid | integer | | | | plain | bid | integer | | | | plain | View definition: SELECT c.aid, b.bid FROM c, b; |
The view stays valid and picks up the new name automatically. Oracle behaves the opposite way — you are left with an INVALID view. A migration tool must be prepared for this at all times, because many Oracle object types can go invalid. Excluding and marking them requires extra logic. Without it, the user experience suffers again: the replication process keeps failing on stale, invalid objects and has to be retried endlessly.
Broken rows and null bytes
Across countless migrations, the same problem recurs: unusable data coming out of Oracle. Two errors dominate — null bytes and broken data. Null bytes have to be excluded, but that is not the end of the story. In industries including finance, changes to data must be documented so regulators can trace what happened to information in transit. Simply dropping obviously broken rows is not acceptable.
Those rows have to be captured and documented. Sometimes transformation rules are needed as well. The goal is revision-safe rules that describe what happened to the data — this is vital for the migration to be accepted. And you cannot keep migrating terabytes just to rediscover that some data will not load. What is needed is a kind of dry run, plus a "find me all the broken data" pass, so the tooling stays usable.
PostgreSQL's handling of COPY in error cases does not help much either. Several mechanisms are needed to guarantee that the transaction loading the data ALWAYS commits, even when a lot of data is loaded at once.
Loading data without excess I/O
Every relational database, PostgreSQL and Oracle alike, writes a WAL when loading data. The consequence is easy to overlook: data is written twice, and that doubled I/O can consume up to 40% of the total time needed to load data into PostgreSQL.
There is more. The first read after a write is usually also a write — a plain SELECT can be a write operation, because of hint bits. Loading data therefore does not mean the disk writes are finished; terabytes of I/O can remain once the migration is over, tanking performance right after the switch. That has to be avoided. Loading must be done intelligently, for example with COPY FREEZE, and with general awareness of hint bits. Tuning during migration demands knowledge, but it pays off: I/O can often be reduced drastically, and when PostgreSQL replication is added later, the load speeds up considerably.
Datatype mapping is not cosmetic
Oracle's set of datatypes differs substantially from PostgreSQL's, so mapping matters for two reasons. First, efficiency: not all datatypes are equal — integer beats numeric, and boolean is far smaller than integer. Second, target expectations: tooling has to account for what the destination schema should look like.
A migration is a chance to do cleanup
Do not miss it. Legacy messes can be removed, unneeded objects dropped, and structures fixed with more suitable and efficient datatypes.
Toolmaker's view
The differences between Oracle and PostgreSQL, and how each nuance should be migrated, could fill far more space. These notes approach the subject from the toolmaker's side rather than the perspective of someone performing a single migration.
