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=alfresco -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 --compatible=postgresql > 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';