6.8 Migrating the MAM Database from MySQL to PostgreSQL

PostgreSQL is the preferred DBMS for MAM. Customers who have already installed MySQL as the DBMS for MAM are not required to migrate their database to use PostgreSQL at this time. However, MySQL is considered deprecated and new installations will only use PostgreSQL.

PostgreSQL does not provide a standard procedure for migrating an existing database from MySQL to PostgreSQL. Adaptive Computing has had success using the py-mysql2pgsql tools for migrating/converting/exporting data from MySQL to PostgreSQL. See https://github.com/philipsoutham/py-mysql2pgsq for additional details.

To Migrate the MAM Database

This procedure was successfully tested on an actual customer MySQL database with millions of transactions on CentOS 6.4. It completed in less than an hour.

Make a backup copy of your MySQL mam database.
```
[root]# mysqldump mam > /archive/mam.mysql
```
Follow the instructions to Install PostgreSQL.
- Manual Install - 6.11.3 Install and Initialize the PostgreSQL Server
- RPM Install - 6.32.4.A Install and Initialize PostgreSQL Server

Install the prerequisite packages.

[root]# yum install git postgresql-devel gcc MySQL-python python-psycopg2 PyYAML termcolor python-devel

[root]# zypper install git postgresql-devel gcc MySQL-python python-psycopg2 PyYAML termcolor python-devel

Install pg-mysql2pgsql (from source).

[root]# cd /software
[root]# git clone git://github.com/philipsoutham/py-mysql2pgsql.git
[root]# cd py-mysql2pgsql
[root]# python setup.py install

Run pg-mysql2pgsql once to create a template yaml config file.
```
[root]# py-mysql2pgsql -v
```

Edit the config file to specify the MySQL database connection information and a file to output the result.

[root]# vi mysql2pgsql.yml

mysql:
hostname: localhost
port: 3306
socket:
username: mam
password: changeme
database: mam
compress: false
destination:
# if file is given, output goes to file, else postgres
file: /archive/mam.pgsql
postgres:
hostname: localhost
port: 5432
username:
password:
database:

Run the pg-mysql2pgsql program again to convert the database.
```
[root]# py-mysql2pgsql -v
```

Create the mam database in PostgreSQL.

[root]# su - postgres
[postgres]$ psql
postgres=# create database "mam";
postgres=# create user mam with password 'changeme!';
postgres=# \q
[postgres]$ exit

Import the converted data into the PostgreSQL database.
```
[root]# su - mam
[mam]$ psql mam < /archive/mam.pgsql
```

Point MAM to use the new postgresql database.

[mam]$ cd /software/mam-latest
[mam]$ ./configure                  # This will generate an etc/mam-server.conf.dist file
[mam]$ vi /opt/mam/etc/mam-server.conf   # Merge in the database.datasource from etc/mam-server.conf.dist

Restart Moab Accounting Manager.
```
[mam]$ mam-server -r
```