Alfresco Mysql to Postgresql migration. This is a simple outline on how to migrate Alfresco from mysql to postgresql.

AlfrescoMysql2Postgresql.txt

Prerequisite
One instance of Alfresco on mysql, one postgresl of exactly the same version (schema version).

Step 1. Dump database
mysqldump --port 3306 -u alfresco --password=alfloftux -h 127.0.0.1 --databases alfresco  --skip-comments --skip-extended-insert --no-create-db --hex-blob --default-character-set=utf8 --skip-triggers --compact --no-create-info --skip-quote-names > mydump.sql

Step 2. Dump local Postgres schema
You can install a clean version of Alfresco to use as for schema dump
(need to insert command for dump here)

Step 3. Split into 2 scripts
Split the postgres schema dump in two parts, second part with constraints only (used in step 7).

Step 4. Fix hex-blob from initial dump
sed "s/0x00/false/g" mydump.sql| sed "s/0x01/true/g"|sed "s/0x([0-9A-F]*)/decode('\1','hex')/g" > mydumpimp.sql
--Also remove/alter any db connection parameters in the beginning of script

Step 5. Run schema script
psql postgres < pgschema.sql

Step 6. Run import of data
psql migrate < mydumpimp.sql

Step 7. Run schema script for constraints
psql migrate < pgschema2.sql

Step 8. Fix 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';

****
--Run both of these, first one, then the other.
    --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';