OpManager DB migration from PGSQL to MSSQL in Version 125230

OpManager DB migration from PGSQL to MSSQL in Version 125230

For OpManager version 125230:

PGSQL to MSSQL Migration Steps :

  1. In the PGSQL setup, go to OpManager home → bin, start Command Prompt with administrator privilege from this path and run DBConfiguration.bat.
  2. In the popup shown, please chose MSSQL, check "Migrate data from the existing database" option and click OK.
  3. After the migration is complete, start the product and check if it is working properly

MSSQL to PGSQL Migration (For Prepopulated and non prepopulated setups):

  1. In the MSSQL setup, make the below changes in db_migration.conf in the <OpManagerHome>\conf directory.
  2. Change the value of dest.db.postgres.dir to <OpManagerHome>/pgsql directory (E.g., dest.db.postgres.dir = <OpManager Base Home>/pgsql).
  3. Note: It is mandatory to use "/" as a directory separator.

  4. Now go to OpManager home → bin, start Command Prompt with administrator privilege from this path and run DBConfiguration.bat.
  5. In the popup shown, please chose PostgreSQL and check "Migrate data from the existing database" option and click OK.
  6. After the migration is complete, start the product and check if it is working properly.

MSSQL to PGSQL Migration (Remote PGSQL)

  1. In the MSSQL setup, make the below changes in db_migration.conf in <OpManager Base Home>\conf directory.
    • create.dest.db=false
    • start.dest.postgres.server=false
  2. Rename the database_params_dbconfig.conf.bkp file in <OpManagerHome></OpManagerHome>\conf\OpManager\POSTGRESQL folder if it exists.
  3. Create a database in Remote PostgreSQL server.
  4. Migrating OpManager Database

  5. Create 'rouser' for read-only permissions. Connect to pgAdmin and execute the below queries:
    • CREATE USER rouser with password '<ROPASSWORD>';
    • CREATE EXTENSION IF NOT EXISTS pgadmin SCHEMA pg_catalog;
    • REVOKE ALL ON SCHEMA public FROM rouser, public;
    • GRANT CONNECT ON DATABASE "<DatabaseName>" TO rouser, public;
    • GRANT USAGE ON SCHEMA public TO rouser, public;
    • GRANT SELECT ON ALL TABLES IN SCHEMA public TO rouser, public;
    • ALTER DEFAULT PRIVILEGES FOR ROLE postgres IN SCHEMA public GRANT SELECT ON TABLES TO rouser, public;
    • GRANT USAGE ON SCHEMA public TO rouser;
    • GRANT SELECT ON ALL TABLES IN SCHEMA public TO rouser;
    • ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO rouser;
  6. Migrating OpManager Database

  7. Changes to be made in the <OpManagerHome>\conf\OpManager\POSTGRESQL\database_params.conf
    • Make the changes in URL field jdbc:postgresql://<remotePgSQLInstalledIPAddress>:<PortNumber>/<DBName>?dontTrackOpenResources=true&useUnicode=true&characterEncoding=utf8
    • Change the username to postgres. (username=postgres)
    • Encrypt the password of the postgres user and change the same in the above mentioned. file (password=<EncryptedPassword>)
    • Add the property isBundledPgSQL=false as well in the same file.
    • Change the ro_password to the password supplied in point 4 <ROPassword>. Encrypt the same and include it in the same file (ro_password=<ROPassword>)
    • Encryption of the plain text can be done using <OpManagerHome>\bin\encrypt.bat by specifying the algorithm as AES.256 and by using CryptTag which can be found in <OpManagerHome>\conf\customer-config.xml (Usage:: encrypt.bat -v <Password> -a AES.256 -k <CryptTag> )
  8. Migrating OpManager Database

  9. Now go to OpManager home → bin, start Command Prompt with administrator privilege from this path and run DBConfiguration.bat.
  10. In the popup shown, please chose PostgreSQL and check the "Migrate data from the existing database" option and click OK.
  11. After the migration is complete, start the product and check if it is working properly.

DB MIGRATION

                  New to ADSelfService Plus?

                    • Related Articles

                    • 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 ...
                    • 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 ...
                    • Installing OpManager on Linux with MSSQL database

                      From version 12.8.330, MSSQL database is supported for Linux setups. As of now MSSQL database cannot be configured directly from the installation wizard for OpManager's Linux installation. Instead, perform a default installation with PgSQL database ...
                    • Installing OpManager with Amazon RDS MSSQL instance

                      Installing MSSQL instance in Amazon RDS Step 1: While installing MSSQL instance in Amazon RDS, please make sure that the edition and version we support are selected. Step 2: Make a note of the Master username and password provided (these credentials ...
                    • Steps for setting up a password for PgSQL DB

                      The steps are verified and tested until 12.4.072.  We request you to take a VM snapshot or a folder backup of the OpManager server itself (stop the OpManager service) as a first measure. Below mentioned steps are applicable for Postgres version till ...