How to resolve Connectivity issues with Postgresql Database from our product?

How to resolve Connectivity issues with Postgresql Database from our product?


Log Traces 
  1. Feb 20, 2024 6:40:00 PM  [SYSERR]  [INFO] : java.sql.SQLException: java.lang.Exception: Exception during getConnection from pool Exception occurred during get connection from datasource
  2. Nov 15, 2023 1:29:10 PM  [SYSERR]  [INFO] : Caused by: java.lang.Exception: Exception during getConnection from pool
  3. Nov 15, 2023 1:29:10 PM  [SYSERR]  [INFO] :  at com.zoho.cp.ConnectionPool._getConnectionDetail(ConnectionPool.java:199)
(OR)
  1. 16.02.2022 08:33:07  [SYSERR]  [INFORMATION] : Caused by: java.net.SocketException: Connection reset
  2. 16.02.2022 08:33:07  [SYSERR]  [INFORMATION] : at java.net.SocketInputStream.read(SocketInputStream.java:210)
  3. 16.02.2022 08:33:07  [SYSERR]  [INFORMATION] : at java.net.SocketInputStream.read(SocketInputStream.java:141)
(OR)
  1. Feb 5, 2022 1:49:17 PM  [SYSERR]  [INFO] : org.postgresql.util.PSQLException: Connection attempt timed out.
  2. Feb 5, 2022 1:49:17 PM  [SYSERR]  [INFO] : at org.postgresql.Driver$ConnectThread.getResult(Driver.java:429)
  3. Feb 5, 2022 1:49:17 PM  [SYSERR]  [INFO] : at org.postgresql.Driver.connect(Driver.java:271)
  4. Feb 5, 2022 1:49:17 PM  [SYSERR]  [INFO] : at java.sql.DriverManager.getConnection(DriverManager.java:664)
Cause :
  1. A "connection timed out" error in PostgreSQL typically occurs when the application attempts to connect to the PostgreSQL server, but the connection establishment process takes longer than the specified timeout duration or may not able to establish. This can happen due to various reasons, and resolving it involves identifying and addressing the underlying cause step by step.
  2. There are a number of factors like Network connectivity, database driver compatibility client configuration, server configuration etc, which can significantly affect the postgres operations and connection establishment. 
  3. Typically, a connection establishment consist of several stages of operations, and this causes may affect in different areas while establishing connection.

Here we are listing the troubleshooting steps in a order, such that if the first set doesn't resolves the issue, then check the next one.
If it resolves, you are good to proceed, no need to follow further steps.

Troubleshooting  :
Environment :
  1. This connection issue relies heavily of the environment that the server is hosted. There are several factors in the environment, which comes into the postgres database connectivity exception.
    1. Firewall / Windows Defender / Antivirus Service :
      1. Ensure, these services doesn't affects / intercepts with the processes running inside <product_installation_directory>.  We recommend you to exclude this location from the antivirus scanning process.
      2. By default, our applications runs in port 65432 (in case of ServiceDeskPlus) & 65433 (in case of AssetExplorer). Make sure such ports are available and doesn't get intercepted by firewall too often. In case of external postgres database, Firewalls or network security configurations may block incoming connections to the PostgreSQL server.
      3. In some of the Windows operating system, windows defender is scanning all processes. Make an exclusion in that list, such that windows defender doesn't intercepts.
      4. Make sure, other application processes doesn't affect our product running processes. and apart from our processes, no processes should run inside the <product_installation_directory>. 
    2. Data Execution Policy :
      1. This segment is applicable only to Windows. Refer here for more details about this policy.
      2. We need to exclude the <product_installation_folder> from restricting due to this policy. Configure in such a way, that the executables inside <product_installation_folder> can be executed successfully.
    3. Network issues
      1. This segment is applicable only for the instance, which has it's database hosted in different servers and accessed externally
      2. After ensuring firewall configuration, High network traffic or network congestion can significantly delay or interrupt the connection process. Ensure the network is seamless and robust in nature. 
Client Configuration :
  1. This block troubleshoots the issue with respect to the version and configuration of Application which acts as the client here.
    1. Database Driver :
      1. If your build version is under 13005, Kindly follow this article.
      2. Check whether only one jar is present with the pattern postgresql-4x.x.x.jar inside the <product_installation_location> \ lib directory. If you find any other jars, Kindly preserve the latest version jar and move other jars to any other directory out of <product_installation_location>.
After ensuring the above points, If you again face this connection failures, Kindly report it to our support team with the below required files.
  1. <product_installation_directory> \ logs folder
  2. <product_installation_directory> \ pgsql \ data \ pg_hba.conf
  3. <product_installation_directory> \ pgsql \ data \ postgresql.conf
  4. <product_installation_directory> \ pgsql \ data \ pg_logs folder
  5. <product_installation_directory> \ pgsql \ ext_conf folder. 

                    New to ADSelfService Plus?

                      • Related Articles

                      • How to Connect External Postgres

                        Install a External Postgres such that the version of that postgres is currently in the series of inbuilt postgres of our Application. Say for example, In Servicedeskplus Application version 14010, which has postgres version 10.21, then you should ...
                      • How to change the database from MySQL/PostgreSQL to MSSQL

                        The steps mentioned below are applicable only if you migrate the DB from PgSQL to MSSQL. This means that the application server is going to remain the same. The data alone is going to be transferred to MSSQL DB from the existing PgSQL DB.    For ...
                      • Securing PostgreSQL Database Connection with SSL in ServiceDeskPlus

                        Overview This article serves as a comprehensive guide for customers intending to secure their database connections when using with ServiceDeskPlus Application with PostgreSQL database, similar to securing Microsoft SQL Server (MSSQL) connections. The ...
                      • Migration failure for Postgres Database with a blank space in it

                        Trace : \ManageEngine\ServiceDesk\Patch\AdventNet_ManageEngine_ServiceDesk_Plus-14.2.0-SP-1.0.0\SERVICEDESK\PreInstall\pg_migrate\pgsql_old\bin\pg_dumpall.exe", -U, postgres, -p, 65433, -h, 127.0.0.1, -r, -w, |, ...
                      • Remote read only access to database for Postgres customers

                        Frequently customers want to connect some reporting / dashboard application like PowerBI or Tableau with our Postgres database server. By default, the bundled Postgres is configured to only listen to the local machine. We can configure to allow ...