If you ever have encountered the need to make Oracle to PostgreSQL database migrations this article may be usefull for you.
At keesoft we’ve accomplished this task sevelar times, specifically on Alfresco migration projects based previously on Oracle. As a result we’ve built a tool to ease the process of transforming Oracle databases to PostgreSQL. To do that, we’ve based our tool in Ora2pg and Docker.
Ora2pg is a Perl module created by Gilles Darold and recognized by PostgreSQL as a tool to convert Oracle schemas into PostgreSQL schemas.
Docker is a “application containerization” technology that allows us to package the transformation process, then configure and reuse it on different projects with different Alfresco versions. We also use Docker Compose to orchestrate Oracle and PostgreSQL containers as part of the same process.
It’s operation is simple, starting from an Oracle database backup file, the tool first imports this in an oracle docker container, then executes Ora2pg in order to export and transform Oracle schema data and finally the result is imported to PostgreSQL and additionally a database dump is done that can be distributed to a remote migration destination.
The resulting tool is easilly shipped and executed on any operating system without any other dependecy installation
How migrations works
- First thing to do is a backup of the Alfreso installation repository, source of the migration, following this steps
- Oracle database backup using Oracle provied tools
- Filesystem binaries backup (contentstore and contentstore.deleted)
- Locate the more recent Solr backup
- Next, the tool needs to be configured to point to the correct location for the backup file and define the specific version of Alfresco
- Once the process is configured, Oracle container is initialized and the backup is imported to a known tablespace
- Then PostgreSQL container is initialized as the migration destination
- Next, Ora2pg is executed to export and tranforms data contained in Oracle schema
- Once data is exported and transformed, PostgreSQL schema is created based on specific version Alfresco dbscripts for PostgreSQL dialect
- Next data is imported to Postgres
- In the last step of the migration shell scripts are executed in order to adapt schema
- Finally pg_dump is executed to export the resulting PostgreSQL database
To complete the migration between Oracle and Postgres, we’ll make a new Alfresco installation using the same version and PostgreSQL as database engine. Once basic funcionality test are done, we’ll stop the instance and restore the Alfresco repository in this way
- contentstore y contentstore.deleted folder restoration from the migration source backup
- Restore Database transformation process result
- Delete indexes or restore from backup
We hope to have soon an available version on our Github.