Want to migrate to PostgreSQL from Oracle on OM? Remember these things!

For a long time, the usage of HP Operations Management (HPOM) has been more or less tied to Oracle RDBMS. It has been a long relationship, but time tends to grind everything down. To keep things from getting stale, sometimes you have to learn to move on in order to explore new horizons. Therefore, the next release of HPOM (version 9.20) includes a long-requested feature… support for an alternative database. In our case, PostgreSQL was chosen as a suitable candidate.

 

PostgreSQL is not really a new player. It has been with us since 1995, and is well related to the Ingres family and has enjoyed increasing popularity in the last few years. It brings support for SQL:2011 standard, supports UTF-8 locale and IPV6 connectivity for a hassle-free operation. IT is also multi-platform and with a very permissive license. And sure, it may have had some weak points in the past (like replication), but recently has worked very hard to cover it. So, all in all, it's a simple but very scalable database server that can handle everything that an HPOM environment needs. And, let's be honest—it's also free.

 

Once the decision has been made and we're ready to move HPOM to this new database, we must stop for a second and consider the plan. A lot of effort has been put to ease this transition, (no one wants to suffer, be it customer or support operator) but it is still necessary to proceed with caution. Therefore you can find below some little tips to get the HPOM up and running in no time.

 

Back-it-up

This should be totally unnecessary to remind you to make sure you create a backup.  Before we go exploring, it is great to have a base camp to return to if things go wrong. Therefore, if you are planning on upgrading an already existing HPOM system, please make a copy of everything contained within:

  • Binaries
  • Configuration files
  • And most importantly, the database

You can use HPOM's own tool for that (opcbackup_offline), which ensures you can recover from losses.

 

Know thine friend

Once we ensure a safe journey, it is time to consider PostgreSQL itself. One of the main causes of migration costs, which is often overlooked, is the time operators need to adapt and get to know the new technology and tools they will work with. A lot of care has been taken so that operation with HPOM on PSQL is as similar as possible as it is with HPOM on Oracle, therefore the learning curve won't be so steep. But to make the process even smoother, it is highly recommended to familiarize yourself with the new database and tools.

 

Therefore, we suggest you download a copy of PostgreSQL binaries from your distribution repositories or from postgresql.org/download, create a database and get familiar with the concepts and tools. pgAdmin is the graphic tool of choice, where we used sqlplus to manually connect to the database from the command line, now we'll use psql, redo log system is called WAL on PostgreSQL. A database instance is called "cluster" just because it can hold more than a single database, and so on. You can find all the needed documentation in http://www.postgresql.org/docs/9.3/interactive/index.html.

 

This way, when it comes to the real thing, things won't sound so alien to you.  We'll also have some extra knowledge to react to and solve any problem along the way.

 

Plan your destination

While PostgreSQL has a lot of functionality in lots of areas (SQL, procedural languages, replication, external databases, complex views, authentication), HPOM storage needs are quite simple and only use the basics of what a modern RDBMS has to offer. By default, HPOM will create a local database with IPV4 connectivity, no SSL, and 32/64 Mb of RAM, which is enough to support as a starting point.

 

But every customer has different needs, this is why both HPOM and PostgreSQL allow for a lot of flexibility. With this in mind it is very important to plan ahead around the scale of our database. Are we ok with a single local server? Do we need a remote machine? Will we need to use replication? Are we going to have to use split table spaces in an array of locations for faster disk access? Most of these issues are solved by editing the main postgresql configuration file, postgresql.conf. However, in some complex environments you will need to use external tools (slony/pgPool).

One of the last considerations is if we will require professional support for the database itself. PostgreSQL has a wide range of free/libre open source packages, but for a company, it may bring an added degree of safety to acquire a support contract. The main company behind this is EnterpriseDB—which besides a plain PostgreSQL flavor—offers a couple of more commercial offerings. These include a more complete Oracle syntax support and management options. However, these are unused at the moment by HPOM, so the main reason to take this path is  the availability of external support for the database system itself, in case a bug is discovered and a quick patch is needed.

 

Prepare for change

As we said, once we move to PostgreSQL, the change is intended to be transparent. Most tools will work without any change on syntax or output. However, it may be the case that we have our own tools and scripts to work with the database. In this case, we need to be aware of some internal differences between Oracle and PostgreSQL:

 

C/C++ binaries

The main change here is the duplication of the old libopcdb.so library, which resides in the HPOM lib folder and contains most functions that connect and interact with the database. With the next release of HPOM, there are plans to have two copies of this library, each one designed to work with a database vendor, and a softlink will be always pointing to the appropriate one.


This way, we achieve the goal to have common binaries that work with both databases. In some particular cases, however, this was not possible. These special binaries have been moved and duplicated in /opt/OV/bin/OpC/db, and in their previous location a softlink will point to the correct version.

Both types of programs will rely on the PostgreSQL client libraries, most importantly libpq.so and libecpg.so. For this purpose a softlink is created from <HPOM lib folder>/PSQL to the PostgreSQL library folder. And here come a couple of points of conflict:

 

  • If you had some binaries built on top of previous versions of the HPOM API, you may need to relink or rebuild them. Otherwise you'll get a range of connection failure messages, missing library warnings, or other representatives from the weird-error world.
  • Likewise, the PostgreSQL libraries have different versions and dependencies and sometimes the packages are not correctly built. This results in a particular library is not found with the proper name under the PSQL directory and it then searches for it in the system path. If finds it there, it may actually be a library from a previous version of PostgreSQL which the operating system uses for its own internal purposes. And such mixture of libraries ends in catastrophic ways.

 

The best way to ensure these problems won't happen, is to install HPOM and PostgreSQL on a mockup machine, without your operating data, and run ldd on both HPOM and your custom binaries. Find libpq, libecpg, libpgtypes in the list and make sure they are all found in <HPOM lib dir>/PSQL. If not, some manual link creation may be in order.

 

Scripts

Scripts that connect directly to the database will require quite some changes, both because the SQL syntax varies somehow, and because the database client tools have different name and parameters. On this, two pieces of advice:

  • The recommended way of discerning if we're using PSQL or Oracle as our database is to check the ovdbconf file, where the DB_TYPE variable indicates the current database.
  • Also, the permission system in PostgreSQL differs a bit from the Oracle counterpath. The recommended procedure to run a SQL command is to do it via opcdbpwd command, in a similar way as the one you'd use with Oracle.

Reports

A variation of scripts, the call_sqlplus.sh tool has been updated to detect the correct database. Every report included by default has been duplicated, we have as an example all_nodes.sql for Oracle and all_nodes.psql for PSQL. The way of calling call_sqlplus doesn't change, as it will always run the appropriate report. In case we have created our custom ones, we must have in mind that both the querying language and the format commands deviate from each other. We will explain some basic differences in SQL below; for the format, you will want to check the "pset" syntax inside the PostgreSQL documentation; you can also compare both versions of the default reports to check the differences between them, to make the transition somehow easier.

 

A special difference is with the use of variables; while in Oracle reports they are specified as &1, &2… in PostgreSQL they appear as :v1 , :v2 … etc. If the value of these variables is a text string, you cannot use :v1 or ':v1'. Instead, set a new variable like this:

\set qv1 '\'' :v1 '\'' . Then you can use :qv1 in the report.

 

SQL syntax

When creating the table structure for the PostgreSQL database that HPOM uses, care has been taken that this structure is not altered; in practice this means that all tables and fields remain the same for both databases, with the only exception of field names (VARCHAR2s in Oracle are VARCHARs in PostgreSQL, NUMBERs become NUMERICs and, more complex, LOBs become BYTEAs, which will require a particular treatment).

 

For the rest, both Oracle and PostgreSQL conform more or less to the same SQL standard, some "peculiar" syntax of Oracle may prove an issue when trying to adapt some of our scripts for PostgreSQL. In most cases Oracle also supports the "standard" syntax, so the best solution is to use this syntax for both databases.

Some quick examples…

 

  • Outer joins: Where Oracle accepts the (+) syntax to select tuples where one of the members of a join doesn’t have a matching record in the other members, for PostgreSQL we must use the outer join syntax. So,

      SELECT

      NG.NODE_GROUP_NAME NAME,

      COUNT(N.NODE_ID) COUNT,

      FROM

      OPC_OP.OPC_NODE_GROUPS NG,

      OPC_OP.OPC_NODES_IN_GROUP NIG,

      OPC_OP.OPC_NODES N

      WHERE

      NG.NODE_GROUP_ID = NIG.NODE_GROUP_ID(+) AND

      NIG.NODE_ID=N.NODE_ID(+)

      GROUP BY

      NG.NODE_GROUP_NAME,

      TON.TOTALCNT

 

      Becomes

      SELECT

      NG.NODE_GROUP_NAME as NAME,

      COUNT(N.NODE_ID) as COUNT,

      FROM

      OPC_NODE_GROUPS NG
      LEFT OUTER JOIN

      OPC_NODES_IN_GROUP NIG ON NG.NODE_GROUP_ID = NIG.NODE_GROUP_ID

     LEFT OUTER JOIN

      OPC_NODES N ON NG.NODE_GROUP_ID = NIG.NODE_GROUP_ID

      GROUP BY

      NG.NODE_GROUP_NAME,

      TON.TOTALCNT

 

      So please use the second way for both databases.

 

  • DECODE: Instead of

      DECODE(SEVERITY,

      2,2,

      4,3,

      8,6,

      16,4,

      32,5,

      1)

      use the following for both databases:

      CASE SEVERITY

      WHEN 2 THEN 2

      WHEN 4 THEN 3

      WHEN 8 THEN 6

      WHEN 16 THEN 4

      WHEN 32 THEN 5

      ELSE 1

      END

 

  • Named fields /subqueries: PostgreSQL is a bit pickier in the subquery / field renaming syntax.  So please include the bold keywords when creating subqueries and custom named fields, for both dbs.

      SELECT

      c.d as NAME,

      COUNT(N.NODE_ID) as COUNT,

      WHERE c.d in (SELECT a FROM b) name;

 

  • Cursors: When creating a cursor in SQL code, it is possible to create it with hold or without it. This means, that when a transaction is committed, the cursor may be "held" open and therefore still accessible, or it will be directly closed. While this behavior can be directly specified in Pro*C as a preprocessor parameter, PostgreSQL has no such option, so it must be directly specified in the code (DECLARE CURSOR xxxx WITH HOLD FOR SELECT …)

Stored procedures

While HPOM database has a great total of two stored procedures (and I'm sure only one might be in use at the moment), have in mind that the syntax changes for both databases. So please have it in account in case you use additional procedures of your own.

 

As a quick and dirty example, a procedure for Oracle may look like this,

CREATE OR REPLACE FUNCTION ip_addr (ip_address IN number)
RETURN CHAR IS
  ip number(20);
  ip1 varchar2(4);
  ip2 varchar2(4);
  ip3 varchar2(4);
  ip4 varchar2(4);
  ip_result varchar2(40);
BEGIN
  ip  := ip_address;
  ip4 := TO_CHAR(MOD(ip, 256));
  ip  := TRUNC(ip / 256);
  ip3 := TO_CHAR(MOD(ip, 256));
  ip  := TRUNC(ip / 256);
  ip2 := TO_CHAR(MOD(ip, 256));
  ip1 := TO_CHAR(TRUNC(ip / 256));
  ip_result := ' ' || ip1 || '.' || ip2 || '.' || ip3 || '.' || ip4 ;
  RETURN(ip_result);
END;

 

While for PostgreSQL it varies a tiny bit…

CREATE OR REPLACE FUNCTION ip_addr (ip_address numeric)
RETURNS CHAR AS $$
  DECLARE
    ip numeric(20);
    ip1 varchar(4);
    ip2 varchar(4);
    ip3 varchar(4);
    ip4 varchar(4);
    ip_result varchar(40);
BEGIN
  ip  := ip_address;
  ip4 := TO_CHAR(MOD(ip, 256),'FM999');
  ip  := TRUNC(ip / 256);
  ip3 := TO_CHAR(MOD(ip, 256),'FM999');
  ip  := TRUNC(ip / 256);
  ip2 := TO_CHAR(MOD(ip, 256),'FM999');
  ip1 := TO_CHAR(TRUNC(ip / 256),'FM999');
  ip_result := ip1||'.'||ip2||'.'||ip3||'.'||ip4 ;
RETURN(ip_result);
END;
$$ LANGUAGE 'plpgsql';

 

Of course, this is in the case of PL/pgSQL, you're free to rewrite them in any other supported language.

 

Let's do it

The procedure on how to install HPOM server on PostgreSQL is described in Installation Guide, so please follow that steps and there should be no problems to get HPOM up and running on new database.

 

Test, test and test again

And well, assuming all went well, we can start using with our new system. As much as we've tried to eradicate any possible bug, there is always the fear that a little problem that may only happen on your specific configuration. Or the database structure you customized may have some problem on its own. So, needless to say, backup often and test, test, test to make sure all is running smoothly.

Leave a Comment

We encourage you to share your comments on this post. Comments are moderated and will be reviewed
and posted as promptly as possible during regular business hours

To ensure your comment is published, be sure to follow the Community Guidelines.

Be sure to enter a unique name. You can't reuse a name that's already in use.
Be sure to enter a unique email address. You can't reuse an email address that's already in use.
Type the characters you see in the picture above.Type the words you hear.
Search
Showing results for 
Search instead for 
Do you mean 
About the Author
Featured


Follow Us
The opinions expressed above are the personal opinions of the authors, not of HP. By using this site, you accept the Terms of Use and Rules of Participation.