backend/postgresql_docs.txt - master - romanchel1s/sql-translator

backend/postgresql_docs.txt
2 114 строк74 KB
Roman Purtov
fix: back
01 сен 2024, 11:57
01 сен 2024, 11:577662cf4
![PostgreSQL Elephant Logo](/media/img/about/press/elephant.png)

  * Home
  * About
  * Download
  * Documentation
  * Community
  * Developers
  * Support
  * Donate
  * Your account

__

August 8th, 2024:  PostgreSQL 16.4, 15.8, 14.13, 13.16, 12.20, and 17 Beta 3
Released!

Documentation → PostgreSQL 14

Supported Versions: Current (16) / 15 / 14 / 13 / 12

Development Versions: 17 / devel

Unsupported versions: 11 / 10 / 9.6 / 9.5 / 9.4 / 9.3 / 9.2 / 9.1 / 9.0 / 8.4
/ 8.3 / 8.2 / 8.1 / 8.0 / 7.4 / 7.3 / 7.2 / 7.1

__

43.13. Porting from Oracle PL/SQL  
---  
Prev | Up | Chapter 43. PL/pgSQL — SQL Procedural Language | Home |  Next  
  
* * *

## 43.13. Porting from Oracle PL/SQL

43.13.1. Porting Examples

43.13.2. Other Things to Watch For

43.13.3. Appendix

This section explains differences between PostgreSQL's PL/pgSQL language and
Oracle's PL/SQL language, to help developers who port applications from
Oracle® to PostgreSQL.

PL/pgSQL is similar to PL/SQL in many aspects. It is a block-structured,
imperative language, and all variables have to be declared. Assignments,
loops, and conditionals are similar. The main differences you should keep in
mind when porting from PL/SQL to PL/pgSQL are:

  * If a name used in an SQL command could be either a column name of a table used in the command or a reference to a variable of the function, PL/SQL treats it as a column name. By default, PL/pgSQL will throw an error complaining that the name is ambiguous. You can specify `plpgsql.variable_conflict` = `use_column` to change this behavior to match PL/SQL, as explained in Section 43.11.1. It's often best to avoid such ambiguities in the first place, but if you have to port a large amount of code that depends on this behavior, setting `variable_conflict` may be the best solution.

  * In PostgreSQL the function body must be written as a string literal. Therefore you need to use dollar quoting or escape single quotes in the function body. (See Section 43.12.1.)

  * Data type names often need translation. For example, in Oracle string values are commonly declared as being of type `varchar2`, which is a non-SQL-standard type. In PostgreSQL, use type `varchar` or `text` instead. Similarly, replace type `number` with `numeric`, or use some other numeric data type if there's a more appropriate one.

  * Instead of packages, use schemas to organize your functions into groups.

  * Since there are no packages, there are no package-level variables either. This is somewhat annoying. You can keep per-session state in temporary tables instead.

  * Integer `FOR` loops with `REVERSE` work differently: PL/SQL counts down from the second number to the first, while PL/pgSQL counts down from the first number to the second, requiring the loop bounds to be swapped when porting. This incompatibility is unfortunate but is unlikely to be changed. (See Section 43.6.5.5.)

  * `FOR` loops over queries (other than cursors) also work differently: the target variable(s) must have been declared, whereas PL/SQL always declares them implicitly. An advantage of this is that the variable values are still accessible after the loop exits.

  * There are various notational differences for the use of cursor variables.

### 43.13.1. Porting Examples

Example 43.9 shows how to port a simple function from PL/SQL to PL/pgSQL.

**Example 43.9. Porting a Simple Function from PL/SQL to PL/pgSQL**

Here is an Oracle PL/SQL function:

    
    
    CREATE OR REPLACE FUNCTION cs_fmt_browser_version(v_name varchar2,
                                                      v_version varchar2)
    RETURN varchar2 IS
    BEGIN
        IF v_version IS NULL THEN
            RETURN v_name;
        END IF;
        RETURN v_name || '/' || v_version;
    END;
    /
    show errors;
    

Let's go through this function and see the differences compared to PL/pgSQL:

  * The type name `varchar2` has to be changed to `varchar` or `text`. In the examples in this section, we'll use `varchar`, but `text` is often a better choice if you do not need specific string length limits.

  * The `RETURN` key word in the function prototype (not the function body) becomes `RETURNS` in PostgreSQL. Also, `IS` becomes `AS`, and you need to add a `LANGUAGE` clause because PL/pgSQL is not the only possible function language.

  * In PostgreSQL, the function body is considered to be a string literal, so you need to use quote marks or dollar quotes around it. This substitutes for the terminating `/` in the Oracle approach.

  * The `show errors` command does not exist in PostgreSQL, and is not needed since errors are reported automatically.

This is how this function would look when ported to PostgreSQL:

    
    
    CREATE OR REPLACE FUNCTION cs_fmt_browser_version(v_name varchar,
                                                      v_version varchar)
    RETURNS varchar AS $$
    BEGIN
        IF v_version IS NULL THEN
            RETURN v_name;
        END IF;
        RETURN v_name || '/' || v_version;
    END;
    $$ LANGUAGE plpgsql;
    

  

Example 43.10 shows how to port a function that creates another function and
how to handle the ensuing quoting problems.

**Example 43.10. Porting a Function that Creates Another Function from PL/SQL
to PL/pgSQL**

The following procedure grabs rows from a `SELECT` statement and builds a
large function with the results in `IF` statements, for the sake of
efficiency.

This is the Oracle version:

    
    
    CREATE OR REPLACE PROCEDURE cs_update_referrer_type_proc IS
        CURSOR referrer_keys IS
            SELECT * FROM cs_referrer_keys
            ORDER BY try_order;
        func_cmd VARCHAR(4000);
    BEGIN
        func_cmd := 'CREATE OR REPLACE FUNCTION cs_find_referrer_type(v_host IN VARCHAR2,
                     v_domain IN VARCHAR2, v_url IN VARCHAR2) RETURN VARCHAR2 IS BEGIN';
    
        FOR referrer_key IN referrer_keys LOOP
            func_cmd := func_cmd ||
              ' IF v_' || referrer_key.kind
              || ' LIKE ''' || referrer_key.key_string
              || ''' THEN RETURN ''' || referrer_key.referrer_type
              || '''; END IF;';
        END LOOP;
    
        func_cmd := func_cmd || ' RETURN NULL; END;';
    
        EXECUTE IMMEDIATE func_cmd;
    END;
    /
    show errors;
    

Here is how this function would end up in PostgreSQL:

    
    
    CREATE OR REPLACE PROCEDURE cs_update_referrer_type_proc() AS $func$
    DECLARE
        referrer_keys CURSOR IS
            SELECT * FROM cs_referrer_keys
            ORDER BY try_order;
        func_body text;
        func_cmd text;
    BEGIN
        func_body := 'BEGIN';
    
        FOR referrer_key IN referrer_keys LOOP
            func_body := func_body ||
              ' IF v_' || referrer_key.kind
              || ' LIKE ' || quote_literal(referrer_key.key_string)
              || ' THEN RETURN ' || quote_literal(referrer_key.referrer_type)
              || '; END IF;' ;
        END LOOP;
    
        func_body := func_body || ' RETURN NULL; END;';
    
        func_cmd :=
          'CREATE OR REPLACE FUNCTION cs_find_referrer_type(v_host varchar,
                                                            v_domain varchar,
                                                            v_url varchar)
            RETURNS varchar AS '
          || quote_literal(func_body)
          || ' LANGUAGE plpgsql;' ;
    
        EXECUTE func_cmd;
    END;
    $func$ LANGUAGE plpgsql;
    

Notice how the body of the function is built separately and passed through
`quote_literal` to double any quote marks in it. This technique is needed
because we cannot safely use dollar quoting for defining the new function: we
do not know for sure what strings will be interpolated from the
`referrer_key.key_string` field. (We are assuming here that
`referrer_key.kind` can be trusted to always be `host`, `domain`, or `url`,
but `referrer_key.key_string` might be anything, in particular it might
contain dollar signs.) This function is actually an improvement on the Oracle
original, because it will not generate broken code when
`referrer_key.key_string` or `referrer_key.referrer_type` contain quote marks.

  

Example 43.11 shows how to port a function with `OUT` parameters and string
manipulation. PostgreSQL does not have a built-in `instr` function, but you
can create one using a combination of other functions. In Section 43.13.3
there is a PL/pgSQL implementation of `instr` that you can use to make your
porting easier.

**Example 43.11. Porting a Procedure With String Manipulation and`OUT`
Parameters from PL/SQL to PL/pgSQL**

The following Oracle PL/SQL procedure is used to parse a URL and return
several elements (host, path, and query).

This is the Oracle version:

    
    
    CREATE OR REPLACE PROCEDURE cs_parse_url(
        v_url IN VARCHAR2,
        v_host OUT VARCHAR2,  -- This will be passed back
        v_path OUT VARCHAR2,  -- This one too
        v_query OUT VARCHAR2) -- And this one
    IS
        a_pos1 INTEGER;
        a_pos2 INTEGER;
    BEGIN
        v_host := NULL;
        v_path := NULL;
        v_query := NULL;
        a_pos1 := instr(v_url, '//');
    
        IF a_pos1 = 0 THEN
            RETURN;
        END IF;
        a_pos2 := instr(v_url, '/', a_pos1 + 2);
        IF a_pos2 = 0 THEN
            v_host := substr(v_url, a_pos1 + 2);
            v_path := '/';
            RETURN;
        END IF;
    
        v_host := substr(v_url, a_pos1 + 2, a_pos2 - a_pos1 - 2);
        a_pos1 := instr(v_url, '?', a_pos2 + 1);
    
        IF a_pos1 = 0 THEN
            v_path := substr(v_url, a_pos2);
            RETURN;
        END IF;
    
        v_path := substr(v_url, a_pos2, a_pos1 - a_pos2);
        v_query := substr(v_url, a_pos1 + 1);
    END;
    /
    show errors;
    

Here is a possible translation into PL/pgSQL:

    
    
    CREATE OR REPLACE FUNCTION cs_parse_url(
        v_url IN VARCHAR,
        v_host OUT VARCHAR,  -- This will be passed back
        v_path OUT VARCHAR,  -- This one too
        v_query OUT VARCHAR) -- And this one
    AS $$
    DECLARE
        a_pos1 INTEGER;
        a_pos2 INTEGER;
    BEGIN
        v_host := NULL;
        v_path := NULL;
        v_query := NULL;
        a_pos1 := instr(v_url, '//');
    
        IF a_pos1 = 0 THEN
            RETURN;
        END IF;
        a_pos2 := instr(v_url, '/', a_pos1 + 2);
        IF a_pos2 = 0 THEN
            v_host := substr(v_url, a_pos1 + 2);
            v_path := '/';
            RETURN;
        END IF;
    
        v_host := substr(v_url, a_pos1 + 2, a_pos2 - a_pos1 - 2);
        a_pos1 := instr(v_url, '?', a_pos2 + 1);
    
        IF a_pos1 = 0 THEN
            v_path := substr(v_url, a_pos2);
            RETURN;
        END IF;
    
        v_path := substr(v_url, a_pos2, a_pos1 - a_pos2);
        v_query := substr(v_url, a_pos1 + 1);
    END;
    $$ LANGUAGE plpgsql;
    

This function could be used like this:

    
    
    SELECT * FROM cs_parse_url('http://foobar.com/query.cgi?baz');
    

  

Example 43.12 shows how to port a procedure that uses numerous features that
are specific to Oracle.

**Example 43.12. Porting a Procedure from PL/SQL to PL/pgSQL**

The Oracle version:

    
    
    CREATE OR REPLACE PROCEDURE cs_create_job(v_job_id IN INTEGER) IS
        a_running_job_count INTEGER;
    BEGIN
        LOCK TABLE cs_jobs IN EXCLUSIVE MODE;
    
        SELECT count(*) INTO a_running_job_count FROM cs_jobs WHERE end_stamp IS NULL;
    
        IF a_running_job_count > 0 THEN
            COMMIT; -- free lock
            raise_application_error(-20000,
                     'Unable to create a new job: a job is currently running.');
        END IF;
    
        DELETE FROM cs_active_job;
        INSERT INTO cs_active_job(job_id) VALUES (v_job_id);
    
        BEGIN
            INSERT INTO cs_jobs (job_id, start_stamp) VALUES (v_job_id, now());
        EXCEPTION
            WHEN dup_val_on_index THEN NULL; -- don't worry if it already exists
        END;
        COMMIT;
    END;
    /
    show errors
    

This is how we could port this procedure to PL/pgSQL:

    
    
    CREATE OR REPLACE PROCEDURE cs_create_job(v_job_id integer) AS $$
    DECLARE
        a_running_job_count integer;
    BEGIN
        LOCK TABLE cs_jobs IN EXCLUSIVE MODE;
    
        SELECT count(*) INTO a_running_job_count FROM cs_jobs WHERE end_stamp IS NULL;
    
        IF a_running_job_count > 0 THEN
            COMMIT; -- free lock
            RAISE EXCEPTION 'Unable to create a new job: a job is currently running'; -- (1)
        END IF;
    
        DELETE FROM cs_active_job;
        INSERT INTO cs_active_job(job_id) VALUES (v_job_id);
    
        BEGIN
            INSERT INTO cs_jobs (job_id, start_stamp) VALUES (v_job_id, now());
        EXCEPTION
            WHEN unique_violation THEN -- (2)
                -- don't worry if it already exists
        END;
        COMMIT;
    END;
    $$ LANGUAGE plpgsql;
    

(1) |  The syntax of `RAISE` is considerably different from Oracle's statement, although the basic case `RAISE` _`exception_name`_ works similarly.  
---|---  
(2) |  The exception names supported by PL/pgSQL are different from Oracle's. The set of built-in exception names is much larger (see Appendix A). There is not currently a way to declare user-defined exception names, although you can throw user-chosen SQLSTATE values instead.  
  
  

### 43.13.2. Other Things to Watch For

This section explains a few other things to watch for when porting Oracle
PL/SQL functions to PostgreSQL.

#### 43.13.2.1. Implicit Rollback after Exceptions

In PL/pgSQL, when an exception is caught by an `EXCEPTION` clause, all
database changes since the block's `BEGIN` are automatically rolled back. That
is, the behavior is equivalent to what you'd get in Oracle with:

    
    
    BEGIN
        SAVEPOINT s1;
        ... code here ...
    EXCEPTION
        WHEN ... THEN
            ROLLBACK TO s1;
            ... code here ...
        WHEN ... THEN
            ROLLBACK TO s1;
            ... code here ...
    END;
    

If you are translating an Oracle procedure that uses `SAVEPOINT` and `ROLLBACK
TO` in this style, your task is easy: just omit the `SAVEPOINT` and `ROLLBACK
TO`. If you have a procedure that uses `SAVEPOINT` and `ROLLBACK TO` in a
different way then some actual thought will be required.

#### 43.13.2.2. `EXECUTE`

The PL/pgSQL version of `EXECUTE` works similarly to the PL/SQL version, but
you have to remember to use `quote_literal` and `quote_ident` as described in
Section 43.5.4. Constructs of the type `EXECUTE 'SELECT * FROM $1';` will not
work reliably unless you use these functions.

#### 43.13.2.3. Optimizing PL/pgSQL Functions

PostgreSQL gives you two function creation modifiers to optimize execution:
“volatility” (whether the function always returns the same result when given
the same arguments) and “strictness” (whether the function returns null if any
argument is null). Consult the CREATE FUNCTION reference page for details.

When making use of these optimization attributes, your `CREATE FUNCTION`
statement might look something like this:

    
    
    CREATE FUNCTION foo(...) RETURNS integer AS $$
    ...
    $$ LANGUAGE plpgsql STRICT IMMUTABLE;
    

### 43.13.3. Appendix

This section contains the code for a set of Oracle-compatible `instr`
functions that you can use to simplify your porting efforts.

    
    
    --
    -- instr functions that mimic Oracle's counterpart
    -- Syntax: instr(string1, string2 [, n [, m]])
    -- where [] denotes optional parameters.
    --
    -- Search string1, beginning at the nth character, for the mth occurrence
    -- of string2.  If n is negative, search backwards, starting at the abs(n)'th
    -- character from the end of string1.
    -- If n is not passed, assume 1 (search starts at first character).
    -- If m is not passed, assume 1 (find first occurrence).
    -- Returns starting index of string2 in string1, or 0 if string2 is not found.
    --
    
    CREATE FUNCTION instr(varchar, varchar) RETURNS integer AS $$
    BEGIN
        RETURN instr($1, $2, 1);
    END;
    $$ LANGUAGE plpgsql STRICT IMMUTABLE;
    
    
    CREATE FUNCTION instr(string varchar, string_to_search_for varchar,
                          beg_index integer)
    RETURNS integer AS $$
    DECLARE
        pos integer NOT NULL DEFAULT 0;
        temp_str varchar;
        beg integer;
        length integer;
        ss_length integer;
    BEGIN
        IF beg_index > 0 THEN
            temp_str := substring(string FROM beg_index);
            pos := position(string_to_search_for IN temp_str);
    
            IF pos = 0 THEN
                RETURN 0;
            ELSE
                RETURN pos + beg_index - 1;
            END IF;
        ELSIF beg_index < 0 THEN
            ss_length := char_length(string_to_search_for);
            length := char_length(string);
            beg := length + 1 + beg_index;
    
            WHILE beg > 0 LOOP
                temp_str := substring(string FROM beg FOR ss_length);
                IF string_to_search_for = temp_str THEN
                    RETURN beg;
                END IF;
    
                beg := beg - 1;
            END LOOP;
    
            RETURN 0;
        ELSE
            RETURN 0;
        END IF;
    END;
    $$ LANGUAGE plpgsql STRICT IMMUTABLE;
    
    
    CREATE FUNCTION instr(string varchar, string_to_search_for varchar,
                          beg_index integer, occur_index integer)
    RETURNS integer AS $$
    DECLARE
        pos integer NOT NULL DEFAULT 0;
        occur_number integer NOT NULL DEFAULT 0;
        temp_str varchar;
        beg integer;
        i integer;
        length integer;
        ss_length integer;
    BEGIN
        IF occur_index <= 0 THEN
            RAISE 'argument ''%'' is out of range', occur_index
              USING ERRCODE = '22003';
        END IF;
    
        IF beg_index > 0 THEN
            beg := beg_index - 1;
            FOR i IN 1..occur_index LOOP
                temp_str := substring(string FROM beg + 1);
                pos := position(string_to_search_for IN temp_str);
                IF pos = 0 THEN
                    RETURN 0;
                END IF;
                beg := beg + pos;
            END LOOP;
    
            RETURN beg;
        ELSIF beg_index < 0 THEN
            ss_length := char_length(string_to_search_for);
            length := char_length(string);
            beg := length + 1 + beg_index;
    
            WHILE beg > 0 LOOP
                temp_str := substring(string FROM beg FOR ss_length);
                IF string_to_search_for = temp_str THEN
                    occur_number := occur_number + 1;
                    IF occur_number = occur_index THEN
                        RETURN beg;
                    END IF;
                END IF;
    
                beg := beg - 1;
            END LOOP;
    
            RETURN 0;
        ELSE
            RETURN 0;
        END IF;
    END;
    $$ LANGUAGE plpgsql STRICT IMMUTABLE;
    
    

* * *

Prev | Up |  Next  
---|---|---  
43.12. Tips for Developing in PL/pgSQL | Home |  Chapter 44. PL/Tcl — Tcl Procedural Language  
  
## Submit correction

If you see anything in the documentation that is not correct, does not match
your experience with the particular feature or requires further clarification,
please use this form to report a documentation issue.

  * ![@postgresql](/media/img/atpostgresql.png)
  * ![Git](/media/img/git.png)

Privacy Policy | Code of Conduct | About PostgreSQL | Contact  

Copyright © 1996-2024 The PostgreSQL Global Development Group

 Skip to main content

![Home](/themes/custom/edb_theme/logo.svg)

  * My Account
    * Dashboard
    * EDB Postgres AI Cloud Service
    * Support Portal
    * Training Portal
    * Migration Portal
    * Deal Registration
    * Account Settings
    * Sign Out
  * Sign In
    * EDB Login
    * EDB Postgres AI Login
  * Contact Us

  * EDB Postgres AI

    * PRODUCTS

      * EDB POSTGRES AI PLATFORM 

        * EDB Postgres Advanced Server 
        * EDB Postgres Extended Server 
        * EDB Postgres Distributed 
        * EDB Postgres AI Cloud Service 
        * Tools and Services 

    * WORKLOADS

      * Enterprise-Grade Postgres 
      * Oracle Migration and Modernization 
      * Geo-distributed, Extreme High Availability 

      * Multi-cloud Data Infrastructure 
      * Data Estate Management 
      * Postgres for AI Applications 
      * Postgres for Kubernetes 
      * Rapid Analytics for Postgres 

    * SUBSCRIPTION PLANS

      * Cloud Plan Fully managed Postgres in the cloud
      * Enterprise Plan Extending Postgres for enterprises
      * Standard Plan PostgreSQL with enterprise tooling
      * Community 360 Plan Leverage the power of PostgreSQL
      * Subscription Plan Comparison 

  * Services

    * PROFESSIONAL SERVICES

      * Services, Support, and Training Overview Increase your Postgres productivity and decrease your risk
      * Professional Services Expert services and support

    * SUPPORT SERVICES

      * Technical Account Management Proactive guidance and partnership
      * Remote DBA Expert proactive support for your deployments

  * Resources

    * TECHNICAL RESOURCES

      * EDB Product Documentation EDB product documentation
      * Platform Compatibility Supported versions and operating systems
      * Knowledge Base Searchable articles and updates
      * Training Learn Postgres from EDB experts
      * Support Support for PostgreSQL everywhere
      * EDB Trust Center Knowledge base on our security posture
      * EDB Purchase Program Simple access to products and services

    * DOWNLOADS

      * Software Downloads EDB integrated portfolio of software
      * Download PostgreSQL Open source PostgreSQL package and installers

    * LEARN & EXPLORE

      * Blog Industry news and thought leadership
      * Customer Success Stories Achieving more with Postgres
      * Events Local events near you
      * Webinars Upcoming and on-demand webinars
      * White Papers PostgreSQL research and analysis
      * Learning Library Introductory guides to Postgres topics

## Resource Feature Callout 1

EDB TRUST CENTER

SECURITY QUESTIONS, ANSWERED

![Trust Center Image](/sites/default/files/2024-05/trust_center.png)

Access our comprehensive knowledge base focused on security, privacy, and
compliance.

Visit Trust Center

  * Company

    * About We are Postgres experts
    * Leadership Meet the EDB leadership
    * Press & News Press Releases and Media Resources
    * Careers Work at EDB
    * Contact Us Talk to sales, get support and more

    * Become a Partner Become a partner with EDB
    * Find a Partner Search for global business partners
    * EDB Partners Robust partner network
    * Why Partner With EDB Benefits of being an EDB global business partner
    * Partner Portal Gateway to success for existing partners

## Header CTA

  * My Account
    * Dashboard
    * EDB Postgres AI Cloud Service
    * Support Portal
    * Training Portal
    * Migration Portal
    * Deal Registration
    * Account Settings
    * Sign Out
  * Sign In
    * EDB Login
    * EDB Postgres AI Login
  * Contact Us

Search

Enter the terms you wish to search for.

SearchClear

#

The Complete Oracle to Postgres Migration Guide: Tools, Schema, and Data

###

### Learn about migrating from Oracle to Postgres–from data migration and
Oracle vs. Postgres features comparisons to instructions for efficient Oracle
transition.

For anyone on the fence or intimidated by the data migration process, there
are several advantages Postgres has over Oracle as a database system, namely
cost, flexibility and customizability. This blog breaks down the migration
process into individual phases (assessment, schema migration, functional
testing, performance testing and data migration) and provides step-by-step
instructions.

Key differences and incompatibilities between the two database systems are
itemized and weighed to help users avoid common mistakes. Alternate migration
strategies and a list of helpful free data migration tools are also taken into
consideration. For any current Oracle users considering Postgres but concerned
about the complexity of the move, this guide is tailor-made for you.

  * What is Database Migration? _east_
  * Why Migrate from Oracle to Postgres? _east_
  * What Are the Advantages Postgres Has over Oracle Database? _east_
  * What Are the Oracle to Postgres Migration Steps and Phases? _east_

  * What Are the Free Tools Available for Database Migration?  _east_
  * Postgres to Oracle Migration Content and Resources _east_
  * Postgres to Oracle Migration: FAQ _east_
  * Planning Your Move to Postgres? _east_

Contact Us

On this page (scroll)

  * What is Database Migration?
  * Why You Should Consider Oracle to Postgres Migr...
  * 7 Advantages Postgres Has over Oracle Database
  * What are the Oracle to Postgres Migration Steps...
  * What Are the Free Tools Available for Database ...
  * Oracle to Postgres Migration Content and Resources
  * Postgres to Oracle Migration: FAQ
  * Ready to Move to PostgreSQL

#####

##

## What is Database Migration?

###

Database migration refers to the process of transferring data from one
database to another, which becomes necessary when an organization seeks to
upgrade to a new database system, consolidate databases, move to a cloud-based
database, or change the database schema.  
  
When considering migration to Postgres, there are several crucial steps
involved:

**Assessment and Planning** | Evaluating the source and target databases, understanding the data and planning the migration strategy.  
---|---  
**Schema Migration** | Transfering the database schema (tables, indexes, constraints) from the source to the target database.  
**Data Migration** | Moving the actual data from the source database to the target database. This can be done in a bulk transfer or incrementally.  
**Data Transformation** | If necessary, transform the data to fit the new schema or to meet the new system’s requirements.  
**Testing and Validation** | It is vital to test the migrated data to ensure accuracy and completeness and to validate if the target database is functioning as expected.  
**Deployment and Monitoring** | Implementing the target database into the production environment and monitor for any issues.  
  
#####

##

## Why You Should Consider Oracle to Postgres Migration

###

Here are several advantageous benefits when you choose to migrate from Oracle
to Postgres:

**Cost** : In addition to Oracle license costs, using Oracle databases incurs
additional fees for features like partitioning and high availability, where
expenses can add up quickly. Open source Postgres on the other hand, is free
to install and use.

**Flexibility** : Postgres has open source licensing and is easily available
from public cloud providers, including AWS. With Postgres, you’re not at risk
of vendor lock-in.

**Customizability** : There are countless extensions and add-ons that can
improve database performance markedly due to Postgres’ open source nature, and
many of them are free to use. With Oracle, similar features quickly add up in
cost.

That’s not to say that migrating from Oracle to Postgres is not an involved
process. Since the data migration is between two relational database
management systems (RDBMS), it can be a challenging and time-consuming process
due to heterogeneous structure/data types. As such, you want to be sure it’s
tackled and handled with the right tools, such as migration software or
tailor-made Oracle migration tools. Follow the steps below and you’ll be well
on your way to achieving a developer-friendly oracle to Postgres migration.

#####

##

## 7 Advantages Postgres Has over Oracle Database

###

  1. **Application Programming**
     * Oracle and Postgres both provide an application API for database communication. However, as Postgres is open source, developers can access any Postgres component simply by including the header file in their project.
  2. **Authentication**
     * Oracle has a built-in authentication system. Postgres relies on host-based authentication and can support a wide range of authentication methods, providing greater flexibility for authentication and the option to delegate the process.
  3. **Extensibility**
     * Oracle has a mostly proprietary plug-in system, whereas the general community supports Postgres' extension system, enabling access to thousands of available plug-ins.
  4. **Languages**
     * While Oracle has a built-in programming language called PL/SQL, Postgres has PL/pgSQL in addition to many others and an extension system that allows users to create additional procedural languages as plug-ins and bindings for even more programming languages.
  5. **Localization**
     * Oracle offers globalization support tools, including a globalization development kit and unicode character support. Postgres' localization system services are built-in to provide automatic character encoding and collation support.
  6. **Performance**
     * Because Postgres can create an unlimited number of nodes in a read cluster, the cost of any particular read operation can be reduced to close to nothing. And because of that, you can tune it differently for every workload. You can do this in Oracle, too, but each node has an additional cost.
  7. **Scalability**
     * Oracle has b vertical read scalability. Still, Postgres can create a virtually unlimited number of nodes in a read cluster, depending on the resources you have available to dedicate to it.

#####

##

## What are the Oracle to Postgres Migration Steps and Phases?

###

There are five steps or phases when dealing with Oracle to Postgres migration:

## **1\. Assessment**

What are the Oracle to Postgres pre-migration assessment steps and phases?
This process involves assessment, compatibility assessment, application code
assessment, architecture assessment, and cleanup and schema assessment.

**1a. Application Programming**

The first step in planning the migration and analyzing the application to
estimate how easy or difficult the Oracle to Postgres migration can be. In
this phase, a thorough analysis should be conducted on technology-related
issues to evaluate the compatibility of the client, application server, data
access, and database features.

**1b. Oracle to Postgres migration: compatibility assessment**

One concern that can easily be overlooked when considering a move to Postgres
is confirming that, if you do not control your application, the packaged
software application you use certifies for Postgres. If not, you must either
convince your application’s vendor to add Postgres support or choose another
application.  
After verifying the source and target database compatibility, the following
prerequisites should be met for data migration:

  * Server resources (memory/disk space/network ports opened between source and destination)
  * Operating system
  * Data migration software and related drivers installed and configured

  
It should go without saying that your target server resources are large enough
and have the scalability to handle the volume of data they will be receiving.
If the volume of data is substantial, then a purely online migration may not
be advisable, and you should consider an export-and-reload approach. It may
also be wise to follow a migration strategy that divides the migration into
parts (see the section on migration strategies below).

**1c. Application code assessment**

The more your application code relies on Oracle-specific frameworks instead of
open classes, the more intricate your migration becomes. The amount of
adjustment you will need to make depends on your application architecture and
database connection layer. The conversion should be relatively easy if you
have Java code that uses generic JDBC classes rather than Oracle-specific
ones. It should be easy to use object-relational mapping (ORM), such as
Hibernate or JCA. Switching the dialect from Oracle to Postgres could be
trivially easy, though some adjustments will still need to be made.

**1d. Oracle to Postgres migration: architecture assessment and cleanup**

The ease or difficulty of a migration can be significantly impacted by the
setup and architecture of your database and its contents. Migration presents
an excellent opportunity to clean up your architecture and database contents.
Deprecate objects you no longer need, such as old temporary tables or backup
copies of data—they’re not worth migrating if no one cares about them. If you
store large files, like images or PDFs, in your database, consider separating
them into a lower-cost storage option to reduce database size and resources
needed for backup and restores. You may want to purge static historical data
or move into an archival store option.

Moving from Oracle to Postgres also opens the possibility of separating online
transaction processing (OLTP) and analytics into different warehouses, which
can improve both responsiveness and analytics capabilities

**1e. Oracle to Postgres migration: schema conversion**

Following the assessment, the next step in the migration process is
identifying and addressing schema and data formatting differences between
Oracle and Postgres. Making these adjustments before the data migration is
crucial to avoid frustrating and time-consuming errors in Postgres.

Postgres supports ANSI SQL standard SQL syntax and data types, whereas Oracle
does not support the same standard; additionally, it includes some non-ANSI
SQL syntaxes. With the help of Oracle migration tools, unsupported objects can
be identified and converted manually with Postgres-supported syntax or feature
workarounds.

## **2\. Schema Migration**

**What is schema in Oracle and Postgres?**

A schema is also known as a “user” in Oracle and has the same name as the
user. The default is for each Oracle user to have their schema. In Postgres,
these are not the same and if you do not explicitly specify a schema, new
objects will go to a public schema by default.

One concern that can easily be overlooked when considering a move to Postgres
is confirming that, if you do not control your application, the packaged
software application you use certifies for Postgres. If not, you must either
convince your application’s vendor to add Postgres support or choose another
application.  
After verifying the source and target database compatibility, the following
prerequisites should be met for data migration:

  * Create a user and schema with the same name
  * The first component in the schema search_path is $user, by default

One advantage of the setup in Postgres is that a user can create multiple
schemas without creating separate users and grant permissions to others for
creating objects in those schemas.

**What are the Oracle to Postgres schema migration tools?**

Several migration tools are available that can help users automate schema
conversion. Below are the tools you can try for free to convert Oracle objects
to Postgres.

  * **Ora2pg**: a robust migration tool that connects to an Oracle database, extracts schemas and tables, and generates SQL scripts that can be loaded into Postgres.
  * **Ora_migrator** : an extension that uses an oracle_fdw foreign data wrapper to extract data from an Oracle database.
  * **Orafce** : this extension allows you to implement many Oracle functions in Postgres. It also provides support for Oracle date formatting and additional Oracle data types.
  * **EDB Migration Portal**: a web-based service for migrating from Oracle to EDB Postgres Advanced Server that features Assessment, Schema conversion, and Compatibility reporting.

_Take aquick look at how EDB Migration Portal works in this tutorial._

In addition, there are a number of commercial conversion tools available,
including Amazon’s AWS Schema Conversion Tool (AWS SCT).

**Oracle to Postgres migration tools comparison matrix**

The below tools comparison matrix will give you a high-level picture of what
they can do and how far they get you in the migration exercise to a specific
target.

| Assessment | Migration of data objects | Migration of code objects | Migration Data | Approach | Target  
---|---|---|---|---|---|---  
EDB Migration Portal |  |  |  | Use EDB MTK | Native + Transformation | Postgres Advanced Server  
EDB MTK |  |  |  |  | Native Only | Postgres Advanced Server  
AWS SCT |  |  |  |  | Transformation Only | PostgreSQL  
Ora2Pg |  |  |  |  | Transformation Only  
  
**The key schema differences to take note of before Oracle to Postgres
migration**

Consider the following differences between Oracle and Postgres when preparing
for schema conversion:

**Columns**  
Until version 12, Postgres had no equivalent to virtual columns, so users were
encouraged to change these to views when migrating. Now, Postgres offers
generated columns, which share many traits with Oracle’s virtual columns.

**Constraints**  
The Primary and Foreign Key, Check, Not-Null, and Unique constraints operate
similarly in both database systems.

**Identifiers**  
Oracle converts names of schema, tables, columns, and functions to uppercase
unless the name is given in quotes, while Postgres converts them to lowercase
unless given in quotes. You should be safe as long as the application
consistently quotes or does not quote the identifiers.

**Indexes**

  * B-tree and descending indexes should function in Postgres
  * Reverse key, bitmap, and join indexes are not currently supported.
  * Global index is not supported in Postgres

**Partitions**  
Hash, List, and Range partitions should all work in Postgres following
migration.

**Tables**  
CREATE TABLE is mostly compatible, with the following exceptions:

  * Postgres lacks global temporary tables. Use temporary tables (LOCAL TEMP) instead.
  * Partitioning: Use Inheritance, Triggers, and CHECK Constraints for partition clauses.
  * Storage clause parameters (INITRANS, MAXEXTENTS) are not recognized in Postgres and should be removed.
  * For the Oracle PCTFREE parameter, replace it with Postgres’ fillfactor.

**Tablespaces**  
There are differences between Oracle and Postgres’ versions of tablespaces,
but they serve the same purpose and should work.

**Data types**  
The following chart lists notable differences between Oracle and Postgres data
types.

Oracle | Postgres | EDB Postgres Advanced Server | Comment  
---|---|---|---  
VARCHAR2(n) | VARCHAR(n) | VARCHAR2(n),  
VARCHAR(n) | Be careful not to confuse ‘n’ in Oracle and Postgres data types. In Oracle it stands for the size in bytes; in Postgres it stands for the number of characters.  
NVARCHAR, NVARCHAR2 | VARCHAR or TEXT | NVARCHAR, NVARCHAR2,  
VARCHAR or TEXT |   
CHAR(n), NCHAR(n) | CHAR(n) | CHAR(n),  
NCHAR(n) | Be careful not to confuse ‘n’ in Oracle and Postgres data types. In Oracle it stands for the size in bytes; in Postgres it stands for the number of characters.  
NUMBER(n, m) | NUMERIC(n,m) | NUMERIC(n,m)  
NUMBER(n, m) | NUMBER type can be converted to NUMERIC, which is unlimited in size. However, the SMALLINT, INT BIGINT, REAL, and DOUBLE PRECISION data types offer better performance.  
NUMBER(4) | SMALLINT | NUMBER(4)  
SMALLINT  
NUMBER(9) | INT | NUMBER(9),  
INT  
NUMBER(18) | BIGINT | NUMBER(18),  
BIGINT  
NUMBER(n) | NUMERIC(n) | NUMBER(n),  
NUMERIC(n) | If n>=19  
BINARY_INTEGER, BINARY_FLOAT | INTEGER, FLOAT | BINARY_INTEGER,  
INTEGER,  
FLOAT |   
DATE | TIMESTAMP(0) | DATE,  
TIMESTAMP(0) | In Oracle the DATE type returns both date and time, whereas in Postgres the DATE type returns the date without the time.  
TIMESTAMP WITH LOCAL TIME ZONE | TIMESTAMPTZ | TIMESTAMP WITH LOCAL TIME ZONE  
TIMESTAMPTZ | Oracle has both TIMESTAMP WITH TIME ZONE and TIMESTAMP WITH LOCAL TIME ZONE data types. Postgres' TIMESTAMPTZ is equivalent to TIMESTAMP WITH LOCAL TIME ZONE. If these are confused it can introduce errors.  
CLOB, LONG | TEXT | CLOB, LONG  
TEXT | Postgres' TEXT type is capable of storing up to 1 GB of text data.  
BLOB,  
RAW(n),  
LONG RAW | BYTEA(1 GB limit)  
Large object | BLOB,  
RAW(n),  
LONG RAW  
BYTEA(1 GB limit)  
Large Object | In Oracle,the BLOB datatype is used for unstructured binary data and essentially has no size limit (up to 128 terabytes of binary data). Postgres' BYTEA data type can store up to 1 GB of binary data. Above that limit use Large Object (these are stored in a separate table).  
  
**What are the challenges while migrating from Oracle to Postgres?**

In this section, we discuss some of the challenges we might face while
migrating from Oracle to Postgres. To address these challenges and limitations
identified in Postgres, those objects should be rewritten manually with a
workaround if they exist.

**1\. Constraints**  
While Oracle allows users to disable and enable constraints as often as you
want, this is not generally recommended practice for any RDBMS, because it can
lead to data corruption if not performed with proper care.  
  
In Postgres, constraints are instead created as deferrable, and the SET
CONSTRAINTS command can be used to defer them. The deferrable setting
indicates the default time for activating the constraint. If the constraint in
Oracle is not deferrable, it will need to be dropped and re-created as
deferrable, though it is sometimes possible to alter the constraint without
having to drop it. Note: to avoid potential errors or bad data, place the
commands for dropping and re-creating the constraint in a transaction, marked
with a BEGIN/COMMIT block, which will lock the tables during the transaction.

**2\. DELETE**  
The FROM clause for specifying the table is required with DELETE statements in
Postgres but not in Oracle.

_Oracle:_

`DELETE mytable WHERE column_name = 'column_amount';`

_Postgres:_

`DELETE FROM mytable WHERE column_name = 'column_amount';`

**3\. Dropping database objects**  
In Postgres, permission to drop objects is restricted to only a database
table’s owner or a super user. It is not a grantable privilege, although
membership in the role that owns the object can be granted. If an action in
Oracle depends on this ability, it may need to be rewritten or reconfigured.

**4\. Dual Table**  
Because the FROM clause is mandatory in Oracle for every SELECT statement,
FROM DUAL is used for SELECT statements where the table name is not necessary.
Postgres does not require the FROM clause, so FROM DUAL is not necessary and
can usually be omitted. If the Dual table is needed in Postgres, it can be
generated as a view.

**5\. Empty Strings and NULL**  
In Oracle, empty strings have NULL values, but they are not considered NULL in
Postgres.  
  
In Oracle, you can check whether a string is empty or not using the IS NULL
operator, but in Postgres, it would return FALSE for an empty string (and TRUE
for a NULL one).

**6\. Federation to Foreign Data Wrappers**  
Oracle’s Federation feature allows users to treat tables from other databases
as local data. Postgres' foreign data wrappers are more versatile and allow
you to connect to a wider range of data.

**7\. GRANT**  
The GRANT command behaves similarly in Oracle and Postgres. There are two
basic variants—it can be used to grant privileges on a database object and to
grant membership to a role.  
  
Not all privileges that are grantable in Oracle are grantable in Postgres. For
example, granting the trigger privilege to a table allows users to create
triggers; but, as opposed to Oracle, only the owner of the table can drop
triggers.

**8\. Hierarchical queries**  
Postgres does not support the START WITH . . . CONNECT BY syntax that Oracle
uses for hierarchical queries. Instead, Postgres uses WITH RECURSIVE.

_Oracle:_

`SELECT  
  
business_name,  
  
city_name  
  
FROM  
  
businesses bs  
  
START WITH bs.city_name = 'BOSTON'  
  
CONNECT BY PRIOR bs.business_name = bs.city_name;`

_Postgres:_

`WITH RECURSIVE tmp AS (SELECT business_name, city_name  
  
FROM businesses  
  
WHERE city_name = 'BOSTON'  
  
UNION  
  
SELECT t.business_name, t.city_name  
  
FROM businesses t  
  
JOIN tmp ON tmp.business_name = t.city_name)  
  
SELECT business_name, city_name FROM tmp;`

**9\. Joins with (+)**  
Oracle has a special shorthand (+) operator for performing left and right
outer joins. Postgres lacks this feature, so the JOIN command would need to be
supplied.

_Oracle:_

`SELECT table1.firstname, table2.lastname  
  
FROM table1, table2  
  
WHERE table1.customer = table2.customer (+);`

_Postgres:_

`SELECT table1.firstname, table2.lastname  
  
FROM table1  
  
LEFT OUTER JOIN table2 ON table1.customer = table2.customer; `

**10\. NOT NULL checking**  
To determine which columns in an Oracle table are NOT NULL, you would use the
command CHECK ( IS NOT NULL).  
  
Postgres instead has a NOT NULL constraint column named attnotnull in
pg_attribute, the systems catalog where information about table columns is
stored.

**11\. Packages**  
Postgres does not have packages, but, using schema architecture, functions,
and procedures can be grouped. Use the “orafce” migration tool library, which
supports some of the standard packages, or EDB Postgres Advanced Server, which
has built-in Packages.

**12\. PL/SQL to PL/pgSQL Conversion**  
Postgres' PL/pgSQL procedural language is similar in many respects to Oracle’s
PL/SQL. Both are block-structured, imperative languages, with similar formats
for assignments, loops, and conditionals. The Postgres documentation has a
thorough run-down of considerations that need to be made when porting from
PL/SQL to PL/pgSQL.

**13\. Remote objects**  
To access remote objects, the DBLINK module or Foreign Data
Wrapper(Oracle_fdw) can be used to access any other database.

_ROWID, CTID and Identity columns_  
  
Postgres does not have an exact equivalent to the ROWID pseudocolumn in
Oracle, which provides the address of a row in a table. CTID in Postgres is
similar, except that its value changes every time VACUUM is performed.
Instead, you can use identity columns, whose value is self-generated when a
row is created and never changes. The value can be specified to be GENERATED
ALWAYS or GENERATED BY DEFAULT. GENERATED BY DEFAULT allows the user the
option to insert or update a value rather than use the system-generated value.

**14\. Sequences**  
Sequences have a different syntax in Oracle and Postgres and will need to be
updated either manually or using a script.

_Oracle:_

`Mysequence.nextval;`

_Postgres:_

`nextval(‘mysequence’); `

**15\. SUBSTR**  
The SUBSTR function behaves differently in Oracle and Postgres. In Oracle the
statement SELECT SUBSTR(‘ABC’,-1) FROM DUAL; returns ‘C’, while the equivalent
SELECT SUBSTR('ABC',-1); in Postgres would return ABC. The Orafce migration
tool includes a SUBSTR function that returns the same result in both
databases.

**16\. Synonyms**  
Postgres does not support synonyms. In place of Oracle’s CREATE SYNONYM for
accessing remote objects, Postgres has you use SET search_path to include the
remote definition.

_Oracle:_

`CREATE SYNONYM abc.mytable FOR xyz.mytable;`

_Postgres:_

`SET search_path TO 'abc.mytable'; `

**17\. SYSDATE**  
Oracle's SYSDATE function returns date and time (in the server’s timezone).
Postgres does not have a corresponding function, but there are a range of
methods for retrieving the date and time for different purposes:
statement_timestamp() gives current date and time from the beginning of the
current statement; now() and transaction_timestamp() give the date and time
from the beginning of the current transaction; and clock_timestamp() gives
current date and time from the execution of the function.

**18\. TO_DATE**  
The to_date() function in both Oracle and Postgres return the date data type.
However, Postgres' date data type provides the date (year, month, day), while
Oracle’s date data type value provides the date and time (year, month, day,
hour, minute, second). To avoid this incompatibility, use Postgres'
to_timestamp().  
  
The solution for this incompatibility is to convert TO_DATE() to
TO_TIMESTAMP(). If you use Orafce tool then it is not necessary to change
anything because Orafce implemented this function so we get the same result as
Oracle.

_Oracle:_

`SELECT TO_DATE ('20180314121212','yyyymmddhh24miss') FROM dual;`

_Postgres:_

`SELECT TO_TIMESTAMP ('20180314121212', 'yyyymmddhh24miss' )::TIMESTAMP(0); `

**19\. Transactions**  
Oracle always uses transactions, but in Postgres, they must be activated. In
Oracle, executing any statement starts a transaction, and it ends with the
COMMIT statement. In Postgres, the transaction starts with the BEGIN
statement, and it also ends with the COMMIT statement. Transaction isolation
levels are the same in Postgres and Oracle, and Read Committed is the default
isolation level for both.

**20\. Transaction error handling**

Postgres is built to facilitate transaction management and error handling,
with full ACID support and isolation levels. It is also capable of handling
run-time errors and provides reliable error codes and messages to PL/pgSQL or
application code. These are handled differently from Oracle, though, so
changes still need to be made. Here are some tips for optimizing error
handling for Postgres:

  * Transaction control inside of PL/pgSQL is not permitted: you cannot commit or roll back a transaction inside a stored procedure. Commits and roll backs must be called from the application, so the application that calls the stored procedure must perform the transaction management—starting and committing or rolling back. The stored procedure executes within that calling transaction context. Obviously, if your existing database code has transaction management in procedures, it must be modified.
  * When there is a run-time exception during a transaction, the transaction must be rolled back before you can execute another statement, because the transaction is aborted when it encounters the error. The application log will show the following error message:

`ERROR: current transaction is aborted, commands ignored until  end of
transaction  block.  
  
Be sure that you have an exception handler set up where errors might be
occurring and either to a savepoint or close the connection before trying
another database operation. `

  * Use a BEGIN…EXCEPTION…END block for exception handling so your code catch any errors that occur. This automatically sets a savepoint before the block and rolls back to it when it encounters an exception. Keep in mind that because exception blocks create a savepoint, they are expensive, so add them carefully.
  * Map the error codes and exception types from Oracle to Postgres. While some error codes are the same in both, others are different. Your programming language affects this as well. For example, Oracle-specific JDBC exceptions need to be replaced with either generic cross-database exceptions or Postgres-specific ones.
  * Ensuring your Postgres database handles transactions and errors correctly is a critical part of the migration process and usually requires a careful review of the database and application code.

For enterprises looking to retain some oracle functionality in Postgres and
wanting to migrate faster without many code rewrites an option is to move to
EDB Postgres Advanced Server. EDB Postgres Advanced server is Oracle
compatible Postgres with native PL/SQL compatibility. The below video
demonstrates how the Advanced Server handles Oracle queries:

_Take aquick look at Oracle compatibility with EDB Postgres Advanced Server._

## **3\. Functional Testing**

**Functional testing after scheme migration from Oracle to Postgres**  
  
Before proceeding further, testing the converted schema on a sample dataset is
vital. A recommended approach is to load some sample data into Postgres from a
source database development or testing environment with production sample data
and then set up an application connection using appropriate data access
(drivers). After the application has connected to the database, allow it to do
complete functional testing on the converted objects with DMLs.  
  
It is advisable to load the same sample dataset in the Oracle and Postgres
databases and then test both to ensure the SQL results are identical. Review
and address any issues revealed by the functional tests.

## **4\. Postgres Performance Testing After Migration**

Performance testing is essential in the migration phase because some Oracle
built-in transactions or feature functionality might differ slightly in
Postgres, and the application might see some differences. In this phase, we
can capture and fix all those differences at the application, data access
(drivers), and database level with proper tuning.

## **5\. Postgres Performance Testing After Migration**

There are different approaches for data migration and tools available in the
market. Usually, they are classified in three ways:

  1. **Snapshot** : Data moved at once
  2. **Snapshot in Parallel** : Data moved in chunks (schema or table)
  3. **Change Data Capture (Replication)** : Data loaded continuously.

For approaches 1 and 2, we need application downtime because data is being
written one time from Oracle to Postgres, whereas in approach 3 data is loaded
continuously, and there is a smaller downtime window. Pick the right data
migration approach that fits in the downtime window.

**Breaking down the strategies**  
  
Let's discuss these three data migration strategies in a little more detail.

**_Snapshot (One Big Bang)_**  
  
In this approach, a snapshot of the source database state is taken and applied
on the target database. Data is moved from Oracle to Postgres all at once.
During the snapshot process, no WRITE operations are allowed on the source
database. It's one of the cleaner and easier methods of data migration.

Pros | Cons  
---|---  
  
  * Data movement will be completed in one go
  * No Data Type challenges (LoBs)
  * After snapshot, applications can start accessing the target database
  * No special configuration required. Easy to manage.
  * READ ONLY users can access source database (in some cases)

|

  * The application will be down during the snapshot.
  * If a snapshot is interrupted, reinitiation is required.

  
  
**_Snapshot - Parallel in Chunks (Trickle)_**  
  
Parallel in chunks is another type of snapshot approach where data objects are
broken into chunks and snapshots are taken in parallel. Most of the tools
support snapshot and the process are invoked in tandem. There are two ways to
perform a snapshot in chunks: 1) table by table or 2) a large table split into
small sets using primary keys or any unique row identifiers. In this approach,
the snapshot duration and downtime window is greatly reduced. Good scripting
skills required to prepare data migration tools for table or large table
migration.

![](https://edb.prod.acquia-
sites.com/sites/default/files/2024-07/p19-img1.png)

Pros | Cons  
---|---  
  
  * Data moved at one time with less downtime
  * Data moved in parallel - table by table or a large table in small sets

|

  * Application downtime required (less compared to Big Bang approach)
  * For large tables broken into small sets, primary key or unique row identifiers are mandatory
  * After snapshot, applications can start accessing the target database
  * Script required to adjust the parallel approach
  * If a snapshot is interrupted, reinitiation is required.

  
  
**Change Data Capture (CDC Data Sync)**  
  
Different traditional Change Data Capture (CDC) approaches have been available
for decades. In the CDC model data migration, the software is designed to
determine/track/capture the data that has changed on the source database in
real time and replay the same on the target database. Currently, CDC model
software is in high demand because it distributes data between heterogeneous
databases with low-latency, reliable, and scalable data. The most common CDC
approaches for migrating Oracle to Postgres databases are:

  * **Trigger-based (CDC)**  
A remote trigger is created to capture DMLs (insert/update/delete) before or
after and the sequence of transaction events is captured in changelog (shadow
tables). They are processed by the software to replay on the target database.

  * **Transaction Log-based (CDC)**  
Every database will have transaction logs (redos) to store all
transactions/events sequentially and be used in case of database crash
recovery. The transactions (DMLs) can be captured to change logs with filters,
transforms, and aggregations using native database transaction log plug-ins.
Later, the sequence of captured DMLs will be replicated to the target
database.  
  
Both CDC approaches can be used to replicate data from Oracle to Postgres.
However, each has its merits and limitations. Depending on the requirements,
we can choose one of the available approaches. There are excellent software
options available to support both CDC approaches.

![](https://edb.prod.acquia-sites.com/sites/default/files/2024-07/p19-img.png)

Pros | Cons  
---|---  
  
  * Data loaded continuously to target database (after initial snapshot)
  * User can access source database while data loads to target database
  * Data sync control (if interrupted, it can be resumed)
  * Partial replication (Set of tables can be replicated)

|

  * Need replication software
  * In trigger-based CDC, there could be a slight performance overhead
  * No Large Objects support
  * Partial to small application downtime (switchover time)
  * Only commercial/free to use tools available, no open source.

  
  
#####

##

## What Are the Free Tools Available for Database Migration?

###

There are open and free to use tools available for data migration in three
different categories we have discussed in the blog. Below are the set of tools
that we came across under those categories:

Snapshot | Snapshot - Parallel in chunks | Large table in sets | Change Data Capture  
---|---|---|---  
  
  * Ora2pg
  * Others (Full convert, Ispirer)

|

  * Schema Tables
  * Ora2pg, ora_migrator
  * EDB Migration Toolkit

|

  * EDB dblink_ora module
  * Oracle FDW(ora_fdw)

|

  * EDB Replication Server
  * Other tools (Oracle GG, DBConvert)

  
  
| Snapshot | Snapshot in Parallel | Change Data Capture | Target  
---|---|---|---|---  
EDB Replication Server |  |  |  | Postgres Advanced Server/PostgreSQL  
EDB MTK |  |  |  | Postgres Advanced Server/PostgreSQL  
AWS SCT |  |  |  | PostgreSQL  
Ora2Pg |  |  |  | PostgreSQL  
Ora_FDW (extension) |  |  |  | PostgreSQL  
Dblink_ora (extension) |  |  |  | Postgres Advanced Server  
  
##

## Oracle to Postgres Migration Content and Resources

### Additional resources to help you get the most out of your oracle migration

![](/sites/default/files/icon-blog-1.svg)

####

Explore what a comprehensive strategy for migrating from Oracle to PostgreSQL
looks like using DevOps practices, GitHub, and specialized EDB tools to
streamline the process.

* * *

Read Blog

![](/sites/default/files/icon-blog-1.svg)

####

Discover how Nibble IT successfully migrated from Oracle to PostgreSQL,
including Oracle Forms, with their comprehensive strategy and innovative tools
like VisionX, in this compelling case study

* * *

Read Now

![](/sites/default/files/icon-whitepaper.svg)

####

Looking for the best tools and solutions to make your Oracle to Postgres
migration seamless for your developers?

* * *

More Information

#####

##

## Postgres to Oracle Migration: FAQ

###

Why move from Oracle to PostgreSQL? _chevron_right_

Businesses may eventually outgrow the capabilities of their Oracle database,
and might require a solution that's more extensible, feature-rich, and
supports a greater range of data types or languages. Some might also leave to
avoid vendor lock-in or expensive licensing costs.

Can PostgreSQL replace Oracle? _chevron_right_

It depends on what a user or business expects from their database, and the
costs or complexity they are willing to bear as they scale the database to
meet future demands. While there are many reasons to remain with Oracle,
businesses might decide to switch to PostgreSQL if they want better control
and agility over their database.  
  
PostgreSQL also has a growing ecosystem of solution providers that offer a
range of enterprise-grade services, such as fully managed Postgres, cloud
migrations, and 24/7 support, all of which makes PostgreSQL a viable
alternative to Oracle.

What are the benefits of moving from Oracle to PostgreSQL? _chevron_right_

For starters, PostgreSQL has zero licensing fees due to its open source
licensing, which allows both the individual and enterprise to access, modify,
and distribute the code freely. PostgreSQL also has numerous other advantages
over Oracle, namely:  
  
Extensibility Support  
PostgreSQL is catalog-driven like most relational databases, but unlike its
counterparts, a wider range of information is stored within PostgreSQL
catalogs. Users can easily access and modify information within PostgreSQL
catalogs, making it more extensible than other databases.  
  
Higher concurrency  
MVCC (Multi-Version Concurrency Control) is a concurrency control system in
PostgreSQL, which allows for multiple concurrent read and write transactions,
while also maintaining transaction isolation to ensure data isn't rewritten
and remains consistent for all users.  
  
Feature-rich flexibility  
PostgreSQL comes with a wider range of features, such as the abovementioned
catalog-driven operations, concurrency control, layered transactions, and
dynamic loading, to name a few. It's also ACID compliant, which ensures high
data integrity and fault tolerance.

Is PostgreSQL faster than Oracle? _chevron_right_

In terms of transactions-per-second, both databases are equal in terms of
performance. Both databases can handle large data volumes and concurrent
users. Both are also equal in terms of scalability, however, businesses will
encounter licensing and cost complications with Oracle - a hurdle they won't
have with PostgreSQL due to its open source and extensible nature.

Is there any syntax difference between Oracle and PostgreSQL? _chevron_right_

PostgreSQL supports ANSI SQL standard SQL syntax and data types, whereas
Oracle does not support the same standard; additionally, it includes some non-
ANSI SQL syntaxes. Using tools, unsupported objects should be identified and
then converted manually with Postgres-supported syntax or feature workarounds.

What are some best practices for Oracle migrations to PostgreSQL?
_chevron_right_

Some best practices to consider before you migrate from Oracle into PostgreSQL
are:  
  
Get organizational buy-in  
Ensure the entire business understands and agrees to the need to migrate to
PostgreSQL. Establish migration goals, timelines, and contingencies.  
  
Determine what needs to be moved  
Compile an exhaustive list of all data or applications that need to be moved
over, eliminate old data the business no longer needs, and identify any
compatibility issues that might occur.  
  
Test and test again  
Perform stability and performance tests on any data that have been migrated
over to the new PostgreSQL to identify potential issues before performing the
actual migration.  
  
Establish new security and observability measures  
Migrating to PostgreSQL provides businesses with the opportunity to leverage
its security and monitoring capabilities to minimize data security and outage
risks.

What are the main challenges of Oracle to PostgreSQL migrations?
_chevron_right_

There are many differences between both databases, namely data types, syntax,
procedural language, and a host of potential compatibility issues for
applications that are migrated over.

How do you convert Oracle SQL schemas to PostgreSQL? _chevron_right_

It’s challenging to convert schemas in Oracle to PostgreSQL without the right
set of tools. For a list of free online conversion tools, see our list here.
While these tools are freely available, they can be challenging to use without
the right expertise. If you’re converting a large database filled with
thousands of query lines, it’s advisable to use an enterprise grade solution
instead.

Can you run PostgreSQL in Oracle compatibility mode? _chevron_right_

You’ll need the right solution and tools to run PostgreSQL in Oracle
compatible mode. EDB Postgres Advanced Server is the only Oracle compatible
solution in the industry that allows users to run Postgres in Oracle
Compatibility mode.

How does EDB provide Oracle compatibility for PostgreSQL? _chevron_right_

EDB has created EDB Postgres Advanced Server (EPAS), which is an enhanced
version of PostgreSQL that includes implementations of many Oracle specific
data types, object types, keywords, procedural language constructs, and other
features. We continually evaluate migration challenges and introduce new
compatibility features in each major version of EPAS. The solution is designed
to mimic Oracle code, drivers, and queries while providing support for certain
Oracle features or workloads. These features could potentially reduce code and
application rewrites by up to 80%. Additionally, EDB Postgres Advanced Server
has robust application security to minimize migration risks and delays.

What does EDB's Fully Managed Oracle-compatible solution offer?
_chevron_right_

Our fully managed, multi-cloud Postgres solution will allow you to focus on
building and scaling your business, while we fully undertake the burden of
database deployment, administration, and operational tasks. We'll also ensure
high availability and backups of your mission-critical data and provide 24/7
professional assistance for any issues you may encounter.

How does EDB conduct Oracle migrations to PostgreSQL? _chevron_right_

We'll first look to gain visibility over your entire database estate and map
applications to related data schema for assessment purposes. We'll then define
which databases are part of scope, define ROI that meets the migration
strategy, and determine a prioritized migration backlog. For full insight into
our iterative approach, click here.

What are the requirements for EDB's Oracle migration? _chevron_right_

Our professional services team will conduct a free migration assessment of
your schema and data to ensure that your database qualifies for a migration to
PostgreSQL.

#####

##

## Ready to Move to PostgreSQL

###

And Unlock Greater Possibilities with Your Data?

Reach out to us with your migration requirements and let’s get you started
towards a more flexible, cost-efficient, and scalable database management
solution

Contact Us

![Footer logo highres](/sites/default/files/images/EDB-logo-highres.png)

Off

Off

## Social Nav Footer

  * __
  * __
  * __
  * __

## Footer Nav Main

  * Products
    * EDB Postgres AI Platform
    * EDB Postgres Advanced Server
    * EDB Postgres Extended Server
    * EDB Postgres Distributed
    * EDB Postgres AI Cloud Service
    * Tools
    * Subscription Plans Comparison
    * Services & Support
  * Workloads
    * Enterprise-Grade Postgres
    * Oracle Migration and Modernization
    * Geo-distributed, Extreme High Availability
    * Multi-cloud Data Infrastructure
    * Data Estate Management
    * Rapid Analytics for Postgres
    * Postgres for AI Applications
    * Postgres for Kubernetes
  * Technical Resources
    * Product Documentation
    * EDB Software Downloads
    * Support Portal
    * Training
    * Trust Center
    * EDB Purchase Program
  * Learn & Explore
    * Blog
    * Customer Success Stories
    * Events
    * Webinars
    * White Papers
    * Learning Library
  * Company
    * About
    * Leadership
    * Press & News
    * Careers
    * Partners
    * Contact Us

## Footer Copyright CTA

  * © 2024 EDB
  * Legal Notices
  * ESG
  * Privacy Policy
  * Marketing Preferences
  * Cookie Preferences

 Пример 1: Простой вывод
PL/SQL:
DECLARE
    v_message VARCHAR2(50) := 'Hello, World!';
BEGIN
    DBMS_OUTPUT.PUT_LINE(v_message);
END;
/
PL/pgSQL:
DO $$
DECLARE
    v_message VARCHAR(50) := 'Hello, World!';
BEGIN
    RAISE NOTICE '%', v_message;
END;
$$;

Пример 2: Условное выражение IF
PL/SQL:
DECLARE
    v_num NUMBER := 5;
BEGIN
    IF v_num > 0 THEN
        DBMS_OUTPUT.PUT_LINE('Positive number');
    ELSE
        DBMS_OUTPUT.PUT_LINE('Negative number or zero');
    END IF;
END;
/
PL/pgSQL:
DO $$
DECLARE
    v_num INTEGER := 5;
BEGIN
    IF v_num > 0 THEN
        RAISE NOTICE 'Positive number';
    ELSE
        RAISE NOTICE 'Negative number or zero';
    END IF;
END;
$$;

Пример 3: Обработка цикла FOR
PL/SQL:
BEGIN
    FOR i IN 1..5 LOOP
        DBMS_OUTPUT.PUT_LINE('Value: ' || i);
    END LOOP;
END;
/
PL/pgSQL:
DO $$
BEGIN
    FOR i IN 1..5 LOOP
        RAISE NOTICE 'Value: %', i;
    END LOOP;
END;
$$;

Пример 4: Работа с курсорами
PL/SQL:
DECLARE
    CURSOR cur IS SELECT ename FROM emp;
    v_ename emp.ename%TYPE;
BEGIN
    OPEN cur;
    LOOP
        FETCH cur INTO v_ename;
        EXIT WHEN cur%NOTFOUND;
        DBMS_OUTPUT.PUT_LINE(v_ename);
    END LOOP;
    CLOSE cur;
END;
/
PL/pgSQL:
DO $$
DECLARE
    cur CURSOR FOR SELECT ename FROM emp;
    v_ename emp.ename%TYPE;
BEGIN
    OPEN cur;
    LOOP
        FETCH cur INTO v_ename;
        EXIT WHEN NOT FOUND;
        RAISE NOTICE '%', v_ename;
    END LOOP;
    CLOSE cur;
END;
$$;

Пример 5: Создание функций
PL/SQL:
CREATE OR REPLACE FUNCTION get_employee_name(p_empno NUMBER) RETURN VARCHAR2 IS
    v_name VARCHAR2(50);
BEGIN
    SELECT ename INTO v_name FROM emp WHERE empno = p_empno;
    RETURN v_name;
END;
/
PL/pgSQL:
CREATE OR REPLACE FUNCTION get_employee_name(p_empno INTEGER) RETURNS VARCHAR AS $$
DECLARE
    v_name VARCHAR(50);
BEGIN
    SELECT ename INTO v_name FROM emp WHERE empno = p_empno;
    RETURN v_name;
END;
$$ LANGUAGE plpgsql;

Пример 6: Обработка ошибок
PL/SQL:
BEGIN
    INSERT INTO emp(empno, ename) VALUES (101, 'John');
EXCEPTION
    WHEN DUP_VAL_ON_INDEX THEN
        DBMS_OUTPUT.PUT_LINE('Duplicate value error');
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('Other error');
END;
/
PL/pgSQL:
DO $$
BEGIN
    INSERT INTO emp(empno, ename) VALUES (101, 'John');
EXCEPTION
    WHEN unique_violation THEN
        RAISE NOTICE 'Duplicate value error';
    WHEN OTHERS THEN
        RAISE NOTICE 'Other error';
END;
$$;

Пример 7: Процедуры с параметрами
PL/SQL:
CREATE OR REPLACE PROCEDURE update_salary(p_empno NUMBER, p_salary NUMBER) IS
BEGIN
    UPDATE emp SET sal = p_salary WHERE empno = p_empno;
    COMMIT;
END;
/
PL/pgSQL:
CREATE OR REPLACE PROCEDURE update_salary(p_empno INTEGER, p_salary NUMERIC) LANGUAGE plpgsql AS $$
BEGIN
    UPDATE emp SET sal = p_salary WHERE empno = p_empno;
    COMMIT;
END;
$$;

Пример 8: Использование NVL
PL/SQL:
SELECT employee_id, NVL(salary, 0) AS salary
FROM employees;
PL/pgSQL:
SELECT employee_id, COALESCE(salary, 0) AS salary
FROM employees;

Пример 9: Использование DECODE для условных операций
PL/SQL:
SELECT employee_id,
       DECODE(department_id, 
              10, 'HR', 
              20, 'Finance', 
              30, 'IT', 
              'Other') AS department_name
FROM employees;
PL/pgSQL:
SELECT employee_id,
       CASE 
           WHEN department_id = 10 THEN 'HR'
           WHEN department_id = 20 THEN 'Finance'
           WHEN department_id = 30 THEN 'IT'
           ELSE 'Other'
       END AS department_name
FROM employees;

Пример 10: Условное обновление с использованием DECODE и NVL
PL/SQL:
BEGIN
    UPDATE employees
    SET salary = NVL(salary, 5000),
        department_name = DECODE(department_id, 10, 'HR', 20, 'Finance', 'Unknown');
    COMMIT;
END;
/
PL/pgSQL:
DO $$
BEGIN
    UPDATE employees
    SET salary = COALESCE(salary, 5000),
        department_name = CASE 
            WHEN department_id = 10 THEN 'HR'
            WHEN department_id = 20 THEN 'Finance'
            ELSE 'Unknown'
        END;
    COMMIT;
END;
$$;

Пример 11: Создание таблицы
PL/SQL:
CREATE TABLE departments (
    department_id NUMBER(4) PRIMARY KEY,
    department_name VARCHAR2(50) NOT NULL,
    manager_id NUMBER(6),
    location_id NUMBER(4)
);
PL/pgSQL:
CREATE TABLE departments (
    department_id INTEGER PRIMARY KEY,
    department_name VARCHAR(50) NOT NULL,
    manager_id INTEGER,
    location_id INTEGER
);

Пример 12: Вставка данных в таблицу
PL/SQL:
INSERT INTO departments (department_id, department_name, manager_id, location_id)
VALUES (10, 'Administration', 200, 1700);
PL/pgSQL:
INSERT INTO departments (department_id, department_name, manager_id, location_id)
VALUES (10, 'Administration', 200, 1700);

Пример 13: Обновление данных в таблице
PL/SQL:
UPDATE employees
SET salary = salary * 1.1
WHERE department_id = 20;
PL/pgSQL:
UPDATE employees
SET salary = salary * 1.1
WHERE department_id = 20;

Пример 14: Удаление данных из таблицы
PL/SQL:
DELETE FROM employees
WHERE employee_id = 100;
PL/pgSQL:
DELETE FROM employees
WHERE employee_id = 100;

Пример 15: Создание индекса
PL/SQL:
CREATE INDEX idx_employee_name ON employees (last_name);
PL/pgSQL:
CREATE INDEX idx_employee_name ON employees (last_name);

Пример 16: Создание представления (VIEW)
PL/SQL:
CREATE VIEW emp_dept AS
SELECT e.employee_id, e.first_name, e.last_name, d.department_name
FROM employees e
JOIN departments d ON e.department_id = d.department_id;
PL/pgSQL:
CREATE VIEW emp_dept AS
SELECT e.employee_id, e.first_name, e.last_name, d.department_name
FROM employees e
JOIN departments d ON e.department_id = d.department_id;

Пример 17: Простая функция без анонимного блока
PL/SQL:
CREATE OR REPLACE FUNCTION get_department_name(p_department_id NUMBER) RETURN VARCHAR2 IS
    v_department_name VARCHAR2(50);
BEGIN
    SELECT department_name INTO v_department_name FROM departments WHERE department_id = p_department_id;
    RETURN v_department_name;
END;
/
PL/pgSQL:
CREATE OR REPLACE FUNCTION get_department_name(p_department_id INTEGER) RETURNS VARCHAR AS $$
DECLARE
    v_department_name VARCHAR(50);
BEGIN
    SELECT department_name INTO v_department_name FROM departments WHERE department_id = p_department_id;
    RETURN v_department_name;
END;
$$ LANGUAGE plpgsql;

Пример 18: Получение списка всех таблиц
PL/SQL:
SELECT table_name FROM user_tables;
PL/pgSQL:
SELECT tablename FROM pg_tables WHERE schemaname = 'public';

Пример 19: Получение списка всех столбцов таблицы
PL/SQL:
SELECT column_name, data_type FROM user_tab_columns WHERE table_name = 'EMPLOYEES';
PL/pgSQL:
SELECT column_name, data_type FROM information_schema.columns WHERE table_name = 'employees';

Пример 20: Создание секвенции (SEQUENCE)
PL/SQL:
CREATE SEQUENCE emp_seq
START WITH 1
INCREMENT BY 1
NOCACHE;
PL/pgSQL:
CREATE SEQUENCE emp_seq
START WITH 1
INCREMENT BY 1
NO CACHE;

Пример 21: Использование COALESCE вместо NVL
PL/SQL:
SELECT employee_id, NVL(commission_pct, 0) FROM employees;
PL/pgSQL:
SELECT employee_id, COALESCE(commission_pct, 0) FROM employees;

Пример 22: CASE вместо DECODE
PL/SQL:
SELECT employee_id,
       DECODE(department_id, 
              10, 'HR', 
              20, 'Finance', 
              'Other') AS department_name
FROM employees;
PL/pgSQL:
SELECT employee_id,
       CASE 
           WHEN department_id = 10 THEN 'HR'
           WHEN department_id = 20 THEN 'Finance'
           ELSE 'Other'
       END AS department_name
FROM employees;