Migrate Applications from PostgreSQL Databases to Autonomous AI Database

You can translate SQL statements written in PostgreSQL to Oracle SQL and run the translated statements on Autonomous AI Database.

SET SERVEROUTPUT ON
   declare output_sql_result CLOB;
BEGIN
  DBMS_CLOUD_MIGRATION.MIGRATE_SQL(      
    original_sql => 'SELECT e.employee_id, e.last_name, e.salary FROM employees AS e;',
    output_sql   => output_sql_result,
    source_db    => 'POSTGRES');
    DBMS_OUTPUT.PUT_LINE (output_sql_result);
END;        
/

Output
–-------------------------------------------------------------
SELECT e.employee_id, e.last_name, e.salary FROM employees e;

SELECT DBMS_CLOUD_MIGRATION.MIGRATE_SQL(
     'CREATE TABLE IF NOT EXISTS cars (brand VARCHAR(255), model VARCHAR(255), year INT)',
     'POSTGRES') AS output FROM DUAL;

OUTPUT
------------------------------------------------------------------------------
create table cars (brand VARCHAR2(255), model VARCHAR2(255), year NUMBER(10);

You can interactively translate and run PostgreSQL statements in your Autonomous AI Database.

1. Connect to your Autonomous AI Database using a SQL client.

   See Connect to Autonomous Database for more information.

   Note
   
   DBMS_CLOUD_MIGRATION.ENABLE_TRANSLATION is not supported in Database Actions and is not supported with the Oracle APEX Service.
2. Run DBMS_CLOUD_MIGRATION.ENABLE_TRANSLATION to enable real-time SQL translation in your session.

   BEGIN
    DBMS_CLOUD_MIGRATION.ENABLE_TRANSLATION('POSTGRES');
   END;
   /

   Use the following query to verify the SQL translation language for your session:

   SELECT SYS_CONTEXT('USERENV','SQL_TRANSLATION_PROFILE_NAME') FROM DUAL;

   See ENABLE_TRANSLATION Procedure for more information.

3. Enter PostgreSQL statements. For example:

   CREATE TABLE movie (film_id int, title varchar(255));
   
   Table MOVIE created.

   This automatically translates and runs the PostgreSQL CREATE TABLE statement.

   You can verify using the DESC command. For example:

   DESC movie;
   
   Name        Null?     Type          
   -------     -----     ------------- 
   FILM_ID               NUMBER(38)    
   TITLE                 VARCHAR2(255)

   The MOVIE table is created and the datatypes of each of the columns are automatically converted to Oracle datatypes.

   You may encounter an error during the translation if the input SQL statement is not supported. See Limitations for Migration and Translation of PostgreSQL Statements to Oracle SQL for more information.

4. Insert data into the MOVIE table. For example:

   INSERT INTO movie (film_id, title) VALUES (123, 'Tangled');
   
   1 row inserted.
   
   INSERT INTO movie (film_id, title) VALUES (234, 'Frozen');
   
   1 row inserted.

   Verify the data insertion by querying the MOVIE table. For example:

   SELECT * FROM movie;
   
   FILM_ID  TITLE
   –------- –--------
   123	 Tangled
   234	 Frozen
   

5. Create an INVENTORY table:

   CREATE TABLE inventory (film_id int, inventory_id int);
   
   Table INVENTORY created.

   You can verify this step with the DESC command. For example:

   DESC inventory;
   
   Name        Null?     Type          
   -------     -----     ------------- 
   FILM_ID               NUMBER(38)    
   INVENTORY_ID          NUMBER(38)

   The INVENTORY table is created and the datatypes of each of the columns are automatically converted to Oracle datatypes.
6. Insert data into the INVENTORY table. For example:

   INSERT INTO inventory(film_id, inventory_id) VALUES (123, 223);
   
   1 row inserted.
   
   INSERT INTO inventory(film_id, inventory_id) VALUES (234, 334);
   
   1 row inserted.

   Verify the data insertion by querying INVENTORY. For example:

   SELECT * FROM inventory;
   
   FILM_ID  INVENTORY_ID
   –------- –------------
   123	 223
   234	 334

7. Perform a left outer join on tables MOVIE and INVENTORY:

   SELECT m.film_id, m.title, inventory.inventory_id 
       FROM movie AS m LEFT JOIN inventory 
       ON inventory.film_id = m.film_id;
   
     FILM_ID  TITLE       INVENTORY_ID
   ---------- ---------- ------------
          234 Frozen      334
          123 Tangled     223
   

   This example performs a LEFT OUTER JOIN on the movie and inventory tables. The AS keyword for table aliases in the FROM clause is not supported in Oracle SQL. The query is first translated to Oracle SQL and then run in your session.

8. Use DBMS_CLOUD_MIGRATION.DISABLE_TRANSLATION procedure to disable real-time SQL language translation for your session.

   BEGIN
    DBMS_CLOUD_MIGRATION.DISABLE_TRANSLATION;
   END;
   /

   This returns an error if SQL language translation is not enabled for your session.

   See DISABLE_TRANSLATION Procedure

SELECT v.* 
    FROM v$mapped_sql v, dba_objects o
    WHERE v.sql_translation_profile_id = o.object_id
        AND o.object_name = 'POSTGRES'
        AND o.object_type = 'TRANSLATION PROFILE';

You can migrate a file containing PostgreSQL statements to a file containing Oracle SQL statements.

1. Connect to your Autonomous AI Database instance.

   See Connect to Autonomous Database for more information.

2. Configure access to Cloud Object Storage using a resource principal or by creating a credential object.

   This step provides access to the Cloud Object Storage where you put the files you are migrating:

   • Use a resource principal to access Cloud Object Storage:

     Define policies and roles to access Oracle Cloud Infrastructure Object Storage with a resource principal. See Use Resource Principal to Access Oracle Cloud Infrastructure Resources and Use Resource Principal with DBMS_CLOUD for more information.

     If your files reside in another vendor's Cloud Object Storage, define the appropriate policies and roles to access the resource. See Configure Policies and Roles to Access Resources for more information.

   • Create a credential to access Cloud Object Storage:

     You can create a credential to access Cloud Object Storage.

     For example:

     BEGIN
       DBMS_CLOUD.CREATE_CREDENTIAL (
         credential_name => 'OBJ_STORE_CRED',
         username        => 'user1@example.com',
         password        => 'password'
       );
     END;
     /
     

     See CREATE_CREDENTIAL Procedure for more information.

3. Optionally, you can list the files in Object Storage. For example:

   VAR function_list CLOB;
   SELECT object_name FROM DBMS_CLOUD.LIST_OBJECTS
      (credential_name => 'OCI$RESOURCE_PRINCIPAL', location_uri => 'https://objectstorage.region.oraclecloud.com/n/namespace/b/bucket/o/files');
   
   OBJECT_NAME
   ---------------------
   postgrestest.sql
   

   Alternatively, if you create a credential instead of the resource principal, OCI$RESOURCE_PRINCIPAL, specify the credential name in the credential_name parameter.

   See LIST_OBJECTS Function for more information.

4. Run DBMS_CLOUD_MIGRATION.MIGRATE_FILE to migrate the PostgreSQL file to Oracle SQL:

   BEGIN
    DBMS_CLOUD_MIGRATION.MIGRATE_FILE (
        credential_name => 'OCI$RESOURCE_PRINCIPAL',
        location_uri    => 'https://objectstorage.region.oraclecloud.com/n/namespace/b/bucket/o/files/postgrestest.sql',
        source_db       => 'POSTGRES'
       );
   END;
   /

   The credential_name parameter specifies the credential to access the Cloud Object Storage URI. The user that runs DBMS_CLOUD_MIGRATION.MIGRATE_FILE must have EXECUTE privilege for the credential object used to access the Object Storage URI. That is, the credential you specify with the credential_name parameter. If you use a credential instead of a resource principal, specify the credential name in the credential_name parameter.

   The location_uri parameter specifies the source file URI. The format of the URI depends on the Cloud Object Storage service you are using. See DBMS_CLOUD URI Formats for more information.

   The source_db parameter specifies the PostgreSQL as database language. Use the value POSTGRES to translate PostgreSQL files to Oracle SQL.

   Running this command translates the PostgreSQL file postgrestest.sql to Oracle SQL and generates a new file with the name original_filename_oracle.sql.

   For this example, running DBMS_CLOUD_MIGRATION.MIGRATE_FILE with the input file postgrestest.sql generates postgrestest_oracle.sql. After the translation step the procedure uploads postgrestest_oracle.sql to Object Storage.

   Optionally, use the target_uri parameter to specify the location where the translated file is uploaded. The default value for this parameter is NULL, which means the translated file is uploaded to the same location as specified in the location_uri parameter.

   See MIGRATE_FILE Procedure for more information.

5. Verify that the output file was generated.

   SELECT object_name FROM DBMS_CLOUD.LIST_OBJECTS (
       credential_name => 'OCI$RESOURCE_PRINCIPAL', location_uri => 'https://objectstorage.region.oraclecloud.com/n/namespace/b/bucket/o/files');
   
   OBJECT_NAME
   ---------------------
   postgrestest.sql
   postgrestest_oracle.sql
   

   Alternatively, if you create a credential instead of the resource principal, OCI$RESOURCE_PRINCIPAL, specify the credential name in the credential_name parameter.

   See LIST_OBJECTS Function for more information.

SELECT UTL_RAW.CAST_TO_VARCHAR2 (DBMS_CLOUD.GET_OBJECT(
   credential_name => 'OCI$RESOURCE_PRINCIPAL', 
   object_uri => 'https://objectstorage.region.oraclecloud.com/n/namespace/b/bucket/o/files')) 
FROM dual;

UTL_RAW.CAST_TO_VARCHAR2(DBMS_CLOUD.GET_OBJECT(CREDENTIAL_NAME=>'CRED1',OBJECT_U
--------------------------------------------------------------------------------
SELECT f.film_id, f.title, inventory_id
FROM film f LEFT JOIN inventory
ON inventory.film_id = f.film_id;

This section summarizes the limitations for migrating SQL statements from PostgreSQL to Oracle SQL.