Migrate Code from Oracle to PostGre database
Consider a scenario such that Dev environment is on oracle database and UAT/Prod is on PostGre SQL database. What could be the possible problems faced? Following questions came to my mind,
1. Is RAP(Product) compatible while migrating code from dev to UAT/Prod environment? Considering, there are some SQL scripts that are created by PRPC itself for alter table which are run on DB during migration.
2. Can there be any runtime rule execution errors when Dev and UAT environments are on different database?
3. Columns datatype created in Oracle are compatible with PostgreSQL?
4. During migration schema.xml contains some queries which are vendor specific. Can that cause a problem?
5. What about the complex SQL queries built for Reports? Becuase those may contain database specific syntax.
In addition to these, please ask any other questions come to your mind.
And mainly, using two different databases for two different environment is feasible or not? What are pros and cons? What could be the problems faced in future for application maintenance.
Judging by your ultimate goals, i.e., using postgres for UAT/Prod, you should take one time pain of converting Oracle to Postgres all together - this will need careful plan to identify what need to be moved from Oracle to Postgres.
I do not see any pros of maintaining two db platforms and plenty of cons. The only reason people use Oracle is due to its maturity and reputation, and of course peace of mind.
Cons:
Oracle in general tends to be slower than Postgres (that is a lot of pain for dev environment, e.g., frequent server restart) unless it is expertly tuned, which would incur cost of hiring Oracle DBA.
Oracle expensive (Versus Postgres cost effective)
Oracle specific queries (your own) need to be retested in UAT/Prod
Hi Kevin,
What is the best approach to migrate our PRPC systems from Oracle to Postgresql.
1. Migrate the entire database using Data Migration tools(AWS DMS) from Oracle to Postgresql
2. Install Pega Base Product on Postgresql and then extract the custom code as RAP and install on top of base product. What happens to Application Data in this case?
Hi Abubaker,
We did SQL server to Oracle data migration, we have used utility provided by oracle for conversion of data and move the table to respective Schema.Similary find any utility for postgre conversion.
Below are the steps we followed,
1. Application Specific data tables - check for any utility that will do conversion from oracle to postgre sql and move the table to Postgre data schema.
2. For work objects, assignments, history tables, you have to move entire table after conversion.
3. For Rule instance, take RAP and install it New postgre environment.
Thanks,
AS
Hi Kunal, here are few comments
1. yes. review the SQL that is being applied in UAT & make necessary changes.
2. this should not be a problem.
3. do you have any specific problem with column types?
4. vendor specific queries needs to reviewed and updated.
5. yes, this would require update as well. you could use circumstancing based on db vendor.
just curious - why do you have different databases for dev & UAT? that is not common.