This site has been archived and you can no longer log in or post new messages. For up-to-date community resources please visit ezplatform.com

eZ Community » Forums » Install & configuration » Migration from MySQL to PostgreSQL
expandshrink

Migration from MySQL to PostgreSQL

Migration from MySQL to PostgreSQL

Friday 05 November 2010 4:20:48 pm - 19 replies

Hello,

I have an eZ publish Website which is using MySQL db.

I would like to know if it's possible to migrate MySQL to PostgreSQL.

Thanks in advance.

Saturday 06 November 2010 2:06:03 am

Hello Kenneth,

Welcome to the eZ Community.

eZ Publish does support PostgreSQL.

But the conversion of an eZ Publish Mysql Db

to a PostgreSQL is not really an eZ Publish specific question.

Re: http://wiki.postgresql.org/wiki/C..._other_Databases_to_PostgreSQL#MySQL

Cheers,

Heath

Saturday 06 November 2010 2:25:51 pm

There are also some php scripts in eZP that can be of use: ezsqldumpschema.php and ezsqlinsertschema.php. Using the two of them, you should be able to migrate an existing eZ database from mysql to postgresql and viceversa.

Thursday 15 January 2015 1:04:05 pm

Thank u Gaetano for you very useful suggestion. Though the provided documentation http://pubsvn.ez.no/doxygen/4.0.7/html/ezsqlinsertschema_8php.html is quite straightforward in a technical way, there isn't much to be refered to as practical examples.

Anyway I reeeaaally need to accomplish this convertion in order to migrate my installation from a mysql to a Postgres db and started from your post.

Usage:

ezsqldumpschema.php (eZ SQL schema dump) for dumping the sql schema to file

php bin/php/ezsqldumpschema.php [OPTION]... [DATABASE] [FILENAME]
e.g. ezsqldumpschema.php --type=mysql --user=root stable33 schema.sql

ezsqlinsertschema.php (eZ SQL schema insert) inserts db schema and data to a specified database

php bin/php/ezsqlinsertschema.php [OPTION]... [FILENAME] [DATABASE]
e.g. ezsqlinsertschema.php --type=mysql --user=root share/db_schema.dba ezp35stable

 

So i tried the following and managed to create a .sql file under the root

php bin/php/ezsqldumpschema.php --type=mysql --user=dbadmin --password=dbpsw dbname schema.sql

which is a 117KB file despite the 20Mb db. Is this meant for the bd schema only? How about the data?

Then I tried to run the other script to try to insert "something" in the destination db located in another server

php bin/php/ezsqlinsertschema.php  --type=postgresql --user=postgres --password=dbpsw --host=x.xxx.xxx.xx --port=5432 --insert-types=all --schema-file=schema.sql share/db_schema.dba dbname

but it returned the following error:

Failed insert schema/data to database
* Tried database 'dbname' at host 'x.xxx.xxx.xx' with user 'postgres' and with a password

Checked the error.log file and it also returned some errors:

[ Jan 15 2015 12:53:04 ] [name.local] eZPostgreSQLDB:
Error: error executing query: CREATE SEQUENCE ezapprove_items_s
  START 1
  INCREMENT 1
  MAXVALUE 9223372036854775807
  MINVALUE 1
  CACHE 1: ERROR:  relation "ezapprove_items_s" already exists
[ Jan 15 2015 12:53:04 ] [name.local] :
Failed inserting the SQL:
CREATE SEQUENCE ezapprove_items_s
  START 1
  INCREMENT 1
  MAXVALUE 9223372036854775807
  MINVALUE 1
  CACHE 1

Any idea what am I possibly doing wrong?

Thanks, thanks indeed in advance!!

 

 

Friday 16 January 2015 6:30:12 am

Hi Lo' F,

"Any idea what am I possibly doing wrong?"

It might not be you. It's probably not you.

What it says the error is that you have some duplicate in your schema file. Open the schema file in an text editor and check for duplicates. Remove if any, and try again. Importing that is.

Remember to backup your PostGreSql database before you try this if you have any other data in it happy.gif Emoticon

About size; have you checked the dumpfile fra mySql for data? I there something in there or is it just the schema?

Hope this helps...

Monday 19 January 2015 12:36:54 am

Hi Atle, it's a new approach for me to Postgres db. I've always worked on MySQL but this time I needed to move the installation to a server that makes use of PostgreSQL. I'll keep in mind what u said 'cause I definetely need to go deep into the ez way of migrating/converting db, but for now as the attempt didn't succeed, since I had to act fast, I chose the easy way by installing a new ez publish directly connected to a Postgres DB and then populated the content by importing the objects packages from the other installation.

One thing that I like to report here is that I encountered a problem while importing objects with eztags attributes. EzTags is not optimized for Postgres. Those eztags tables were missing so had to create them and this blog helped me through http://lutz.donnerhacke.de/ger/Blog/ezTags-auf-PostgreSQL

CREATE SEQUENCE eztags_s;
CREATE TABLE eztags (
   id integer DEFAULT nextval('eztags_s'::text) NOT NULL,
   parent_id integer not null default 0,
   main_tag_id integer not null default 0,
   keyword varchar(255) NOT NULL default '',
   depth integer NOT NULL default 1,
   path_string varchar(255) NOT NULL default '',
   modified integer NOT NULL default 0,
   remote_id varchar(100) NOT NULL default '',
   PRIMARY KEY (id),
   CONSTRAINT remote_id UNIQUE  (remote_id)
);
CREATE INDEX eztags_keyword ON eztags (
   keyword
);
CREATE INDEX eztags_keyword_id ON eztags (
   keyword,
   id
);
CREATE SEQUENCE eztags_attribute_link_s;
CREATE TABLE eztags_attribute_link (
   id integer DEFAULT nextval('eztags_attribute_link_s'::text) NOT NULL,
   keyword_id integer not null default 0,
   objectattribute_id integer not null default 0,
   objectattribute_version integer not null default 0,
   object_id integer not null default 0,
   PRIMARY KEY (id)
);
CREATE INDEX eztags_attr_link_keyword_id ON eztags_attribute_link (
   keyword_id
);
CREATE INDEX eztags_attr_link_kid_oaid_oav ON eztags_attribute_link (
   keyword_id,
   objectattribute_id,
   objectattribute_version
);
CREATE INDEX eztags_attr_link_kid_oid ON eztags_attribute_link (
   keyword_id,
   object_id
);
CREATE INDEX eztags_attr_link_oaid_oav ON eztags_attribute_link (
   objectattribute_id,
   objectattribute_version
);

blunk.gif Emoticon

Modified on Tuesday 20 January 2015 12:14:44 pm by Lo' F.

Saturday 24 January 2015 12:32:09 am

Hello Lo' F,

Would you consider contributing to a eztags pull request to include this solution in the default distribution of eztags so that we can standardize a solution for all users?

https://github.com/ezsystems/eztags/pulls

Cheers,
Heath 

Friday 30 January 2015 9:21:12 pm

Sure! ..done blunk.gif Emoticon

Sunday 01 February 2015 7:33:19 am

Hello Lo' F,

It seems there are very serious problems with your pull request. Please take the time to solve these.

https://github.com/ezsystems/eztags/pull/85

In theory your pull request should only need to add one commit, which adds one file to the eztags extension under the following file path, 'sql/postgresql/schema.sql'.

Best wishes!

Cheers,
Heath

Monday 02 February 2015 11:50:51 am

Thanks for warning me. I guess, I don't know what to do exactly.. :/

I'd probably better close it. Wait for your reply before I can take any action...

Modified on Monday 02 February 2015 11:52:23 am by Lo' F.

Monday 02 February 2015 8:56:40 pm

Hello Lo' F,

You should create a new branch, based off the 'multilanguage' branch and commit one new commit, adding your one new sql file.

Then push these changes from your local computer to your github repository, then edit your pull request to use the 'multilanguage' branch instead (ie: multilanguage -> multilanguage).

This is totally fixable (by you). You should be able to salvage / change the pull request to still solve this problem with a little more work.

I hope this helps! 

Cheers,
Heath 

Friday 13 March 2015 2:28:20 am

Hi Heath, getting beck to where I left, I just need to check my pull request

https://github.com/ezsystems/eztags/pull/89

I forked the repo into mine and committed to add the eztags/sql/postgresql/schema.sql file in order to make a pull request to the multilanguage branch of the eztags extension.

Well, it was good to approach github but, as my first time, again things didn't work the way they should and I don't really get it why all the files have been reported as changed when there was only one file physically added to the structure.

Is that normal?! If not, then, I'll try to fix it...

Modified on Friday 13 March 2015 2:43:01 am by Lo' F.

Friday 13 March 2015 5:36:27 am

Hello Lo' F,

It's good to hear your returning to this subject and trying again happy.gif Emoticon

I'm sorry your still having problems.

I'm 100% certain what your -real- problem is with the process but I'll try to help.

First problem I see right off the bat is that you made two commits:

https://github.com/Ramna/eztags/c...4b990e0fe2f2ba7750c9bd7889994e10f759

https://github.com/Ramna/eztags/c...e57fd1ce892218cb0a038eb64687a146d5bc

But to be honest you should not have made two commits, only one.

In your first commit (that was not needed) you committed almost all the files of the extension: https://github.com/Ramna/eztags/c...4b990e0fe2f2ba7750c9bd7889994e10f759

Now I'm not exactly clear on why you did that just that you did. Sadly I think you'll need re-do this work again, I'll get back to that in a moment.

In your second commit you did very good work, you committed correctly the one new file and the commit message, 'Add SQL file meant to create tables for postgreSQL DB' while not in the correct format it was still a pretty good for an attempt :

https://github.com/Ramna/eztags/c...e57fd1ce892218cb0a038eb64687a146d5bc

OK. I'm going to tell you exactly how to do this the right way, right now. First lets start with the basics.

When you create a Pull Request you FIRST need to create a jira.ez.no issue ticket. You need to do this first so that you can document the problem in detail before you try to submit a fix or improvement, you also do this first so you can get an issue ticket number which you need to include in your commit message, pull request title and link the url to the issue ticket in the pull request description text. Your jira issue ticket title text should be this string exactly (no changes please), 'eZ Tags missing schema sql required for postgresql support' (without quotes). The rest of the issue ticket description field should explain in painstaking detail the problem, how to reproduce it and how you think it should be fixed. Remember that in your issue ticket you basically have to write well enough to -convince- eZ Systems that your problem, bug, bugfix, new feature, etc is valid and worth fixing. So put your hard hat on and be convincing! 

Next, at this point back up your new sql file outside your copy of the eztags fork repository on your local disk.

Then run: rm -vrf /path/to/your/local/checkout/folder/of/eztags;

Then run: cd /path/to/your/local/checkout/folder/of/;

Then run: git clone git@github.com:Ramna/eztags.git;

Then run: git checkout master;

Then run: git branch -d multilanguage;

Then run: git push -u origin :multilanguage;

Then run: git remote add upstream git@github.com:ezsystems/eztags.git;

Then run: git fetch upstream;

Then run: git merge upstream/master;

Then run: git checkout -b multilanguage upstream/multilanguage;

Then run: git push -u origin multilanguage -f;

Then run: git branch multilanguage-add-postgresql-support;

Then run: git checkout multilanguage-add-postgresql-support;

Then run: cp -va ../../path/to/eztags-postgresql-schema-backup.sql sql/postgresql/schema.sql;

Then run: git add sql/postgresql/schema.sql;

Note: Please replace the text string EZP-00000 with the actual jira issue ticket number you got when you created the required issue ticket (as requested above).

Then run: git commit -m"EZP-00000: Fix eZ Tags missing schema sql required for postgresql support" sql/postgresql/schema.sql;

Then run: git push -u origin multilanguage-add-postgresql-support;

Then go to https://github.com/Ramna/eztags/c...multilanguage-add-postgresql-support and look for your one new commit.

Then go to: https://github.com/Ramna/eztags and GitHub should (after a few seconds after page load; slight delay) display a box in the middle of the page with the new branch name, it will look something like, 'multilanguage-add-postgresql-support (less than a minute ago)' and a green button titled, 'Compare & pull request'.

Then: Click the 'Compare & pull request' button. This will take you to the Pull Request creation form.

Then: Add this text only into the title, 'EZP-00000: Fix eZ Tags missing schema sql required for postgresql support' (without the quotes and replace the text string EZP-00000 with the actual jira issue ticket number you got when you created the required issue ticket (as requested above)).

Then: In the description field add a full url link to the issue ticket in you created above.

Then: Then review the rest of the Pull Request details displayed on the page. You should see a very short page with only the new sql file additions you have made. You should also see that GitHub is showing you that you are creating a pull request with only 1 commit.

If you see more than 1 commit on the page or other file changes besides the added sql file changes then -do not create the pull request- as it would be a waste of resources, time and confuse everyone involved even more.

If you see more than 1 commit when creating the Pull Request add a message here and I'll look at your branch and try to make more specific instructions to try to help further. But ... these instructions above should be enough to help guide you through the only commands you should need to run to solve your problems and re-create the commit you need to then re-create the Pull Request. 

 If you only see only 1 commit on the page and only the added sql file changes then click, 'Create pull request'. Then post a message here in the forums with the url link to your newly created pull request and I'll try to help review it for accuracy.

Then post an jira issue ticket comment with your new pull request url and explain your requesting the merging of your solution to the issue ticket 'problem'.

Sorry in advance for being so verbose and long winded but I really really wanted to stop hoping you would get it right and just tell you what needed to be done by writing down almost every critical thing you must do to do this the right way the first time.

My instructions basically (at least) cover creating the required issue ticket, deleting everything you have done locally, doing a fresh checkout, adding the required upstream remote repository, deleting the local multilingual branch, deleting the remote multilingual branch (which you mistakenly added commits to directly which corrupts it), fetching and merging the upstream changes (required), recreating the multilingual branch from upstream sources, pushing a clean copy of the upstream multilingual branch to your fork (required since you corrupted the last copy), create a new unique 'feature addition' branch, add and commit your 1 commit with only 1 file committed, push your feature branch, create a pull request, update your issue ticket with a link to your new clean and ready to merge pull request.

It's worth noting many of your problems happened in a small way because you did not create a new separate branch (feature branch) based off of the multilingual branch (target branch) to add the sql file and make your only 1 commit. This is a best practice that saves lives. You should learn to do this like the greats happy.gif Emoticon

I hope this helps!

Cheers,
Heath

Modified on Friday 13 March 2015 5:51:53 am by // Heath

Friday 13 March 2015 7:12:39 pm

Hey Heath, thank u very very much for the whole set of instructions. I'll work on the fix ASAP.

p.s. ...Apologies for my rookie attempts ;P

Modified on Friday 13 March 2015 7:53:12 pm by Lo' F.

Friday 13 March 2015 11:43:05 pm

Oh maaaan, your well detailed and thoroughly explained steps made the work easy and neat like drinking a glass of water and saved me (..and the repo) from causing (..and receiving) further mess.

Anyway, before I can count my chickens (..and make sure they've hatched) here's the links:

https://jira.ez.no/browse/EZP-24138

https://github.com/ezsystems/eztags/pull/90

Looks to me everything is as it's supposed to be, but just in case (U know better than me). I just hope I have been enough clear and convincing explaining the whole thing, but if u believe I need to make some adjustments, please tell me and I'll be pleased to make it right.

...

Just couple of things I had to alter when running the commands since cloning with SSH denied permission and so had to go with the HTTPS clone URL (I am using Terminal on a Mac pc)

git clone https://github.com/Ramna/eztags.git
..
git remote add upstream https://github.com/ezsystems/eztags.git

the rest went well like a charm blunk.gif Emoticon

THANK U VERY MUCH!

Saturday 14 March 2015 10:03:56 am

Hello Lo' F,

Your very welcome! I'm happy to help. I do this every day so it only took a little extra time to write out all the specifics and test them to make sure you would have no problems.

I took a look at the work you did in the new PR and it looks really good.

I think you did it! I don't thing the eZ crew will have any trouble merging your PR but remember to watch it for updates / further requests from the maintainers.

Thanks again for sticking with it and for continuing to work with me to ensure this problem is solved once and for all!

Best wishes! 

Cheers,
Heath

Monday 16 March 2015 10:03:05 pm

Hello Lo' F,

We posted some new questions / guidance to your pull request. Please let us know your thoughts.

https://github.com/ezsystems/eztags/pull/90#issuecomment-81936137

I hope this helps!

Cheers,
Heath

Monday 16 March 2015 11:48:14 pm

Yes, sure, just gimme a few days and I'll get back to it again soon...

For now this should be the postgresql version of the sql file for the multilanguage

CREATE SEQUENCE eztags_s;
CREATE TABLE eztags (
   id integer DEFAULT nextval('eztags_s'::text) NOT NULL,
   parent_id integer not null default 0,
   main_tag_id integer not null default 0,
   keyword varchar(255) NOT NULL default '',
   depth integer NOT NULL default 1,
   path_string varchar(255) NOT NULL default '',
   modified integer NOT NULL default 0,
   remote_id varchar(100) NOT NULL default '',
   main_language_id integer not null default 0,
   language_mask integer not null default 0,
   PRIMARY KEY (id),
   CONSTRAINT remote_id UNIQUE  (remote_id)
);
CREATE INDEX eztags_keyword ON eztags (
   keyword
);
CREATE INDEX eztags_keyword_id ON eztags (
   keyword,
   id
);
 
CREATE SEQUENCE eztags_attribute_link_s;
CREATE TABLE eztags_attribute_link (
   id integer DEFAULT nextval('eztags_attribute_link_s'::text) NOT NULL,
   keyword_id integer not null default 0,
   objectattribute_id integer not null default 0,
   objectattribute_version integer not null default 0,
   object_id integer not null default 0,
   priority integer not null default 0,
   PRIMARY KEY (id)
);
CREATE INDEX eztags_attr_link_keyword_id ON eztags_attribute_link (
   keyword_id
);
CREATE INDEX eztags_attr_link_kid_oaid_oav ON eztags_attribute_link (
   keyword_id,
   objectattribute_id,
   objectattribute_version
);
CREATE INDEX eztags_attr_link_kid_oid ON eztags_attribute_link (
   keyword_id,
   object_id
);
CREATE INDEX eztags_attr_link_oaid_oav ON eztags_attribute_link (
   objectattribute_id,
   objectattribute_version
);
 
CREATE SEQUENCE eztags_keyword_s;
CREATE TABLE eztags_keyword (
   keyword_id integer not null default 0,
   language_id integer not null default 0,
   keyword varchar(255) NOT NULL default '',
   locale varchar(255) NOT NULL default '',
   status integer not null default 0,
   PRIMARY KEY (keyword_id, locale)
);

Is this correct?

More that the committed file, it has the eztags_keyword table and "main_language_id" and "language_mask" columns for the eztags table.

Modified on Tuesday 17 March 2015 7:03:25 pm by Lo' F.

Monday 04 May 2015 12:10:46 pm

Hello Lo' F,

This morning I had some free time and worked on completing the work you started to introduce postgresql support into the eztags extension.

First we re-did your first master branch changes in a new PR (since we could not edit your PR), https://github.com/ezsystems/eztags/pull/91

Second we re-did your follow up multilingual branch changes in a new PR, https://github.com/ezsystems/eztags/pull/92

It took a few revisions to address some minor bugs but we quickly fixed the remaining problems and both PRs were merged. Also your PR was closed in favor of the above PRs.

As of the time of this forum post update we now have postgresql support in the eztags legacy extension in both the master and multilingual branches by default!

Now anyone who uses postgresql, ezpublish and eztags can use the new support that you started and I finished!

Pardon me but I'm kinda excited that I was able to complete the work required this morning in my spare time happy.gif Emoticon

I hope these changes help you and others in the future.

Take it eZ!

Cheers,
Heath

Wednesday 06 May 2015 6:26:13 pm

Heath, thank U for bringing to an end that unfinished business of mine!

Believe it or not that was nailed in my head for so long but for that simple and yet "delicate" work really needed time to concentrate/learn (..easy when I just had to follow the step-by-step you provided me) and to be fully aware of the git actions to take, avoiding foolish damages. You know, the early snakebites of the very first attempts made a painful approach... so, guess what?! I am in the middle of a lynda's git course... blunk.gif Emoticon

But I am glad u completed the work. And thanks for thanking me despite my useless part in this blunk.gif Emoticon

p.s. Put ez (that is always my favorite above all) on a side for a while in favor of working with other platforms (different targets make different choices) that is how I justify my prolonged absence from the forum... But never say never, I will always need this safe haven, and hope to bring some new issues very soon!! ;D

p.p.s. Wanted to like twice but.. 's not allowed ;P

Modified on Wednesday 06 May 2015 6:37:55 pm by Lo' F.

expandshrink

You must be logged in to post messages in this topic!

36 542 Users on board!

Forums menu

Proudly Developed with from