Migrating from MySQL to Postgres

[venur]

Need suggestions,

we have a instance of alfresco 5.2.g which installed using distribution long time ago that uses Mysql. we are planning migrate to postgresql. 

I looked into backup and restore process document. And want to check if that will work straightway

If we just dump db and restore it in postgresql will it work? Would there be any specific changes needed to on postgresql in terms of indexes or anything if we move to postgresql.
Is there some tool that can be used to do this type of migration? 

We are clear on setting up database and user+permissions like we do for intial setup. 

[openpj · Accepted solution]

If we just dump db and restore it in postgresql will it work?

Yes, it should work but please consider to use a consistent backup of the Alfresco storage, I mean not only the database but also the contentstore where Alfresco is managing files in the file system.

You can create a consistent backup of an Alfresco installation in the following ways:

1. Cold backup: after shutting down all the services, export the database, copy the contentstore folder and the index folders, then if you want you can start again the services
2. Hot Backup: without shutting down the services, invoke the creation of the index backup folders via Administration Console or via JMX, export the database and only after the export is finished, copy contentstore folder

In any case you have only to translate the exported database in a different ER dialect and then you can import it in the new database server. After that you only need to add the new JDBC driver in the tomcat/lib folder and readdress correctly the JDBC endpoint in the alfresco-global.properties.

Is there some tool that can be used to do this type of migration? 

Not specifically provided by Alfresco but in the market you can find commercial tools that allows you to export from a specific dialect and then import in a different dialect.

[venur]

Thanks @openpj for answering. We were able to make this work already on the similar lines as you have mentioned.