Migration from embedded PostgreSQL to Oracle DB

[#1]

Hello, we would like to move our environment from the embedded PostgreSQL database to an ad-hoc Oracle database.

We experimented a in a toy cluster moving from the embedded Postgres to an external one and it was quite easy following the documentation (for reference: https://docs.cloudera.com/documentation/enterprise/6/6.3/topics/cm_ag_migrate_postgres_db.html).

The problem is moving from a db to another. The documentation is still pretty good and it explains everything about how to configure the Oracle instance in order to correctly work with Cloudera, but there is no clear indication on how to move data from Postgres to Oracle.

We know that there are instruments capable of migrating data between Postgres and Oracle, but we would like to know if there are best practices from Cloudera in order to do so.

Thanks

[#2]

@geralt You need two steps process because of embedded DB. 

1. Migrating from the Cloudera Manager Embedded PostgreSQL Database Server to an External PostgreSQL Da....

2. Then Migrating from the Cloudera Manager External PostgreSQL Database Server to a MySQL/Oracle Database S...

Hope this helps. 

 

[#3]

Thanks. As I said we already tested the migration from the embedded database to an external PostgreSQL without any issue. The problem is that it's not clear how we should backup and migrate data from the external PostgreSQL to Oracle.

In the link you posted there is no indication about how to migrate the database (https://docs.cloudera.com/documentation/enterprise/6/6.3/topics/cm_ag_migrate_postgres_db_to_oracle_...). Maybe I'm missing something... Here are the steps

Step 6: prepare the target database for Cloudera Manager. For details, refer to Install and Configure MySQL for Cloudera Software or Install and Configure Oracle Database for Cloudera Software.

So, we went to Install and Configure Oracle Database for Cloudera Software and we configured our Oracle Database in order to host Cloudera Manager and other CDH services. Obviously there is no info about how to migrate data between databases since this guide is for a clean cluster setup.

Back to the guide you posted:

step 7: is about stopping cloudera manager agents

step 8-13: they talk about restoring the cloudera manager configuration, there's not any suggestion about how to migrate data between an external postgresql database and a oracle database.

could you kindly enlighten me about this aspect? because from the way you indicated it seems to be right under my nose but I can't see it.

[#4]

@geralt  The 2nd step form the doc Export your Cloudera Manager Configuration. First, get the latest supported API version will create a json file which contains all the Configuration of CM. 

Then the 11th step will restore the same Configuration file and will insert the data into new (Oracle) database. So practically you are not going to take database dump and then restore that again as you did in other cases, but you are going  to insert the Configuration into new DB. 

Hope that make sense. 

[#5]

Ok thanks @GangWar. So this is going to migrate only CM configuration from PostgreSQL to Oracle, right ? But what about other data like for example details about Oozie workflows and schedules ?
They're stored inside the PostgreSQL database, how we're supposed to move this data to Oracle ?

[#6]

@geralt Oozie workflows etc. is not the part of CM database. Hue has it's own Database so for migration those stuffs you will be needing to Migrate Hue Database.