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.
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