How to backup alfresco and convert

1. Convert postgreSQL to MySQL.
2. Create MySQL DB for Alfresco
3. Import data from postgreSQL

Convert postgreSQL to MySQL

Stop tomcat

# cd /opt/alfresco-4.2.x # ./alfresco.sh stop tomcat

Dump DB from postgreSQL

# cd /root/ # /opt/alfresco-4.2.x/postgresql/bin/pg_dump -U alfresco alfresco -a --inserts --format p -f alfresco-pg.sql

Convert postgreSQL’s SQL to MySQL format

# cd /root/source # wget http://www.lightbox.ca/pg2mysql/pg2mysql-1.9.tar.bz2 # tar -jxf pg2mysql-1.9.tar.bz2 # cd pg2mysql-1.9 # php pg2mysql_cli.php /root/alfresco-pg.sql /root/alfresco-mysql.sql InnoDB

 

 Apply some case-/binary fixes on the export

# cd /root # perl -ne "      s/insert into (act_[a-z_]+)/\"insert into \".uc(\$1)/ie;      s/insert into (jbpm_[a-z_]+)/\"insert into \".uc(\$1)/ie;      s/'\\\{1,2}x([0-9a-f]+)'/unhex('\$1\')/g;      print" <alfresco-mysql.sql >alfresco-mysql-fix.sql

Create MySQL DB for Alfresco

Create DB for Alfresco

#  mysqladmin create alfresco # mysql -p -e "grant all on alfresco.* to 'alfresco'@'192.168.0.%' identified by 'PASSWORD' with grant option;"

Download MySQL Connector/J (http://www.mysql.com/downloads/connector/j/)

# cd /root/source # wget http://cdn.mysql.com/Downloads/Connector-J/mysql-connector-java-5.1.22.tar.gz # tar -zxf mysql-connector-java-5.1.22.tar.gz # cd mysql-connector-java-5.1.22 # cp mysql-connector-java-5.1.22-bin.jar /opt/alfresco-4.2.x/tomcat/lib/

Modify alfresco-global.properties

# vim /opt/alfresco-4.2.x/tomcat/webapps/alfresco/WEB-INF/classes/alfresco-global.properties

Modify as follow

#for mysql-database db.name=alfresco db.username=alfresco db.password=PASSWORD db.host=MYSQL_SERVER db.port=3306 db.driver=org.gjt.mm.mysql.Driver db.url=jdbc:mysql://${db.host}:${db.port}/${db.name}?useUnicode=yes&characterEncoding=UTF-8

#for pg-database

db.name=alfresco
db.username=alfrescoxx
db.password=xxxxxxxxx
db.host=alfrxxxesco-comxxxmunxxity-pg.cgnhyzov2xxvwb.us-west-2.rds.amazxxonaws.com
db.port=5432
db.driver=org.postgresql.Driver
db.url=jdbc:postgresql://alfresco-community-pg.cxxgnhyzov2vwb.us-west-2.rds.xxamazonaws.com:5432/alfresco?useUnicode=yes&charxxacterEncoding=UTF-8

Backup Alfresco’s data

# cd /opt/alfresco-4.2.x/ # cp -ar alf_data alf_data.`date +%F`

Create MySQL DB tables by starting Alfresco

Start Alfresco

# ./alfresco.sh start tomcat

Stop Alfresco after tomcat start finish

#

./alfresco.sh stop

Dump MySQL DB create by Alfresco

# cd /root # mysqldump alfresco > alfresco.sql

Remove INSERT from alfresco.sql

# cd /root # grep -v INSERT alfresco.sql  > alfresco-create-tables.sql;

Cretae alfresco tables

# cd /root # mysql alfresco < alfresco-create-tables.sql;

Import data from postgreSQL

Import data

# cd /root # echo 'SET foreign_key_checks=0;' > alfresco-mysql-fix2.sql # cat alfresco-mysql-fix.sql >> alfresco-mysql-fix2.sql # echo 'SET foreign_key_checks=1;' >> alfresco-mysql-fix2.sql # mysql alfresco < alfresco-mysql-fix2.sql