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)
(OR)
  1. Caused by: java.net.SocketTimeoutException: Read timed out

  2. at java.net.SocketInputStream.socketRead0(Native Method)

  3. at java.net.SocketInputStream.socketRead(SocketInputStream.java:116)

  4. at java.net.SocketInputStream.read(SocketInputStream.java:171)

  5. at java.net.SocketInputStream.read(SocketInputStream.java:141)
(OR)

  1. Sep 17, 2024 3:24:30 PM  [com.adventnet.db.adapter.postgres.DefaultPostgresDBInitializer]  [INFO] : is DB registered as a service ::: false
  2. Sep 17, 2024 3:24:30 PM  [com.zoho.postgres.migrate.BundledPGMigration]  [SEVERE] : nulljava.lang.NullPointerException
  3. at com.adventnet.db.adapter.postgres.DefaultPostgresDBInitializer.isPgReady(DefaultPostgresDBInitializer.java:1194)
  4. at com.adventnet.db.adapter.postgres.DefaultPostgresDBInitializer.getServerStatus(DefaultPostgresDBInitializer.java:973)
  5. at com.adventnet.db.adapter.postgres.DefaultPostgresDBInitializer.isServerStarted(DefaultPostgresDBInitializer.java:913)


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.

Quote
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  :
Info
Environment :

Alert
Connection Issues are more specific to the environment in which the product is installed. Incase of external databases, it may additionally rely on database hosted servers environment too. 90% of issues resolved in this section. If you are unsure about your environment, try to make a different test environment without the firewall, group policy and other scanning services and verify if the issue recreates.
  1. There are several factors in the environment, which comes into the postgres database connectivity exception.
    1. Firewall / Windows Defender / Antivirus Service / Anti Malware Scanning Interface / Other Scanning Services :
      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. If the issue recreates, try disabling the antivirus and other scanning services before invoking the upgrade and reenable it after the completion of upgrade.
      2. By default, our applications runs in port 65432  & 65433. 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 \ Group policy of File or Port Restriction:
      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. 
    4. Ephemeral Ports :
      1. In some of the environment, ephemeral ports are enabled by default. To clarify this better Ephemeral ports are temporary, short-lived ports assigned by the operating system for the duration of a communication session. For more details refer : Ephemeral Ports
      2. They are typically used for making new postgres connections from application to database. 
      3. Kindly make a firewall rule, such that it allows all inbound connections for all ports from our product (or) allows all inbound connections for all ports in the ephermal ports range.
      4. Since we create this rule for inbound connections from internal processes, this will not be ease to security vulnerability. Apart from this, you can employ intrusion detection systems (IDS), and access control lists (ACLs) without affecting our product operations. 
Info
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>.
    2. JDBC URL Configuration :
      1. If your build version is under 14610, We can increase the timeout limit for a connection by modifying the configuration of JDBC. For versions above 14610, this parameters are already modified to this recommended values by default. Refer to the below instructions on how to configure this.
        1. Navigate to <product_installation_location> \ conf \ database_params.conf .
        2. Make a backup copy of this file and place it a different location.
        3. Find the property "url" and modify the values of below parameters. 
        4. Set
          1. loginTimeout=30
          2. socketTimeout=1200,
          3. connectTimeout=20,
        5. add the below param at the end of URL similar to other params,
          1. sslmode=disable,
        6. For Example, 
Idea
Before Modification : 
  1. url=jdbc\:postgresql\://localhost\:65432/servicedesk?charSet\=UTF-8&loginTimeout\=5&socketTimeout\=1200&connectTimeout\=5
After Modification :
  1. url=jdbc\:postgresql\://localhost\:65432/servicedesk?charSet\=UTF-8&loginTimeout\=30&socketTimeout\=1200&connectTimeout\=20&sslmode\=disable

After this modification, save the file and restart the application server. If the application fails to start, then the configuration change is incorrect. Kindly check and reach our support team for more assistance.

Notes
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.