CYBERTEC offers comprehensive services to move from Oracle to PostgreSQL. Intelligent tooling is available on our website. We want to help you to move to PostgreSQL faster. This quick-start migration guide will get you rapidly moving in the right direction.
There are various aspects you need to take into consideration when moving from Oracle to PostgreSQL, which include:
Let’s take a look at these vital points in detail.
The first thing you will see is that PostgreSQL has many more data types than Oracle. It is therefore important to make the right choices. Selecting the right data type can massively impact performance.
If you are not sure how to map Oracle data types to PostgreSQL, we have compiled a conversion matrix. The matrix shows you which types can be mapped to what counterpart.
| Oracle type | Possible PostgreSQL type |
|---|---|
| CHAR | char, varchar, text |
| NCHAR | char, varchar, text |
| VARCHAR | char, varchar, text |
| VARCHAR2 | char, varchar, text, json, jsonb |
| NVARCHAR2 | char, varchar, text |
| CLOB | char, varchar, text, json, jsonb |
| LONG | char, varchar, text |
| RAW | uuid, bytea |
| BLOB | bytea |
| BFILE | bytea (read-only) |
| LONG RAW | bytea |
| NUMBER | numeric, float4, float8, char, varchar, text |
| NUMBER (n, m) with m<=0 | numeric, float4, float8, int2, int4, int8, boolean, char, varchar, text |
| FLOAT | numeric, float4, float8, char, varchar, text |
| BINARY_FLOAT | numeric, float4, float8, char, varchar, text |
| BINARY_DOUBLE | numeric, float4, float8, char, varchar, text |
| DATE | date, timestamp, timestamptz, char, varchar, text |
| TIMESTAMP | date, timestamp, timestamptz, char, varchar, text |
| TIMESTAMP WITH TIME ZONE | date, timestamp, timestamptz, char, varchar, text |
| TIMESTAMP WITH LOCAL TIME ZONE | date, timestamp, timestamptz, char, varchar, text |
| INTERVAL YEAR TO MOTH | interval, char, varchar, text |
| INTERVAL DAY TO SECOND | interval, char, varchar, text |
| XML TYPE | xml, char, varchar, text |
| MDSYS.SDO_GEOMETRY | geometry (see “PostGIS support” below) |
Ideally, you will decide on a data type that provides the least overhead. What does that mean? Here is an example: number(10, 0) can be mapped to numeric(10, 0) but you can also map it to int8. Using int8 is usually a lot more efficient. Int8 will definitely eat 8 bytes – however, calculations are a lot faster if you use int8 rather than numeric.
varchar2 can be mapped directly to varchar or the text data type. However, we have found it useful to consider the “citext” extension in PostgreSQL. The idea behind citext is to have a case insensitive text data type, which makes searching a lot more convenient. In web applications, case insensitive search is usually what you are looking for. If you are generally interested in fuzzy search, consider checking out our blog post about this exceptionally important topic. A migration is usually a good chance to clean up and improve things.
BLOBs are also an issue many people keep asking about. In general, PostgreSQL offers the bytea data type (“byte array”). It feels like a text column but it can actually hold binary data. If you don’t want to use a column to store BLOBs, it is also possible to use the PostgreSQL BLOB interface directly.
NOTE: What is important to mention here is that Oracle Spatial can be mapped to PostGIS and PostgreSQL. So if you are using GIS data there is a way to migrate to PostgreSQL as well. There are no practical limitations in that case, either.
To map data types automatically, consider checking out CYBERTEC Migrator. It has advanced data type mapping capabilities and offers an easy way to predict the best possible data type to store large volumes of data in PostgreSQL.
Oracle provides the CREATE SYNONYM command which is not available in PostgreSQL. Some time ago, we at CYBERTEC implemented CREATE SYNONYM for PostgreSQL, but this modification was rejected because it does not fit into the overall concept of PostgreSQL.
SELECT * …)SELECT * …)In general, it is better to get rid of these synonyms during migration. It makes future development a lot easier. Setting the search_path has proven to be generally useful, and often reduces the need to use synonyms in the first place.
When migrating data structures from Oracle to PostgreSQL, you will notice that PostgreSQL uses strict dependency tracking. Let me provide you with an example: If a table is dropped, PostgreSQL will automatically drop all of its dependent objects. In Oracle this is not the case. You might have to deal with invalid views, which is an issue you should be aware of during a migration project.
Once the data structures found in Oracle have been moved to PostgreSQL, it is time to think about moving the data in general.
A lot of stuff can be done by the migration tool, but there are some things that need some attention on the Oracle database side as well. Here are some of the most important aspects:
As previously stated, most of the above problems cannot easily be solved during the migration process, but need some pre-treatment on the source side. Of course, the migration tool is able to fix many issues, but not all of them. You need to be aware of that.
When data is read from Oracle, it has to be done in a single transaction to ensure consistency (or at least in many transactions capable of seeing the same snapshot of data as it is done by the CYBERTEC Migrator). To achieve this, you will end up running a long transaction on Oracle. This can be a problem in and of itself, because Oracle terminates a transaction in the case of “ORA-01555: snapshot too old”.
The solution is to configure it so that it can handle very long transactions before you start the migration of data. The way to do this is to use really large UNDO tablespaces.
Moving data is also a bit tricky. In Oracle, you will find a couple of restrictions in the OCI interface which can lead to very poor performance when large fields are migrated to PostgreSQL. Those limitations and performance issues are caused by the way Oracle reads data, and in many cases, client code cannot really do much about them.
In Oracle, the concept of a “tablespace” is highly important. One could argue that Oracle is basically implementing its own filesystem. In contrast to that, PostgreSQL relies more heavily on operation system functionality (POSIX). Resist the urge to create tablespaces during migration. Tablespace does not make much sense in the PostgreSQL world.
Additionally, in a modern cloud environment, tablespaces are mostly a thing of the past. So if you are creating a new infrastructure for PostgreSQL on Kubernetes, such as provided by Scalefield, there is no need to use tablespaces at all.
Oracle has some specialities in its flavor of SQL which have to be considered when migrating code from Oracle to PostgreSQL. This section shows some of the most common issues people encounter during their migrations.
Oracle uses a different syntax for outer joins than the rest of the world. You should keep that in mind when converting code from Oracle to PostgreSQL. The following example shows how this works.
Oracle
SELECT b.col1, a.col2
FROM base_table b, attributes a
WHERE b.id=a.b_id(+);
has to be translated to
PostgreSQL
SELECT b.col1, a.col2
FROM base_table b
LEFT JOIN attributes a ON b.id = a.b_id;
The change is actually easy, but quite annoying to do.
In Oracle a subselect does not need a name. It can be “anonymous”:
SELECT * FROM (SELECT * FROM some_table)
You can easily rewrite this in PostgreSQL. The following code shows what the counterpart in the Open Source work would actually look like:
SELECT * FROM (SELECT * FROM some_table) AS your_fancy_name
Oracle treats NULL as an empty string. This has a couple of implications:'hello' || NULL is not NULL in Oracle. Therefore, it has to be translated to the following SQL fragment in order to have the same semantics in PostgreSQL:
concat('hello', NULL)
or usecoalesce(strcol, '')
Migrating null statements can lead to subtle bugs that can be hard to track down. You should keep a sharp eye out for such details and explicitly check for them.
Most Oracle code uses proprietary functions such as …
SYSDATESYSTIMESTAMPIt’s necessary to translate those calls to suit PostgreSQL:
clock_timestamp()current_date or current_timestampFortunately, this change can easily be done. Search and replace is usually enough.
Oracle databases handle sequences quite differently. You’ll see the following method to produce the next value:
asequence.NEXTVAL
The PostgreSQL equivalent is:
nextval('asequence')
Both databases are not using the ANSI SQL way of fetching values from a sequence which would look like this: NEXT VALUE FOR asequence
In PostgreSQL, a query does not necessarily need a FROM-clause. It is perfectly fine not to use one. Therefore there is no need for DUAL.
SELECT * FROM dual;
… is not necessary in PostgreSQL. orafce can simulate DUAL, but it is better to avoid it on the PostgreSQL side. There is simply no need for it.
DECODE is an Oracle-specific thing:
decode(expr, search, result [, search, result...] [, default])
The way to change things in PostgreSQL is to …
Plain PostgreSQL works as follows:
CASE WHEN expr THEN expr [...] ELSE expr END
In general, DECODE can be replaced quite easily. It does not contain complex logic.
In Oracle, there are two ways to handle recursions:
WITH RECURSIVE: New syntax
CONNECT BY: Old syntax
PostgreSQL has a full WITH RECURSIVE implementation. The old CONNECT BY syntax is highly Oracle-specific and cannot be used in PostgreSQL (or any other SQL database engine we are aware of).
Here is an example showing how CONNECT BY can be translated to WITH RECURSIVE:
SELECT empno, ename, level
FROM emp
START WITH empno = 7788
CONNECT BY PRIOR mgr = empno;
… versus …
WITH hierarchy (empno, ename, mgr, "level") AS (
SELECT empno, ename, mgr, 1 AS "level"
FROM emp
WHERE empno = 7788
UNION ALL
SELECT e.empno, e.ename, e.mgr, h."level" + 1
FROM hierarchy h
JOIN emp e ON h.mgr = e.empno
) SELECT empno, ename, "level" FROM hierarchy;
The most expensive part of every migration is to migrate the stored procedure code. While PL/pgSQL and PL/SQL are pretty similar, there are still differences that have to be taken into account. If you are using PL/Java on the Oracle side, you can make use of the PostgreSQL counterpart but we would suggest to rather replace the code completely. Running server-side Java is not too common in the PostgreSQL world.
PL/pgSQL is a clone of PL/SQL, but it is sufficiently different to require some attention (e.g., RETURNS vs.RETURN). The CYBERTEC Migrator has the means to automatically convert code.
PostgreSQL has no such thing as PRAGMA AUTONOMOUS_TRANSACTION. One has to work around this missing functionality. In general, it is better to replace autonomous transactions with savepoints and try to make sure that transactions actually can commit. In some rare cases, there is no way to work around autonomous transactions. The way to model that in PostgreSQL is to use the dblink extension.
BULK COLLECT is not available on the PostgreSQL side. The way to work around this in PostgreSQL is to use a row-by-row approach.
Be careful in this case. Efficiency might become an issue. It is therefore better to use SQL statements rather than loops.
In many cases PostgreSQL offers the same functionality as Oracle. However, often the names of functions are simply different from each other.
The orafce extension for PostgreSQL implements many of those Oracle-specific functions in PostgreSQL. By using the extension, the number of manual code changes can be reduced dramatically.
The following listing shows some code samples and shows how things will be exposed to the end user:
SELECT last_day('2000-02-01 121143');
SELECT next_day ('2008-01-01 121212', 'sunAAA');
SELECT months_between ('2007-02-28 111111', '2007-04-30 112121');
select dbms_pipe.purge('bob');
select instr('Tech on the net', 'e') = 2;
orafce has been around for many years and is mature. It can be relied upon.
In PostgreSQL, there is no concept of PL/SQL packages. However, there are ways to simulate packages. There are various ways to approach the problem:
You will figure out that packages are not really an issue, and that you can easily live without them on the PostgreSQL side.
In PostgreSQL, a trigger will always call a function. The CREATE TRIGGER command does not contain the code directly:
demo=# \h CREATE TRIGGER
Command: CREATE TRIGGER
Description: define a new trigger
Syntax:
CREATE [ CONSTRAINT ] TRIGGER name { BEFORE | AFTER | INSTEAD OF } { event [ OR ... ]
}
ON table_name
[ FROM referenced_table_name ]
[ NOT DEFERRABLE | [ DEFERRABLE ] [ INITIALLY IMMEDIATE | INITIALLY DEFERRED ]
]
[ REFERENCING { { OLD | NEW } TABLE [ AS ] transition_relation_name } [ ... ] ]
[ FOR [ EACH ] { ROW | STATEMENT } ]
[ WHEN ( condition ) ]
EXECUTE { FUNCTION | PROCEDURE } function_name ( arguments )
where event can be one of:
INSERT
UPDATE [ OF column_name [, ... ] ]
DELETE
TRUNCATE
The beauty on the PostgreSQL side is that the code can easily be reused. What is also noteworthy here is that triggers to increment values are not needed. You can simply use DEFAULT columns to automatically determine the next value and set it.
LOGON triggers are not supported in PostgreSQL. If you need this type of functionality, it is necessary to shift the code to the application, or avoid the thing altogether.
We at CYBERTEC have helped countless customers to migrate from Oracle to PostgreSQL. Furthermore, we offer the CYBERTEC Migrator to faciliatate the transition. Contact us today an receive your personal migration offer. We offer over 20 years of PostgreSQL experience, a professional handling and a timely delivery.
+43 (0) 2622 93022-0
office@cybertec.at
You are currently viewing a placeholder content from Facebook. To access the actual content, click the button below. Please note that doing so will share data with third-party providers.
More InformationYou are currently viewing a placeholder content from X. To access the actual content, click the button below. Please note that doing so will share data with third-party providers.
More Information