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:
- Convert database from MSSQL Server to MySQL
- Convert database from MySQL to PgSQL
Convert database from MSSQL Server to MySQL
- 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.
- Download the mssql2mysql.exe convertor
- 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
It will download and set up all missing components.
- Click Connect to connect to mssql database and select the database to convert
- Select all necessary tables or press Select All to select all tables
- Click Start to start database conversion
- 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.
- Download the mysql/pgsql dump convertor archive and unpack it:
tar zxvf my2pg.tgz
- Dump tables and data from mysql:
mysqldump.exe hsphere_mysql > hsphere_dump
- As the result, you will get a MySQL dump with table structure and data (hsphere_dump)
- In MySQL dump, rename the following fields:
table esc_rules: rename interval2 to interval
table revenue: rename usage2 to usage
- Convert mysql dump to pgsql dump:
my2pg.pl hsphere_dump > hsphere_pgsql
As the result, you will get a converted dump (hsphere_pgsql)
- Replace TIMESTAMP to TIMESTAMP WITH TIME ZONE.
- If the database already exists, delete it:
dropdb -h PG_HOST_NAME -U wwwuser hsphere_pgsql
- Create a new (empty) database:
createdb -h PG_HOST_NAME -U wwwuser hsphere_pgsql
- 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.
- Connect to the database and check all tables and data:
psql -h PG_HOST_NAME -d hsphere_pgsql -U wwwuser
- For each record of the sequences table, run the following two commands against the Postgres DB:
CREATE SEQUENCE "<seq_name>" start <id>;
For example, for the record newid -> 276488, execute the following SQL statements:
SELECT nextval ('<seq_name>');
CREATE SEQUENCE "newid" start 276488;
SELECT nextval ('newid');