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)

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 :

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

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 M365 Manager Plus?
        New to M365 Manager Plus?
          New to RecoveryManager Plus?
          New to RecoveryManager Plus?
            New to Exchange Reporter Plus?
            New to Exchange Reporter Plus?
              New to SharePoint Manager Plus?
              New to SharePoint Manager Plus?
                  See all integrations
                  New to ADManager Plus?
                  See all integrations
                  New to ADManager Plus?

                    New to ADSelfService Plus?

                    Start your free trial
                    Start your free trial

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

                      • How to connect to external postgres(external DB)

                        1. From the postgres installed path navigate to bin folder and open a command prompt 2. Execute the command --> pg_ctl -D "C:\Program Files\PostgreSQL\10\data" start (Postgres server gets started) 3. Connect to DB using the command-->psql -U postgres ...

                      • 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, |, ...

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

                        Related Products