How to resolve Connectivity issues with Postgresql Database from our product?
Log Traces
- 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
- Nov 15, 2023 1:29:10 PM [SYSERR] [INFO] : Caused by: java.lang.Exception: Exception during getConnection from pool
- Nov 15, 2023 1:29:10 PM [SYSERR] [INFO] : at com.zoho.cp.ConnectionPool._getConnectionDetail(ConnectionPool.java:199)
(OR)
- 16.02.2022 08:33:07 [SYSERR] [INFORMATION] : Caused by: java.net.SocketException: Connection reset
- 16.02.2022 08:33:07 [SYSERR] [INFORMATION] : at java.net.SocketInputStream.read(SocketInputStream.java:210)
- 16.02.2022 08:33:07 [SYSERR] [INFORMATION] : at java.net.SocketInputStream.read(SocketInputStream.java:141)
(OR)
- Feb 5, 2022 1:49:17 PM [SYSERR] [INFO] : org.postgresql.util.PSQLException: Connection attempt timed out.
- Feb 5, 2022 1:49:17 PM [SYSERR] [INFO] : at org.postgresql.Driver$ConnectThread.getResult(Driver.java:429)
- Feb 5, 2022 1:49:17 PM [SYSERR] [INFO] : at org.postgresql.Driver.connect(Driver.java:271)
- Feb 5, 2022 1:49:17 PM [SYSERR] [INFO] : at java.sql.DriverManager.getConnection(DriverManager.java:664)
(OR)
- Caused by: java.net.SocketTimeoutException: Read timed out
- at java.net.SocketInputStream.socketRead0(Native Method)
- at java.net.SocketInputStream.socketRead(SocketInputStream.java:116)
- at java.net.SocketInputStream.read(SocketInputStream.java:171)
- at java.net.SocketInputStream.read(SocketInputStream.java:141)
(OR)
- Sep 17, 2024 3:24:30 PM [com.adventnet.db.adapter.postgres.DefaultPostgresDBInitializer] [INFO] : is DB registered as a service ::: false
- Sep 17, 2024 3:24:30 PM [com.zoho.postgres.migrate.BundledPGMigration] [SEVERE] : nulljava.lang.NullPointerException
- at com.adventnet.db.adapter.postgres.DefaultPostgresDBInitializer.isPgReady(DefaultPostgresDBInitializer.java:1194)
- at com.adventnet.db.adapter.postgres.DefaultPostgresDBInitializer.getServerStatus(DefaultPostgresDBInitializer.java:973)
- at com.adventnet.db.adapter.postgres.DefaultPostgresDBInitializer.isServerStarted(DefaultPostgresDBInitializer.java:913)
Cause :
- 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.
- 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.
- 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.
- There are several factors in the environment, which comes into the postgres database connectivity exception.
- Firewall / Windows Defender / Antivirus Service / Anti Malware Scanning Interface / Other Scanning Services :
- 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.
- 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.
- 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.
- 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>.
- Data Execution Policy \ Group policy of File or Port Restriction:
- This segment is applicable only to Windows. Refer here for more details about this policy.
- 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.
- Network issues :
- This segment is applicable only for the instance, which has it's database hosted in different servers and accessed externally
- 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.
- Ephemeral Ports :
- 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
- They are typically used for making new postgres connections from application to database.
- 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.
- 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.
Client Configuration :
- This block troubleshoots the issue with respect to the version and configuration of Application which acts as the client here.
- Database Driver :
- If your build version is under 13005, Kindly follow this article.
- 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>.
- JDBC URL Configuration :
- 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.
- Navigate to <product_installation_location> \ conf \ database_params.conf .
- Make a backup copy of this file and place it a different location.
- Find the property "url" and modify the values of below parameters.
- Set
- loginTimeout=30
- socketTimeout=1200,
- connectTimeout=20,
- add the below param at the end of URL similar to other params,
- sslmode=disable,
- For Example,
Before Modification :
- url=jdbc\:postgresql\://localhost\:65432/servicedesk?charSet\=UTF-8&loginTimeout\=5&socketTimeout\=1200&connectTimeout\=5
After Modification :
- 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.
- <product_installation_directory> \ logs folder
- <product_installation_directory> \ pgsql \ data \ pg_hba.conf
- <product_installation_directory> \ pgsql \ data \ postgresql.conf
- <product_installation_directory> \ pgsql \ data \ pg_logs folder
- <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 ...Troubleshooting PostgreSQL Upgrade & Startup Issues
When troubleshooting PostgreSQL issues, we are currently identifying the generic trace, but we missed identifying the absolute cause of that issue and following the workaround, provided. Most of those issues have their own workaround in our KBs with ...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, |, ...