-
-
Save hoangclinh/0481c8cbe2c36d8afd1b to your computer and use it in GitHub Desktop.
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| 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'; |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment