postgresql to ms sql

[Jack Bakker, modifié il y a 9 années.]

I may have to migrate from postgresql to ms sql. Any Liferay-specific advice wrt migrating an lportal db ?

[David H Nebinger, modifié il y a 9 années.]

Eek, that's going to prove challenging...

I would start Liferay against ms sql to let it build tables and indices.

Do a dump from postgres of sql insert statements. Massage the files to deal with any case issues that you might run into (don't know if ms sql is case sensitive).

Since there aren't any foreign keys in the Liferay tables, you should be able to load them separately and in any order you want.

After everything's loaded, start up liferay and reindex, verify integrity, etc., all the time hoping for the best

[Jack Bakker, modifié il y a 9 années.]

Eek back...

what would you call contact_.userid as a field ... perhaps maybe a 'foreign key' ? as for how the id is derived, well that is another thing.. as for integritty, I think this would be my challenge if this migrate from postgresql to ms sql is necessary

[David H Nebinger, modifié il y a 9 années.]

They're not foreign keys because the database doesn't manage them. You can stuff any numerical value in there, the db/table won't complain.

So if you use your insert statements to insert the records into a clean database (oh, I forgot to mention you should truncate the tables prior to loading), the data will all be valid. The counter table will be properly populated so once Liferay starts back up, the new records should add onto the end.

[Jack Bakker, modifié il y a 9 années.]

David H Nebinger:

They're not foreign keys because the database doesn't manage them.

They are still foreign keys. And if in Service Builder id-type="sequence", then db does manage them.

Anyways, semantic definition of foreign keys aside... I see you are focused on counter approach on id management.

[David H Nebinger, modifié il y a 9 années.]

I'm not, Jack, but Liferay is. They do not use sequences on any of their tables, they are all based on counters. The liferay data will transfer very easily.

Foreign keys managed by the database are an issue because you would be forced to order your sql loads such that FK records were there before a dependent record is loaded.

Since liferay doesn't use these FKs, you can load them in any order and the DB won't complain.

I'm not arguing semantics here, what I'm arguing is that the SQL loading will be greatly simplified because there are no real foreign keys in the LR data.

[Jack Bakker, modifié il y a 9 années.]

So you introduce a 'real foreign key' as different from a 'foreign key'. k, generically such is still a foreign key, independent of how such ids are generated

To add background, my solutions generally involve the lportal db and at least one biz db (better stated as multiple datasources - where datasources might be more than just sql datasources, but that is out of scope for my post here).

Biz db is used by other subsystems and also has references (foreign keys) into lportal db .. so I have to use db generated ids and ensure ids have integrity across biz db and lportal db.

[David H Nebinger, modifié il y a 9 années.]

Yeah, Liferay uses "fake" foreign keys. They are not DB maintained and often times point to one of many tables (whenever you see the class stuff next to an id field, this is one of those fake keys).

Others are "logical" foreign keys in that they do refer to other records but they are not real foreign keys because the database itself is not managing the referential integrity.

That will make it easy to migrate the liferay database between systems because the DB itself won't get in the way when the loads occur.

When there are real FKs involved, you typically have to drop the constraints, load all of your data, then reapply the constraints and hope that everything is satisfied. Big pain in the tookus.

Migrating your biz db, well that's your own issue that I can't quite help you out with, but will probably involve the pain in the tookus thing I just mentioned.

BTW, I typically recommend against doing any FKs against the Liferay database. Whenever you assume that they won't change something, they turn around and change it and you're left trying to pick up the pieces on something that fails but you had zero notice about. Besides, the biz db is often in a separate database server altogether, and few of those handle FK references across servers well if at all.

The weak reference you get is pretty weak, but it fits with the Liferay environment much better than actual foreign keys...

[Jack Bakker, modifié il y a 9 années.]

First you put the chicken in the bucket... is that a Muppet Swedish Chef phrase ?

[David H Nebinger, modifié il y a 9 années.]

you should always fry the chicken before it goes in the bucket...

[Jack Bakker, modifié il y a 9 années.]

Thanks again for your continued expertise.