Parallels H-Sphere Documentation System Administrator Guide

 

Converting H-Sphere System Database From MS SQL to PgSQL

 

Last modified: 27 Dec 2007

 

WARNING: This documentation covers Parallels H-Sphere versions up to 3.1. For the latest up-to-date Parallels H-Sphere documentation, please proceed to the official Parallels site.

PgSQL is the only supported format for the H-Sphere system database. The conversion procedure suggested in this document takes two steps:

  1. Convert database from MSSQL Server to MySQL
  2. Convert database from MySQL to PgSQL

 

Convert database from MSSQL Server to MySQL

  1. Rename the following fields:
    table esc_rules: rename interval to interval2
    table revenue: rename usage to usage2
    This must be done to avoid conflicts in MySQL, and must be changed back in the MySQL dump.
  2. Download the mssql2mysql.exe convertor
  3. Start mssql2mysql.exe and configure setting for MSSQL/MySQL servers (hosts, usernames, passwords, new database name for mysql) and save settings.
    If you get warnings about missing componenets, download and run the MtaEdt22.exe utility. It will download and set up all missing components.
  4. Click Connect to connect to mssql database and select the database to convert
  5. Select all necessary tables or press Select All to select all tables
  6. Click Start to start database conversion
  7. To see the database after the conversion:
    mysql hsphere_mysql (for example)

 

Convert database from MySQL Server to PgSQL

Execute all suggested queries in one transaction. Replace PG_HOST_NAME with the name of the host where PgSQL server is running, like example.com.

  1. Download the mysql/pgsql dump convertor archive and unpack it:
    tar zxvf my2pg.tgz
  2. Dump tables and data from mysql:
    mysqldump.exe hsphere_mysql > hsphere_dump
  3. As the result, you will get a MySQL dump with table structure and data (hsphere_dump)
  4. In MySQL dump, rename the following fields:
    table esc_rules: rename interval2 to interval
    table revenue: rename usage2 to usage
  5. Convert mysql dump to pgsql dump:
    my2pg.pl hsphere_dump > hsphere_pgsql

    As the result, you will get a converted dump (hsphere_pgsql)
  6. Replace TIMESTAMP to TIMESTAMP WITH TIME ZONE.
  7. If the database already exists, delete it:
    dropdb -h PG_HOST_NAME -U wwwuser hsphere_pgsql
  8. Create a new (empty) database:
    createdb -h PG_HOST_NAME -U wwwuser hsphere_pgsql
  9. Restore the database from dump (tables and data):
    psql -h PG_HOST_NAME -d hsphere_pgsql -U wwwuser -f hsphere_pgsql > migrate_errors

    -d - database name
    -f - file with dump
    As a result, you will see convertion results in the migrate_errors file.
  10. Connect to the database and check all tables and data:
    psql -h PG_HOST_NAME -d hsphere_pgsql -U wwwuser
  11. For each record of the sequences table, run the following two commands against the Postgres DB:
    CREATE SEQUENCE "<seq_name>" start <id>;
    SELECT nextval ('<seq_name>');
    For example, for the record newid -> 276488, execute the following SQL statements:
    CREATE SEQUENCE "newid" start 276488;
    SELECT nextval ('newid');


© Copyright 2017. Parallels Holdings. All rights reserved.