Converting H-Sphere System Database From MS SQL to PgSQL

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
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');

12. If you are rinning H-Sphere older than 2.4, download indexes.db and create indexes in the PgSQL database:

    psql -h PG_HOST_NAME -d hsphere_pgsql -U wwwuser -f indexes.db > indexes_errors