Skip to content

Instantly share code, notes, and snippets.

@hoangclinh

hoangclinh/AlfrescoMysql2Postgresql.txt

Forked from loftux/AlfrescoMysql2Postgresql.txt
Last active August 29, 2015 14:24
Show Gist options
  • Save hoangclinh/0481c8cbe2c36d8afd1b to your computer and use it in GitHub Desktop.
Save hoangclinh/0481c8cbe2c36d8afd1b to your computer and use it in GitHub Desktop.
Download ZIP

Revisions

  1. @loftux loftux created this gist Feb 27, 2013.
    45 changes: 45 additions & 0 deletions AlfrescoMysql2Postgresql.txt
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,45 @@
    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';