Database Migration - MySQL to PgSQL

Database Migration - MySQL to PgSQL

The DB migration from MySQL to PgSQL involves taking a PgSQL compatible backup, re-installing the product with PgSQL database and restoring the backup.

The procedure for the above is a little tedious and time consuming in version 9 & 10. So, please upgrade your current installation to the 11 and then follow the steps to migrate the DB. The upgrade packs & instructions can be found in this link.

Steps to migrate from MySQL to PgSQL:

1. Stop OpManager service.

2. Open a command prompt in Administrator mode and execute the backup script like shown below:

C:\...\OpManager\bin\backup> BackupDB.bat -targetdb pgsql

3. The backup file will be created under \OpManager\backup folder. The file name would be like Backup_Mysql_to_Pgsql_Oct07_181226_11600.zip. Copy this file into a different location, as we will be uninstalling this instance.

4. Also, copy & save the following files found under \OpManager\classes folder:

      1. AdventNetLicense.xml 
      2. petinfo.dat
      3. product.dat 

5. Download the appropriate OpManager installation file (32 bit or 64 bit) from this link and install it. While installing, choose PgSQL as the database. This process will uninstall the MySQL installation.

6. Once installed successfully, start the product. This will create the OpManager database in PgSQL. Login to the product to confirm the DB creation is successful. 

7. Stop OpManager service.

8. Open a command prompt in Administrator mode and execute the restore script with arguments below:

C:\...\OpManager\bin\backup> RestoreDB.bat "Complete path of the PgSQL compatible backup file"

For example:

C:\...\OpManager\bin\backup> RestoreDB.bat "D:\...\Backup\Backup_Mysql_to_Pgsql_Oct07_181226_11600.zip"

9. Once the restore is complete, paste the files copied in Step 4 into the same location, replacing the original files and start OpManager service.

The product will now use PgSQL as its database. You can verify this from the 'Installation & JVM info' column in OpManager UI --> Support --> Support page.

          • Related Articles

          • OpManager DB migration from PGSQL to MSSQL in Version 125230

            For OpManager version 125230: PGSQL to MSSQL Migration Steps : In the PGSQL setup, go to OpManager home → bin, start Command Prompt with administrator privilege from this path and run DBConfiguration.bat. In the popup shown, please chose MSSQL, check ...
          • OpManager DB migration from PGSQL to MSSQL on the same server in Version 12.3

            Please follow the below mentioned steps to migrate DB from PGSQL to MSSQL on the same server. These steps are applicable only on builds 12.3.045 and above. If you are in older versions below 12.3.045, please upgrade to 12.3.045 and follow these ...
          • How do I set a password for my OpManager pgsql database ?

            1) Start OpManager service 2)Connect  to OpManager database :   a)Go to OpManager\pgsql\bin folder   b)Type psql -U postgres -h 127.0.0.1 -p13306  OpManagerDB   c)Once connected to the Database, execute the below query to set password          ALTER ...
          • Reindex & Vacuum OpManager Database

            Reindex & Vacuum OpManager Database Stop OpManager service Make sure there is no running postgres process running in the Task Manger Open a command prompt as an administrator and go to /OpManager/bin directory Execute startPgsql.bat to start PGSQL ...
          • Unable to start OpManager - PGSQL

            Sometimes OpManager will not start when the server ran out of disk space in the pg_xlog directory. It will not be possible to retrieve the connection to OpManager database. The files present under OpManager\pgsql\data\pg_xlog get disappeared in such ...