===== Migrating a OAR database from MySQL to PostgreSQL =====
The Migrating a OAR database from MySQL to PostgreSQL is not straight-forward. A Tool like mysql2psql does not do the trick by itself.
==== New procedure ====
A tool has been developed to help migrating from a mysql to a postgresql OAR database: oar-database-migrate.
See: https://github.com/oar-team/python-oar-cli
Installation of oar-cli which provides oar-database-migrate:
$ sudo apt-get install python-pip
$ sudo apt-get install python-psycopg2
$ sudo apt-get install python-mysqldb
$ sudo pip install oar-cli
Create the new OAR database (postgresql) + oar database users, and empty the base.
$ sudo oar-database --create --db-is-localuser
$ sudo su - postgres
$ dropdb oar
$createdb oar -O oar
$ exit
Run the migration
oar-database-migrate --current-db-url mysql://oar_ro:PASSWORD@mysql_server/oar --new-db-url postgresql://oar:PASSWORD@/pgsql_server
Fix the rights of the oar database users (oar and oar_ro in this example):
$ psql oar2
GRANT ALL PRIVILEGES ON schema,accounting,admission_rules,assigned_resources,
challenges,event_log_hostnames,event_logs,files,frag_jobs,gantt_jobs_predictions,
gantt_jobs_predictions_visu,gantt_jobs_resources,gantt_jobs_resources_visu,
job_dependencies,job_resource_descriptions,job_resource_groups,
job_state_logs,job_types,jobs,moldable_job_descriptions,queues,
resource_logs,resources,admission_rules_id_seq,event_logs_event_id_seq,
files_file_id_seq,job_resource_groups_res_group_id_seq,
job_state_logs_job_state_log_id_seq,job_types_job_type_id_seq,
moldable_job_descriptions_moldable_id_seq,resource_logs_resource_log_id_seq,
resources_resource_id_seq,jobs_job_id_seq TO oar;
GRANT SELECT ON schema,accounting,admission_rules,assigned_resources,event_log_hostnames,
event_logs,files,frag_jobs,gantt_jobs_predictions,gantt_jobs_predictions_visu,
gantt_jobs_resources,gantt_jobs_resources_visu,job_dependencies,
job_resource_descriptions,job_resource_groups,job_state_logs,job_types,
jobs,moldable_job_descriptions,queues,resource_logs,resources,admission_rules_id_seq,
event_logs_event_id_seq,files_file_id_seq,job_resource_groups_res_group_id_seq,
job_state_logs_job_state_log_id_seq,job_types_job_type_id_seq,
moldable_job_descriptions_moldable_id_seq,resource_logs_resource_log_id_seq,
resources_resource_id_seq,jobs_job_id_seq TO oar_ro;
This procedure is based on Grid'5000 documentation: https://www.grid5000.fr/mediawiki/index.php/Migration_Postgres_OAR (access is restricted to Grid'5000 users)
==== Old Procedure ====
The procedure proposed here follows these steps:
- extract the data from the MySQL database, and convert the dump to PostgreSQL SQL Syntax
- create the structure of a new empty database in PostgreSQL, using oar-database
- inject the data to the PosgreSQL database
- restore the missing properties, admission rules, fix the sequence number
- test
This procedure has only been tested on a very small database (~1000 jobs), so it may contain some errors or oversights.
=== Frontend server ===
New packages:
$ sudo aptitude install oar-user-pgsql
In /etc/oar/oar.conf, edit these 2 lines:
DB_TYPE="Pg"
DB_PORT=5432
=== OAR server ===
New packages:
$ sudo aptitude install ruby-dbd-pg libdbd-pg-ruby oar-server-pgsql oar-user-pgsql postgresql postgresql-client
== Postgresql configuration ==
host oar oar_ro 127.0.0.1 255.255.255.255 md5
host oar oar 127.0.0.1 255.255.255.255 md5
host oar oar_ro md5
host oar oar md5
listen_addresses = '*'
== Create a new database and user ==
**This part can be improved, the postgresql database can be initialized using oar-database**
$ sudo su - postgres
$ createuser -P
Enter name of role to add: oar
Enter password for new role:
Enter it again:
Shall the new role be a superuser? (y/n) n
Shall the new role be allowed to create databases? (y/n) n
Shall the new role be allowed to create more new roles? (y/n) n
CREATE ROLE
$ createuser -P
Enter name of role to add: oar_ro
Enter password for new role:
Enter it again:
Shall the new role be a superuser? (y/n) n
Shall the new role be allowed to create databases? (y/n) n
Shall the new
$ createdb oar
$ sudo /etc/init.d/postgresql reload
== Populate the database and set the privileges ==
$ psql -Uoar -h127.0.0.1 oar
\i /usr/lib/oar/pg_structure.sql
$ psql oar
GRANT ALL PRIVILEGES ON schema,accounting,admission_rules,assigned_resources,
challenges,event_log_hostnames,event_logs,files,frag_jobs,gantt_jobs_predictions,
gantt_jobs_predictions_visu,gantt_jobs_resources,gantt_jobs_resources_visu,
job_dependencies,job_resource_descriptions,job_resource_groups,
job_state_logs,job_types,jobs,moldable_job_descriptions,queues,
resource_logs,resources,admission_rules_id_seq,event_logs_event_id_seq,
files_file_id_seq,job_resource_groups_res_group_id_seq,
job_state_logs_job_state_log_id_seq,job_types_job_type_id_seq,
moldable_job_descriptions_moldable_id_seq,resource_logs_resource_log_id_seq,
resources_resource_id_seq,jobs_job_id_seq TO oar;
GRANT SELECT ON schema,accounting,admission_rules,assigned_resources,event_log_hostnames,
event_logs,files,frag_jobs,gantt_jobs_predictions,gantt_jobs_predictions_visu,
gantt_jobs_resources,gantt_jobs_resources_visu,job_dependencies,
job_resource_descriptions,job_resource_groups,job_state_logs,job_types,
jobs,moldable_job_descriptions,queues,resource_logs,resources,admission_rules_id_seq,
event_logs_event_id_seq,files_file_id_seq,job_resource_groups_res_group_id_seq,
job_state_logs_job_state_log_id_seq,job_types_job_type_id_seq,
moldable_job_descriptions_moldable_id_seq,resource_logs_resource_log_id_seq,
resources_resource_id_seq,jobs_job_id_seq TO oar_ro;
\q
== Optimization ==
Create this cron job:
$ crontab -l
# m h dom mon dow command
02 02 * * * vacuumdb -a -f -z
== MySQL database migration ==
Export all the admission rules to files:
$ for i in ; do oaradmin -x $i -f adminrules_$i ; done
Stop OAR
$ sudo /etc/init.d/oar-server stop
Update the OAR server configuration in /etc/oar/oar.conf:
DB_TYPE="Pg"
DB_PORT=5432
Dump the mysql db and stop mysql
$ mysqldump --compatible=postgresql --default-character-set=utf8 -r oar_data.mysql -u root oar
$ sudo /etc/init.d/mysql stop
Create the postgresql data file with https://github.com/lanyrd/mysql-postgresql-converter
$ git clone https://github.com/lanyrd/mysql-postgresql-converter.git
$ python ./mysql-postgresql-converter/db_converter.py oar_data.mysql oar.psql
In the file oar.psql, remove all the CREATE queries and these 2 INSERT queries:
INSERT INTO "gantt_jobs_predictions" VALUES (0,1408970596);
INSERT INTO "gantt_jobs_predictions_visu" VALUES (0,1408970596);
Create all the missing properties in the right order (all the fields between last_available_upto and PRIMARY KEY in oar_data.mysql):
grep -A 40 'CREATE TABLE "resources"' oar_data.mysql
Append the parameter "-c" if the field is a varchar, or nothing if the field is an integer
$ oarproperty -a cpu
$ oarproperty -a core
$ oarproperty -a host -c
$ oarproperty -a mem
$ oarproperty -a maintenance -c
$ oarproperty -a ip -c
Import the data file
$ sudo su - postgres
$ psql
\i oar.psql
Set the "next val" for all the sequences (implicitely created) in the postgresql database:
SELECT setval('admission_rules_id_seq', max(id)) FROM admission_rules;
ALTER TABLE "admission_rules" ALTER COLUMN "id" SET DEFAULT nextval('admission_rules_id_seq');
SELECT setval('event_logs_event_id_seq', max(event_id)) FROM event_logs;
ALTER TABLE "event_logs" ALTER COLUMN "event_id" SET DEFAULT nextval('event_logs_event_id_seq');
SELECT setval('files_file_id_seq', max(file_id)) FROM files;
ALTER TABLE "files" ALTER COLUMN "file_id" SET DEFAULT nextval('files_file_id_seq');
SELECT setval('job_resource_groups_res_group_id_seq', max(res_group_id)) FROM job_resource_groups;
ALTER TABLE "job_resource_groups" ALTER COLUMN "res_group_id" SET DEFAULT nextval('job_resource_groups_res_group_id_seq');
SELECT setval('job_state_logs_job_state_log_id_seq', max(job_state_log_id)) FROM job_state_logs;
ALTER TABLE "job_state_logs" ALTER COLUMN "job_state_log_id" SET DEFAULT nextval('job_state_logs_job_state_log_id_seq');
SELECT setval('job_types_job_type_id_seq', max(job_type_id)) FROM job_types;
ALTER TABLE "job_types" ALTER COLUMN "job_type_id" SET DEFAULT nextval('job_types_job_type_id_seq');
SELECT setval('jobs_job_id_seq', max(job_id)) FROM jobs;
ALTER TABLE "jobs" ALTER COLUMN "job_id" SET DEFAULT nextval('jobs_job_id_seq');
SELECT setval('moldable_job_descriptions_moldable_id_seq', max(moldable_id)) FROM moldable_job_descriptions;
ALTER TABLE "moldable_job_descriptions" ALTER COLUMN "moldable_id" SET DEFAULT nextval('moldable_job_descriptions_moldable_id_seq');
SELECT setval('resource_logs_resource_log_id_seq', max(resource_log_id)) FROM resource_logs;
ALTER TABLE "resource_logs" ALTER COLUMN "resource_log_id" SET DEFAULT nextval('resource_logs_resource_log_id_seq');
SELECT setval('resources_resource_id_seq', max(resource_id)) FROM resources;
ALTER TABLE "resources" ALTER COLUMN "resource_id" SET DEFAULT nextval('resources_resource_id_seq');
Ensure that you have one unique version recorded in the table "schema"
$ psql oar
oar=# select * from schema;
version | name
---------+------
2.5.2 |
2.5.0 |
2.5.0 |
(3 rows)
oar=# delete from schema where version!='2.5.2';
DELETE 2
oar=# select * from schema;
version | name
---------+------
2.5.2 |
(1 row)
== Restart the OAR server: ==
$ sudo /etc/init.d/oar-server start
== Remove the current admission rules and restore the previously exported ones ==
$ oaradmin -d `seq 1 14`
$ for i in ; do oaradmin -a $i -f adminrules_$i ; done
At this point, you should be able to remove the mysql packages and submit jobs from the frontend.
==== Other documentation resources ====
* https://wiki.postgresql.org/wiki/Things_to_find_out_about_when_moving_from_MySQL_to_PostgreSQL
* https://www.grid5000.fr/mediawiki/index.php/Migration_Postgres_OAR (needs a Grid'5000 account)