Replacing Oracle with Postgres

[JOE MURRAY]

Hi Folks

 

We are planning on moving to 12.3 early next year and have spotted that 12.3 supports Postgres as the underlying database.

 

Has anyone migrated from Oracle to Postgres and if so did you encounter any issues ?

 

Regards

Joe

 

Please note that Revenue cannot guarantee that any personal and sensitive data, sent in plain text via standard email, is fully secure. Customers who choose to use this channel are deemed to have accepted any risk involved. The alternative communication methods offered by Revenue include standard post and the option to use our (encrypted) MyEnquiries service which is available within myAccount and ROS. You can register for either myAccount or ROS on the Revenue website. Tabhair faoi deara nach féidir leis na Coimisinéirí Ioncaim ráthaíocht a thabhairt go bhfuil aon sonraí pearsanta agus íogair a gcuirtear isteach i ngnáth-théacs trí r-phost caighdeánach go huile is go hiomlán slán. Meastar go nglacann custaiméirí a úsáideann an cainéal seo le haon riosca bainteach. I measc na modhanna cumarsáide eile atá ag na Coimisinéirí ná post caighdeánach agus an rogha ár seirbhís (criptithe) M'Fhiosruithe a úsáid, tá sí ar fáil laistigh de MoChúrsaí agus ROS. Is féidir leat clárú le haghaidh ceachtar MoChúrsaí nó ROS ar shuíomh gréasáin na gCoimisinéirí.

[Tobias Stanzel]

I can just give you some approaches on migrating from oracle to postgresql:

1) Use Unload in oracle / and Load in posgresql to migrate
This can take quite some time and memory (on the utilities)
Not recommended to unload / load with all statistics/execution data for that reason but only for definitions. Make sure you only export the last month of statistics by reorg/unload/... before starting the migration or only migrate definitions and skip all statistics records.

2) Setup a new system with postgresql and use transportcase or client copy to move project by project to this new system.

Hope that helps.

[JOE MURRAY]

Thanks Tobias

 

I was hoping it was that straight forward.

We never brought the stats and reports with us when we migrated to a new oracle database , so these are  no loss.

 

Have you  encountered any issues with characters sets ?

 

Regards

Joe

​

[Tony Ferraro]

Joe, or anyone else!

Did you successfully migrate from Oracle to Postgres using unload/load? Or any other method?

Thanks!

[Yuan Chieh Ying]

Hello Tobias,

It's been over 5 years so this may be a long shot but are there specific parameters we need to set with the UNLOAD/LOAD utility to include execution history? We recently tried to use the UNLOAD utility with the -btransportall flag but it did not carry any execution history over when we loaded in the unloaded .txt file. As for the second approach, does the client copy ensure that the execution history is copied over? 

Thanks,

Jay

[Markus Embacher]

Hi Jay,

the parameter -BTRANSPORTALL only exports objects. Depending on your use case you have several options

*) -BNORMAL
This unloads the full database.
*) Client copy utility
Enables you to copy a full client including all objects and execution records.

Please be careful when migrating from a database which is case sensitive to a database which is case insensitive. This may create duplicate keys for VARA objects.

Regards, Markus

[Yuan Chieh Ying]

Deleting since it's a duplicate of my other reply

[Scott Hughes]

I migrated us from Oracle 12.x to postgres earlier this year.  There is a glitch in the lower versions of Automic 12 which consume a lot of db sessions, fixed in 12.3.8  I migrated as Tobias details, load and unload with a brand new system.  We do not care about statistics and old reports.  You should take special care to populate all of the sql variables with postgres query variants, some postgres functions change from oracle (of the top of my head sysdate is different).

I tried to load my prod postgres into an analytics database and this did not work, I will have to change the user on analytics to allow them to work together.  Analytics was installed as automic and has matching tables, just a bad conflict and needs to be avoided.

You will want to pay special attention to postgres networking and your pg_hba.conf file  postgresql.conf recommendations in the manual are good, I would turn on session logging if you want to troubleshoot connectivity out of the box. 
log_connections = on
log_disconnections = on
I found those gems my second or third install when chasing connectivity, I set them before starting now.

------------------------------
Scott Hughes
Senior Automation Engineer
Verizon
Albuquerque, NM
------------------------------