Combination View Flat View Tree View
Threads [ Previous | Next ]
toggle
Marco Ferretti
migration strategy
May 7, 2012 8:17 AM
Answer

Marco Ferretti

Rank: Junior Member

Posts: 83

Join Date: October 4, 2010

Recent Posts

Hello all,

I have a liferay 6.0.6 CE ( GA4 ) with a PostgreSQL backend.
I would like to migrate to liferay 6.1.x CE ( hopefully GA2 will come out soon since there are a few bugs listed in LPS in GA1 that are keeping me from upgrading ) and to MySQL in the backend in order to be as "standard" as possible ( I saw tons of examples of LF with MySQL and Tomcat, very little when PG is in the game ).

Is there any document/best practice I should follow ?


In my test env. I tried to uprade to 6.1 ( some problems with image gallery ) and then to use the data migration tool to move the database to a MySQL db but I am getting errors; I thought to myself that it must be something in my environment because the process doesn't even start thus I convinced myself to ask before trying any other step.



Thanks in advance for any advice you can give.


Marco.
David H Nebinger
RE: migration strategy
May 7, 2012 10:14 AM
Answer

David H Nebinger

Community Moderator

Rank: Liferay Legend

Posts: 11793

Join Date: September 1, 2006

Recent Posts

Marco Ferretti:
I have a liferay 6.0.6 CE ( GA4 ) with a PostgreSQL backend.
I would like to migrate to liferay 6.1.x CE ( hopefully GA2 will come out soon since there are a few bugs listed in LPS in GA1 that are keeping me from upgrading )


That would be my suggestion, wait for the next GA.

and to MySQL in the backend in order to be as "standard" as possible ( I saw tons of examples of LF with MySQL and Tomcat, very little when PG is in the game ).


That doesn't mean that Postgres is not supported. It's just recommended for users who want to start w/ a persistent database instead of the HSQL in the bundle. There's nothing magical about any of the databases that can be used, so there's no reason to switch the database.

Is there any document/best practice I should follow ?


1. Have a backup of the database and the data directory prior to launching the new version.
2. If you want to restart the upgrade process, restore the database and data directory prior to re-launch.
Marco Ferretti
RE: migration strategy
May 8, 2012 12:58 AM
Answer

Marco Ferretti

Rank: Junior Member

Posts: 83

Join Date: October 4, 2010

Recent Posts

David,

thanks for the reply.



That doesn't mean that Postgres is not supported. It's just recommended for users who want to start w/ a persistent database instead of the HSQL in the bundle. There's nothing magical about any of the databases that can be used, so there's no reason to switch the database.


I never said it was not supported, in fact we've been using PG with liferay since version 4. What I am experiencing is a whole bunch of problems with migrations and conversions that kept us from beeing up to date . As an example, we did not update to version 6.0.x until GA4 came out because there were problems in the database upgrade process with PG. Right now I am trying to migrate the checking algorithm to 6 because LF 6.2 will not support anything else and am getting SQL errors ( https://www.liferay.com/community/forums/-/message_boards/message/12629757 ). Most of the problems I had during the migrations ( we have two portals ) were due to bugs in the SQL : I think that if we used MySQL we would have experienced less since it looks like most of the tests/regression tests are done with the Tomcat + MySQL configuration.


1. Have a backup of the database and the data directory prior to launching the new version.
2. If you want to restart the upgrade process, restore the database and data directory prior to re-launch.


I believe I didn't explain quite right what I my goal is : at this stage ( Liferay 6.0.6 CE GA4) I would like to migrate the database from PostgreSQL to MySQL; I noticed that there is a "migrate database" functionality in the control panel. I tried that but ended up with SQL errors :
 116:09:21,939 DEBUG [ConvertDatabase:146] Migrating database tables
 216:09:21,941 INFO  [Table:171] Starting backup of Counter to /tmp/tomcat6-tmp/temp-db-Counter-1336399761941
 316:09:21,951 FATAL [ConvertProcessMessageListener:35] Unable to process message {destinationName=liferay/convert_process, response=null, responseDestinationName=null, responseId=null, payload=com.liferay.portal.convert.ConvertDatabase, values={principalPassword=null, principalName=51651, companyId=10112}}
 4com.liferay.portal.convert.ConvertException: org.postgresql.util.PSQLException: ERROR: relation "counter" does not exist
 5  Position: 15
 6        at com.liferay.portal.convert.ConvertProcess.convert(ConvertProcess.java:53)
 7        at com.liferay.portal.convert.messaging.ConvertProcessMessageListener.doReceive(ConvertProcessMessageListener.java:47)
 8        at com.liferay.portal.convert.messaging.ConvertProcessMessageListener.receive(ConvertProcessMessageListener.java:32)
 9        at com.liferay.portal.kernel.messaging.InvokerMessageListener.receive(InvokerMessageListener.java:65)
10        at com.liferay.portal.kernel.messaging.SerialDestination$1.run(SerialDestination.java:101)
11        at com.liferay.portal.kernel.concurrent.ThreadPoolExecutor$WorkerTask._runTask(ThreadPoolExecutor.java:669)
12        at com.liferay.portal.kernel.concurrent.ThreadPoolExecutor$WorkerTask.run(ThreadPoolExecutor.java:580)
13        at java.lang.Thread.run(Thread.java:662)
14Caused by: org.postgresql.util.PSQLException: ERROR: relation "counter" does not exist
15  Position: 15
16        at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2103)
17        at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1836)
18        at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:257)
19        at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:512)
20        at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:388)
21        at org.postgresql.jdbc2.AbstractJdbc2Statement.executeQuery(AbstractJdbc2Statement.java:273)
22        at org.apache.commons.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:96)
23        at org.apache.commons.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:96)
24        at com.liferay.portal.upgrade.util.Table.generateTempFile(Table.java:183)
25        at com.liferay.portal.upgrade.util.Table.generateTempFile(Table.java:147)
26        at com.liferay.portal.convert.ConvertDatabase.migrateTable(ConvertDatabase.java:246)
27        at com.liferay.portal.convert.ConvertDatabase.doConvert(ConvertDatabase.java:161)
28        at com.liferay.portal.convert.ConvertProcess.convert(ConvertProcess.java:44)
29        ... 7 more


and was wondering if it was my specific configuration ( LF 6 + PG 8 ) and if I should follow some specific migration path or if this functionality never worked and I would better be coding table+data migration manually.


TIA


Marco
Jack Bakker
RE: migration strategy : postgresql to mysql
July 13, 2012 10:09 AM
Answer

Jack Bakker

Rank: Liferay Master

Posts: 883

Join Date: January 3, 2010

Recent Posts

I also am considering PostgreSQL to MySQL migration

There have been issues in LR v6.0.6 with import from LDAP wrt PostgreSQL indexes. I am using a Postgresql 8.4 now though really want to use Postgresql 9.1 so I can take advantage of the new syncronous replication features while just 'simply' upgrading my PostgreSQL dbs

I also am looking at Amazon RDS but they only support MySQL Oracle and SQL Server. I suppose I could just use EC2/S3