PostGress SqlAgent showing error

[Amit Sharma]

Hi Experts

We have recently installed Postgrees SQL agent in the similar way we have done other SQL (DB_SERVICE) agent, the agent is running fine and started from SMgr, but when we check the logs of the agent from AWI, it is having the below error

20200707/160022.836 - U02012053 RunID: '198453436/1': Failed to create a connection to the database.

Also we have placed the jar file(postgresql-42.2.1.jar) under location .../bin/jdbc, jar file is shared to us by users.

Connection object which is created as part of that is also not working attached screen shot of the connection object.

Showing also the log from agent side. 

cat ucxjsqlx_log_00.TXT

20200707/155623.351 - U02000000 Started program Agent 'PGSQLAGENT', version '12.2.2+build.1549023827471', changelist '1549018248'.
20200707/155623.352 - U02000232 Build Date: '2019-02-01', '13:24:53'
20200707/155623.353 - U02000037 Started Agent with INI file '/opt/uc4/PGsqlagent/bin/ucxjsqlx.ini'.
20200707/155623.353 - U02000090 Java Runtime Environment version: '1.8.0_251'
20200707/155623.353 - U02000091 Java Runtime Environment vendor: 'Oracle Corporation'
20200707/155623.354 - U02000113 Path for native libraries: ':/opt/uc4/java/current/lib:/u01/app/oraclt/product/instantclt12.2.0.1.0/lib:/opt/uc4/server/bin:/opt/uc4/uc4smgr/bin:/opt/uc4/utility/bin:/usr/java/packages/lib/amd64:/usr/lib64:/lib64:/lib:/usr/lib'
20200707/155623.355 - U02000192 Operating system: 'Linux', version: '3.10.0-1127.el7.x86_64'.
20200707/155623.355 - U02000193 JVM Architecture: 'amd64'
20200707/155623.355 - U02000110 Maximum Heap Memory: '2276' MB
20200707/155623.356 - ------------------------------------------------------------------------------------------
20200707/155623.357 - [GLOBAL]
20200707/155623.357 - name=PGSQLAGENT
20200707/155623.357 - system=UC4SIT
20200707/155623.357 - language=(E,D)
20200707/155623.357 - logging=../temp/ucxjsqlx_log_##.TXT
20200707/155623.358 - logCount=10
20200707/155623.358 - helplib=./uc.msl
20200707/155623.358 - log_To_File=1
20200707/155623.358 - max_logging_kb=10000
20200707/155623.358 - LogMaxSize=
20200707/155623.359 -
20200707/155623.359 - [TRACE]
20200707/155623.359 - file=../temp/ucxjsqlx_trace_##.txt
20200707/155623.359 - trccount=10
20200707/155623.359 - TraceMaxSize=
20200707/155623.359 - max_trace_kb=10000
20200707/155623.360 - tcp/ip=0
20200707/155623.360 -
20200707/155623.360 - [TCP/IP]
20200707/155623.360 - cp=uc4sit1.de.db.com:2217
20200707/155623.360 - connect=60
20200707/155623.360 -
20200707/155623.361 - [AUTHORIZATION]
20200707/155623.361 - initialPackage=
20200707/155623.361 - keystore=
20200707/155623.361 -
20200707/155623.361 - [ORACLE]
20200707/155623.362 - buffer_Size=8000
20200707/155623.362 - enable_Output=1
20200707/155623.362 - vsession_Machine=
20200707/155623.362 -
20200707/155623.362 - [SQL]
20200707/155623.362 - connect=60
20200707/155623.363 - newline=1
20200707/155623.363 - retention_Time=180
20200707/155623.363 - retry=0
20200707/155623.363 - type=POSTGRESQL
20200707/155623.363 - useOraClient=0
20200707/155623.363 - WindowsAuthentication=0
20200707/155623.364 -
20200707/155623.364 - [VARIABLES]
20200707/155623.364 - UC_EX_PATH_BIN=.
20200707/155623.364 - UC_EX_PATH_JOBREPORT=../temp
20200707/155623.365 - UC_EX_PATH_TEMP=../temp
20200707/155623.365 - UC_HOST_JCL_VAR=SQL
20200707/155623.365 -
20200707/155623.365 - [CP_LIST]
20200707/155623.365 - 2218=uc4sit1.de.db.com
20200707/155623.366 - 2210=uc4sit1.de.db.com
20200707/155623.366 - 2211=uc4sit1.de.db.com
20200707/155623.366 - 2215=uc4sit2.de.db.com
20200707/155623.366 - 2214=uc4sit2.de.db.com
20200707/155623.366 - 2216=uc4sit2.de.db.com
20200707/155623.367 -
20200707/155623.367 - ------------------------------------------------------------------------------------------
20200707/155623.367 - U02000071 Current directory: /opt/uc4/PGsqlagent/bin
20200707/155623.368 - U02000066 Host information: Host name='frainautmcd8.de.db.com', IP address='10.245.21.179'
20200707/155623.368 - U02012038 The Agent has been started as Service.
20200707/155623.421 - U02000153 The JVM Option HeapDumpOnOutOfMemoryError is enabled.
20200707/155623.426 - U02000072 Connection to system 'UC4SIT' initiated.
20200707/155623.426 - U02000011 Connection to Server 'uc4sit1.de.db.com/10.245.21.146:2211' initiated.
20200707/155623.439 - U02000004 Connection to Server 'UC4SIT#CP002' successfully created.
20200707/155623.439 - U02000075 CP Server 'UC4SIT#CP002' has '1' client connections.
20200707/155623.490 - U02000020 Environment: Hardware = 'DB_SERVICE'.
20200707/155623.490 - U02000021 Environment: Software = 'DB_SERVICE'.
20200707/155623.491 - U02000022 Environment: SW version = '1.0'.
20200707/155624.565 - U02000017 The check interval for 'Jobs' has been set to '60' seconds.
20200707/155624.565 - U02000017 The check interval for 'Server' has been set to '660' seconds.
20200707/155624.565 - U02000017 The check interval for 'Reconnect' has been set to '90' seconds.
20200707/155624.566 - U02000017 The check interval for 'Report' has been set to '60' seconds.
20200707/155624.568 - U07001001 Charset used by the Agent: 'ISO-8859-15'
20200707/160022.735 - U02012035 Start Agent query with RunID '198453487'.
20200707/160022.743 - U02012066 Query of a table (PREP_PROCESS_VAR) for RunID '198453487'.
20200707/160022.746 - U02000106 Reading content of connection object 'DB_SYS.AUTOMIC.BASIC.CN.AE_DB_READONLY'.
20200707/160022.816 - U02000107 Connection object 'DB_SYS.AUTOMIC.BASIC.CN.AE_DB_READONLY' with time stamp '1489690197000' was successfully transferred to the Agent.
20200707/160022.829 - U02012035 Start Agent query with RunID '198453436'.
20200707/160022.830 - U02012066 Query of a table (PREP_PROCESS_VAR) for RunID '198453436'.
20200707/160022.836 - U02012053 RunID: '198453436/1': Failed to create a connection to the database.
20200707/160022.836 - U02012053 RunID: '198453487/1': Failed to create a connection to the database.
20200707/160022.837 - U02012037 Query with RunID '198453436' ended abnormally.

Help will be highly appreciated

Thanks
Amit Sharma

[Kyle Harlow]

Is there anything in the log on the Postgres side? <Postgres Install Directory>\data\log\
My first thought is that the agent server is being blocked in pg_hba.conf. By default, I believe only the local server can connect to the database, but it can be restricted by IP address or subnet.

[Amit Sharma]

Hi Kyle,

Thanks for response.. just a thought as our other SQL agents are running fine, only we have problem with Postgress SQL agent. Also it is not connecting from Automic, but direct login to Database with this user is possible.

So wondering might I have missed some settings ?

Thanks 

[Kyle Harlow]

To be honest, my experience with Postgres (and SQL in general) is pretty limited - the only reason why I've used Postgres is because we recently implemented the Analytics package, which requires its own Postgres database, so it's still pretty fresh in my head :)

Are jobs running using that connection object? If not, creating a SQL job and generating an Agent Log (in the attributes tab in the Report section) usually gives you a bit more detail than "Something happened¯\_(ツ)_/¯"

[Amit Sharma]

Do I need to add the connect string also in agent ini file , not sure what is missing but the connection object is also not working , throwing the below error

U02000112 Remote System is not available via Connection Object '22060-6.S4HANA_GL.CN.PG_CONN_TEST'.

Should I try using the jar file for postgress which comes along with our bundle to check 

Thanks 
Amit Sharma

[Scott Hughes]

At your Postgres server you might try editting postresql.conf and setting:
log_connections = on
log_disconnections = on

The above should tell you about attempts to reach postgres even if pg_hba.conf denies the connection.

------------------------------
Scott Hughes
Senior Automation Engineer
Verizon
Albuquerque, NM
------------------------------

[Tony Ferraro]

I don't see Postgres agent details listed on the 12.3 or even v21 docs, any reason for this?

https://docs.automic.com/documentation/webhelp/english/ALL/components/DOCU/12.3/Automic%20Automation%20Guides/help.htm#Installation_Manual/InstallAgents/InstallAgentDatabase_JobsEvents.htm#link1

https://docs.automic.com/documentation/webhelp/english/ALL/components/DOCU/21.0.6/Automic%20Automation%20Guides/Content/InstallAgents/InstallAgentDatabase_JobsEvents.htm?tocpath=Installing%7CInstalling%20the%20Agents%7CInstalling%20On-Premises%20Agents%20Manually%7CInstalling%20the%20Agent%20for%20Databases%7C_____1

I do see details for v11 though.
https://docs.automic.com/documentation/WEBHELP/English/ASO/11.2/AE/11.2/All%20Guides/Content/ucacob.htm