Hi moodlers,
We have Moodle 1.6.2+ running on a server with MySQL 4.1.20 and PHP
4.3.9. Everything works fine, but we are considering to move on to
Postgres. Will the performance really improve a lot or not? Is there
worth on migrating?
The migration process seems to be hard and difficult, but I was
wondering if there is a script or something like that to automatically
make the process (Martin? Iñaki?) If it does not exists... what are the
steps to do it in the best way?
I have been reading several posts in the forums but I can't find good answers to this questions yet...
Thank you very much!!! ;)
[Excuse me, this post is also in the Database forum, I wasn't sure if
there was enough people reading that forum to solve this problem...]
In reply to Noé Ortega Quijano
Re: Moodle1.6.2+, script to migrate MySQL->Postgre?
by Iñaki Arenaza -
The migration process seems to be hard and difficult, but I was wondering if there is a script or something like that to automatically make the process (Martin? Iñaki?) If it does not exists... what are the steps to do it in the best way?
As far as I know, there is no script that does all the work automatically. In fact, our experience tells us, at least in the 1.5.x series, the database schema is/was not the same for MySQL and Postgres (we did the switch with 1.5.3+). There are/were several differences that make the process a little bit of trial and error.
Now to the good news: if you know mysql and postgres a little bit, it's doable with a reasonable amount of work. We spent most of the time during our tests restoring the mysql dump into postgres just to find several minutes later that it stopped at some table where the definitions didn't match, like not having a certain field, or it had a slightly different -incompatible- type (I'm not sure if we really had this last situation, I'm talking from memory and my memory's not that good ). So we either dropped the field in the MySQL database (the test copy, of course
and dumped the contents again, or adjusted the definition in Postgresql and tried the import again (having emptied the tables in postgresql beforehand).
After a few iterations (really not that many) we had a clear path to import everything from our MySQL dump into Postgresql. I'm afraid I didn't write down what all those 'fixes' were (which tables and fields), so this part is a little bit trial an error. If your installation isn't very big (ours was less than half a million records total) and you have a fast db server, you can make each iteration in under 15-20 minutes.
Maybe all this has improved in later versions of Moodle (1.6.x) but we needed to switch before 1.6.x (to be able to upgrade to 1.6.x and Unicode without using non-official mysql packages).
You can see my partial notes of the migration process at:
http://moodle.org/mod/forum/discuss.php?d=49195
I hope this helps.
Saludos. Iñaki.
In reply to Iñaki Arenaza
Re: Moodle1.6.2+, script to migrate MySQL->Postgre?
by Noé Ortega Quijano -
Our site is still quite small, so we are trying to make a new Moodle
installation, this time on Postgres. I have installed it, but when I
follow the steps o the Moodle installation guide, some errors appear.
When I type:
> psql -c "create database moodle WITH ENCODING = 'UTF8';" -U moodleuser template1
The system returns the following message:
FATAL: IDENT authentication failed for user moodleuser
I have a user account on the system wich has the same name of the database user, 'moodleuser'. I have googled and seen that it is a very common error, but I cannot solve it. Can you help me?
When I type:
> psql -c "create database moodle WITH ENCODING = 'UTF8';" -U moodleuser template1
The system returns the following message:
FATAL: IDENT authentication failed for user moodleuser
I have a user account on the system wich has the same name of the database user, 'moodleuser'. I have googled and seen that it is a very common error, but I cannot solve it. Can you help me?
In reply to Noé Ortega Quijano
Re: Moodle1.6.2+, script to migrate MySQL->Postgre?
by Rahim Virani -
Please display the line which you used in postgres to add the user.
In reply to Rahim Virani
Re: Moodle1.6.2+, script to migrate MySQL->Postgre?
by Noé Ortega Quijano -
I typed these comands:
mkdir /usr/local/pgsql
mkdir /usr/local/pgsql/data
useradd moodleuserpg
chown -R moodleuserpg:moodleuserpg /usr/local/pgsql/data
su - moodleuserpg
/usr/bin/initdb -D /usr/local/pgsql/data -E LATIN1 --locale=es_ES
/usr/bin/pg_ctl -D /usr/local/pgsql/data -l logfile start
[Postmaster succesfully started]
su - postgres
psql -c "create user moodleuserpg createdb;" template1
psql -c "create database moodlepg WITH ENCODING = 'UTF8';" -U moodleuserpg template1
psql: FATAL: la autentificación IDENT falló para el usuario "moodleuserpg"
[psql: FATAL: authentication IDENT failed for user "moodleuserpg]
I also tried to change the password of "moodleuserpg" before creating moodlepg database, but it appeared the same error, so I finally typed psql -c "create database moodlepg WITH ENCODING = 'UTF8';" template1, and the database was succesfully created. If I type psql -l, it appears moodlepg owned by postgres with UNICODE encoding.
I guess the main thing is to make moodleuserpg the owner of the database moodlepg, but how?
mkdir /usr/local/pgsql
mkdir /usr/local/pgsql/data
useradd moodleuserpg
chown -R moodleuserpg:moodleuserpg /usr/local/pgsql/data
su - moodleuserpg
/usr/bin/initdb -D /usr/local/pgsql/data -E LATIN1 --locale=es_ES
/usr/bin/pg_ctl -D /usr/local/pgsql/data -l logfile start
[Postmaster succesfully started]
su - postgres
psql -c "create user moodleuserpg createdb;" template1
psql -c "create database moodlepg WITH ENCODING = 'UTF8';" -U moodleuserpg template1
psql: FATAL: la autentificación IDENT falló para el usuario "moodleuserpg"
[psql: FATAL: authentication IDENT failed for user "moodleuserpg]
I also tried to change the password of "moodleuserpg" before creating moodlepg database, but it appeared the same error, so I finally typed psql -c "create database moodlepg WITH ENCODING = 'UTF8';" template1, and the database was succesfully created. If I type psql -l, it appears moodlepg owned by postgres with UNICODE encoding.
I guess the main thing is to make moodleuserpg the owner of the database moodlepg, but how?
In reply to Noé Ortega Quijano
Re: Moodle1.6.2+, script to migrate MySQL->Postgre?
by Viorel Tabara -
> psql -c "create user moodleuserpg createdb;" template1
I also run:
//
-bash-2.05b$ psql -c "alter user moodleuserpg with encrypted password '???';" template1
ALTER USER
//
If the above doesn't fix your problem, then as 'postgres', run the command below and post the output:
//
-bash-2.05b$ grep -v "^#" /usr/local/pgsql/data/pg_hba.conf | grep -v "^:space:" | grep -v "^$"
//
--
Viorel
I also run:
//
-bash-2.05b$ psql -c "alter user moodleuserpg with encrypted password '???';" template1
ALTER USER
//
If the above doesn't fix your problem, then as 'postgres', run the command below and post the output:
//
-bash-2.05b$ grep -v "^#" /usr/local/pgsql/data/pg_hba.conf | grep -v "^:space:" | grep -v "^$"
//
--
Viorel
A few things,
Its been a while since I worked doing psql admin stuff, after you modify your acls you need to reload the database
Also, you need to look at hba.conf which does host-based authentication for pgsql.
R.
Its been a while since I worked doing psql admin stuff, after you modify your acls you need to reload the database
Also, you need to look at hba.conf which does host-based authentication for pgsql.
R.
In reply to Rahim Virani
Re: Moodle1.6.2+, script to migrate MySQL->Postgre?
by Noé Ortega Quijano -
I have tried almost everything, but it still fails to connect to the database.
However, we have finally decided to continue with MySQL, because it is enough for the number of students that we have, it is nearly as fast as Postgres (with Moodle), and much easier.
Thank you very much for your help!
However, we have finally decided to continue with MySQL, because it is enough for the number of students that we have, it is nearly as fast as Postgres (with Moodle), and much easier.
Thank you very much for your help!
In reply to Noé Ortega Quijano
Re: Moodle1.6.2+, script to migrate MySQL->Postgre?
by Iñaki Arenaza -
Noé, if you later decide to switch to postgresql, bear in mind that you should edit pg_hba.conf to allow connections from localhost using the password method too.
Hava a look at the the "Client authentication" chapter of the PostgreSQL documentation. It's quite detailed
Saludos. Iñaki.
Hava a look at the the "Client authentication" chapter of the PostgreSQL documentation. It's quite detailed
Saludos. Iñaki.