Steps I took to migrate my Alfresco 3.4d on MariaDB 5.5 to Alfresco 4.0d Postgres 9.3

gistfile1.txt

# Marco Garcês - mgarces at sysadmin dot pt
# 2014
# These are the steps I took to migrate an Alfresco 3.4d community, running on top of MariaDB 5.5.35-1
# to Alfresco 4.0d on PostgreSQL 9.3.4 [CentOS release 6.5 (Final)]
#
# Prerequisites:
# You need the original MySQL Database (I used a cloned virtual machine of my original server), 2 empty Postgres databases, with # name alfresco and alfresco40, with owner alfresco, password alfresco.
# I have also used the soon-to-be opensource tool, DBTransfer (https://github.com/mgarces/dbtransfer) to move the data from 
# MySQL to PostgreSQL.
#
# I assume this is all done on a backup copy of your data, so use this at your own risk. I also assume you have both 
# MySQL/MariaDB and PostgreSQL running on localhost.

1) Upgrade your alfresco 3.4d to 4.0d, following the docs. You can also migrate indexing to Solr.

2) After first run in 4.0. test that everything looks ok, and that the schema for MySQL was updated correctly.

3) Stop alfresco.

4) Move your 'alf_data' to 'alf_data_old' and create an empty alf_data.

5) change your 'alfresco-global.properties' to point to the Postgres server, to the 'alfresco40' database:
db.driver=org.postgresql.Driver
db.url=jdbc:postgresql://127.0.0.1:5432/alfresco40
db.name=alfresco40
db.username=alfresco
db.password=alfresco
db.host=127.0.0.1
db.port=5432

6)start alfresco and let it create the "vanilla" postgres database. After everything starts up, stop alfresco again.

7) Make 2 pg_dump from 'alfresco40' database, one with pre-data (the schema) the other with post-data (constraints)
pg_dump -U alfresco -h 127.0.0.1 --section=pre-data alfresco40 > ~/sql1.sql
pg_dump -U alfresco -h 127.0.0.1 --section=post-data alfresco40 > ~/sql2.sql

7.1) add this at the end of ~/sql2.sql to fix the sequences
------------------------------------------------------+
CREATE OR REPLACE FUNCTION "reset_sequence" (tablename text, columnname text, sequence_name text) RETURNS "pg_catalog"."void" AS 
    $body$  
      DECLARE 
      BEGIN 
 
      EXECUTE 'SELECT setval( ''' || sequence_name  || ''', ' || '(SELECT MAX(' || columnname || ') FROM ' || tablename || ')' || '+1)';
  exception when others then
	raise notice 'Wrong column name, ignore';
 
      END;  
 
    $body$  LANGUAGE 'plpgsql';

select reset_sequence(replace(S.relname, '_seq', ''), 'id', S.relname) from pg_class S where S.relkind = 'S';
select reset_sequence(replace(S.relname, '_seq', ''), 'sequence_id', S.relname) from pg_class S where S.relkind = 'S';
------------------------------------------------------



8) This step was necessary because column 'HAS_START_FORM_KEY_' is of type SMALLINT in MySQL, 
but BOOLEAN in PosgtgreSQL, so DBTransfer was trying to migrate data to INT, and we don't want that.
Perhaps in the future, we can pass extra option to DBTransfer to deal with these scenarios,
but for now DBTransfer tries to be a one-to-one (schema/data/constraints) generic database transfer tool.


ALTER TABLE ACT_RE_PROCDEF ADD COLUMN HAS_START_FORM_KEY_BIT BIT;
UPDATE ACT_RE_PROCDEF SET HAS_START_FORM_KEY_BIT = HAS_START_FORM_KEY_;
SELECT DISTINCT HAS_START_FORM_KEY_BIT > 0, HAS_START_FORM_KEY_ FROM ACT_RE_PROCDEF; #should return same values
ALTER TABLE ACT_RE_PROCDEF DROP COLUMN HAS_START_FORM_KEY_;
ALTER TABLE ACT_RE_PROCDEF CHANGE HAS_START_FORM_KEY_BIT HAS_START_FORM_KEY_ BIT;

9) Create the DBtransfer properties file with only transfer enabled:
----------------------------------------------------
alfresco.properties
----------------------------------------------------
URL_DB_SOURCE = jdbc:mysql://localhost:3306/alfresco
USER_DB_SOURCE = alfresco
PASSWORD_DB_SOURCE = alfresco
URL_DB_DESTINATION = jdbc:postgresql://127.0.0.1:5432/alfresco
USER_DB_DESTINATION = alfresco
PASSWORD_DB_DESTINATION = alfresco
ONLY_NOT_EMPTY = false
TRANSFER.THREADS = 4
MYSQL.NORMALIZE.BIT_BOOLEAN = true
CONSTRAIN.KEEP_NAMES = true
DEBUG = true
TRANSFER.CHECK_DEPS = true
ANALYSE = false
TRANSFER = true
CONSTRAIN = false
----------------------------------------------------

10) Create the schema in your 'alfresco' Postgres destination, using script1.sql:
psql -U alfresco -h 127.0.0.1 alfresco < ~/sql1.sql

11) run DBtransfer with the propertie file created: 
#note: you will need Java 7 to run this.
java -cp dbtransfer.jar dbtransfer.Main alfresco.properties >> dbt_$(date +%Y%m%d_%H%M).log
# watch the log to make sure there are no errors.

12) Apply the constrains in your -alfresco' Postgres destination, using script2.sql:
psql -U alfresco -h 127.0.0.1 alfresco < ~/sql2.sql

13) change your 'alfresco-global.properties' to point to the migrated 'alfresco' database:
db.driver=org.postgresql.Driver
db.url=jdbc:postgresql://127.0.0.1:5432/alfresco
db.name=alfresco
db.username=alfresco
db.password=alfresco
db.host=127.0.0.1
db.port=5432

14) Remove 'alf_data' that you created before, and move you 'alf_data_old' to 'alf_data' again.

15) Start alfresco and watch catalina.out... you are looking for no ERRORs and the sentence " INFO  [domain.schema.SchemaBootstrap] [main] No changes were made to the schema." is great to see! (if everything is fine)

16) Test everything thoroughly!!! When you are done, test again! Also, it's good to compare your MySQL/MariaDB database and your migrated PostgreSQL, to find out if anything is missing. Also, compare the vanilla Postgres database with your migrated one (watch for the indexes and constraints).

17) Enjoy your alfresco on Postgres!!


# Many thanks to Evolute (http://evo.pt) and the main developer of DBtransfer, Luís Flores, for all the support and dedication 
# on improving DBTransfer and also working on releasing it opensource 
# to the public (https://github.com/mgarces/dbtransfer). [SOON]
#
# Also, thank you to @bmejias on #alfresco (freenode) and @loftux for his notes on his migration 
# (https://gist.github.com/loftux/5048391).