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';

Thanks for this outline of the steps needed to migrate to postgres.
I am running sed on openSuse and I am having trouble with the last piped sed command sed "s/0x([0-9A-F]*)/decode('\1','hex')/g". it returns and error: invalid reference \1 on `s' command's RHS. Do you have any suggestions?

sed "s/0x([0-9A-F]*)/decode('\1','hex')/g" needs to be
sed -r "s/0x([0-9A-F]*)/decode('\1','hex')/g" to allow the regex works for sed command.

use pg_dump -s to dump only schema with constraints, manually create two files, one to keep the schema part, one to keep only the constraints.