Oracle to PostgreSQL Migration Planned.

[David Briars]

Our reporting data warehouse is migrating from Oracle to PostgreSQL.  

Our reporting application is mostly TABLEing views and reporting via Excel formatted reports.  

Has anyone worked with a migration like this before and can share any challenges encountered on the reporting application side?    

[Patrick Huebgen]

Hi @David Briars - this depends on what you are changing

If your WebFOCUS repository is moving as well - there are tools/techniques to do this transfer - not too complicated.

If it's "only" your reporting data is will depend on how you move the warehouse - in most cases this could be enough to change the SUFFIX - but very often there will be other changes as well as some datatypes will be different between the data sources.

You can try to recreated some of the masters for PostgreSQL and compare them with the existing Oracle masters (using Winmerge) to learn what is changing and then use Notepad++ for a mass update of the existing masters.

Patrick

 

  

[David Briars]

Thanks Patrick.  Yes, we have created the masters for PostreSQL, and are now comparing them to the masters under Oracle.  

[David Beagan]

It will be interesting to see if you encounter any surprises.

[Patrick Huebgen]

15 hours ago, David Beagan said:

It will be interesting to see if you encounter any surprises.

Great idea - @David Briars keep us in the loop about your findings - maybe we can help and learn at the same time

[David Briars]

Following up on this thread.  

We've successfully completed migrating our reporting data warehouse from Oracle to PostgreSQL.

There were no issues on the WebFOCUS enterprise reporting side.  Kudos to ibi for how backend/frontend separation/connections are setup and maintained within the WebFOCUS environment.  

If anyone else goes this route you will want to review and compare the masters created for each database before testing reports.  

  

[Patrick Huebgen]

@David Briars - great to hear and thanks for the update - please feel free to "Mark as Solution" one of the answers to help other users 

[David Beagan]

David, when you did the review and compare of the masters created, did you find many differences?

[David Briars]

The most frequent difference between the two sets of masters was for 'date' fields.  

In Oracle we had a data type of 'DATE' for dates.  

In PostgreSQL we have a date type of 'timestamp' for dates.  

Comparing the two masters therefore, we would see Usage = 'Smart Date' for Oracle, and Usage = 'DateTime' for PostgreSQL:  

We have a common -INCLUDE DEFINE FILE file in all of our reporting, so an easy way to handle this for us was to redefine the format of the field using the HDATE function.  

Also, in Oracle we had a data type of 'NUMBER' for several numeric fields.  

In PostgreSQL these fields had a date type of 'float8'. 

Comparing the two masters therefore, we would see different Usage values.  

 

The different Usage values didn't affect calcs using these fields.  While going from Usage of 'P' to 'D' was going to display commas; this wasn't an issue for us, as we were redefining these fields 'D', to get commas, anyway.    

My thought is that when a DBA team does this type of database conversion, whether manually or using a tool, they are going to match up the data types as best as possible; given that each database has their own unique way to define storage you'll want to compare the two sets of masters. 

 The master comparisons I show above may or may not apply to other sites.  

Hope this helps. 

[Patrick Huebgen]

The date behavior can be changed in a setting - Oracle has datetime as standard for date fields but you can „tell“ WebFOCUS to ignore the time part while creating the masters  - same is true for PostgeSQL the master not always reflects the real format - same is true for P vs. D