MySQL/MS SQL to PostgreSQL migration

ADAudit Plus allows administrators to store and retrieve Windows servers' audit log data from MySQL, MS SQL, or PostgreSQL databases. To move the DB and/or data from MySQL/MS SQL to PostgreSQL, follow the steps found below.

1. Stop ADAudit Plus, go to the Start menu > Services > Right-click the ADAudit Plus service, and select Stop.
2. Download PostgreSQL and extract the downloaded file to <Installation_folder>\ManageEngine\ADAudit Plus.

   Note: Before downloading the file, please ensure that the ADAudit Plus installation folder does not contain any folder named pgsql. If it does, please rename the folder to something else, like pgsql_old, before downloading the PostgreSQL file.

3. Go to <Installation-folder>\ManageEngine\ADAudit Plus\bin, open an elevated Command Prompt (right-click Command Prompt and select Run as administrator), and execute ChangeDB.bat.
4. In the Database Setup Wizard that pops up, fill in the following details, and click Save:
   • Beside Server Type, select PostgreSQL from the drop-down.
   • Beside Host name, enter localhost.
   • Beside Port, enter 33307 or 33308. If you are migrating from MS SQL, the port number is 33307, and if you are migrating from MySQL, the port number is 33308.
   • Beside Database, enter ADAP or any other name of your choice.
   • Beside Migrate Data, select Yes or No, depending on whether you want to migrate the data or not.
   • Beside User Name and Password, enter a user name and password of your choice.

Note: The migration usually takes a few minutes, however, it might take a bit longer depending on the volume of data to be migrated from your MySQL/MS SQL database.

Converting the format of archived files after migration

The data in archived files is stored in different formats across databases. After migrating from one database to another, the format of the existing archived files has to be converted for them to be compatible with the new database. To convert the format of the archived files, follow the steps below:

1. Navigate to <Installation_Folder>\ManageEngine\ADAudit Plus\archive and create a new folder with a suitable name, say, tableBackup_new.
2. Open the Command Prompt as an administrator, navigate to <Installation_Folder>\ManageEngine\ADAudit Plus\bin, and execute the following command:
   ChangeArchive2BCPSupport "<Archive_Directory>" "<New_Directory>" <Old_Backend_DB> <New_Backend_DB>

   In the above command,

   • Replace <Archive_Directory> with the path to the original folder that contains the archived files. The default name of this folder is tableBackup and it can be found at <Installation_Folder>\ManageEngine\ADAudit Plus\archive.
   • Replace <New_Directory> with the path to the tableBackup_new folder that you just created in step 1.
   • Replace <Old_Backend_DB> and <New_Backend_DB> with postgres or mysql or mssql depending on which database you are migrating from and which database you are migrating to.

   For example: If you are migrating from MySQL to PostgreSQL, and your archive directory and new directory are C:\ManageEngine\ADAudit Plus\archive\tableBackup and C:\ManageEngine\ADAudit Plus\archive\tableBackup_new respectively, then the command will be as follows:

   ChangeArchive2BCPSupport "C:\ManageEngine\ADAudit Plus\archive\tableBackup" "C:\ManageEngine\ADAudit Plus\archive\tableBackup_new" mysql postgres

   Once the command executes successfully, new files whose format is compatible with the PostgreSQL database will be created in the tableBackup_new folder.

3. Move the archived files from the original folder (tableBackup) to a secure location for backup.
4. Copy the new files from the tableBackup_new folder and paste them in the original folder (tableBackup).