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:

  1. extract the data from the MySQL database, and convert the dump to PostgreSQL SQL Syntax
  2. create the structure of a new empty database in PostgreSQL, using oar-database
  3. inject the data to the PosgreSQL database
  4. restore the missing properties, admission rules, fix the sequence number
  5. 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:

/etc/oar/oar.conf
  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
/etc/postgresql/9.1/main/pg_hba.conf
  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            <frontend ip>      <netmask>    md5
  host    oar         oar               <frontend ip>      <netmask>    md5
/etc/postgresql/9.1/main/postgresql.conf
  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 <list of admin rule id> ; 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:

/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 <list of admin rule id> ; 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

wiki/migration_from_a_mysql_to_a_postgresql_database.txt · Last modified: 2015/09/23 17:11 by neyron
Recent changes RSS feed GNU Free Documentation License 1.3 Donate Powered by PHP Valid XHTML 1.0 Valid CSS Driven by DokuWiki