Databases
More and more organizations are switching from Oracle to PostgreSQL due to lower costs, flexibility, and modern cloud and GIS capabilities. Participants will learn how to efficiently migrate Oracle databases to PostgreSQL, including SQL conversions, procedures, performance tuning, and data migration. The course also covers geo-data. It combines theory, migration strategies, and hands-on exercises from realistic Geo-ICT environments.
Effortless Migration from Oracle to PostgreSQL is a hands-on course in which participants learn how to migrate Oracle databases to PostgreSQL in a controlled and efficient manner. PostgreSQL is increasingly being chosen due to its lower licensing costs, flexibility, strong performance, and broad applicability within modern data and Geo-ICT environments.
During this 2-day course, participants will learn the key differences between Oracle and PostgreSQL. The course covers SQL conversions, data types, constraints, indexes, procedures, functions, migration tools, and performance tuning. Spatial data is also addressed, with a focus on the transition from Oracle Spatial to PostGIS.
The course is intended for database administrators, developers, data engineers, application administrators, and Geo-ICT specialists involved in database management or migration projects.
During the course, participants will work step-by-step on a migration from Oracle to PostgreSQL. First, we will examine the architectural differences between the two database systems, including user management, storage, transactions, schemas, and configuration.
Participants then learn how to convert Oracle SQL to PostgreSQL SQL. Topics covered include data types, sequences, identity columns, views, materialized views, constraints, and indexes. The course also covers migration tools such as ora2pg, pgloader, DBeaver, and PostgreSQL command-line tools.
On the second day, the focus is on more complex migration components. Participants learn how to translate PL/SQL code into PL/pgSQL, how to build triggers and functions, and how to analyze performance issues in PostgreSQL using EXPLAIN ANALYZE, indexing, VACUUM, and ANALYZE.
In addition, specific attention is given to Geo-ICT applications. Participants learn how spatial data from Oracle Spatial can be migrated to PostGIS and how spatial queries, geometries, SRIDs, and spatial indexes are applied within PostgreSQL.
This course offers a practical and realistic approach for organizations looking to switch from Oracle to PostgreSQL. Participants will not only learn about the technical differences but, more importantly, how to identify and resolve migration issues.
By combining database management, SQL conversion, migration tools, performance tuning, and PostGIS, this course is highly suitable for organizations in government, infrastructure, geoinformation, data analysis, and software development.
Upon completion, participants will have a clear understanding of the technical considerations, risks, and opportunities involved in an Oracle-to-PostgreSQL migration.
Need help?
Contact usOn the first day of the course, participants will learn about the key differences between Oracle and PostgreSQL. The course covers the architecture, storage structures, user management, and transaction handling of both database systems. Participants will then learn how to convert Oracle SQL to PostgreSQL SQL, including differences in data types, sequences, views, constraints, and indexing.
In addition, the course utilizes professional migration tools such as ora2pg, pgloader, and pgAdmin. Participants will perform a step-by-step, hands-on migration, transferring tables, data, constraints, and indexes from an Oracle environment to PostgreSQL.
The second day focuses on more complex migration issues and the optimization of PostgreSQL environments. Participants will learn how to translate PL/SQL procedures, functions, and triggers into PL/pgSQL, and how to analyze and resolve performance issues using techniques such as indexing, EXPLAIN ANALYZE, and query optimization.
In addition, attention is given to Geo-ICT applications and the migration from Oracle Spatial to PostGIS. This covers spatial data, geometries, SRIDs, and spatial queries. The course concludes with a practical case study in which a Geo-ICT database environment is migrated and optimized within PostgreSQL.
Do you have questions about the course content? Or are you unsure whether the course aligns with your learning goals or preferences? Would you prefer an in-house or private course? We’d be happy to help.
Not always. Much of the PL/SQL functionality can be ported to PL/pgSQL, but certain Oracle-specific features—such as packages, proprietary functions, or specific exception handling—often require modifications or a different approach in PostgreSQL.
Commonly used tools include ora2pg, pgloader, pg_dump, DBeaver, and native PostgreSQL tools. In the course, participants will learn which tools are suitable for different migration scenarios and how to use them in practice.
The fundamentals of SQL are very similar, but there are significant differences in data types, sequences, functions, joins, transaction handling, and stored procedures. During the course, participants will learn how to effectively address these differences during a migration.
In many cases, yes, but spatial data requires extra attention. Geometries, SRIDs, spatial indexes, and spatial functions must be carefully checked and converted to PostGIS functionality within PostGIS.
After a migration, databases are validated through data checks, row counts, query comparisons, performance tests, and functional checks of applications. We also examine indexing, query performance, and the proper functioning of stored procedures and spatial queries.
