Re: PostgreSQL
From: richard goulet <rjgoulet_at_comcast.net>
Date: Tue, 10 Jan 2023 18:19:35 -0500
Message-ID: <9f2bc170-b19c-a2a4-4758-28f96b51782c_at_comcast.net>
As one who has tried migrations from Oracle6 thru 21 to PostgreSQL and failed, as well as succeeded, the path is not easy. Please start at the beginning as if you were implementing the application from the start on PostgreSQL in a temporary install. This way you will find all of the pitfalls and areas where re-coding/re-implementation are necessary without impacting the original application. Oracle SQL is not Postgres SQL and adding the "LANGUAGE=PL/SQL" to the end of a procedure/package/function is not fool proof. Yes there are a lot of tools out there to help, but again they are not foolproof either and yes some are better than others but each has a weakness that can hit you right where it hurts. Also do not underestimate your need for Vacuum, which in later versions of PostgreSQL can be invoked automatically by the RDBMS as necessary, but then look out for issues with DB performance, Vacuum can be a CPU and IO hog depending on the number of versions it has to purge. I would look at your current disk allocation on the host and double that easily. Also be aware of rows that get very frequent updates. Finding that current row isn't hard, but vacuum may have issues with the current version and those that it thinks are still active.
Date: Tue, 10 Jan 2023 18:19:35 -0500
Message-ID: <9f2bc170-b19c-a2a4-4758-28f96b51782c_at_comcast.net>
As one who has tried migrations from Oracle6 thru 21 to PostgreSQL and failed, as well as succeeded, the path is not easy. Please start at the beginning as if you were implementing the application from the start on PostgreSQL in a temporary install. This way you will find all of the pitfalls and areas where re-coding/re-implementation are necessary without impacting the original application. Oracle SQL is not Postgres SQL and adding the "LANGUAGE=PL/SQL" to the end of a procedure/package/function is not fool proof. Yes there are a lot of tools out there to help, but again they are not foolproof either and yes some are better than others but each has a weakness that can hit you right where it hurts. Also do not underestimate your need for Vacuum, which in later versions of PostgreSQL can be invoked automatically by the RDBMS as necessary, but then look out for issues with DB performance, Vacuum can be a CPU and IO hog depending on the number of versions it has to purge. I would look at your current disk allocation on the host and double that easily. Also be aware of rows that get very frequent updates. Finding that current row isn't hard, but vacuum may have issues with the current version and those that it thinks are still active.
So the question becomes is it easy, Yes, is it foolproof, NO, and in the end is it worth it. That last question is the hardest to answer and the quickest to bite you where it really hurts.
-- http://www.freelists.org/webpage/oracle-lReceived on Wed Jan 11 2023 - 00:19:35 CET