Oracle to PostgreSQL database migrations

/ / Architecture, Blog, Document Management
Alfresco Application Framework
Installing Alfresco 5.1.g from scratch

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.

Unidad de negocio, keensoft

4 Comments to “ Oracle to PostgreSQL database migrations”

  1. GeorgeH says :

    Hi,
    This is for an upgrade from 4.2 to 5.x.
    Will it also work on a migration of an Alfresco 5.1 Oracle DB to Alfresco 5.1 Postgres database?
    In that case, would it be necessary to run some of the migration scripts?
    Thanks

    1. Mikel Asla Remon says :

      Hi GeorgeH,

      You will need to adapt all the 4.2 scripts and also the schema reference to 5.1 in order to make it work.
      Btw, you can check this project on github that was the base for this presentation i did in the Beecon2017 back on April this year.

      Regards

  2. Miguel says :

    Hi Mikel,
    Does this work to migrate oracle to postgres in Alfresco 3?

    Thanks.

  3. Angel Borroy says :

    Yes, it works, but you’ll have to adapt 3.x scripts.