Whether migrating a database or application from Oracle to Postgres, as a first step, we need to analyze the database objects(DDLs), to find out the incompatibilities between both the databases and estimate the time and cost required for the migration. In schema migration, having a good knowledge of Oracle and Postgres helps to identify incompatibilities and choose the right tool for analysis/conversion. In this webinar, we discuss schema incompatibility hurdles when migrating from Oracle to Postgres and how to overcome them.
What you will learn in this webinar:
- How you identify if your oracle schema is compatible with PostgreSQL
- Incompatibility hurdles and identifying them with Migration tools
- How to Overcome incompatibility hurdles
- Available tools for conversion
- Post-migration activities - functional testing, performance analysis, data migration, application switchover
Webinar video:
Webinar slides:
Webinar Q&A:
- Is there a checklist that details what to check for wrt compatibility? A tool that can run a sanity check on say the application code outside the database such as java classes?
- AFAIK, currently, there’s no tool to check the compatibility at application level.
- I have a schema that used sdo_geometry and need to convert to geometry in PostgreSQL, is the only way to convert the data first to wkt?
- PostGIS is the extension you need to enable in PostgreSQL to get the sdo_geometry data type.
- Is there a difference between role and user in PostgreSQL?
- No difference, because CREATE USER is aliased to ROLE. A role WITH LOGIN can function like USER.
- Is there a tool or a way to migrate Oracle Packages, procedures (homegrown) to Postgres?
- Some of the tools will transform by applying the workaround like in AWS SCT or ORA2PG but they are limited and their target is to PostgreSQL. EDB Postgres Advanced Server has a compatibility layer, which means most of your packages/procedure/function may work without any change.
- Question about partitioning within PostgreSQL: Is SUB-Partitioning possible?
- Yes, supported. Oracle version of sub-partition should be changed to PostgreSQL partitioning syntax. Refer the Link: https://www.postgresql.org/docs/12/ddl-partitioning.html
- As I understand the biggest benefit is for those applications that store tables and views in the database - If you have a lot of logics will it be hard or impossible to migrate?
- It will be extra effort to migrate, because you need to review your logic compatibility in a new database platform and do changes accordingly.
- In Oracle, a user account and schema are the same things. After migration to Postgres, how will it look, a schema or a user account?
- User only. Schema are default enabled to users in the form of “search_path” in PostgreSQL. Example(User1 connecting to database and his default schema)
-bash-4.2$ psql -U user1
psql (12.1.2)
Type "help" for help.
edb=> show search_path ;
search_path
-----------------
"$user", public
(1 row)
- If I have Hash partition in Oracle, can I migrate to Postgres or do we have to write codes to convert it?
- Hash Partition is supported in PostgreSQL. No conversion required.
- Are Java stored procedures supported?
- For Java, we need to enable JAVA language in PostgreSQL(pl/java) and then it can be called. However, the recommended approach is move the Java code to application end, storing in Database and calling the OS will always increase the dependency.
- Global indexes are not currently supported - will they be in the future?
- There are some discussions around it on the pgsql-hacker list, but I'm not sure when it will be available. Here’s a to do list of PostgreSQL: https://wiki.postgresql.org/wiki/Todo
- How can I migrate a 3GB CLOB to PostgreSQL?
- Maximum limit is 1GB so you can map the CLOB column to TEXT. I am not sure if the application/driver has the capacity to lift the 3GB data after migration to PostgreSQL. Because, they are designed as the database limitation and functionality.
- Data Types: What are the limits of these datatypes? E.g. a CLOB can handle 8TB.
- No. CLOB upto 1GB
- In general, the index in Postgres takes more storage than that in Oracle. Is there any workaround to reduce storage usage by PG index when converting Oracle DB to PG DB?
- In PG, dmls statements create bloats in the indexes. Follow index maintenance to reduce the storage. https://wiki.postgresql.org/wiki/Index_Maintenance
- Oracle recommends usage of bulk collect in pl/sql to avoid context switching between pl/sql to sql? Is context switching not an issue in EDB PostgreSQL?
- No.
- Are there any tools that do this code conversion consistently?
- Depending on the target there are limited tools to convert the code consistently. After conversion, there are other things to be considered like functional and performance testing. We need to get the performance on the converted objects as well, otherwise it will not right migration. We recommend choosing a target that natively supports functionality in the database so conversion is minimized. Please refer to my slides and choose the tool as per the target.
- How does one convert all the oracle DBA related SQL used by DBAs into PostgreSQL DBA PgSQL query?
- There are many queries in PostgreSQL documentation for DBAs. Here’s the link: https://wiki.postgresql.org/wiki/Monitoring. EDB Postgres Enterprise Manager, has the DBA alerts. A good way to test it.
- Is there an equivalent to EXPLAIN PLAN?
- Yes, EXPLAIN ANALYZE is equivalent in PostgreSQL
https://www.postgresql.org/docs/12/sql-explain.html
- Is there a matrix of data types and compatibility wrt EDB, native PostgreSQL vs Oracle?
- Yes, we have white papers that describes the differences at very high level.
EDB Postgres vs Oracle
EDB Postgres vs PostgreSQL
- Do sequences in EDB support currval, nextval, etc. without the parentheses?
- Yes.
edb=# create sequence seq1;
CREATE SEQUENCE
edb=# select seq1.nextval;
nextval
---------
1
(1 row)
edb=# select seq1.currval;
currval
---------
1
(1 row)
- Are global indexes supported with partitioned tables and/or temporary tables?
- Not yet in the current version.
- What is the best way to handle/Keep/Architect Hot & Cold Data in the PostgreSQL database?
- Using Pg_prewarm extension we can handle the hot/cold data.
https://www.postgresql.org/docs/12/pgprewarm.html
- What is the best way to do Migration Data from Hot to Cold in a frequent manner in the PostgreSQL database?
- Using Foreign Data Wrapper(FDW) or Replication Snapshot we migrate Hot to Cold data. This is discussed in more detail in our next webinar.
- Does EDB Advanced server include a JDBC driver with native CALL support?
- Yes.
- From a security perspective, is the virtual private database (VPD) functionality of Oracle supported by EDB Advanced Postgres?
- Some of the VPD functionality covers in EDB Postgres Advanced server
- Can the migration tool handle oracle spatial conversion to Postgis?
- No.
- Is there a SQL certification for Postgres?
- Natively supported.
https://www.postgresql.org/docs/12/libpq-ssl.html
- Are there any white papers on differences between Oracle and PostgreSQL?
- EDB Postgres vs Oracle
- What should be the strategy to move database from Oracle to Postgres (On-Prem to AWS)?
- If its AWS RDS Postgres, use AWS SCT.
- Should we create tablespaces for individual databases assuming we are using the same storage array?
- No. The Database cluster will be on the same storage array. If we want any database to be on different storage array then using tablespace it can be moved.
- Which kind of oracle RDBMS (DWH, OLTP, ETC.) are suggested to migrate on Postgres?
- Both are supported. We should be cautious if DWH, the ETL tools/drivers are compatible or not.
- What are the challenges for converting Java stored procedures?
- If we have to convert Oracle Java objects to PostgreSQL, then a recommended approach to keep the Java code outside the database and allow applications to take control of it, so we can reduce the conversion work else it will be a lot of manual work.
- UDT was mentioned in the EDB Postgres Advanced Server slide. What is the support for UDT in Postgres?
- Composite and Enumerate Types are supported. Refer to compatibility link:
EDB Postgres vs Oracle
- Does EDB have an OCI-compatible layer? Isn't it possible to connect to an EDB database just by changing the OCI dll?
- Yes. Using OCL connector. Here’s the reference link.
- Can we still use the AWS SCT tool for migrations that are not being hosted in the cloud?
- AWS SCT is a standalone tool. You can still use it for non cloud environment also.
- How does EDB handle reference cursors?
- Reference cursors are natively supported. Please go through the compatibility guide.
EDB Postgres vs Oracle
- Does EDB support modules such as PgPool, Barman, PgAudit, etc. under EDB SE license?
- PgPool is supported, PgAudit/Barman not in support list.
- Can a Weblogic implementation's backend DB get migrated from Oracle to Postgres?
- Weblogic Application server is closely tied with Oracle backend database. Need to check if Weblogic supports Postgres DB/Driver.
- Can you discuss transaction support limitations in procedures and functions? In Oracle, we can commit and rollback within functions and procedures, but in PostgreSQL, procedures running under as DEFINER cannot COMMIT nor ROLLBACK.
- COMMIT/ROLLBACK cannot be used inside function/procedure. We need to use EXCEPTION handling and if transaction rolled back we need to use retry logic from application.
- Is there a checklist that details what to check for wrt compatibility? A tool that can run a sanity check on say the application code outside the database such as java classes?
- No.
- Are types and type bodies supported?
- Yes.
EDB Postgres vs Oracle
Free Oracle® to Postgres Migration Tool
Convert Oracle to compatible Postgres in just 3 simple steps!