IM&P 10.5 and PostgreSQL 12 - Schema created but Cisco XCP Message Archiver service won't start

[Shaun H]

Hello,

Has anyone ever attempted to use PostgreSQL 12 as an external database successfully? My IM&P version is 10.5.2.24900-8. I could only find information concerning PostgresSQL up to version 10, but as the Windows server I had been given access to already had version 12 installed I thought I would give it a go. Connectivity seems to be fine, and the database schema containing 27 tables has been generated.

I have activated the Cisco XCP Message Archiver but the service itself will not start; the page states that "Cisco XCP Message Archiver Service Start Operation was Successful", but under the service status it is 'Not Running'. I have tried stopping and starting the Cisco XCP Router service both before and after activating the Archiver but this did not make a difference. 

The postgresql file is amended as follows:

• escape_string_warning = off
• standard_conforming_strings = off

I have also unconfigured the Compliance setup and the External DB and added it all back in, ensuring that services were restarted, all to no avail.

When I attempt to start the Archiver, RTMT tells me this:

16:20:30.104 |f768eb70| debug| MAService.cpp:206 Got db config. Configuring.

16:20:30.242 |f768eb70| info| ODBCConnection.cpp:202 connect dsn:xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx username:jabber type:2

16:20:30.253 |f768eb70| error| ODBCConnection.cpp:654 unable to fetch columns for jm

16:20:30.253 |f768eb70| info| DBConnection.cpp:392 validate schema for database: xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx /jabber missing table: jm

16:20:30.256 |f768eb70| error| DBConnection.cpp:418 validate schema for database: xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx /jabber result: mismatch

As you can see it is unable to retrieve the jm table, but the schema is definitely there and I can confirm that no data has been written to it so far. Would anyone have any other things in mind for me to check or change, before I try going for an older PostgreSQL version?

Many thanks!

[Mohajibrahim]

I think you will have to try to downgrade to the Cisco tested and recommended PostgreSQL ver. 10.7 or ver. 10.5. The PostgreSQL download website for  x64-bit windows download .

I am not sure what version of Windows server you are running but it has to conform to the PostgreSQL install prerequisite.  I have the same issue at the moment but I had installed PostgreSQL 13 on Windows server 2019 and got errors in the PostgreSQL log located here C:\Program Files\PostgreSQL\13\data\log. In fact it created the following error in the log STATEMENT: select n.nspname, c.relname, a.attname, a.atttypid, t.typname, a.attnum, a.attlen, a.atttypmod, a.attnotnull, c.relhasrules, c.relkind, c.oid, pg_get_expr(d.adbin, d.adrelid), case t.typtype when 'd' then t.typbasetype else 0 end, t.typtypmod, c.relhasoids from (((pg_catalog.pg_class c inner join pg_catalog.pg_namespace n on n.oid = c.relnamespace and c.relname like E'users' and n.nspname like E'public') inner join pg_catalog.pg_attribute a on (not a.attisdropped) and a.attnum > 0 and a.attrelid = c.oid) inner join pg_catalog.pg_type t on t.oid = a.atttypid) left outer join pg_attrdef d on a.atthasdef and d.adrelid = a.attrelid and d.adnum = a.attnum order by n.nspname, c.relname, attnum
ERROR: column c.relhasoids does not exist at character 245

Basically I have ended up downgrading to PostgreSQL version 10.5 and gave the DB account to login from IM&P Message Archiver super user admin role and rights the PostgreSQL. As well do not forget to allow the IP address of the IM&P in PostgreSQL pg_hba.conf file  (md5, password or trust allow permission. Test with trust or password only first).