OpManager
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 "Migrate data from the existing database" option and click OK.
- 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):
- In the MSSQL setup, make the below changes in db_migration.conf in the <OpManagerHome>\conf directory.
- Change the value of dest.db.postgres.dir to <OpManagerHome>/pgsql directory (E.g., dest.db.postgres.dir = <OpManager Base Home>/pgsql).
This step is not applicable for OpManager versions 125324 and above.
Note: It is mandatory to use "/" as a directory separator.
- Now go to OpManager home → bin, start Command Prompt with administrator privilege from this path and run DBConfiguration.bat.
- In the popup shown, please chose PostgreSQL and check "Migrate data from the existing database" option and click OK.
- After the migration is complete, start the product and check if it is working properly.
MSSQL to PGSQL Migration (Remote PGSQL)
- In the MSSQL setup, make the below changes in db_migration.conf in <OpManagerBase Home>\conf directory.
- create.dest.db=false
- start.dest.postgres.server=false
- Rename the database_params_dbconfig.conf.bkp file in <OpManagerHome>\conf\OpManager\POSTGRESQL folder if it exists.
- Create a database in Remote PostgreSQL server.
- 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;
- 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> )
- Now go to OpManager home → bin, start Command Prompt with administrator privilege from this path and run DBConfiguration.bat.
- In the popup shown, please chose PostgreSQL and check the "Migrate data from the existing database" option and click OK.
- After the migration is complete, start the product and check if it is working properly.
Migrating SQL server from one machine to another
Note: These steps are applicable for all versions of OpManager.
- Stop OpManager Service.
- Take a SQL DB backup using the SQL Management tool.
- Restore the MSSQL DB backup(taken from old server) in the new server using the SQL Management tool.
- Open cmd with admin privilege and go to <OPMHome>/bin and run DBConfiguration.bat. Provide new sql details and save.
- Start OpManager Service.
For OpManager version 12.5
Note: The following steps are only applicable for Windows installations. The supported PgSQL versions are 10.12 and above.
Migrating from PgSQL to MSSQL
- Download and install the latest version of OpManager (choose MSSQL DB while installing).
- Do not hit 'Finish' at the end of installation.
- In the old PGSQL setup, go to <OpManagerHome>\bin>, start Command Prompt from that path and run the 'MigrateDB.bat'file with the required parameters.
MigrateDB.bat mssql <opm_home_from_new_MSSQL_setup>/conf/databaseParams.conf
- After migration, navigate to <OpManagerHome>\conf\OpManager in the old PgSQL setup, copy the data-dictionary.xml file and replace it under the same directory in the new MSSQL setup.
- Start the OpManager service and check if it works properly.
Migrating from MSSQL to PgSQL (Pre-populated setup)
- Download and install the latest version of OpManager (choose PGSQL DB while installing).
- Do not hit 'Finish' at the end of installation.
- Provide a new DBNAME for database_params.conf in the new PGSQL setup.
- In the old MSSQL setup, make the below changes in the db_migration.conf file under<OpManager Base Home>\conf:
create.dest.db=true
start.dest.postgres.server=true
- In the db_migration.conf file (in the old MSSQL setup) under<OpManager Base Home>\conf, change the value of dest.db.postgres.dir
dest.db.postgres.dir = <OpManager New PgSQL Home>/pgsql
Note: It is mandatory to use "/" as the directory separator
- In the old MSSQL setup, go to <OpManagerHome>\bin, start Command Prompt with administrative privilege from the same path and run the 'MigrateDB.bat' file with the required parameters.
MigrateDB.bat postgres <OpManager New PgSQL Home>\conf\database_params.conf
- After migration, navigate to <OpManagerHome>\conf\OpManager in the old MSSQL setup, copy the data-dictionary.xml file and replace it under the same directory in the new PgSQL setup
- Now, start OpManager Service from the new PGSQL setup and check.
Migrating from MSSQL to PgSQL (Non Pre-populated setup - All 32 bits and EE setups)
- Download and install latest(same) version of OpManager (Choose PGSQL DB while installing).
- Do not hit 'Finish' at the end of installation.
- Make the below changes in the db_migration.conf file (in the old MSSQL setup) under<OpManager Base Home>\conf:
create.dest.db=true
start.dest.postgres.server=true
- In the db_migration.conf file (in the old MSSQL setup) under<OpManager Base Home>\conf, change the value of dest.db.postgres.dir
dest.db.postgres.dir = <OpManager New PgSQL Home>/pgsql
Note: It is mandatory to use "/" as the directory separator
- In the old MSSQL setup, go to <OpManagerHome>\bin, start Command Prompt with administrative privilege from the same path and run the 'MigrateDB.bat' file with the required parameters.
MigrateDB.bat postgres <OpManagOpManagerer New PgSQL Home>\conf\database_params.conf
- After migration, navigate to <OpManagerHome>\conf\OpManager in the old MSSQL setup, copy the data-dictionary.xml file and replace it under the same directory in the new PgSQL setup.
- Now, start OpManager Service from the new PGSQL setup and check.
Migrating from MSSQL to Remote PgSQL
- Download and install latest version of OpManager (Choose PGSQL DB while installing).
- Do not hit 'Finish' at the end of installation.
- Create a new database in the Remote PgSQL server. Provide the new DBNAME and its remote server details in the database_params.conf file of the new PGSQL setup.
- Change the value of 'isBundledPgSQL' in the database_params.conf filrto false.
- In the db_migration.conf file (in the old MSSQL setup) under<OpManager Base Home>\conf, change the value of dest.db.postgres.dir
dest.db.postgres.dir = <OpManager New PgSQL Home>/pgsql
Note: It is mandatory to use "/" as the directory separator
- In the old MSSQL setup, go to <OpManagerHome>\bin, start Command Prompt with administrative privilege from the same path and run the 'MigrateDB.bat' file with the required parameters
MigrateDB.bat postgres <OpManager New PgSQL Home>\conf\database_params.conf
- After migration, navigate to <OpManagerHome>\conf\OpManager in the old MSSQL setup, copy the data-dictionary.xml file and replace it under the same directory in the new PGSQL setup.
- Now start OpManager Service from new PGSQL setup.
For OpManager version 12.4
Migrating from PGSQL TO MSSQL
Migrating from MSSQL TO PGSQL
- Download the latest version of OpManager (.exe/.bin)
- After the installation is complete, do not hit the 'Finish' button.
- In the old MSSQL setup, go to OpManagerhome directory → pgsql → bin, start Command Prompt from this path and run the following command:
psql.exe -U postgres -p <postgre_port_number> -h 127.0.0.1
This establishes a connection between OpManager and the PGSQL server.
- Next, invoke the create databasecommand with a DB name of your choice.
create database <New_database_name>;
- Now, copy the name of the newly created database, and replace it in the databaseParams.conf file in the 'conf' directory.
- Go to the OpManager home directory of the old PGSQL setup, and run the 'MigrateDB.bat' file with the required parameters.
MigrateDB.bat postgres <opm_home_from_new_PGSQL_setup>/conf/databaseParams.conf
(Pointing the newly created PGSQL database from the old MSSQL database)
- After migration is complete, copy the data-dictionary.xml file from the 'mssql' folder of the old installation to the 'pgsql' folder of the new installation.
- Start the product, and check if it's working properly.
For builds earlier than 11600, follow the steps below to migrate from MySQL to PGSQL/MSSQL.
Migrating from MySQL to PGSQL