Remote PostgreSQL PPM Migration from OpManager version 12.6 and 12.7

Note:The below steps are applicable only for upgrading OpManager from version 12.6 to 12.7, and 12.7 to any version above (PgSQL 14.11 to 14.x)

Remote PostgreSQL Migration (version 10.x to 14.x)

Once OpManager is upgraded, it is mandatory to migrate the remote PgSQL to its latest version. Follow the below steps to migrate remote postgreSQL from version 10.x to 14.x.

1. Create a new database in the new PgSQL server.

Create database "<databasename>";

2. Create a new user named 'rouser' with read-only permission.

CREATE USER rouser with password '<ROPASSWORD>';

3. Execute the below queries:
   
   • 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;

Dump the data from the old PgSQL database to the new one

Once the migration is completed, dump the data from the old PgSQL database to the new one by executing the following command:

"<OldPgSQLHome>\bin\pg_dump.exe" -U postgres -p <OldPgSQLPort> -h <OldPgSQLHost> -x -W OpManagerDB | "<NewPgSQLHome>\bin\psql.exe" -U postgres -p <NewPgSQLPort> -h <NewPgSQLHost> -W OpManagerDB

Example: "C:\Program Files\PostgreSQL\10\bin\pg_dump.exe" -U postgres -p 5431 -h 127.0.0.1 -x -W OpManagerDB | "C:\Program Files\PostgreSQL\14\bin\psql.exe" -U postgres -p 5432 -h 127.0.0.1 -W OpManagerDB

Changes to be made in the OpManager directory

1. Open the database_params.conf file from <OpManagerHome>\conf.
2. Change the existing URL in the following format with the new PgSQL details.
   

   url jdbc:postgresql://<NewPgSQLHost>:<NewPgSQLPort>/<NewlyMigratedDBName>?dontTrackOpenResources=true&useUnicode=true&characterEncoding=utf8
   
   Example: url jdbc:postgresql://remote-pgsql-host:5432/OpManagerDB?dontTrackOpenResources=true&useUnicode=true&characterEncoding=utf8

After starting service, for sanity testing, execute a query in the submit query page and try adding device or any other basic functionality in product. [Sample Query - Select * from BuildDetails;]